Device Finddevicewithoutcurrentavscan
KQL detection rule by reprise99 · MIT
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
// 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