KQL判断datetime是否在任何时间窗口中

Question

我正在寻找日志中特定错误的时间戳，因此我可以识别在其他表中N秒内发生的其他事件。

我能够构建datetime间隔/窗口的表(集)，但我正在努力确定何时从另一个表的datetime发生在集合中的任何间隔内。

// 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

我也试着研究around()函数，但没能想出一个解决方案。

下面是另一个带有示例数据的示例:

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" |

Answer 1

一种可能有效的技术是在时间窗口上进行连接，请参阅相关文章，您可以与其他表进行连接，然后根据您感兴趣的时间间隔进行过滤，请参阅标题为&;一节中的相关示例重写查询以考虑时间窗口">

Answer 2

// 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_iderror_env_timeerror_message表log_record_idlog_env_timelog_messagetbody><<tr>32022 - 05 - 18 t11:53:48.7571282z我的特定的错误:3logs2332022 - 05 - 18 t11:53:52.2075125zlogs2: 33182022 - 05 - 18 t12:05:10.2440591z我的特定的错误:18logs1482022 - 05 - 18 t12:05:06.1936749zlogs1: 4862022 - 05 - 18 t12:11:11.8643195z我的特定的错误:6logs2152022 - 05 - 18 t12:11:08.0750978zlogs2: 1512022-05-18T12:38:15.4453636Z我的具体错误:1k8slogs112022-05-18T12:38:12.473693Z一些其他错误:1112022 - 05 - 18 t12:38:15.4453636z我的特定的错误:1logs1732022 - 05 - 18 t12:38:18.8940209zlogs1: 73