Using multiple sqlite3 databases, replacing values in the panel

Grafana Configuration
1

Hi, noob here, please humor me…

I’m trying to build some dashboards, using information from the pi-hole sqlite3 databases.
I’ve already managed to get a panel up and running, extracting data from the query database, using the following sql query:

SELECT strftime('%d/%m/%Y %H:%M:%S', datetime(timestamp, 'unixepoch', 'localtime')), domain, additional_info, client FROM queries WHERE status = '9' GROUP BY queries.domain ORDER BY MAX(timestamp) DESC LIMIT 10;

This results in a panel looking like this:

image
image898×273 29.3 KB

As you can see, the client is displayed as an IP address.

the client name is in another database (the comment field)

image
image1153×673 81.4 KB

I’ve already added the required ‘Data Sources’, selected ‘Mixed’ and added a second query to retrieve the ip and the comment field (SELECT ip, comment from client;)

From what I’ve been reading, it looks like I need to use ‘Transformations’ to replace the ip address in the panel (see screenshot above) with the associated name (= comment from the second query).

This is where I’m stuck, I’ve been reading and trying a lot of suggestions, however, I failed, thus the question: How do I achieve this?

Thank you for your time and effort.

2

Did you try an “Outer Join”? I think that’s what you’re after in principle.

3

Tried that, but haven’t found a way to make the replacement in the panel.

As I’ve said, I’m a noob, basically need a step by step to make the replacement, after creating the queries. Searched for it, but didn’t find anything, coming even close to achieving this.

4

In the panel editor go to the “Transformations” tab and click on “Outer join”. Then select the name of the field to join on (must be the same field name in the two queries). If that doesn’t work, send over a screenshot of where you’ve got to.

5

that is a problem

database 1:

image
image1265×562 59.9 KB

database 2:

image
image1512×396 46.5 KB

‘client’ in database 1 matches ‘ip’ in database 2 (both fields contain IP address)

value of ‘client’ (database 1) needs to be replaced with value of 'comment (database 2).

I can modify the second query

SELECT ip, comment from client;

into

SELECT ip AS client, comment FROM  client;

but than the panel changes into

just showing all the results of the second query (with data) and the fields of the first query (without data)

sending the screenshot in the next post, new users can only embed 2 screenshots in a post.

6

edit I was wrong, the outer join actually works.

image
image1059×234 21.3 KB

it shows all the fields from the second query (SELECT ip AS client, comment FROM client;) and some of the results of the first query.

I would than assume, by adding another transformation, I would be able to filter the results:

image
image759×175 9.93 KB

unfortunately, that doesn’t work, no changes in the result.

any ideas (also tried ‘include’ and ‘is not null’, same result)?

7

Glad to hear it worked out. I was actually just revisiting a similar issue, and realized that the “Merge” transform may be better than the “Outer Join” for this - though it looks like the latter did the job for you.

Regarding the filtering, given that you’re trying to filter on a string, you could try either of the following:

  • Exclude rows where domain response is equal to a blank string (rather than null?) - just a stab in the dark
  • Include only rows where domain response matches the regex filter .+ (i.e. it’s a string with at least one character)
1 Like
8

It doesn’t work out for me
query 1 shows the correct results, there is always a domain.
query 2 shows ip and client name

the join now shows all the results for query 2, only the rows where the ip is also in the query 1 results, contain the full result. I want to filter out the rows that don’t have ip information in both queries, can’t use the domain, since that is no such field in query 2

the merge transform also doesn’t work in this case, there are no configurable options for merge, and it doesn’t produce the desired result.

So far, after a lot of testing, this doesn’t appear to be possible with outer join. still requesting help, if any…

9

Ok, based on my initial reading of your second-to-last post I thought that the outer join was actually producing the result you were after, and it was just the filtering that you wanted to get working - but it sounds like the join itself isn’t actually producing the right result set?

I guess that’s the first thing to get sorted before looking at potential filtering. Unfortunately the Outer Join transform doesn’t actually do an outer join at present. A couple of related GitHub issues:

As I mentioned, I personally did have better luck with the “Merge” transformation, but it’s pretty difficult to troubleshoot when it doesn’t work. That functionality is quite new and not very polished.

Sorry I can’t be any more help. I suppose the ideal solution would be to put the two SQL tables in the same database, so you can just do the join in SQL?

1 Like