How to filter in query?

  • What Grafana version and what operating system are you using?
    —> Grafana Cloud (free tier) with AWS Timestream as datasource.

  • What are you trying to achieve?
    —> Filter data from the time series database before it gets graphed.

  • How are you trying to achieve it?
    ----> I want to make the data that gets selected conditional on the value of a field.

  • What happened?
    —> I can’t make the query work

  • Can you copy/paste the configuration(s) that you are having problems with?

I currently have this query working:
SELECT * FROM “DBNAME”.“TABLENAME” WHERE measure_name = ‘temp’

It doesn’t filter on device ID. (I want to graph per device, but all logs are in the same timesteam db).

The fields in the timestream database are:

time - timestamp
location - VARCHAR with description of location
measure_name - “temp”
measure_value::double - value of the measured temperature value (e.g. 24.1 or 19.6)
measure_name - “deviceID”
measure_value::double - the ID of the device uploading (eg. 33.0 or 44.2 etc).

I would like to have a query that only selects where the value of the row with deviceID is a certain number. Something like this:

SELECT * FROM “DBNAME”.“TABLENAME” WHERE measure_name = ‘temp’ AND WHERE measure_value::double(of deviceID) = 33.0.

But I can’t get this to work. Either I’m doing something wrong with storing the time series in the database, or I’m doing something wrong filtering it.

Any help?

Hi,

Have you tried writing a query and then querying the database directly? It sounds like it might be worth trying to achieve that first, then you can troubleshoot the Grafana layer…

Hello @djvanhelmond!
Were you able to resolve this issue?
I am also stuck at something similar. I want to filter data before it gets graphed but I am not sure how to do it.

I would really appreciate if you could help.

Hi @bieintern,

You need to store the device id (or the value that you want to sort on) as a dimension in the record. It becomes a new column in the TS database.

https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/timestream-write.html#TimestreamWrite.Client.write_records