Consolidating SQL IAAS On-Demand Assessment Result from multiple servers

KQL

let selectedCategories = dynamic([]);
let selectedTotSev = dynamic([]);
SqlAssessment_CL
| where _ResourceId startswith “/subscriptions/5b300c58-01eb-44b3-8e06-a74c2cc6e873/resourceGroups/SQLAlwaysOn/providers/Microsoft.Compute/virtualMachines”
and TimeGenerated > ago(30d)
| extend asmt = parse_csv(RawData)
| extend AsmtId=tostring(asmt[1]), CheckId=tostring(asmt[2]), DisplayString=asmt[3], Description=tostring(asmt[4]), HelpLink=asmt[5], TargetType=case(asmt[6] == 1, “Server”, asmt[6] == 2, “Database”, “”), TargetName=tostring(asmt[7]),
Severity=case(asmt[8] == 30, “High”, asmt[8] == 20, “Medium”, asmt[8] == 10, “Low”, asmt[8] == 0, “Information”, asmt[8]==1, “Warning”, asmt[8]==2, “Critical”, “Passed”), Message=tostring(asmt[9]), TagsArr=split(tostring(asmt[10]), “,”), Sev = toint(asmt[8])
| where AsmtId !=”NULL”
and (set_has_element(dynamic([‘*’]), CheckId) or “‘*'” == “‘*'”)
and (set_has_element(dynamic([‘*’]), TargetName) or “‘*'” == “‘*'”)
and set_has_element(dynamic([30,20,10,0]), Sev)
and (array_length(set_intersect(TagsArr, dynamic([‘*’]))) > 0 or “‘*'” == “‘*'”)
and (CheckId == ”” and Sev == 0 or “”” == “””)
| project TargetType, TargetName, Severity, Message, Tags=strcat_array(array_slice(TagsArr, 1, -1), ‘,’), CheckId, Description, HelpLink = tostring(HelpLink), SeverityCode = toint(Sev)
| order by TargetName,SeverityCode desc, TargetType desc, TargetName asc
| project-away SeverityCode

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s