InfluxDB query to get distinct values from a table

InfluxDB 1 InfluxQL
1

Hi Guys,

I am working on visualizing InfluxDB data in Grafana. I am having a table that shows the status of the devices connected to my system. The table is updated values every 1 minute (ie my software checks for connection status every minute for all the 500 devices and inserts them in the table those devices that are offline). Now I want to display the result. I display results for the last 1 minute using the query

SELECT * FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s GROUP BY (*)

My table looks like this

Timestamp | Connection    | IPLike      | EdgeDeviceID | Status
================================================================
12345679  | 192.168.7.14  | 192.168.7.  | ED1          | Offline
12345678  | 192.168.14.15 | 192.168.14. | ED2          | Offline
12345667  | 192.168.14.15 | 192.168.14. | ED2          | Offline

Sometimes, the query outputs the same device twice.

Now I figured out I can restrict this by using the distinct command.

SELECT distinct("Connection") FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s

This solves the repetition problem. But I don’t see the other columns. Can somebody help me with this, please?

Thanks in advance.

Cheers,
SD

2

Hello @sahebdatta,
Apologies in advanced as my InfluxQL is rusty since I mostly use Flux now,
but have you tried selecting for the other fields as well?

SELECT distinct("Connection"), "IPLike", "EdgeDeviceID", "Status" FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s

Or whatever your fields are?

3

Hi @Anaisdg,

Thanks for the response. I also assumed that this way it should work but unfortunately this returns no results. It shows an error that says

InfluxDB Error: aggregate function distinct() cannot be combined with other functions or fields

My query looks like this

SELECT distinct("Connection"), "EdgeDeviceID" FROM "3_weeks"."STATUS" WHERE ("Connection" =~ /^$IPLike^*/ AND "Status" = 'Offline') AND time>now()-60s

4

Darn, then I’m not sure how you would do this with InfluxQL in 1.x other than to perform a join with kapacitor. What version of Influx are you using? We could do this with Flux. Otherwise I would suggest making an issue.

5

InfluxDB version used: 1.8.x

6

If you enable Flux,
Your query would look something like:

from(bucket: "my_db")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "my_meas")
  |> filter(fn: (r) => r["_field"] == "Connection" or r["_field"] == "IPLike" or r["_field"] == "EdgeDeviceID" or r["_field"] == "Status" )
  |> schema.fieldsAsCols()
  |> distinct(column: "Connection")

And the result will be a table with all of your fields as columns and it will be filtered for the distinct values of the Connection column and the values for the other fields at that those distinct timestamps.