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.

 

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

Author: Courtenay Bernier

Courtenay is a technology professional with expertise in aligning traditional software and cloud services to strategic business initiatives. He has over 20 years of experience in the technology field as well as industry experience working with distribution centers, call centers, manufacturing, retail, restaurant, software development, engineering, and consulting. I am a PM on the Microsoft Intune Engineering Team, all posts, opinions, statements are my own.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s