Kusto Query - Get a list all Security Recommendations groupedby Resource Groups

Question

Kusto Query - Get a list all Security Recommendations groupedby Resource Groups, via Azure Graph Explorer.

Link the types : type == 'microsoft.resources/subscriptions/resourcegroups' and type == 'microsoft.security/assessments'

Get a report with : subscriptionName, resourceGroup, resource type, tags.owner, recommendationName, recommendationSeverity, description, remediationDescription,portalLink.

Answer 1

To get the tags of a resource, you need to join with the resources table. For the subscription name you need the resourcecontainers table. This should work:

securityresources 
| where type == 'microsoft.security/assessments' 
| join kind=leftouter  (resourcecontainers 
            | where type == 'microsoft.resources/subscriptions'
            | project subscriptionId, subscription=name)
        on subscriptionId
| extend resourceId = tolower(tostring(properties.resourceDetails.ResourceId))
| join kind=leftouter  (resources | project resourceId=tolower(id), rtags=tags, resourceName=name) on resourceId
| project 
    subscription,
    resourceGroup,
    resourceType=tostring(properties.resourceDetails.ResourceType), 
    recommendationName=properties.displayName, 
    description=properties.metadata.description, 
    remediationDescription=properties.metadata.remediationDescription, 
    recommendationSeverity=properties.metadata.severity, 
    portalLink=properties.links.azurePortal,
    resourceId,
    resourceName,
    owner=rtags.owner