Starting with Log Analytics: Part 5 - Running KQL queries in Log Analytics with PowerShell

In this post we will continue the blog series about Log Analytics and see how to use PowerShell and Log Abalytics API to run KQL queries in our workspace.

Other articles

- Part 1: Creating our first Log Analytics workspace (you are here) 

- Part 2: Importing your own datas into the workspace (Soon)

- Part 3: Creating our first workbook (Soon)

- Part 4: Add Intune datas into Log Analytics workspace

- Part 5: RunningKQL queries in Log Analytics through PowerShell

What do we want ?

- You have a Log Analytics workspace

- You have different bascic logs (like IntuneDevices)

- You have Custom Logs

- You want to run query on those logs with PowerShell

The solution

We will proceed as below:

1. Create an Azure application

2. Configure a secret 

3. Assign a role to Azure app to access Log Analytics 

4. Authenticate to the workspace with PowerShell

5. Run a KQL query with PowerShell

Creating the Azure application

1. Go into Azure portal

2. Go to App registrations

3. Click on New registration

4. Type a name

5. Let by default and click on Register

Creating a secret

We will proceed as below:

1. Click on Certificates & secrets

2. Click on New client secret

3. Type a name

4. Cliquez sur Add

5. Copy the secret for later

Application Azure and Log Analytics

In order to be able to run KQL query through PowerShell and the Azure app, we will 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 Review + Assign

Query Log Analytics with PowerShell

Authentication

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

Fill below variables with your datas:

To get a token we will use the below code:

Run KQL queries

We will first run KQL queries in the IntuneDevices table.

This one gives information about enrolled devices in Intune.

Through the portal you can do this in the Logs part.

See below the query we will run:

To do this with PowerShell use the below code:

We will now run KQL queries in the table we have previously created.

See below the query we will run:

See below the result through the portal:

To do this with PowerShell use the below code:

See below the result: