Data-Driven Defence: Obtaining actionable data from M365 Defender for Endpoint using KQL

How to Obtain Actionable data from M365 Defender vulnerabilities tables, using KQL.

Defender for Endpoint contains a whole host of software inventories and vulnerability information for onboarded devices in your environment – which is great in terms of technical capability.

However, the issue I have really is with the tables, as joining them together to get actionable data for patch management isn’t always the easiest. Whilst extended vulnerability information is useful for security teams to investigate threats, patch management teams just want to know the information which is pertinent to updating the vulnerable software.

Furthermore, the Graphical User Interface (GUI) in my opinion is difficult to navigate. There are too many pages and panes to open to get the information which is important. Which I define to be three following things:

• The vulnerability information.
• The vulnerable devices.
• The software on those devices which makes the device vulnerable.

So, to help speed up the process of gathering this crucial data, I created some KQL to help obtain actionable data from M365 Defender vulnerabilities tables.

The code

The full query to achieve my goal is below. It includes a few joins and nifty functions, which we will talk through to show how these work in conjunction to get the relevant data.

```csharp
let CvssMinimum = 6.9;
let VulnerabiltiesList = ( DeviceTvmSoftwareVulnerabilitiesKB
	| where CvssScore > CvssMinimum
	  and IsExploitAvailable == true
	  and PublishedDate < ago(30d)
	| join DeviceTvmSoftwareVulnerabilities on CveId
		| where isnotempty(DeviceId)
		| summarize AffectedSoftware=make_set(SoftwareName) by DeviceName, CVE=CveId, Severity=VulnerabilitySeverityLevel, PublishedDate
);
let SoftwareInEnvironment = ( DeviceTvmSoftwareInventory
	| distinct SoftwareName, DeviceName
	| summarize SoftwareInventory=make_set(SoftwareName) by DeviceName
);
VulnerabiltiesList
	| join SoftwareInEnvironment on DeviceName
	| extend SoftwareOnDevice = set_intersect(todynamic(AffectedSoftware), SoftwareInventory)
	| project-away SoftwareInventory, AffectedSoftware
	| summarize AffectedDevices=make_set(DeviceName) by CVE, PublishedDate, Severity, tostring(SoftwareOnDevice)
```

Getting vulnerabilities, devices and affected software

So, how exactly do I leverage this code?

1. I first start by getting all the vulnerabilities I want to see depending on a few properties in the vulnerabilities knowledge base (KB) table. You can adjust the properties based on your risk appetite or patch management process. For the purpose of this blog, I have chosen vulnerabilities with a CVSS score of 7 or higher, with exploits available, that were published over 30 days ago.
2. I then join on the other vulnerabilities table to get the tenants device information.
3. Finally, I summarise the software affected by the CVE Id, the device name, severity and published date. I want this information in my final output, therefore I need to summarise it here. I store all of this information into a table called `VulnerabiltiesList`.

The key to this is the `make_list()` function which creates a dynamic list of all software affected by the CVE. It’s important to note that this is ***all*** software and not just the software on the devices in your environment.

More on this next…

Finding vulnerable software in your environment

As mentioned, patch management teams only want to know the software they need to update. I’m surprised that Microsoft haven’t included this information in the software vulnerabilities tables to be honest as it seems like a key property that’s missing to me.

Nevertheless, we can do it ourselves. Microsoft do of course provide a software inventory table, which details all of the software and versioning of the onboarded devices in the tenant.

Using the software inventory table I get a distinct list of software by device. Again, I make a list of this software using the `make_set()` function. I store this information in a variable called `SoftwareInEnvironment`.

Let’s pause here for a second and take a look at what we have…

• We have a list of software types that are on the machines in an environment.
• We have a list of software which is vulnerable on machines in an environment.

We now need to compare these two lists to check for commonalities in both. This can be achieved using an incredibly useful function called `set_intersect()`.

Set intersect returns common values in two lists, it’s honestly helped me out so much in KQL, if you take nothing else away from this post, start using `set_intersect()`

Putting it all together

To put this all together we join the two tables that we created by the device name and then use our `set_intersect()` function on our lists of software. I then make a final set of the devices by summarising by the other pertinent information. This is personal preference, but I found the data looked better with a list of devices and a list of affected software, rather than having each device on a separate row.

When we put this altogether, we get results that look like the following:

Now tell me who doesn’t want to speed up their patch management process, simply by using some handy KQL! For those that have made it this far, I hope you enjoyed this intel, and that it makes your patch management process, slightly less painful.

Thanks for reading, and remember Keep Calm and KQL!

---

Want to know more about how our team can help your business leverage the power of Microsoft Defender for Endpoint? Get in touch and speak directly to the experts today.