1

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.

WindyFields
  • 2,697
  • 1
  • 18
  • 21
Pradeep s
  • 51
  • 1
  • 4
  • Have a look at this question: https://stackoverflow.com/questions/10452940/sql-select-newest-records-that-have-distinct-name-column. They are doing something similar. – sanastasiadis Jun 20 '18 at 17:00

2 Answers2

1

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

Samuel Toh
  • 18,006
  • 3
  • 24
  • 39
0

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
WindyFields
  • 2,697
  • 1
  • 18
  • 21
  • Thanks for your response. I tried using "group by" but its shows all the values of host-1 and host-2 in groups. – Pradeep s Jun 21 '18 at 14:40
  • Usually, when you use Time Series Database the data you get is intended to be processed by another program (e.g visualization tool), not by human. You should not care you the output look, since it gives you what you've asked. However, if you want to execute the query from CLI and get output in another format, you can specify the output like this: `influxdb -execute "SELECT ..." -ouput csv`, also you can specify json. The output you get will not be grouped, but returned as one list. – WindyFields Jun 22 '18 at 09:26
  • I used " **select host AS Host,(value/100) AS Load from metrics where time > now() - 1h and command='Check_load_current' and value>4000 group by host order by time desc limit 1;** " which almost solved my purpose. – Pradeep s Jun 22 '18 at 18:19