Forum Discussion
Help with KQL / Advanced Hunting - Antivirus Scan
Hi, Trying to come up with a solution to find all devices via Advanced Hunting where a full scan was never successful. The report that can be downloaded via `Defender XDR > Reports > Device Heal...
am1357 nice exercise you got there.
I can't see a way around this without getting dirty with join. Can you check if the following query helps? It basically identifies cancelled or failed Full AV scans and removes any Full completed scans based on DeviceName.
let incompleteAVScan = DeviceEvents
| where ActionType has_any ("AntivirusScanFailed", "AntivirusScanCancelled")
| extend AdditionalFields = parse_json(AdditionalFields)
| extend ScanType = AdditionalFields.["ScanTypeIndex"]
| where ScanType == "Full"
| project Timestamp, DeviceName, ActionType;
incompleteAVScan
| join kind = leftanti (DeviceEvents
| where ActionType has "AntivirusScanCompleted"
| extend AdditionalFields = parse_json(AdditionalFields)
| extend ScanType = AdditionalFields.["ScanTypeIndex"]
| where ScanType == "Full"
| project Timestamp, DeviceName, ActionType) on DeviceName
If I have answered your question, please mark your post as Solved
If you like my response, please consider giving it a like
Thanks for the query. Always great to see how other people solve these challenges.
I was able to find a different way using make_set
DeviceEvents | where ActionType has_any ("AntivirusScanCompleted", "AntivirusScanCancelled") | extend AdditionalFields = parse_json(AdditionalFields) | extend ScanType = AdditionalFields.["ScanTypeIndex"] | where ScanType == "Full" | summarize make_set(ActionType) by DeviceId, DeviceName | where set_ActionType !has ("AntivirusScanCompleted")
