Pass time queried from sql to influxdb

Hey guys,
I have some dates and times in a postgres db that refer to the beginning and end of a drying process but the records of humidity and temperature variations are recorded in an influxdb database. Is there any way to query these dates in postgresdb, select by id and pass the dates of this particular id to the influx time series graph?
look my variables:



my simple table:
image
how can i pass this for my time series graph?

Can anyone give me some guidance if I need to use regex in “query0”, and then how I can send it to the influxdb query?

Hi @edsonjabastosx ,
I used approach with table and data links instead of selecting variables through drop down menu. There are 2 reasons for that:

  • By clicking on specific row (or cell) in table you can set start and end variables which will be called in Influx query (instead of $timeFilter) which will restrict Influx query to specific time. start and end variables are also placed in URL so that dashboard time range changes accordingly (i.e. so that you always see time range that you are querying). The second can’t be done with variables only. Note that query time range and dashboard time range doesn’t have to be the same but with this approach we are ensuring they are the same.
  • Each column in table can set one variable which means that multiple variable can be set by clicking on specific row in table.

 

Steps:

  1. Create 2 dashboard variables start and end with type Constant and don’t assign any value to them.
  2. Create table in which you will show ID, start_time, end_time from Postgres database.
SELECT id, start_time, end_time FROM timestamps
  1. While editing table go to OverridesAdd field overrideFields with name → select id → Add override propertyData links -> Data linksAdd data link and in URL write:
http://<grafana_server>:3000/d/<dashboard_UID>/?var-start=${__data.fields.start_time}ms&var-end=${__data.fields.end_time}ms&from=${__data.fields.start_time}&to=${__data.fields.end_time}

 

Explanation of table data link:

  • Dashboard UID can be found in URL
  • I purposely removed dashboard name after dashboard_UID because that is matter of change (but UID isn’t).
  • ${__data.fields.start_time} and ${__data.fields.end_time} reference values in start_time and end_time columns for that specific table row.
  • var-start=${__data.fields.start_time}ms means that start_time value from table will be assigned to dashboard variable start. Note that I added ms at the end in order to specify that those epoch timestamps are in miliseconds (this is important later for Influx query). I choosed miliseconds because in grafana URL you can specify only epoch time in that unit.
  • &var-end=${__data.fields.end_time}ms means that end_time value from table will be asigned to dashboard variable end. Everything mentioned in previous point applies here too.
  • from=${__data.fields.start_time} means that start_time value from table will be assigned to special variable from which is used in URL.
  • to=${__data.fields.end_time} means that end_time value from table will be assigned to special variable to which is used in URL.
    from and to variables are global grafana variables which allow specify dashboard time range. More about that can be found in Global variables. Note that I didn’t put ms suffix in start_time and end_time while assigning to from and to variables (because grafana work with ).

 

  1. Create time series graph/panel in which you will query data in Influx.
SELECT mean("usage_user") FROM "cpu" WHERE ("cpu"::tag = 'cpu-total') AND time >= $start AND time <= $end GROUP BY time($__interval) fill(none)

 

In this Influx query I am using dashboard variables $start and $end in order to specify query time range. More about Influx query for specific time range can be found Specify a time range with epoch timestamps.

So, every time I click on different ID in table, start and end variables are set based on values in that specific row. At the same time, both dashboard and query time range changes which allows you to see only period that you are querying. Note that until you click on specific row in table start and end variables won’t be set (you can check that in URL) and therefor time series graph will show No data with error sign. Maybe you can set some default values for those variables in dashboard settings (see step 1) for example now() - 3h for start and now() for end variable (didn’t test that).

 

Result:
When clicked on ID=1:

When clicked on ID=4:

 

Best regards,
ldrascic

1 Like