Influx-DB Uniq Query

1
votes

I am trying to do a query against the INFLUX-DB to get unique values.Below is the query I use,

select host AS Host,(value/100) AS Load from metrics  where time > now() - 1h and command='Check_load_current' and value>4000;

The output for the query is,

enter image description here

What I actually want is the unique "Host" values. For example I want "host-1" as a output repeated only once(latest value) eventhough the load values are different.How can I achieve this? Any help would be much helpful.

2
influxdb
Have a look at this question: stackoverflow.com/questions/10452940/…. They are doing something similar. - sanastasiadis

2 Answers

1
votes

Q: I want the latest values from each unique "Host", how do I achieve it?

Given the following database:

time                host   value
----                ----   -----
1529508443000000000 host01 42.72
1529508609000000000 host05 53.94
1529508856000000000 host01 40.37
1529508913000000000 host02 41.02
1529508937000000000 host01 44.49

A: Consider breaking the problem down.

First you can group the "tag values" into their individual buckets using the "Groupby" operation.

Select * from number group by "host"

name: number
tags: host=host01
time                value
----                -----
1529508443000000000 42.72
1529508856000000000 40.37
1529508937000000000 44.49

name: number
tags: host=host02
time                value
----                -----
1529508913000000000 41.02

name: number
tags: host=host05
time                value
----                -----
1529508609000000000 53.94

Next, you will want to order the data in each bucket to be in descending order and then tell influxdb to only return the top 1 row of each bucket.

Hence add the "Order by DESC" and the "limit 1" filter to the first query and it should yield you the desire result. 

> select * from number group by "host" order by desc limit 1;
name: number
tags: host=host05
time                value
----                -----
1529508609000000000 53.94

name: number
tags: host=host02
time                value
----                -----
1529508913000000000 41.02

name: number
tags: host=host01
time                value
----                -----
1529508937000000000 44.49

Reference:

https://docs.influxdata.com/influxdb/v1.5/query_language/data_exploration/#the-group-by-clause

https://docs.influxdata.com/influxdb/v1.5/query_language/data_exploration/#order-by-time-desc

https://docs.influxdata.com/influxdb/v1.5/query_language/data_exploration/#the-limit-and-slimit-clauses

0
votes

If you want to get only the latest value for each unique host tag do the following:

SELECT host AS Host, last(value)/100 AS Load
FROM metrics
GROUP BY host