Forum Discussion
Null Values When looking for Security Scores
Hello everyone,
I'm seeking assistance with a KQL code I've drafted to extract information from Defender for Cloud. As this is one of my initial attempts at writing such code, I'm not an expert in the field. I welcome any feedback or suggestions you may have.
// Regulatory compliance CSV report query for standard "Microsoft cloud security benchmark"
// Change the 'complianceStandardId' column condition to select a different standard
securityresources
| where type == "microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols/regulatorycomplianceassessments"
| extend scope = properties.scope
| where isempty(scope) or scope in~("Subscription", "MultiCloudAggregation")
| parse id with * "regulatoryComplianceStandards/" complianceStandardId "/regulatoryComplianceControls/" complianceControlId "/regulatoryComplianceAssessments" *
| extend complianceStandardId = replace("-", " ", complianceStandardId)
| where complianceStandardId == "Microsoft cloud security benchmark"
| extend failedResources = toint(properties.failedResources), passedResources = toint(properties.passedResources), skippedResources = toint(properties.skippedResources)
| where failedResources + passedResources + skippedResources > 0 or properties.assessmentType == "MicrosoftManaged"
| join kind = leftouter(
securityresources
| where type == "microsoft.security/assessments") on subscriptionId, name
| extend scope = properties.scope
| where isempty(scope) or scope in~("Subscription", "MultiCloudAggregation")
| extend complianceState = tostring(properties.state)
| extend resourceSource = tolower(tostring(properties1.resourceDetails.Source))
| extend recommendationId = iff(isnull(id1) or isempty(id1), id, id1)
| extend resourceId = trim(' ', tolower(tostring(case(resourceSource =~ 'azure', properties1.resourceDetails.Id,
resourceSource =~ 'gcp', properties1.resourceDetails.GcpResourceId,
resourceSource =~ 'aws' and isnotempty(tostring(properties1.resourceDetails.ConnectorId)), properties1.resourceDetails.Id,
resourceSource =~ 'aws', properties1.resourceDetails.AwsResourceId,
extract('^(.+)/providers/Microsoft.Security/assessments/.+$',1,recommendationId)))))
| extend regexResourceId = extract_all(@"/providers/[^/]+(?:/([^/]+)/[^/]+(?:/[^/]+/[^/]+)?)?/([^/]+)/([^/]+)$", resourceId)[0]
| extend resourceType = iff(resourceSource =~ "aws" and isnotempty(tostring(properties1.resourceDetails.ConnectorId)), tostring(properties1.additionalData.ResourceType), iff(regexResourceId[1] != "", regexResourceId[1], iff(regexResourceId[0] != "", regexResourceId[0], "subscriptions")))
| extend resourceName = tostring(regexResourceId[2])
| extend recommendationName = name
| extend recommendationDisplayName = tostring(iff(isnull(properties1.displayName) or isempty(properties1.displayName), properties.description, properties1.displayName))
| extend description = tostring(properties1.metadata.description)
| extend remediationSteps = tostring(properties1.metadata.remediationDescription)
| extend severity = tostring(properties1.metadata.severity)
| extend azurePortalRecommendationLink = tostring(properties1.links.azurePortal)
| mvexpand statusPerInitiative = properties1.statusPerInitiative
| extend expectedInitiative = statusPerInitiative.policyInitiativeName =~ "ASC Default"
| summarize arg_max(expectedInitiative, *) by complianceControlId, recommendationId
| extend state = iff(expectedInitiative, tolower(statusPerInitiative.assessmentStatus.code), tolower(properties1.status.code))
| extend notApplicableReason = iff(expectedInitiative, tostring(statusPerInitiative.assessmentStatus.cause), tostring(properties1.status.cause))
| project-away expectedInitiative
| project complianceStandardId, complianceControlId, complianceState, subscriptionId, resourceGroup = resourceGroup1 ,resourceType, resourceName, resourceId, recommendationId, recommendationName, recommendationDisplayName, description, remediationSteps, severity, state, notApplicableReason, azurePortalRecommendationLink
| join kind = leftouter (
securityresources
| where type == "microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols"
| parse id with * "regulatoryComplianceStandards/" complianceStandardId "/regulatoryComplianceControls/" *
| extend complianceStandardId = replace("-", " ", complianceStandardId)
| where complianceStandardId == "Microsoft cloud security benchmark"
| where properties.state != "Unsupported"
| extend controlName = tostring(properties.description)
| project controlId = name, controlName
| distinct controlId, controlName
) on $right.controlId == $left.complianceControlId
| project-away controlId
| distinct *
| order by complianceControlId asc, recommendationId asc
// Appended Section:
| union (
securityresources
| where type == 'microsoft.security/securescores'
| extend percentageScore=properties.score.percentage,
currentScore=properties.score.current,
maxScore=properties.score.max,
weight=properties.weight
| project tenantId, subscriptionId, percentageScore, currentScore, maxScore, weight
)
Thanks!!!
Thanks!!!
No RepliesBe the first to reply