Grouping and Sorting in Azure Operational Insights Search

I’ve been playing with the preview of Azure Operational Insights lately. Operational Insights (https://preview.opinsights.azure.com) is an online service that analyzes installations of Microsoft server software by using System Center Operations Manager or agents directly connected to servers. Operational Insights collects data from your installations, analyzes it, and generates alerts that identify potential issues (such as missing security patches) or deviations from identified best practices with regard to configuration and usage. Operational Insights also provides current and historical views of server configurations in your environment.

You can find more information about Operational Insights on TechNet.

Back to the title of this blog post. Today I was trying to Group and Sort the results for missing Update Rollups from the System Update Assessment Intelligence Pack. Intelligence Packs are a collection of logic, visualization and data acquisition rules that address key customer challenges today. They allow deeper insights to help investigate and resolve operational issues faster, collect and correlate various types of machine data and helps you be proactive with activities such as Capacity Planning, Patch status reporting and security auditing.

image

When selecting the System Update Assessment on my Operational Insights Overview Dashboard I got the following overview.

image

 

As you can see here I have 3 Types of Updates missing and for the Type of Update Rollups I have 11 Update Rollup missing. When drilling down to the Search pane of Operational Insights by clicking on the Update Rollups Type in above windows I see the following results.

image

Now I want to group the results by KBID. That’s easy, just pipe above query to Measure by Count() by KBID

Type:RequiredUpdate UpdateClassification:"Update Rollups" | select UpdateTitle,KBID,UpdateClassification,UpdateSeverity,PublishDate,Server | Measure count() by KBID

image

But how would I now sort the group result by KBID ascending?

Type:RequiredUpdate UpdateClassification:"Update Rollups" | select UpdateTitle,KBID,UpdateClassification,UpdateSeverity,PublishDate,Server | Measure count() by KBID | Sort AggregatedValue Asc

Remark: AggregatedValue property is case-sensitive! aggregatedvalue or AGGREGATEDVALUE is not working!

 image

There is small learing curve, especially if you know how to group and sort in PowerShell, but there are some great blog posts from Daniele Muscetta on how to Search in Azure Operational Insights:

And Stefan Roth published a Microsoft Azure Operational Insights – Search Data Explorer Cheat Sheet which you can also use for exploring the data in Operational Insights.

Have fun exploring!