Queries for the SQLAssessmentRecommendation table

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