Running KQL queries on Microsoft Defender for Endpoint through Azure Automation

In this post we will see how to run KQL queries on a Microsoft Defender for Endpoint through Azure Automation, PowerShell. and Graph API

The solution

We will proceed as below:

1. Create an Azure Automation

2. Configure it with managed identity

3. Assign permissions to the managed identity

4. Authenticating with managed identity

5. Run a KQL query with PowerShell

We will see also an alternative using an Azure app registration.

Get script

Click on the below GitHub picture to get the scripts.

You will find:

- Assign_permissions.ps1: script to add permissions to the MI

- Runbook.ps1: the Azure Automation runbook

Creating automation account

Purpose: here we will create the automation account that will be used to run the script to send data.

1. Go to Azure

2. Go to Automation accounts

3. Click on Create

4. Type a name

5. Choose a Subscription, resource group and region

6. Click on Create

7. Click on Go to resource

Setting Managed Identity

Purpose: the managed identity is used to authenticate to your tenant, this way we don't need to provide credentials.

It allows you to avoid the credentials part.

When you configure the managed identity, a new Azure Enterprise application will be created.

This one will be used to authenticate to our tenant and do API calls.

For that we will proceed as below:

1. Go to your automation account

2. Go to Identity

3. Go to System assigned

4. Select On

5. Click on Save

6. Click on Yes

7. When it's configured a new enterprise application will be created with the same name than the automation account

Adding permissions

Purpose: in order to be able to run KQL query through our Automation account we need to add permissions to the managed identity.

We need permission AdvancedQuery.Read.All

We will proceed as below:

1. Edit the Assign_permissions_MDE.ps1 script

2. Modify variable $TenantID and $DisplayNameOfMSI

3. Open PowerShell with global admin rights

4. Run the assign_permissions.ps1 script

Editing the script

Purpose: here we will modify the runbook script to adapt it to our needs.

We will proceed as below:

1. Edit the Runbook.ps1 script

2. Modify the variable $My_Advanced_Query with your KQL query

Creating the Runbook

Purpose: here we will create the script that run the KQL query remotely.

1. Click on Create a runbook

2. Type a name

3. In Runbook type, select PowerShell

4. Click on Create

5. Click on Edit on the runbook

6. copy the Runbook.ps1 script content

7. Click on Save

8. Click on Publish

9. Run the runbook

10. The KQL query will show results

No Managed identity way (App registration)

Purpose: here we will do the same without using an Azure app registration).

Creating the Azure Application

1. Go to App registrations

2. Click on New registration

3. Type a name

4. Let other fields by default

5. Click on Register

Adding permissions

Here we want to add some permissions to our Azure Application.

We will proceed as below:

1. Go to API permissions

2. Choose APIs my organization uses

3. Search WindowsDefenderATP

4. Click on it

5. Select Application permissions

6. Check AdvancedQuery.Read.All

7. Click on Add permissions

8. Click on Grant admin consent

Creating a secret

We will proceed as below:

1. Go to Certificates & Secrets

2. Go to Clients secrets

3. Click on New client secret

4. In description type a name

5. Choose when it should expire

6. Click on Add

Editing the runbook

We will proceed as below:

1. Edit the Runbook.ps1 script

2. Modify the following variables $appId, $appSecret, $tenantID

Export result to CSV

Purpose: here we want to export result of the query to a CSV file, then export it to SharePoint.

Adding module

Purpose: here we need to add modules to authenticate to Graph and send  mail.

Use this part only if you want to send info by mail.

1. Go to your automation account

2. Click on Modules gallery

3. Import the following modules

Microsoft.Graph.Users.Actions

Microsoft.Graph.Authentication

Adding mail permission

To send mail we need to add permission on the managed identity.

For that we will use the assign_permission_mail.ps1 script.

Then you need to add permissions to be able to send mail:

- Mail.Send

To add this permission you will need to use PowerShell, it can't be done through the portal.

For that use the script Assign_permissions_Mail.ps1 on my GitHub repo.

You just need to fill below variables:

- $TenantID: your tenant ID

- $DisplayNameOfMSI: name of your automation account

Configuring the script

Configure the below variables if you want to send info by mail:

- $Notif_Title, $Notif_Message 

- $Send_Mail, $Mail_From , $Mail_To

Sending CSV by mail

Here we want to send query result to CSV on SharePoint.

See here more info about how to prepare the solution for sending CSV on SharePoint.

1. Edit the Runbook.ps1 script

2. Modify the following variables $appId, $appSecret, $tenantID