KQL Determine if datetime is in any time windows

user_6525639 0
Viewed 41

I'm looking for a timestamp of specific error in a log and so I can identify which other events occurred within N seconds of it in other tables.

I'm able to construct a table (set) of datetime intervals/windows, but I'm struggling to determine when a datetime from another table occurs within any of the intervals in the set.

// Create a table of time windows (interval) +/- 5 seconds of target error
let intervals = k8slogs
| where Message contains "my specific error"
| project begin=datetime_add('second', -5, env_time), end=datetime_add('second', 5, env_time)

// Show all messages within 5 seconds of "my specific error"
k8slogs
| union logs1
| union logs2
| where env_time // is in any 'window' from above query
| project env_time, Message
| order by env_time asc

I also tried looking into the around() function, but wasn't able to come up with a solution.

Here is another example below with sample data:

k8slogs
| env_time | message                 |
|:-------- |:----------------------- | 
| 15       | "my specific exception" |
| 45       | "my specific exception" |

logs1
| env_time | message                 |
|:-------- |:----------------------- | 
| 11       | "another error"         |
| 35       | "hello world"           |

intervals
| begin | end    |
|:----- |:------:| 
| 10    | 20     | 
| 40    | 50     | 

desired query result
| env_time | message                 |
|:-------- |:----------------------- | 
| 11       | "another error"         |
| 15       | "my specific exception" |
| 45       | "my specific exception" |
kql
2 Answers

One technique that might work is doing joins on the time window, see the relevant article, you can do the join with the other tables and then filter based on the time interval that you are interested in, see the relevant example in the section titled "Rewrite the query to account for the time window"

user_101679140 
// Data sample generation. Not part of the solution
let t1 = materialize(range record_id from 1 to  20 step 1 | extend env_time = ago(1h*rand()), Message = strcat(case(rand()<0.5, "my specific error:", "some other error:"), tostring(record_id)));
let t2 = materialize(range record_id from 1 to 100 step 1 | extend env_time = ago(1h*rand()), Message = strcat("logs1 : ", tostring(record_id)));
let t3 = materialize(range record_id from 1 to 100 step 1 | extend env_time = ago(1h*rand()), Message = strcat("logs2 : ", tostring(record_id)));
let k8slogs = view(){t1};
let logs1 = view(){t2};
let logs2 = view(){t3};
// Solution starts here
let time_window = 5s;
k8slogs
| where Message contains "my specific error"
| mv-expand i = range(-1,1) to typeof(int)
| extend env_time_bin = bin(env_time + i * time_window, time_window)
| project-away i
| project-rename error_env_time = env_time,  error_message = Message, error_record_id = record_id
| join kind=inner 
( union withsource=table (k8slogs | where not(Message contains "my specific error") | as k8slogs), logs1, logs2
| extend env_time_bin = bin(env_time, time_window) 
| project-rename log_env_time = env_time, log_message = Message, log_record_id = record_id
) on env_time_bin
| where abs(log_env_time - error_env_time) <= time_window
| project-away env_time_bin*
| order by log_env_time asc
error_record_id error_env_time error_message table log_record_id log_env_time log_message
3 2022-05-18T11:53:48.7571282Z my specific error:3 logs2 33 2022-05-18T11:53:52.2075125Z logs2 : 33
18 2022-05-18T12:05:10.2440591Z my specific error:18 logs1 48 2022-05-18T12:05:06.1936749Z logs1 : 48
6 2022-05-18T12:11:11.8643195Z my specific error:6 logs2 15 2022-05-18T12:11:08.0750978Z logs2 : 15
1 2022-05-18T12:38:15.4453636Z my specific error:1 k8slogs 11 2022-05-18T12:38:12.473693Z some other error:11
1 2022-05-18T12:38:15.4453636Z my specific error:1 logs1 73 2022-05-18T12:38:18.8940209Z logs1 : 73

Fiddle

user_63364790
Related