AppLocker via MDE with Power BI

MMS logo

MMS 2022 - Tips & Tricks session

I was lucky enough to attend MMS 2022 at the Mall of America (MMSMOA) last week and one of the sessions on the Wednesday was “Tips and Tricks”.

I always enjoy this session as it gives attendees a chance to present for 3 minutes on either something they’re working on, something they find valuable, or just something plain cool.

Earlier that week I attended the brilliant session from Maurice Daly and Sandy Zeng where they were talking about pushing data regarding AppLocker from Intune managed devices to Log Analytics and building workbooks to analyse it.

I asked during the session if they had done much with the AppLocker/Windows Defender Application Control (WDAC) data gathered by Microsoft Defender for Endpoint (MDE) that you can view using Advanced Threat Hunting, but they hadn’t.

That got me thinking…

I was helping out the 2PintSoftware team on their stand just before the session and had a bit of time, so I decided to see if I could pull some of the data out of MDE and into Power BI.

Main activity page of the AppLocker MDE Power BI Report in the desktop app
The activity page showing AppLocker information pulled from MDE

I’ve included a link to download the pbit (template) of this report at the end of this post so you can point it at your own data and have a play.

My time on stage was unfortunately very brief as the number of people wanting to get up and share knowledge was staggering and the session was running over time, so I promised I’d blog a bit more detail and here it is!


Firstly, please bear in mind this is not a finished polished product.

I threw this together just before the Tips & Tricks session, purely as a demonstration of the simplicity, yet power, of BI reporting and to also show that it’s possible to connect to Microsoft Defender for Endpoint (MDE) as a datasource.


You’re more than welcome to download, play and use this as a basis to make something that suits your needs. Just don’t expect it to deliver the world by simply pointing it at your data…

How to connect?

Once you’ve downloaded the pbit template file, open it in Power BI Desktop and the queries will attempt to refresh and pull data.

At this point you should get prompted to specify some credentials to use to connect to the datasource. 


Choose “Organizational account” and then click “Sign in”.

N.B. You will need to use an account that has a role in Defender for Endpoint with:

  • View Data
  •          Security Operations
  •          Threat and vulnerability management
Shows the connection to MDE using an Organizational Account

What's the magic behind this?

Microsoft Defender for Endpoint has an Advanced Threat Hunting API that querys can be passed to and will return appropriate data.


To query for AppLocker (and WDAC) events we can use the following query:

| where ActionType startswith 'AppControl'

Running this query within the Advanced hunting part of the M365 Defender console will return all the data where the action type starts with AppControl. However, this isn’t the most friendly to work with, but useful for quick checks.

Shows the Advanced Hunting query in the MDE portal for AppLocker data
Advanced Hunting query in the MDE portal for AppLocker data

This query can obviously be written to be much more custom and return more streamlined results, but… we could also take all this raw data and dump it into Power BI and do some analysis and presentation in there instead!

Using the Advanced Editor in Power BI we can craft an M Query that takes the query, structures it in a way to pass to the API and retrive a JSON object back with all the results.

Firstly, we specify the query:

AdvancedHuntingQuery = "DeviceEvents

| where ActionType startswith 'AppControl'",

Then the URL we will be passing the query to:

HuntingUrl = "",

Combine it into a URL that we will submit using the Web.Contents command and specify to treat the return as JSON using Json.Document: 

Response = Json.Document(Web.Contents(HuntingUrl, [Query=[key=AdvancedHuntingQuery]])),
Shows the M Query used in Power BI
M Query used in Power BI to get AppLocker events

That alone should be enough, but there’s some more bits I’ve added to the query to help with automatically format the columns based on the returned JSON schema and convert into a nicely formated table.


If you dig into the query, you can also see I’ve done some work to add some custom columns for things like date/time to help with slicing and I’ve also done some work around the exe rules.

Generating Rules Automatically

One thing I really liked that Maurice and Sandy showed in their solution was the building of rules automatically.

Therefore I added some custom columns to the dataset that pulled out the Product Name, Binary Name, Binary Version and combined them into the formatting for an AppLocker rule.

Shows custom columns in Power BI to generate rules for exes
Cusom columns in Power BI for Applocker EXE rules

N.B. Again, please bear in mind, this was a quick and dirty solution to show some of the posibilities only. The auto rule generation I made is only setup for EXEs. Anything else, DLLs, Scripts etc, would need you to do further work and I’ve also done very minimal checking of the produced rules so don’t take them verbatim without fully checking them!!

If you check the Rule Information tab in the report, you should be able to select an app and see a suggested rule.

Shows rules automatically created for AppLocker
AppLocker - Auto rules


Hopefully this little demonstration report gives you some ideas on what you might be able to do with the information related to AppLocker and WDAC events stored within Microsoft Defender for Endpoint.

I’d love to hear what you do with it, drop me a message using one of the contact options on this page or message me on Twitter – @StevybSC

Download the template

Share on:


Webinar: Protecting your Data in 2022

Let’s face it – backups are rarely checked or updated once implemented. So how would you know if your data had been breached?
Find out more
Share on Facebook
Share on Twitter
Share on LinkedIn

Related blogs

two people at desk looking at code

AOVPN DPC V4.0 is Now Live!

Today we’re very excited to announce the release of AOVPN DPC 4.0 with support for Windows 11! AOVPN Dynamic Profile Configurator is now functional with