Hi,
I get data from DB using dbxquery. I set the time filter by:
WHERE time BETWEEN DATE_TRUNC('hour',NOW()) - INTERVAL '4 HOURS' AND DATE_TRUNC('hour',NOW()) - INTERVAL '2 HOURS'
I use DATE_TRUNC in order to get data from exact hour (7:00-9:00, insteads of 7:10-9:10 or example)
After that, using Splunk, I make a span = 2h
In the alert, I want to send it every 2 hours.
There is a problem from 4:00 - 6:00 but at 9:30, I don't receive any alert (because there is nothing return from the search)
However, now, at 10:10, when I run the search, it sort the result that I want.
| _time |
id |
count |
| 2022-10-14 04:00 |
123 |
0 |
| 2022-10-14 06:00 |
123 |
0 |
Effectively, there is no data for id "123" in the filter period in SQL query.
Do you have any idea how can I do it more generally, not filter time like what I am doing now in the SQL, to avoid this problem? Or a way to filter time by Splunk, not by SQL
Here is my search:
|dbxquery connection="database" query="
SELECT id as id, time as time, count(*) as count
FROM table
WHERE time BETWEEN DATE_TRUNC('hour',NOW()) - INTERVAL '4 HOURS' AND DATE_TRUNC('hour',NOW()) - INTERVAL '2 HOURS'
GROUP BY id, time"
|lookup lookup.csv id OUTPUT id
|eval list_id = "123,466,233,111"
|eval split_list_id= split(list_id ,",")
|mvexpand split_list_id
|where id=split_list_id
|eval _time=strptime(time,"%Y-%m-%dT%H:%M:%S.%N")
|timechart span=2h count by id
| untable _time id count
| makecontinuous
| where count = 0
|stats max(_time) as date_time by id
|eval date_time=strftime(date_time,"%Y-%m-%dT%H:%M:%S")
