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

[BcyberS]

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 deleting that email within 5 minutes from their Sent Items. 

This query is looking at the OfficeActivity (Exchange) table of the Office 365 Data connector.

Within this there is the Operation column which has two values which detect the scenario described: "Send" where the User sends the email from their inbox "Soft Delete" where the user deletes the email from a specified 'Path' - I have parsed out the "\Sent Items" path for this to focus on this path only.

Because both outputs "Send" and "Soft Delete" come under the Operation column I need to 1) separate these and 2) try and query the data so it looks for when an email has been sent and then 'soft deleted' from the \Sent Items path within a timeframe (5 mins for example).

Can anyone point me in the right direction?

Appreciate any help in advance. Thanks

[#2 · Accepted solution]

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

[#3]

Thank you Clive_Watson, this is really helpful. I can now parse the path and additional elements to add further context.