“We needed to find solutions to a variety of issues whilst being a complex business, operating in a 24/7 environment. Stripe OLT listened and understood immediately the challenges we faced.”

IT Operations Manager
Simon Darley
Trusted by industry leaders

Let's Talk

Call us on one of the numbers below, we cover the whole of the UK, so call the nearest office.

BriSTOL HQ & The South West

London & Surrounding Areas

Manchester & the North

“We needed to find solutions to a variety of issues whilst being a complex business, operating in a 24/7 environment. Stripe OLT listened and understood immediately the challenges we faced.”

IT Operations Manager
Simon Darley
Trusted by industry leaders

Request a Call-back.

First we need a few details.

ENQUIRY - Contact Popup DEPRECIATED (#3)

Keep up to date with the experts

Get insights direct to your email inbox

MAIL LIST - Newsletter, Exit Intent Popup (#13)

Follow us on social

“We needed to find solutions to a variety of issues whilst being a complex business, operating in a 24/7 environment. Stripe OLT listened and understood immediately the challenges we faced.”

IT Operations Manager
Simon Darley
Trusted by industry leaders

Request a Call

First we need a few details.

ENQUIRY - Popup w/ Captcha for light backgrounds (#21)
Expert Intel

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

Published: May 24, 2024
Updated: May 29, 2024
Expert: Liam Jones
Role: Lead Security Automation Engineer
Specialises in: Security Automation
What you will learn:
In this intel I will show you how to leverage KQL to streamline the patch management process in Microsoft Defender for Endpoint.
Navigating through the complex GUI of Microsoft Defender for Endpoint can be frustrating for many, especially when vital information is buried under multiple pages. By using KQL to simplify and speed up the data retrieval process, we can significantly ease this pain, making patch management less overwhelming and more efficient.

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.

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…

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`.

  • 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()`

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.

Our latest expert Intel

  • October 11, 2024
    Read full article
  • Copilot logo
    September 5, 2024
    Read full article
  • crowdstrike key learnings
    August 6, 2024
    Read full article
  • apps-management
    July 18, 2024
    Read full article
  • expert-intel-back-ground
    June 20, 2024
    Read full article
  • nhs-cyber-attack
    June 13, 2024
    Read full article
  • Obtaining actionable data from M365 Defender for Endpoint using KQL background
    May 29, 2024
    Read full article
  • af5i6049
    May 29, 2024
    Read full article
  • penetration-testing-services
    May 29, 2024
    Read full article
  • Gootkit
    May 29, 2024
    Read full article
  • flyd-olrxnzxfbjo-unsplash
    May 29, 2024
    Read full article
  • Malvertising
    May 29, 2024
    Read full article