During the last couple of months System Center Advisor or as probably will be known as Microsoft Azure Operational Insights Preview after TechEd Europe 2014 has received a lot of improvements and feature so we are now to Part 7. With this blog post I am also renaming all other blog posts. Here is the full list:
- Microsoft Azure Operational Insights Preview Series – System Update Assessment (Part 1)
- Microsoft Azure Operational Insights Preview Series – Malware Assessment (Part 2)
- Microsoft Azure Operational Insights Preview Series – Log Management (Part 3)
- Microsoft Azure Operational Insights Preview Series – Capacity Planning (Part 4)
- Microsoft Azure Operational Insights Preview Series – Change Tracking (Part 5)
- Microsoft Azure Operational Insights Preview Series – Time Matters in Dashboard (Part 6)
In this post we will have a quick look at a new intelligence pack called SQL Assessment:
After you add it to you Operational Insight account the following tile:
Keep in mind that the first assessment can take up to 4 hours before you see any data. After data is gathered and assessed you will see similar results. Lets click on the tile and see what surprise awaits us:
As you can see we have now the well known graphics in Advisor but you will notice a few differences compared to the other packs. While the other packs focus on providing you the information, so you can make easier assessment and decisions this one gives you a straight knowledge.
During my deployments of System Center I know a thing or two about SQL but I do not consider myself expert. This Intelligence Pack not only can show you potential issues/problems but also can give you some knowledge about SQL. The reason why this pack is different because it tries to provide some continuum to the old Configuration Assessment which I didn’t covered in these series. May be I had the feeling that the old Configuration Assessment will probably be converted to something else. BTW I’ve showed this Intelligence Pack to a SQL MVP and received positive feedback.
Now let’s click on graphic or recommendation and see what we will face:
As I’ve said you will be faced with a lot of knowledge just like the knowledge in the SCOM Management Packs. You will see that the knowledge has some rating. I guess that rating is a static number for every recommendation that is available in the IP. I couldn’t find if the rating rises up if you more affected objects. The good part about this recommendations are that they go down to database level making the results more granular.
From here clicking on the affected object or on the graphic will lead you to the search query result:
As you can see there are a lot of properties that are collected to help you make interesting queries query. Here is a list of those provided by the Advisor team:
I will show you a little more. I want to see what are the recommendation result statuses and how many are available for each of them:
Type=SQLAssessmentRecommendation | measure count() by RecommendationResult
Now rather seeing where I’ve failed I want to see where I’ve done good and for which servers:
Type=SQLAssessmentRecommendation IsRollup=false RecommendationResult=passed
As you will see the Intelligence Pack makes even some server checks that are valid for SQL servers.
Let’s see which server passed most checks:
Type=SQLAssessmentRecommendation IsRollup=false RecommendationResult=passed | Measure count() by Computer
Let’s measure by affected object name which could divide results to granular objects like database:
Type=SQLAssessmentRecommendation IsRollup=false RecommendationResult=passed | Measure count () by AffectedObjectName
I am curious what those inconclusive results are:
Type=SQLAssessmentRecommendation IsRollup=false RecommendationResult=inconclusive
It is interesting that there is another status specific to the affected object:
Type=SQLAssessmentRecommendation IsRollup=false | Measure count() by AffectedObjectResult
Some last tips when you use queries:
- Use RecommendationPeriod in queries to get more accurate results.
- Use sorting sort RecommendationWeight desc to get more urgent recommendations on top
Definitely interesting Intelligence Pack and I encourage you to try it. Happy searching.
Tip from Daniele Muscetta:
‘inconclusive’ means that check wasn’t able to complete, hence it can’t really tell for sure if you are affected by an issue, or not. it is a way to mitigate false positives. Often this might have to do with permissions required to collect some config point, check the runas account info in the documentation http://technet.microsoft.com/en-us/library/dn818161.aspx (we are updating this doc as there are a few more permissions currently not listed), and/or let me know if any specific check consistently does not work we can investigate – it’s like the previous alert rules – and escalate to the content owner.
I will be using my SQL monitoring account for this:
Another tip from me if you want to disable Advisor SQL monitoring for certain servers override this rule for them:
Cloud Administrator in Azure World 
‘inconclusive’ means that check wasn’t able to complete, hence it can’t really tell for sure if you are affected by an issue, or not. it is a way to mitigate false positives. Often this might have to do with permissions required to collect some config point, check the runas account info in the documentation http://technet.microsoft.com/en-us/library/dn818161.aspx (we are updating this doc as there are a few more permissions currently not listed), and/or let me know if any specific check consistently does not work we can investigate – it’s like the previous alert rules – and escalate to the content owner.