Hi Nguyen, Hoa •,
Thanks for your patience.
We have got the below reply from the internal team:
First, you can use the query below to list all the sub-assessments (aka Findings) for the mentioned recommendation by running this query:
securityresources
| where type == "microsoft.security/assessments/subassessments"
// Filter for the "SQL databases should have vulnerability findings resolved". That Guid is called "AssessmentKey", and uniquely identifies that recommendation
| where id has "82e20e14----------"
// projection of the properties you mentioned. Feel free to look around the original "properties" column for other information
| project CVE = properties.id, displayName = properties.displayName, description = properties.description, fullDatabaseId = properties.resourceDetails.id, fullServerId = properties.resourceDetails.NativeResourceId, additionalData = properties.additionalData
It will return all the sub-assessments for that recommendation for all resources.
In can you want to aggregate them by server or database, you can use the query below to see all the CVEs for each database. You can modify it to aggregate by server instead or make list of a different property:
securityresources
| where type == "microsoft.security/assessments/subassessments"
| where id has "82e20e14------------" // this filters for the "SQL databases should have vulnerability findings resolved". That guid is called "AssessmentKey", and uniqly identifies that recommendation
| project CVE = properties.id, displayName = properties.displayName, description = properties.description, fullDatabaseId = properties.resourceDetails.id, fullServerId = properties.resourceDetails.NativeResourceId, additionalData = properties.additionalData
| summarize make_list(CVE) by tostring(fullDatabaseId)
I hope this helps.
Let us know if you have more queries.
Thanks.