Forum Discussion
snteran
Jan 18, 2025Copper Contributor
KQL Secure score controls and Assessments
I have a query that is working but is not producing what I need.
a query that will combine the Recommedation categories( 13 listed under the Classic View in recommendations) and the individual assessments associated to those categories:
securityresources | where type == "microsoft.security/securescores/securescorecontrols" | extend category_name = tostring(properties.displayName) //category name | extend Tenant_Id=tostring(tenantId) | extend healthy = properties.healthyResourceCount | extend unhealthy = properties.unhealthyResourceCount | extend notApplicable = properties.notApplicableResourceCount | extend score = properties.score | extend scr= parse_json(score) | project category_name, healthy, unhealthy, notApplicable, CurrentScore=scr.current, MaxScore=scr.max, Tenant_Id | join ( securityresources | where type == "microsoft.security/assessments" | extend assessment_name = tostring(properties.displayName) //assessment name | extend Tenant_Id=tostring(tenantId) | extend resourceName = properties.resourceDetails.ResourceName | extend status = properties.status.code | extend metadata = properties.metadata | extend severity = metadata.severity | project assessment_name, resourceName, status, severity, Tenant_Id ) on Tenant_Id | project category_name, assessment_name, resourceName, status, severity, healthy, unhealthy, notApplicable, CurrentScore, MaxScore,Tenant_Id
This is a work in progress script, I do get a valid script but I know it is not working like I need it to work. For example, when I run this script, I get for "assessment_name: EDR solution should be installed on Virtual Machines" but for the "category_name" I get "Restrict unauthorized network access". It should be category_name = Enable endpoint protection. I'm trying to find a valid join field but not getting it correctly. Perhaps I need to add anothere "Type" but I'm not sure which.
Please advise, Serge
No RepliesBe the first to reply