Queries for the SQLAssessmentRecommendation table

For information on using these queries in the Azure portal, see Log Analytics tutorial. For the REST API, see Query.

SQL Recommendations by Focus Area

Count all SQL reccomendations by focus area.

SQLAssessmentRecommendation
| summarize AggregatedValue = count() by FocusArea

SQL Recommendations by Computer

Count SQL recommendations with failed result by computer.

SQLAssessmentRecommendation
| where RecommendationResult == "Failed"
| summarize AggregatedValue = count() by Computer

SQL Recommendations by Instance

Count SQL recommendations with failed result by instance.

SQLAssessmentRecommendation
| where RecommendationResult == "Failed"
| summarize AggregatedValue = count() by SqlInstanceName

SQL Recommendations by Database

Count SQL recommendations with failed result by database.

SQLAssessmentRecommendation
| where RecommendationResult == "Failed"
| summarize AggregatedValue = count() by DatabaseName

SQL Recommendations by AffectedObjectType

Count SQL recommendations with failed result by affected object type.

SQLAssessmentRecommendation
| where RecommendationResult == "Failed"
| summarize AggregatedValue = count() by AffectedObjectType

How many times did each unique SQL Recommendation trigger?

Count SQL recommendations with failed result by recommendation.

SQLAssessmentRecommendation
| where RecommendationResult == "Failed"
| summarize AggregatedValue = count() by Recommendation

High priority SQL Assessment recommendations

Latest high priority security recommendation with result failed by recommendation Id.

SQLAssessmentRecommendation
| where FocusArea == 'Security and Compliance' and RecommendationResult == 'Failed' and RecommendationScore>=35
| summarize arg_max(TimeGenerated, *) by RecommendationId