KQL Search

// Devices without successful AV scan in the last n days
// As of 27.01.2022 only the following platforms are support
// Windows10, Windows10WVD, Windows11, WindowsServer2012R2, WindowsServer2016, WindowsServer2019, WindowsServer2022

//Data connector required for this query - Advanced Hunting license
//Query only works in Advanced Hunting

let Timerange = 14d;
DeviceInfo
| where OnboardingStatus == "Onboarded"
| where isnotempty( OSVersion)
| where Timestamp > ago(Timerange)
| summarize LastSeen = arg_max(Timestamp, *) by DeviceId
| extend LastSuccessfulAVScan = strcat("Not in the last ",format_timespan(Timerange,'d')," days")
| project LastSeen, DeviceId, DeviceName, MachineGroup, OSPlatform, OSVersion, DeviceType, LastSuccessfulAVScan, JoinType
// use rightsemi to return all devices that had a successful AV scan in the last n days
// use leftanti to return all devices that NOT had a successful AV scan in the last n days
| join kind=leftanti (
    DeviceEvents
    | where ActionType == "AntivirusScanCompleted"
    | where Timestamp > ago(Timerange)
    | summarize LastSuccessfulAVScan = max(Timestamp) by DeviceName, DeviceId
    | join kind=innerunique (
        DeviceInfo
        | where isnotempty( OSVersion )
    ) on DeviceId
    | summarize LastSeen = arg_max(Timestamp,*) by DeviceName
    | project LastSeen, DeviceId, DeviceName, MachineGroup, OSPlatform, OSVersion, DeviceType, LastSuccessfulAVScan, JoinType
) on DeviceId
| where OSPlatform in ("Windows10","Windows10WVD","Windows11","WindowsServer2012R2","WindowsServer2016","WindowsServer2019","WindowsServer2022")
| sort by DeviceType, MachineGroup, OSPlatform