Kusto Query Language (KQL) queries for Advanced Hunting (Microsoft Defender for Endpoint)
Here is the KQL query list to check the Defender client details with specific areas.
Virus Definition status
DeviceTvmSecureConfigurationAssessment
| where Timestamp > ago(30d)
| where ConfigurationId == ‘scid-2011′ and Context !='[]’
| extend DefUpdate = todatetime(parse_json(Context)[0][2])
| extend DefAge = datetime_diff(‘day’,now(),DefUpdate)
| extend DefageGroup=case(DefAge <7,’0 to 7 Days’, DefAge <14,’7 to 14 Days’, DefAge <30,’15 to 30 Days’,’More Than 30 Days’)
| summarize dcount(DeviceId) by DefageGroup
DeviceTvmSecureConfigurationAssessment
| where Timestamp > ago(30d)
| where ConfigurationId == ‘scid-2011′ and Context !='[]’
| extend SigUpdate = todatetime(parse_json(Context)[0][2])
| extend SigAge = datetime_diff(‘day’,now(),SigUpdate)
| project Timestamp, DeviceName, SigAge, SigUpdate
DeviceTvmSecureConfigurationAssessment
| where Timestamp > ago(30d)
| where ConfigurationId == ‘scid-2011′ and Context !='[]’
| extend SigUpdate = todatetime(parse_json(Context)[0][2])
| extend SigAge = datetime_diff(‘day’,now(),SigUpdate)
| where SigAge > 7
| project Timestamp, DeviceName, SigAge, SigUpdate
DeviceTvmSecureConfigurationAssessment
| where Timestamp > ago(30d)
| where ConfigurationId == ‘scid-2011′ and Context !='[]’
| extend SigUpdate = todatetime(parse_json(Context)[0][2])
| extend SigAge = datetime_diff(‘day’,now(),SigUpdate)
| where SigAge > 7
| where DeviceName contains “lht00001”
| project Timestamp, DeviceName, SigAge, SigUpdate
Product AV Version Details
DeviceTvmSecureConfigurationAssessment
| where ConfigurationId == “scid-2011”
| where isnotempty(Context) and Context != “[]”
| extend avdata = parse_json(Context)
| extend AVProductVersion = tostring(avdata[0][3])
| where isnotempty(AVProductVersion)
| summarize DeviceCount = dcount(DeviceId) by AVProductVersion
| order by DeviceCount desc
DeviceTvmSecureConfigurationAssessment
| where ConfigurationId == “scid-2011”
| where isnotempty(Context) and Context != “[]”
| extend avdata = parse_json(Context)
| extend AVProductVersion = tostring(avdata[0][3])
| where isnotempty(AVProductVersion)
| summarize arg_max(Timestamp, *) by DeviceId
| project DeviceName, DeviceId, AVProductVersion, Timestamp
| order by DeviceName asc
DeviceTvmSecureConfigurationAssessment
| where ConfigurationId == “scid-2011”
| where isnotempty(Context) and Context != “[]”
| extend avdata = parse_json(Context)
| extend AVProductVersion = tostring(avdata[0][3])
| where isnotempty(AVProductVersion)
| summarize arg_max(Timestamp, *) by DeviceId
| where DeviceName contains “lht00001”
| project DeviceName, DeviceId, AVProductVersion, Timestamp
| order by DeviceName asc
DeviceTvmSecureConfigurationAssessment
| where ConfigurationId == “scid-2011”
| where isnotempty(Context) and Context != “[]”
| extend avdata = parse_json(Context)
| extend AVProductVersion = tostring(avdata[0][3])
| where isnotempty(AVProductVersion)
| summarize arg_max(Timestamp, *) by DeviceId
| where AVProductVersion == “4.18.23.0.0”
| project DeviceName, DeviceId, AVProductVersion, Timestamp
| order by DeviceName asc
Firewall
DeviceNetworkEvents
| where ActionType == “ConnectionBlocked”
| project Timestamp, DeviceName, LocalIP, RemoteIP, RemotePort, Protocol, InitiatingProcessFileName
| sort by Timestamp desc
DeviceNetworkEvents
| where ActionType == “InboundConnectionBlocked”
| summarize BlockCount = count() by RemoteIP, DeviceName, bin(Timestamp, 1h)
| sort by BlockCount desc
DeviceNetworkEvents
| where ActionType == “ConnectionBlocked”
| where InitiatingProcessFileName !in~ (“chrome.exe”,”msedge.exe”,”firefox.exe”)
| project Timestamp, DeviceName, RemoteIP, RemotePort, InitiatingProcessFileName
DeviceEvents
| where Timestamp > (30d)
| where ActionType in (“FirewallOutBoundConnectionBlocked”, “FirewallInBoundConnectionBlocked”,”FirewallOutBoundConnectionToAppBlocked”)
| project DeviceName, Timestamp, InitiatingProcessFileName, InitiatingProcessParentFileName, RemoteIP, RemotePort, LocalIP, LocalPort,ActionType
//| where DeviceName contains “”
DeviceNetworkEvents
| where Timestamp > (7d)
| project Timestamp, DeviceName, ActionType, RemoteIP, RemotePort, RemoteUrl, LocalIP, LocalPort,Protocol, LocalIPType, InitiatingProcessFileName
//| where ActionType == “ConnectionFailed”
| where RemotePort == “3389”
| where DeviceName contains “desktop-3jtb9c6”
| order by Timestamp
DeviceEvents
| where ActionType in (“FirewallRuleAdded”, “FirewallRuleModified”, “FirewallRuleDeleted”)
| project Timestamp, DeviceName, ActionType, InitiatingProcessAccountName
| sort by Timestamp desc
DeviceNetworkEvents
| where ActionType == “ConnectionBlocked”
| summarize Count = count() by RemotePort
| sort by Count desc
DeviceNetworkEvents
| where ActionType == “ConnectionBlocked”
| summarize BlockCount = count() by DeviceName, bin(Timestamp, 5m)
| where BlockCount > 200
| sort by BlockCount desc
DeviceInfo
| summarize LastSeen = max(Timestamp) by DeviceName
| join kind=leftouter (
DeviceEvents
| where ActionType in (“FirewallDisabled”)
| summarize FirewallDisabledCount = count() by DeviceName
) on DeviceName
| join kind=leftouter (
DeviceNetworkEvents
| where ActionType == “ConnectionBlocked”
| summarize BlockedConnections = count() by DeviceName
) on DeviceName
| extend RiskLevel = case(
FirewallDisabledCount > 0, “High Risk”,
BlockedConnections > 1000, “Medium Risk”,
“Normal”
)
| project DeviceName, LastSeen, FirewallDisabledCount, BlockedConnections, RiskLevel
KQL for ASR and EDR
DeviceEvents
| where ActionType == “AsrRuleBlocked”
| project Timestamp, DeviceName, InitiatingProcessFileName, FileName, ActionType, AdditionalFields
| sort by Timestamp desc
DeviceEvents
| where ActionType == “AsrRuleAudited”
| project Timestamp, DeviceName, InitiatingProcessFileName, FileName, ActionType
| sort by Timestamp desc
DeviceEvents
| where ActionType startswith “ASR”
| extend Fields=parse_json(AdditionalFields)
| extend IsAudit = tostring(Fields.IsAudit)
| where IsAudit == “false”
| project Timestamp, DeviceName, ActionType, IsAudit, ReportId, DeviceId
DeviceEvents
| where ActionType startswith 'ASR'
| project DeviceName, ActionType
| evaluate pivot(ActionType)DeviceEvents
| where ActionType == “AsrRuleBlocked”
| summarize BlockCount = count() by DeviceName
| sort by BlockCount desc
DeviceEvents
| where ActionType == “AsrRuleBlocked”
| summarize Count = count() by InitiatingProcessFileName
| sort by Count desc
DeviceEvents
| where ActionType == “AsrRuleBlocked”
| where InitiatingProcessFileName in~ (“winword.exe”,”excel.exe”,”powerpnt.exe”)
| project Timestamp, DeviceName, InitiatingProcessFileName, FileName
DeviceEvents
| where ActionType == “AsrRuleBlocked”
| extend RuleId = tostring(parse_json(AdditionalFields).RuleId)
| summarize BlockCount = count() by DeviceName, RuleId
| sort by BlockCount desc
DeviceInfo
| join kind=leftouter (
DeviceEvents
| where ActionType in (“AsrRuleBlocked”,”AsrRuleAudited”)
| summarize LastASR = max(Timestamp) by DeviceName
) on DeviceName
| where isnull(LastASR)
| project DeviceName, OSPlatform, SensorHealthState
DeviceEvents
| where ingestion_time() > ago(30d)
| where ActionType in ('AsrRansomwareBlocked', 'AsrRansomwareAudited')
| summarize arg_max(Timestamp, *), TotalEvents = count(), TriggeredFiles = make_set(FileName), FileHashes = make_set(SHA1), IntiatingProcesses = make_set(InitiatingProcessCommandLine) by DeviceName, AccountName
| project Timestamp, DeviceName, AccountDomain, AccountName, TotalEvents, TriggeredFiles, FileHashes, IntiatingProcesses
DeviceInfo
| summarize LastSeen = max(Timestamp), Health = any(SensorHealthState), Onboard = any(OnboardingStatus) by DeviceName
| extend EDRStatus = case(
Onboard != “Onboarded”, “Not Onboarded”,
Health != “Active”, “Sensor Issue”,
LastSeen < ago(3d), “No Recent Check-in”,
“Active”
)
| project DeviceName, LastSeen, Health, Onboard, EDRStatus
| order by EDRStatus asc
KQL Query for USB Device Control Policy
DeviceEvents
| where ActionType == “RemovableStoragePolicyTriggered”
| extend parsed=parse_json(AdditionalFields)
| extend RemovableStorageAccess = tostring(parsed.RemovableStorageAccess)
| extend RemovableStoragePolicyVerdict = tostring(parsed.RemovableStoragePolicyVerdict)
| extend MediaBusType = tostring(parsed.BusType)
| extend MediaClassGuid = tostring(parsed.ClassGuid)
| extend MediaDeviceId = tostring(parsed.DeviceId)
| extend MediaInstanceId = tostring(parsed.DeviceInstanceId)
| extend MediaName = tostring(parsed.MediaName)
| extend RemovableStoragePolicy = tostring(parsed.RemovableStoragePolicy)
| project Timestamp, DeviceId, DeviceName, ActionType, RemovableStorageAccess,
RemovableStoragePolicyVerdict, MediaBusType, MediaClassGuid, MediaDeviceId,
MediaInstanceId, MediaName, RemovableStoragePolicy
| where RemovableStoragePolicyVerdict == “Deny”
| where DeviceName contains “LHT00001”
| order by Timestamp desc
DeviceEvents | where ActionType == "PnpDeviceConnected" | extend PNPInfo = parse_json(AdditionalFields) | extend ClassName = tostring(PNPInfo.ClassName), DeviceDescription = tostring(PNPInfo.DeviceDescription), VendorIds = tostring(PNPInfo.VendorIds), DeviceId = tostring(PNPInfo.DeviceId) | extend PnPType = tostring(split(DeviceId, @"\", 0)[0]) | where PnPType == "USB" | project-reorder ClassName, PnPType, DeviceDescription, VendorIds, DeviceId | summarize TotalEvents = count() by DeviceDescription | sort by TotalEvents
General KQL Query
let avmodetable = DeviceTvmSecureConfigurationAssessment
| where ConfigurationId == “scid-2010” and isnotnull(Context)
| extend avdata=parsejson(Context)
| extend AVMode = iif(tostring(avdata[0][0]) == ‘0’, ‘Active’ , iif(tostring(avdata[0][0]) == ‘1’, ‘Passive’ ,iif(tostring(avdata[0][0]) == ‘4’, ‘EDR Blocked’,iif(tostring(avdata[0][0]) == ‘2’, ‘SxS Passive’ ,’Unknown’))))
| project DeviceId, AVMode;
DeviceTvmSecureConfigurationAssessment
| where ConfigurationId == “scid-2011” and isnotnull(Context)
| extend avdata=parsejson(Context)
| extend AVSigVersion = tostring(avdata[0][0])
| extend AVEngineVersion = tostring(avdata[0][1])
| extend AVSigLastUpdateTime = tostring(avdata[0][2])
| project DeviceId, DeviceName, OSPlatform, AVSigVersion, AVEngineVersion, AVSigLastUpdateTime, IsCompliant, IsApplicable
| join avmodetable on DeviceId
| project-away DeviceId1
DeviceEvents
| where ActionType in (“AntivirusScanCompleted”, “AntivirusScanCancelled”)
| extend A=parse_json(AdditionalFields)
| project Timestamp, DeviceName, ActionType,ScanType = A.ScanTypeIndex, StartedBy= A.User
| sort by Timestamp desc
let CanBeOnboarded = “Can be onboarded”;
DeviceInfo
| summarize arg_max(Timestamp, *) by DeviceId
| where OnboardingStatus == CanBeOnboarded
let ProcessBased = DeviceProcessEvents | where ProcessCommandLine has "Get-MpPreference" | extend Table = "DeviceProcessEvents" | project-reorder Table, Timestamp, DeviceName, ProcessCommandLine, InitiatingProcessFileName; let EventBased = DeviceEvents | extend Command = parse_json(AdditionalFields).Command | where Command == "Get-MpPreference" | extend ScriptLocation = extract(@"literalPath '(.*?)'", 0, InitiatingProcessCommandLine) | extend Table = "DeviceEvents" | project-reorder Table, Timestamp, DeviceName, InitiatingProcessCommandLine, InitiatingProcessParentFileName, ScriptLocation; union ProcessBased, EventBased
let ExclusionOptions = dynamic(['ExclusionPath', 'ExclusionExtension', 'ExclusionProcess', 'ExclusionIpAddress']); let Modules = dynamic(['Add-MpPreference','Set-MpPreference']); let CommandLineExecutions = DeviceProcessEvents | where ProcessCommandLine has_any (Modules) and ProcessCommandLine has_any (ExclusionOptions); let PowerShellExecutions = DeviceEvents | where ActionType == 'PowerShellCommand' | where AdditionalFields has_any (Modules) and AdditionalFields has_any (ExclusionOptions); union PowerShellExecutions, CommandLineExecutions
let CanBeOnboarded = “Can be onboarded”;
DeviceInfo
| summarize arg_max(Timestamp, *) by DeviceId
| where OnboardingStatus == CanBeOnboarded
let LastLogins=DeviceLogonEvents
| where LogonType == “Interactive”
| where InitiatingProcessParentFileName == “wininit.exe”
| summarize LastLogon=arg_max(Timestamp, *) by AccountName, DeviceName
| project AccountName, DeviceName, LastLogon;
let Logins=DeviceLogonEvents
| where LogonType == “Interactive”
| where InitiatingProcessParentFileName == “wininit.exe”
| summarize Logins=count() by AccountName, DeviceName
| project AccountName, Logins, DeviceName;
let NetworkInfo=DeviceNetworkInfo
| where IPv4Dhcp <> “”
| mvexpand parse_json(IPAddresses)
| where IPAddresses.IPAddress !contains “:”
| summarize arg_max(Timestamp, *) by DeviceName
| project DeviceName, IPAddress=IPAddresses.IPAddress, Timestamp;
Logins
| join kind=inner ( LastLogins
| project AccountName, DeviceName, LastLogon
) on DeviceName, AccountName
| join kind=leftouter ( NetworkInfo
| project DeviceName, IPAddress, Timestamp
) on DeviceName
| project AccountName, DeviceName, LastLogon, Logins, IPAddress
| sort by DeviceName
DeviceEvents | where Timestamp > ago(30d) | where ActionType startswith "SmartScreen" | extend SmartScreenTrigger = iff(ActionType == "SmartScreenUrlWarning", RemoteUrl, FileName) | extend ReasonForTrigger = parse_json(AdditionalFields).Experience | project Timestamp, DeviceName, ActionType, SmartScreenTrigger, ReasonForTrigger, InitiatingProcessCommandLine
// Author: Alex Teixeira (alex@opstune.com) DeviceProcessEvents | where Timestamp > ago(60d) // Speed up the query by filtering most frequent processes | where FolderPath matches regex @'(?i)^[a-z]:\\\S+\.exe' and not ((FolderPath contains "c:\\windows" and FolderPath matches regex @'(?i)microsoft\.net|softwaredistribution|system32|syswow64|ccm|servicing|winsxs') or FolderPath matches regex @'(?i)^(d:\\apps|c:\\_datas\\)') // Normalize to frequent (known) RATs | extend RAT=case( FolderPath contains "teamviewer", "TeamViewer", FolderPath contains "anydesk", "AnyDesk", FolderPath contains "rustdesk", "RustDesk", FolderPath contains "vnc", "VNC", FolderPath contains "manageengine", "ManageEngine", FolderPath contains "fastclient", "FastClient", FolderPath contains "logmein", "LogMeIn", FolderPath contains "bomgar", "Bomgar", FolderPath contains "netviewer", "NetViewer", FolderPath contains "ultraviewer", "UltraViewer", FolderPath contains "dwrcs", "Dameware", FolderPath contains "splashtop", "Splashtop", FolderPath contains "zerotier", "ZeroTier", FolderPath contains "supremo", "Supremo", "Other" ) | summarize count(), count_distinct(DeviceName), make_set(DeviceName), max(Timestamp) by RAT, FolderPath | extend r_1=@'(?i)[\\]+(NetWire|rport)[\\]+|Rsocx|BeAnywhere|DWservice|Fleetdeck|Itarian Endpoint Manager|Splashtop|Level\.io|ManageEngine|ScreenConnect|TrendMicro BaseCamp|Sorillus|ZeroTier|JollyFastVNC|AB Tutor|Barracuda Workplace|SolarWinds RMM|Naverisk' | extend r_2=@'(?i)(NetSupport|TeamViewer|Anydesk|UltraViewer|realvnc|TightVNC|LogMeIn|fastclient|ultraVNC|bomgar.+scc|accessserver|aeroadmin|alitask|alpemix|ammyy|ateraagent|basupsrvc|basupsrvcupdate|basuptshelper|beamyourscreen|beanywhere|cagservice|chrome remote desktop|clientmrinit|connectwise|connectwisecontrol|crossloopservice|ctiserv|dameware|datto|domotz|dwrcs|dwservice|eratool|ericomconnnectconfigurationtool|ezhelpclient|fixmeit|fixmeitclient|fleetdeck|goverrmc|guacd|instanthousecall|intelliadmin|iperiusremote|islalwaysonmonitor|isllightservice|itarian|jumpclient|jumpdesktop|jumpservice|kaseya|landeskagentbootstrap|laplink|laplinkeverywhere|ldsensors|llrcservice|lmiignition|ltsvcmon|mgntsvc|mikogo|mionet|myivomanager|nateon|neturo|netviewer|nhostsvc|ntrntservice|orcus|pcaquickconnect|pcstarter|pcvisit|pocketcontroller|ptdskclient|pulseway|rcengmgru|rcmgrsvc|rdpwrap|remobo|remote utilities|remoteconsole|remotepass|remotepc|remotepcservice|remotesupportplayeru|remoteview|rfusclient|romfusclient|romserver|romviewer|rpaccess|rpcgrab|rpcsetup|rpcsuite|rpwhostscr|rustdesk|rutserv|rutview|rxstartsupport|screenconnect|seetrolclient|seetrolremote|serverproxyservice|showmypc|simplehelpcustomer|simpleservice|sorillus|sragent|supremo|supremohelper|syncro|tacticalrmm|take\s*control|tdp2tcp|tigervnc|trend.+basecamp|turbomeeting|ultraviewer|vncconnect|webex remote|webrdp|weezo|weezohttpd|windows admin centre|wmcsvc|zerotier|zoho assist).*\.exe$' | extend r_3=@'(?i)\\(baseclient|BASupApp|DWAgent|ITSMAgent|level|Atera|radmin|srserver|rvagent|intouch)\.exe$' | where (FolderPath matches regex r_1 or FolderPath matches regex r_2 or FolderPath matches regex r_3) | extend set_DeviceName=iff(count_distinct_DeviceName>5, strcat("Too many (", count_distinct_DeviceName, ")"), set_DeviceName) | summarize TotalEvents=sum(count_), DeviceCount=count_distinct(set_DeviceName), Devices=make_set(set_DeviceName), Processes=make_set(FolderPath), LastSeen=max(max_Timestamp) by RAT | sort by DeviceCount desc, TotalEvents desc
DeviceNetworkEvents | where RemotePort == 445 | where ActionType == "ConnectionSuccess" // Collect the last event that a device has connected via SMB to a unique remote IP | summarize arg_max(Timestamp, *) by DeviceId, RemoteIP | summarize RemoteSMBUrls = make_set_if(RemoteUrl, isnotempty(RemoteUrl)), make_set_if(RemoteIP, isempty(RemoteUrl)), TotalConnections = dcount(RemoteIP) by DeviceName | sort by TotalConnections