Create a Variable from a sql source to be used in a influx time series filter

InfluxDB 2
1

Hi,

I am trying to build a dashboard which has a variable desription as Dashboard variable. This variable is sourced by a postgres DB and has 3 fields. “Description”,“host_name”,“service_description” .

The Dashboard user selects “Description” and should get Data from influx bucket filter by tag host_name and service_description.

Where i am stuck now to get a variable from the sql.from used in the 2nd flux query . ( in this try host_name only )

This is what i came up with ( mostly search and trial and error ) . sry for this …
( $description is alread set from the grafana Dashboard this works )

getInput = (mylist) => {
_idRecord = sql.from(
driverName: “postgres”,
dataSourceName: “postgresql://grafana:password@127.0.0.1?sslmode=disable”,
query: “SELECT host_name FROM interfaces where description=‘${description}’”,
)
|> findRecord(idx: 0, fn: (key) => true)
return _idRecord.host_name
}

input_host_name = getInput(mylist: v.host_name)

But i get an error that “v.host_name” is undefined .

The select returns 1 single host_name = “myhostname” .

In the flux query i would like to use then :slight_smile:

|> filter(fn: (r) => r[“host_name”] == input_host_name)

Thx for any help or hints. Still beginner .

The hole Idea behind is that the Dashboard User should only see those host with services listed in the sql DB and should not see the real host and serivces names . ( only eg parts Services from 1 host ) . The influx is feeded by checkmk with host_name,interface(service_description) . ( Interface Traffic )

Best regards, Dietmar

2

Hello @dietmarG,
(an aside can you please format code blocks next time so I can more easily read your question? it would be much appreciated)
Yes to do input_host_name = getInput(mylist: v.host_name)
You would need to have defined a variable in the UI:

Instead you could use a query to find your host_name and use the findRecord function like you did above to extract it and then you can place it in your function.
Or replace it with a hardcoded value.

Does that help?
Thank you

3

Hi,
Thx for your input. Actually i tried to find a way/sample to extract with findrecord a field from a Table ( not a stream - i think this is different ) from the sample above and use it as variable on other query filters.

For this sample i ended up using 3 Dashboard Variables ( 1 Visible ) and 2 hidden chained from the first. So with this i would be able to use 1 Dashboard pull down and have 2 Variables needed in the different queries .

Still it would be nice to see an example how to extract from sql (no key) with findrecord the correct field.
( have not been able to get it working )

thx, Dietmar