KQL - Correlating data within the same table column and applying a threshold

Hi, I was wondering if anyone could help. Trying to correlate some data within the same table and apply conditions to it. So looking at the scenario of users sending an email and then deletin...

analytics

KQL

kusto

Log Data

siem

Clive_Watson

Jul 13, 2022

BcyberS

This is a method (basic framework) to do this, which you can adapt.

OfficeActivity 
| where TimeGenerated > ago(1h)
| where Operation == 'Send'
| summarize arg_max(TimeGenerated,*) by UserId, 1stTime = TimeGenerated
| join kind= inner 
(
    OfficeActivity
    | where TimeGenerated > ago(1h)
    | where Operation == 'SoftDelete'
    | summarize arg_max(TimeGenerated,*) by UserId, 2ndTime = TimeGenerated
) on UserId
// within 5mins and only SoftDelete after Send
| where datetime_diff('minute',2ndTime,1stTime) < 5 and 2ndTime > 1stTime
| project UserId, 1stTime, 2ndTime,  timeDiffInMins=datetime_diff('minute',2ndTime,1stTime), Operation, Operation1

You can see by UserId who did a SEND then a SOFTDELETE within 5mins. You'll have to add the "path" part or a method to make sure the "SoftDelete" relates to the same item as the "Send"

Clive_Watson

Bronze Contributor

Jul 13, 2022

BcyberS

This is a method (basic framework) to do this, which you can adapt.

OfficeActivity 
| where TimeGenerated > ago(1h)
| where Operation == 'Send'
| summarize arg_max(TimeGenerated,*) by UserId, 1stTime = TimeGenerated
| join kind= inner 
(
    OfficeActivity
    | where TimeGenerated > ago(1h)
    | where Operation == 'SoftDelete'
    | summarize arg_max(TimeGenerated,*) by UserId, 2ndTime = TimeGenerated
) on UserId
// within 5mins and only SoftDelete after Send
| where datetime_diff('minute',2ndTime,1stTime) < 5 and 2ndTime > 1stTime
| project UserId, 1stTime, 2ndTime,  timeDiffInMins=datetime_diff('minute',2ndTime,1stTime), Operation, Operation1

You can see by UserId who did a SEND then a SOFTDELETE within 5mins. You'll have to add the "path" part or a method to make sure the "SoftDelete" relates to the same item as the "Send"

BcyberS
Brass Contributor
Jul 13, 2022
Thank you Clive_Watson, this is really helpful. I can now parse the path and additional elements to add further context.

Forum Discussion

KQL - Correlating data within the same table column and applying a threshold

Resources