Windows update compliance - Querying Azure Log Analytics data using PowerShell

  • Article

 

With the abundance of data across services it’s important to have a method (API) to access the data for export.  Most organizations I speak with have some sort of SIEM to aggregate data and analyze it for informational and alerting purposes.  Microsoft also offers a service called Microsoft Operations Management suite and within that suite is a service called Log Analytics.  For more details about Log Analytics please visit: https://azure.microsoft.com/en-us/services/log-analytics/ 

For the purposes of this post we’ll look at how to query Log Analytics using PowerShell.

 

Requirements

Azure Log Analytics workspace (aka OMS)

Add and configure solutions so data is available to query

 

Let’s get started

Sign into Azure Log Analytics

Download the module from the PowerShell reference link above.

 

Here’s a view of the Log Analytics portal, for this post I’ll focus on Windows Analytics

SNAGHTML6bdc4ff

 

PowerShell access

Open PowerShell ISE and import the module

Import-Module .\LogAnalyticsQuery.psm1

In the query below I’m looking for Windows devices that are missing security updates:

$Query = @'

WaaSUpdateStatus

| where NeedAttentionStatus=="Missing multiple security updates"

| render table

'@

$SubID = "subscriptionID"

$ResourceGrp = "resource group"

$workspace="workspace name"

$(Invoke-LogAnalyticsQuery -WorkspaceName $workspace -SubscriptionId $SubID -ResourceGroup $ResourceGrp -Query $Query).Results|Out-GridView

 

Below is the output from PowerShell query using GridView.  Because the data is in JSON format we can use the data to import into an existing SIEM or dump the data in whatever format needed.

image

 

 

Below is a view of a query from log analytics, as we can see the query’s are identical using both methods.  So utilizing the Log Analytics portal, we can craft queries and then use those queries in our PowerShell scripts to extract data.  I recommend using the Log Analytics portal to prove out queries before utilize PowerShell.

image

 

References