Starting with Log Analytics: Part 9 - Running KQL queries on a workspace through Azure Automation

In this post we will see how to run KQL queries on a Log Analytics workspace through Azure Automation and PowerShell.

Other articles

- Part 1: Creating our first Log Analytics workspace 

- Part 2: Importing your own datas into the workspace

- Part 3: Creating our first workbook

- Part 4: Add Intune datas into Log Analytics workspace

- Part 5: Running KQL queries in Log Analytics through PowerShell

- Part 6: Creating a lab by importing a CSV with fake data

- Part 7: Give your workbook a better look

- Part 8: Sending data to Log Analytics from Azure Automation  and Data collector API

- Part 9: Running KQL queries on a workspace through Azure Automation (you are here)

What do we want ?

- You have a Log Analytics workspace

- You have different logs

- You want to run query on logs through Azure Automation

The solution

We will proceed as below:

1. Create an Azure Automation

2. Configure it with managed identity

3. Assign a role to the automation account

4. Authenticate to the workspace with managed identity

5. Run a KQL query with PowerShell

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

6. Choose the Resource group

7. Choose your region

8. Click on Create

9. Wait a bit

10. Click on Go to resource

Set 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

Give access to the automation account

Purpose: in order to be able to run KQL query through our Automation account we need to configure a role.

We will proceed as below:

1. Go to your Log Analytics workspace 

2. Go to Access control (IAM)

3. Click on Add > Add role assignment

4. Go to Roles > Log Analytics Contributor > Next

5. Click on Select members > select your automation account

6. Click on Review + Assign

Creating the Runbook

Purpose: here we will create the script that will send data to Log Analytics.

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

Query Log Analytics with runbook

Authentication

The first step is to get a token which will prove that we can access to the workspace and run query.

The authentication will be done trough the managed identity using the below code:

KQL queries

See below the KQL query we want to run:

See below the PowerShell code used to run the KQL query:

See below the full runbook script: