I have two queries and would like to join them. One of the queries already has a join so I’m getting confused on the process. At the end I need a complete security report that will show all the main parts. I need to include a specific resource tag and I’m trying to get Subscription rather than the ID, along with resource and resource group.
KQL:
SecurityResources
| where type == 'microsoft.security/assessments'
| extend resourceId=id,
recommendationId=name,
recommendationName=properties.displayName,
source=properties.resourceDetails.Source,
recommendationState=properties.status.code,
description=properties.metadata.description,
assessmentType=properties.metadata.assessmentType,
remediationDescription=properties.metadata.remediationDescription,
policyDefinitionId=properties.metadata.policyDefinitionId,
implementationEffort=properties.metadata.implementationEffort,
recommendationSeverity=properties.metadata.severity,
category=properties.metadata.categories,
userImpact=properties.metadata.userImpact,
threats=properties.metadata.threats,
portalLink=properties.links.azurePortal
| project tenantId, subscriptionId, resourceId, recommendationName, recommendationId, recommendationState, recommendationSeverity, description, remediationDescription, assessmentType, policyDefinitionId, implementationEffort, userImpact, category, threats, source, portalLink
Join KQL:
resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId, subscriptionName = name
| join (resourcecontainers
| where type == 'microsoft.resources/subscriptions/resourcegroups')
on subscriptionId
| project subscriptionName, resourceGroup, tags.SystemID
Hopefully this is enough information. I’m trying to use copilot in vs code but keep getting no where.
Here is what I tried:
// List Microsoft Defender recommendations for security assessment and include resource tags that have SystemID.
SecurityResources
| where type == 'microsoft.security/assessments'
| extend resourceId=id,
recommendationId=name,
recommendationName=properties.displayName,
source=properties.resourceDetails.Source,
recommendationState=properties.status.code,
description=properties.metadata.description,
assessmentType=properties.metadata.assessmentType,
remediationDescription=properties.metadata.remediationDescription,
policyDefinitionId=properties.metadata.policyDefinitionId,
implementationEffort=properties.metadata.implementationEffort,
recommendationSeverity=properties.metadata.severity,
category=properties.metadata.categories,
userImpact=properties.metadata.userImpact,
threats=properties.metadata.threats,
portalLink=properties.links.azurePortal
| project tenantId, subscriptionId, resourceId, recommendationName, recommendationId, recommendationState, recommendationSeverity, description, remediationDescription, assessmentType, policyDefinitionId, implementationEffort, userImpact, category, threats, source, portalLink
| join kind=leftouter (
resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId, subscriptionName = name
| join kind=leftouter (
resourcecontainers
| where type == 'microsoft.resources/subscriptions/resourcegroups'
) on subscriptionId
| project subscriptionId, subscriptionName, resourceGroup, systemID = tostring(tags.SystemID)
| project subscriptionId, subscriptionName, resourceGroup, systemID = tostring(tags.SystemID)
) on subscriptionId
| project tenantId, subscriptionId, subscriptionName, resourceId, recommendationName, recommendationId, recommendationState, recommendationSeverity, description, remediationDescription, assessmentType, policyDefinitionId, implementationEffort, userImpact, category, threats, source, portalLink, systemID
3
I was able to correct:
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.SystemID, 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,
SystemID=rtags
```