0

I have a query in influxql to get cpu, memory, load and disk values ​​with a WHERE clause to indicate that it only brings the data from 1 minute ago. The query works but it always brings the same date, that is, the exact date of the WHERE. I need you to bring me the latest dates, that is, the last time the server sent data to the influexdb database.

What I want is to determine if there are machines that are not sending data, that is, I need the last date that the condiict has:

 time> = now () - 1h

Now for example I put the date of the WHERE, so:

02/02/2019, 19:33:35
02/02/2019, 19:33:35
02/02/2019, 19:33:35
02/02/2019, 19:33:35
02/02/2019, 19:33:35

What I need is the last date, because within that hour (1h) there are sure records with a longer date (time> =), I mean there must be records with date:

02/02/2019, 19:33:35
02/02/2019, 19:35:12
02/02/2019, 19:43:30
02/02/2019, 19:40:25
02/02/2019, 19:36:32

I appreciate the help of someone who knows what is happening. Thank you!

This is the SQL:

SELECT 
  LAST(cpu_used) AS cpu, 
  LAST(mem_used) AS mem, 
  LAST(load) AS load, 
  LAST(disk_await) AS disk_await 
FROM custom  
WHERE time >= now() - 1m 
GROUP BY hostname

This is a complete exit of the query:

name: custom tags: hostname=linux7 time cpu mem load disk ---- --- --- ---- ---- 2019-02-02T18:46:00.42366206Z 1 43 0 0

name: custom tags: hostname=linux24 time cpu mem load disk ---- --- --- ---- ---- 2019-02-02T18:46:00.42366206Z 11 34 0 0

name: custom tags: hostname=linux4 time cpu mem load disk ---- --- --- ---- ---- 2019-02-02T18:46:00.42366206Z 11 42 0 0

name: custom tags: hostname=linux3 time cpu mem load disk ---- --- --- ---- ---- 2019-02-02T18:46:00.42366206Z 73 32 1 0

name: custom tags: hostname=linux20 time cpu mem load disk ---- --- --- ---- ---- 2019-02-02T18:46:00.42366206Z 1 41 0 0

name: custom tags: hostname=linux1 time cpu mem load disk ---- --- --- ---- ---- 2019-02-02T18:46:00.42366206Z 36 55 0 0

Avellino
  • 61
  • 7

1 Answers1

0

Don't use WHERE condition, but use only LAST() InfluxDB selector.

Jan Garaj
  • 25,598
  • 3
  • 38
  • 59
  • Hello, thanks for answering. If I do not use WHERE, it brings records with very old dates, for example: name: custom tags: hostname = linux3 time cpu mem load disk ---- --- --- ---- ---- 1970-01-01T00: 00: 00Z 36 55 0 0 – Avellino Feb 03 '19 at 13:29
  • I need the latest records, that is, the most recent ones. – Avellino Feb 03 '19 at 13:29
  • That is because you are grouping only by `hostname`. For instance 3 records of different time are grouped as one item then how would `influx` knows which `datetime` of the 3 records to use? To group them the `beginning of epoch time` is chosen. – Samuel Toh Feb 06 '19 at 01:13