Using searches in OMS to identify what is assessed in the SQL assessment

Microsoft OMS provides an excellent best practices analyzer (SQL Assessment) for SQL server which is easy to install and quickly gain insights from for your SQL servers. The top level tile is shown below.

This expands into a series of views which show the underlying recommendations based upon various top level areas (Security and Compliance, Availability and Business Continuity, Performance and Scalability, Upgrade, Migration and Deployment, Operations and Monitoring, Change and Configuration Management):

Each of these in turn provide a detailed drill down with explanations of the recommendations including the suggested actions, prioritization guidance, affected objects, context and a link to learn more.

Recently I had a request to get a list of the types of best practices which are provided by Microsoft OMS for SQL server. If we look at our top level tile, we expect to have over 100 recommendations which are being assessed as part of this process (7+12+85=105). I reached out to Microsoft to see if there was documentation for what these best practice are and they provided a great way to see what these are from the OMS functionality itself. If you want to see what the SQL Assessment solution is checking and you have it added in your workspace, use the following query to get a list of all of the recommendations from the Log Search area:

SQLAssessmentRecommendation | summarize arg_max(TimeGenerated, *) by RecommendationId | project Recommendation, Description, FocusArea, ActionArea

Note: As this assessment is scheduled to run once a week you may need to change the duration from the default of 1 day to 7 days as shown below.

The results are the list of recommendations in the SQL Assessment (by default in a list view):

By changing this query to sort on the FocusArea and the ActionArea and switching this to the Table option we get a more intuitive set of results: (matching the total of 105 we are expecting)

SQLAssessmentRecommendation | summarize arg_max(TimeGenerated, *) by RecommendationId | sort by FocusArea asc, ActionArea asc | project Recommendation, Description, FocusArea, ActionArea

From here we have a couple of options for this data – if we have activated the public preview for PowerBI we can export this information to PowerBI (see details available at http://blogs.catapultsystems.com/cfuller/archive/2016/03/29/using-power-bi-and-oms-for-security-dashboards-and-reports/). However for this example, the option to export data to Excel should be sufficient. To do this we just click on the Excel button in the Log Search area:

This exports the data into a CSV file which we can then update as needed. An example of this is shown below:

Technical note: Due to nature of data stored in solr, dedup command may not work for all the available types. It does work and been tested for all assessment types. TechNet documentation on dedup command is available at: https://azure.microsoft.com/en-us/documentation/articles/log-analytics-search-reference/#dedup

Thank you to Sachin with Microsoft who provided me with the approach and the original search criteria which made this all possible!

Leave a Reply