PowerShell: KQL Queries

There are several ways to pull logs from Azure. Get-MgAuditLogDirectoryAudit will get Entra ID audit logs, Get-AzLogs will get logs from the IaaS side of Azure, but what if you have a Log Analytics Workspace doing the heavy-lifting on your log collection? You can use PowerShell and the Az modules, specifically Az.OperationalInsights, to send KQL queries to your workspace and receive results. You may also find this useful to get beyond data retention for logs, where most disappear after 30 days while a log analytics workspace can be configured to hold logs for longer.

A few things to note before moving forward. Try your query in the Azure portal first if you can. Navigate to Azure Monitor, Logs, and then set your scope to your workspace. Confirm what you are looking for is being logged by your log analytics workspace and that your query is valid before expecting an output from your PowerShell script. You will also need to ensure you have the proper permissions to view logs. The least privileged role is Log Analytics Reader, though more privileged roles may be used if necessary.

Preparing To Query

The Invoke-AzOperationalInsightsQuery is a cmdlet in the Az.OperationalInsights module. If you do not have the Az.Accounts (for Connect-AzAccount) and Az.OperationalInsights modules installed, you should do so before moving forward. You can check out the article on managing installed modules if you need help installing and maintaining PowerShell modules.

The main cmdlet delivering your KQL query will be Invoke-AzOperationalInsightsQuery, but there is some setup to be done beforehand. First, we'll use Connect-AzAccount to connect to our tenant and subscription. Declare your tenant and Subscription IDs to connect and authenticate if prompted.

### Connect to Azure, specifytenant and subscription ID values
$tenantId = 'xxxxx'
$subId = 'yyyyy'
Connect-AzAccount -TenantId $tenantId -Subscription $subId

Invoke-AzOperationalInsightsQuery

We will need to setup the parameters of our query before running. The three things you will need is your workspace ID value, a timespan (if not declared in the query itself), and the query. Considering the queries can get quite lengthy, I find it best to make each piece a variable. You can find your workspace ID by navigating to your log analytics workspace resource in Azure. Use the New-Timespan cmdlet to set how far back you want to query. There are parameter options for days, hours, etc. Your query will be a string, similar to how you would enter it into the portal if you were to make a query in Azure Monitor. Once those are declared, plug them into the Invoke-AzOperationalInsightsQuery Workspace, Timespan, and Query parameters and run.

### Set parameters for log analytics workspace query and run
### Set workspace ID, timespan, and query
$workspaceId = 'zzzzz'
$timespan = New-Timespan -Hours 12
$kqlQueryInput 'AuditLogs | where OperationName == "Update User" and TargetResources contains "MFA" | project TimeGenerated,InitiatedBy,TargetResources'
$kqlQueryResults = Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceId -Timespan $timespan -Query $kqlQueryInput

In this example, we're looking at our stored audit logs and getting all users in our Entra ID tenant that have updated their MFA registrations in the past 12 hours. The attributes in the output will be limited to the time the log was generated, who or what initiated the change, and the target resource, specifically the resource affected by the change. You can make the output of this query a variable and treat it as any other array.

The output of Invoke-AzOperationalInsightsQuery is an object with two attributes, one for metadata and one named "value" which holds your query results. You can isolate these results by encapsulating your Invoke-AzOperationalInsightsQuery query in parentheses and specifying the value attribute. This will give you an array with only your set of results.

### Query Log Analytics Workspace and limit output to results
$kqlQueryResults = (Invoke-AzOperationalInsightsQuery -WorkspaceId $workspaceId -Timespan $timespan -Query $kqlQueryInput).value

Conclusion

This array of results can be treated the same as any other array. Export it to a CSV file, use a foreach loop to act upon each object in the array, put it in a formatted table and send it in an email, etc. The full scope of information you get in this output will closely resemble what you would get from audit and sign-in logs, but the ability to pull logs outside of the 30-day constraint of audit and sign-in logs can prove useful.

Got any interesting KQL queries? Anything to add? How would you implement this into a production script? As always, I'd love to hear from you. Reach out to me at [email protected] or on LinkedIn.