Showing the Difference between 2 sources / queries in Grafana

Question

At the minute I have 2 lists of data from 2 different queries and sources, for example I have:

Source 1

Red
Blue
White
Green
Black

Source 2

Red
Blue
Black

I want to be able to compare the 2 and show the ones that are missing from Source 2 (White and Green) in a table.

I have tried various options within Transform but can't seem to get it to do what I need.

By two different sources, you mean two different data sources in Grafana? — markalex, Jul 13 '23 at 10:28
Does your queries return a single column, or are the any additional columns not shown in your example? — markalex, Jul 13 '23 at 10:35
I can get additional columns if needed but the only thing that would match is the "Hostname" which is the column I need to compare. — user2572639, Jul 13 '23 at 10:44

score 0 · Answer 1 · answered Jul 13 '23 at 09:54

0

Try the following:

SELECT S1.Item
FROM Source1 AS S1
LEFT JOIN Source2 AS S2 ON S1.Item = S2.Item
WHERE S2.Item IS NULL

Or :

SELECT Item
FROM Source1
EXCEPT
SELECT Item
FROM Source2

answered Jul 13 '23 at 09:54

Amira Bedhiafi

Thanks, I'm not sure I can do that in Grafana? – user2572639 Jul 13 '23 at 10:30
This solution will work ONLY if both queries are directed to the same data source, and can be replaced with a single query. – markalex Jul 13 '23 at 10:50

score 0 · Answer 2 · answered Jul 13 '23 at 10:48

In case your actual dataset for second query include something more than a single column:

Use two transformations:

Mode: OUTER,
Field: name of column with the same values (columns have to be named the same in both datasets),

Filter type: EXCLUDE,
Conditions: Match all,
and add a condition for every column present in dataset 2 (except for the "key" column): Is null.

If your actual dataset for second query contains only key fields, you'll need to modify your queries to have additional columns.

For example, it can be done with query

with t1 as (
  --your original query here
)
select val, val unique2 from t1
-- val here is assumed name of your "key" column

Then apply solution described above, with additional Transformation "Organize fields", to hide additional column.

2 Answers2