Practical Endpoint: Using KQL to Pull the Deployment Status of the Defender for Endpoint

Over the last year, we have experienced an uptake for Defender for Endpoint deployments. This is mainly due to mergers and acquisitions happening on the software vendor side (like Symantec Enterprise Antivirus taken by Broadcom) and this has forced customers to move away from existing solutions due to many reasons (mostly due to price hikes and lack of support after take over). As part of this exercise, many of my customers realized their Microsoft 365 subscriptions included Defender for Endpoint, so switching over to Microsoft’s solution became an easy choice.

Onboarding Endpoint to Defender for Endpoint

If you use Hybrid join, the onboarding process is very straightforward. All you need is to add a GPO to deploy the tenant information. You can even do that while other antivirus software runs as Defender in Endpoint will be placed in passive mode automatically. Defender for Endpoint will automatically switch to active mode once you uninstall any existing antivirus from the Windows endpoint. If you are using Intune to manage the environment, you can deploy a configuration profile to deploy the tenant ID, for example, and other required registry values to all endpoints for onboarding to ensure the required Windows component is enabled and knowing which M365 tenant this endpoint belongs to. Refer here for detailed steps using Intune for onboarding and details about the registry values needed can be found here

The Challenges for Onboarding

When you work in an environment where most of the endpoints are not in the same physical locations, like those where people work from home or work in remote disconnected offices, a way is needed to check if the Defender for Endpoint deployment is completed. The obvious way is to use the Devices view in the Defender portal, as shown in Figure 1, which indicates if the onboarding is successful.

However, the issue with using the device inventory page in the Microsoft Defender portal is that it only provides basic information unless you select a device and open the detail pane. You cannot tell if the onboarded device is in passive, active mode, or engine version to determine if they are running the required version to support onboarding. A solution to the problem is to use Advanced Hunting to run custom KQL to retrieve additional information.

Using KQL to get Information About Defender for Endpoint

Defender for Endpoint gathers some system information to support operations and detection needs. We can leverage that data to build a custom report to show the deployment status. Note that the way described here works for tenants with Defender for Endpoint P1 and P2 licenses. However, if you need some additional info to enrich your report, you should check the information here to make sure that what you want is covered by your current plan.

The key information related to the deployment status is stored in this DeviceTvmSecureConfigurationAssessment schema. This schema stores all the assessments performed against each supported endpoint and respective results performed for managed endpoints. Just like what we see in Figure 2. You can see different assessments performed on managed endpoints and each assessment is identified by a configuration ID.

To find the data we want to examine, we need to filter the data in the schema by the configuration ID scid-2010. You may wonder how to get this ID. You can obtain that by looking at the Security recommendations from your tenant, as shown in Figure 3.

At the time of writing, there is no full list published by Microsoft. We can only find the configuration ID needed by looking at the assessment result from sample endpoints or by making a query using advanced hunting against a table DeviceTvmSecureConfigurationAssessmentKB which lists all the information related to each assessment (as identified by configuration ID). A sample query against the table is illustrated in Figure 4.  

DeviceTvmSecureConfigurationAssessmentKB | order by ConfigurationId asc

You can either export the table as an Excel file for reference or use configuration ID to join the tenant’s data using configuration ID. The key fields to read are the ConfigurationName and ConfigurationDescription, which tell you detailed information about that specific assessment performed.

Each configuration may have different datasets related, which makes it difficult to normalize them into a flat structure. This is the purpose of the Context data field. A Context Data field is a large text field for storing additional data in JSON format. You can parse the Context Data and extract information from it. For our case, the Defender for Endpoint status, signature version, and other information is stored inside the JSON value. Below is an example query to extract information from the Context Data. Figure 4 shows a sample result.

DeviceTvmSecureConfigurationAssessment

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

| extend data=parsejson(Context)

| project DeviceId, DeviceName, data;

Notice that we used the parsejson function to extract the information from the Context data field. In some assessments (as referred by configuration ID), there may be more data. In this case (configuration ID=scid-2010), the context data field only includes the value representing the mode (Passive, Active) of Defender for Endpoint. If there is no data, this usually means the endpoint does not have Defender for Endpoint installed. Based on my research, the following are the possible values of this field.

Mastering KQL Becoming Essentials

Microsoft will keep enhancing their product to help us do the job easier. But yet, there are situations like the one above where we would like data presented in the way we need. This is where KQL comes in handy because it can pull information from different parts of the tenant. You can refer to this article to know more about KQL or Microsoft’s documentation here.