Using KQL to capture Defender status for Windows devices - Let's hunt!

 

There are loads of reports in Defender which can be deemed more than just fit for purpose. Particularly the Device Health report that gives plenty of insights into the the status of Defender Antivirus. If you are on a look out for gathering the Defender AV status, then you can easily find it over here.

Let's say that you want to gather details on the Defender onboarding status of Windows devices. With the default reports, you can go under each modes and extract the data. You can then filter for Windows OS as a platform and get the list. But is there a better way? You bet! 

Enter Defender Advanced Kusto query language.. With Kusto operators and statements one can construct queries and locate information across the Defender schema. I have been working with KQLs for some time now and I can't imagine working with Defender without Advanced hunting.

In order to query for Defender onboarding modes, one can use the DeviceTvmSecureConfigurationAssessment which contains an assessment events for a specific security configuration from Microsoft Defender Vulnerability Management. Join it with the DeviceTvmSecureConfigurationAssessmentKB table using ConfigurationId to get the text description of the configuration ids and use whatever is applicable to you.

One can also use DeviceTvmInfoGathering to gather the Defender AV modes. However, since I wanted to capture the compliance against configuration ids as part of a separate reporting requirement, I chose not to use this and instead use the configuration assessment tables to capture compliance and information on the Defender AV modes altogether.

I queried the DeviceTvmSecureConfigurationAssessmentKB and identified the configuration ids that are relevant to me.

DeviceTvmSecureConfigurationAssessmentKB | where ConfigurationId in ("scid-2010", "scid-2011") | project ConfigurationId, ConfigurationDescription

If you are looking for the full list, then just remove the where operator and the condition values for ConfigurationId and run the query.

Following query is constructed with the intention of gathering the Defender onboarding mode status and representing in form of the pie chart for Windows devices.

let avmodetable = DeviceTvmSecureConfigurationAssessment

| where ConfigurationId == "scid-2010" and isnotnull(Context)

| where OSPlatform =="Windows10"

| 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' ,'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])

| extend DeviceName = trim(@".therjmdmlab.onmicrosoft.com", DeviceName)

| project DeviceId, DeviceName, OSPlatform, AVSigVersion, AVEngineVersion, AVSigLastUpdateTime, IsCompliant, IsApplicable

| join avmodetable on DeviceId

| project-away DeviceId1

| summarize Count = count() by AVMode

| render piechart  

To get the list, we just remove the last 2 lines and run the query again.

Final thoughts..

Advanced hunting lets you explore up to 30 days of raw data. It supports two modes, guided (perfect for beginners who are not familiar with KQL) and advanced which allows you to build custom KQLs from scratch. The important thing to note is that to use Advanced hunting, one needs to turn on Microsoft Defender XDR first.

There are various applications of Advanced hunting, one being creating custom detections, something that I am currently exploring and hope to capture my experience pretty soon.

Thanks for reading. Until next time..