Flux: Find values not existing in database, but in filter

InfluxDB 2
1

Hi

i can’t find a way to get all values in my filter which do not match with the table:

myMeasurement:

Schema: Datacenter, value, myDevice

row 1: X,1,Host1
row 2: X,1,Host2
row 3: X,1,Host4

data = from(bucket: “test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “myMeasurement”)
|> filter(fn: (r) => r[“myDevice”] =~ /^(Host1|Host2|Host3)$/)

My goal is ho have a table with the following output in this case:

ResultRow1: Host3

any idea how to solve this?

2

@sil I’m sorry Im not quite understanding.
You can’t filter for values that do not match with the table.
If you want to filter for everything, don’t include a filter.

3

Is there no way, to find if a value i have as a Variable in grafana (here used in a filter) is in my database or not.

So to make it clear, i have a grafana variable and want to know if i have this value in field myDevice in my measurement. Hope this makes it more clear

4

If I understood correctly, you want to specify some criteria in your query and get only rows that do not meet your criteria.

I would play with join for that purpose:

import "array"
import "join"
// Replace this with original query
rows = [{Datacenter : "x", value : 1, myDevice : "Host1"},
{Datacenter : "x", value : 1, myDevice : "Host2"},
{Datacenter : "x", value : 1, myDevice : "Host4"}]
left  = array.from(rows : rows)

// Dummy query to specify all hosts that you want to check with filter
right  = array.from(rows : [{myDevice : "Host1"},
{myDevice : "Host2"},
{myDevice : "Host3"},
{myDevice : "Host4"}])
|> filter(fn: (r) => r.myDevice =~ /^(Host1|Host2|Host3)$/)

// Right join and filter to find missing hosts
join.right(
    left: left,
    right: right,
    on: (l, r) => l.myDevice == r.myDevice,
    as: (l, r) => ({absent: l.myDevice, result: r.myDevice})
)
|> filter(fn: (r) => not exists r.absent)
|> keep(columns:["result"])
5

that looks interessting - but is there a way to define the dummy query array (right) from variables out of grafana?

6

You can replace filter in my example by variable that comes from Grafana.

Is full list of hosts fixed? May be you can get it by querying particular tag values in InfluxDB? If yes, replace dummy query in my example to such type of query.

7

thanks for your answer.

i know that i can replace this part with a grafana variable:

|> filter(fn: (r) => r.myDevice =~ /^(Host1|Host2|Host3)$/)

but i do not know how to solve this:

right  = array.from(rows : [{myDevice : "Host1"},
{myDevice : "Host2"},
{myDevice : "Host3"},
{myDevice : "Host4"}])

as this list is not fixed and depends on different parameters. so what i want to do is to create this array (right) also from a variable out of grafana. is this possible? and i can’t use another query to get tags from influx, because i do not know if every value is in the database (in our example, Host3 is not in the database).

thanks a lot for your support

8

For me it doesn’t make sense to query hosts that never existed in the database. I thought that Host3 is in the database, but doesn’t have metrics in the chosen timeframe.

What is the expected use case? User enters list of hosts manually, and you want to display which hosts from his input are not valid and do not exist in the database as of now?

9

the usecase is the following:

i get hosts from our CMDB as variables in grafana. and i want to build a dashboard, where the users can see, which hosts in de CMDB have never written logs to the influxdb.

10

Ok. So if you can arrange Grafana variable dummy to have its value in the following way:

{myDevice : "Host1"}\,
{myDevice : "Host2"}\,
{myDevice : "Host3"}\,
{myDevice : "Host4"}

You can easily use it in Flux query:
right = array.from(rows : [${dummy}])

dummy
dummy1281×741 50.9 KB

11

perfect, thank you. this solved my problem.

i now get a variable from my CMDB in Grafana to use for a array. then your code works perfectly.
thanks