I've been working with an influxql query to retrieve CPU, memory, load, and disk values using a WHERE clause to specify only data from 1 minute ago. The challenge I'm facing is that the query consistently returns the same date as specified in the WHERE clause. What I really need is to fetch the most recent dates, i.e., the latest timestamps when the server sent data to the InfluxDB database.
My main goal is to identify machines that haven't been sending data lately. To achieve this, I want to determine the last timestamp recorded within the past hour using the condition:
time> = now () - 1h
Currently, my query example specifies the following static date range:
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 actually require is a dynamic approach to get the latest timestamps within the one-hour window so that I can compare them against the currently fetched records' timestamps:
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
Your assistance in understanding this issue would be greatly appreciated. Thank you!
This is the SQL code snippet I am using:
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
Here's a sample output from 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