如何按Timestamp排序并在InitiatingProcessParentCreationTime值变化时生成changeid列

要实现按Timestamp排序并生成changeid列（当InitiatingProcessParentCreationTime值变化时更新），可以使用Kusto的窗口函数和累加函数完成，具体查询如下：

let data = datatable (Timestamp: datetime, InitiatingProcessParentCreationTime: datetime)
[
    datetime(2024-07-24 17:33:52.862), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:52.779), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:51.900), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:51.771), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:51.338), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:51.192), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:51.145), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:51.057), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:50.791), datetime(2024-07-17 15:22:47.997),
    datetime(2024-07-24 17:33:50.339), datetime(2024-07-17 16:49:53.432),
    datetime(2024-07-24 17:33:50.297), datetime(2024-07-17 16:49:53.432)
];
data
| sort by Timestamp desc  // 按时间戳降序排序，需要升序可去掉desc
| extend prev_parent_time = prev(InitiatingProcessParentCreationTime)
| extend is_change = case(
    isnull(prev_parent_time), 1,  // 第一行默认算一次变化
    prev_parent_time != InitiatingProcessParentCreationTime, 1,
    0
)
| extend changeid = row_cumsum(is_change)  // 累加变化标记生成changeid
| project Timestamp, InitiatingProcessParentCreationTime, changeid  // 保留需要的列

关键步骤说明 #

• 排序：通过sort by Timestamp确保数据按时间顺序排列
• 对比上一行值：prev()函数获取前一行的父进程创建时间，用于判断当前行是否发生变化
• 生成变化标记：用case()函数标记变化行，第一行和值发生变化的行标记为1，其余为0
• 累加生成changeid：row_cumsum()对变化标记累加，每次变化时changeid自动递增

内容的提问来源于stack exchange，提问作者HarriS