Get the full list of Defender sub assessments given an assessment?

Question

We have multiple subscriptions with hundreds of different Azure resource types.
I would like to work to remediate the assessments and sub assessments found on the sql server, Azure SQL and Azure SQL MI. The portal shows about 2K sub assessments on the hundreds sql related resources. Thus I definitely need some type of organization and prioritization for remediation.

I have a resource graph Explorer query to list all Azure resources that are marked with a security check/recommendation/assessment "SQL databases should have vulnerability findings resolved". The query is attached below.

BUT I need to drill deeper into the sub assessments. Given that one recommendation/assessments, there are many findings/sub assessments with affected azure resources.

Q: How do I get a full list of findings/sub assessments for my resources with the Vulnerability number such as VA2018, sub assessment name, remediation, risk level so that I can group them into logical work categories?

An example is I would like to group all "findings/sub assessments" surrounding the GUEST login as in grid below and create a work item for the flagged databases. The query output should have a contains clause such as "GUEST" as search filter and return the list the VAxxxx, the finding name, the risk level besides the recommendation name, the subscriptionName and ResourceName that I currently have on the assessment query.
Thank you very much for your time and assistance.

|VA1020|database|Database user GUEST
should not be a member of any role|High||| | -------- | -------- | -------- | -------- | -------- | -------- | |VA1020|database|Database user GUEST should not be a member of any role|High| | | |VA1096|database|Principal GUEST should not be granted permissions in the database|Low| | | |VA1097|database|Principal GUEST should not be granted permissions on objects or columns|Low| | | |VA1099|database|GUEST user should not be granted permissions on database securables|Low|||

securityresources
| where type == "microsoft.security/assessments" 
| extend name = properties.displayName 
| extend resourceDetails = properties.resourceDetails 
| where name contains "SQL databases should have vulnerability findings resolved" and resourceDetails contains "sqlserver-"
| join kind=leftouter (   resourcecontainers | where type == "microsoft.resources/subscriptions" 
| extend resolvedSubId = tostring(split(id, '/', 2)[0]), subscriptionName = name | project resolvedSubId, subscriptionName ) on $left.subscriptionId == $right.resolvedSubId 
| project name, resourceDetails, subscriptionName
| extend resourname= parse_json(resourceDetails)
| project name,subscriptionName,ResourceName=resourname.ResourceName, ResourceID=resourname.ResourceId
| sort by subscriptionName, tostring(ResourceName )

Answer 1

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.