Forum Discussion

am1357's avatar
am1357
Brass Contributor
Apr 02, 2024

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 Health > Microsoft Defender Antivirus Health` as well as the device health page only provide the result of the last antivirus scan. If a device ran a full scan successfully in the past but the most recent full scan was cancelled the report shows that the full scan failed.

 

Here's an example of what I mean: The device health status shows "Full scan failed" with a failed scan on March 28, 2024 at 3:35:57PM.

 

 

When querying the device via Advanced Hunting (see query below) I receive the information that a full scan successfully ran on March 28, 2024 at 3:35:36PM.

 

 

 

Here's the query I am currently using and I already played around with it a little bit (distinct, summarize). I could export it to Excel and then remove duplicate entries but was hoping that can be done with KQL:

 

DeviceEvents
| where ActionType has_any ("AntivirusScanCompleted", "AntivirusScanCancelled")
| extend AdditionalFields = parse_json(AdditionalFields)
| extend ScanType = AdditionalFields.["ScanTypeIndex"]
| where ScanType == "Full"
| project Timestamp, DeviceName, ActionType

 

This is the result I would like the query to return. If any device has had an entry for `ActionType == AntivirusScanCompleted` and `ScanType == Full` then all rows for that device should be removed.

 

 

 

automation
microsoft defender for endpoint
Search
  • cyb3rmik3's avatar
    cyb3rmik3
    Iron Contributor
    Apr 29, 2024

    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

    • am1357's avatar
      am1357
      Brass Contributor
      May 03, 2024

      cyb3rmik3 

       

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

       

Resources