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.

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

 

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.

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

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!

 

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:

• Azure Operational Insights Search How To: Part I - How to filter big data
• Azure Operational Insights Search How To: Part II – More on Filtering, using Boolean Operators, the Time Dimension, Numbers and Ranges
• Azure Operational Insights Search How To: Part III – Manipulating Results: the pipeline “|” and Search Commands
• Azure Operational Insights Search How To: Part IV – Introducing the MEASURE command
• Azure Operational Insights Search HowTo: Part V – Max() and Min() Statistical functions with Measure command
• Azure Operational Insights Search How To: Part VI – Measure Avg(), and an exploration of Type=PerfHourly
• Azure Operational Insights Search How To: Part VII – Measure Sum() and Where command

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!

Comments

• Anonymous
  November 20, 2014
  Daniele Muscetta (MSFT) nous propose une série d’articles pour détailler comment manipuler les informations présentées par System Center Advisor :
  
  Useful Advisor Search Query Collection
  Azure Operational Insights
• Anonymous
  November 20, 2014
  Daniele Muscetta (MSFT) nous propose une série d’articles pour détailler comment manipuler les informations présentées par System Center Advisor :
  
  Useful Advisor Search Query Collection
  Advisor Search How To