KQL Search

//Calculate the time Office 365 took to detect malware after the file was uploaded

//Data connector required for this query - Office 365

//First find the malware detection event
OfficeActivity
| where TimeGenerated > ago(60d)
| where Operation == "FileMalwareDetected"
| project
    DetectionTime=TimeGenerated,
    OfficeWorkload,
    ['File Name']=SourceFileName,
    ['File Location']=OfficeObjectId
//Then join back to the upload event on the same file location
| join kind=inner 
    (
    OfficeActivity
    | where TimeGenerated > ago (60d)
    | where Operation in ("FileUploaded", "FileSyncUploadedFull")
    | project
        UploadTime=TimeGenerated,
        OfficeWorkload,
        ['File Name']=SourceFileName,
        ['File Location']=OfficeObjectId,
        ['Relative File URL']=SourceRelativeUrl
    | summarize min(UploadTime) by ['File Location'], UploadTime
    )
    on ['File Location']
//Calculate the time difference between upload and malware detection
| project
    ['File Name'],
    UploadTime,
    DetectionTime,
    ['Time Difference in Minutes']=datetime_diff("minute", DetectionTime, UploadTime),
    ['File Location']