Windows update compliance - Querying Azure Log Analytics data using PowerShell
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.
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
Open PowerShell ISE and import the module
In the query below I’m looking for Windows devices that are missing security updates:
$Query = @'
| where NeedAttentionStatus=="Missing multiple security updates"
| render table
$SubID = "subscriptionID"
$ResourceGrp = "resource group"
$(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.
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.
- Azure Log Analytics PowerShell reference: https://dev.loganalytics.io/documentation/Tools/PowerShell-Cmdlets
- Log Analytics documentation: /en-us/azure/log-analytics/