0

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.

Peter Bons
  • 26,826
  • 4
  • 50
  • 74
Edwin Orina
  • 3
  • 1
  • please share the query which you have tried – Ikhtesam Afrin Aug 23 '23 at 11:14
  • I intend to join the output of these two: resourcecontainers | where type == 'microsoft.resources/subscriptions' | project subscriptionId, subscriptionName = name | join (resourcecontainers | where type == 'microsoft.resources/subscriptions/resourcegroups') on subscriptionId | project subscriptionName, resourceGroup, tags.owner | order by resourceGroup asc – Edwin Orina Aug 23 '23 at 12:03
  • And: securityresources | where type == 'microsoft.security/assessments' | extend resourceId=id, source=properties.resourceDetails.Source, recommendationName=properties.displayName, description=properties.metadata.description, remediationDescription=properties.metadata.remediationDescription, recommendationSeverity=properties.metadata.severity, portalLink=properties.links.azurePortal | project source,recommendationName, recommendationSeverity, description, remediationDescription,portalLink – Edwin Orina Aug 23 '23 at 12:05

1 Answers1

0

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
Peter Bons
  • 26,826
  • 4
  • 50
  • 74
  • Thanks Peter. However, the query does not work, the outcome I get from running the query you shared is : No Result – Edwin Orina Aug 23 '23 at 15:08
  • @EdwinOrina thats odd, I checked it agains my recommendations. I've updated my answer to use outer joins, what result does that give you? – Peter Bons Aug 23 '23 at 15:13
  • Looks good! However, it also brings recommendations for resourcetype subscription. I don't know if this is accurate, what's your thought on this? Thank you. – Edwin Orina Aug 23 '23 at 15:30
  • Yes, well, those might be of interest from a security perspective, like "Subscriptions should have a contact email address for security issues" is one of those subscription level recommendations. – Peter Bons Aug 23 '23 at 15:51
  • Under resourceid, what do i do such that it shows the resource name only? Thank you. – Edwin Orina Aug 23 '23 at 15:56
  • @EdwinOrina I've modified the join to the resources to include the resource name, see updated answer – Peter Bons Aug 23 '23 at 16:05
  • Thank you @Peter Bons, Much Appreciated! – Edwin Orina Aug 24 '23 at 07:00