KQL Search

Query Details

HomeAI ToolsDevice Query

Xdr Alerts Related Ciem Assessment

Query

SecurityAlert
| where TimeGenerated >ago(30d)
| mv-expand parse_json(Entities)
| where Entities.Type == "oauth-application"
| extend PrincipalId = tolower(Entities.OAuthObjectId)
| distinct AlertName, AlertDescription = Description, AlertSeverity, AlertStatus = Status, AlertRemediationSteps = RemediationSteps, PrincipalId
| join hint.remote=left (arg("").securityresources | where type =~ "microsoft.security/assessments/subassessments"
        | project PrincipalId=tostring(properties.displayName),
            AssessmentDescription=tostring(properties.description),
            AssessmentRemediation=tostring(properties.remediation),
            AssessmentSeverity=tostring(properties.status.severity),
            AssessmentStatus=tostring(properties.status.code),
            AssessmentAdditionalData=tostring(properties.additionalData),
            AssessmentScopeResourceId = tostring(properties.resourceDetails.id)
    ) on PrincipalId
| project-away PrincipalId1

Explanation

This query retrieves security alerts from the past 30 days and expands the JSON entities. It filters for entities of type "oauth-application" and converts the OAuth object ID to lowercase. It then selects distinct values for alert name, description, severity, status, remediation steps, and principal ID. It performs a left join with a remote hint on the security resources, filtering for assessments and subassessments. It projects the principal ID, assessment description, remediation, severity, status, additional data, and scope resource ID. Finally, it removes the PrincipalId1 column from the results.

Details

Thomas Naunheim profile picture

Thomas Naunheim

Released: January 7, 2024

Tables

SecurityAlertarg("").securityresources

Keywords

SecurityAlert,TimeGenerated,Entities,Type,oauth-application,PrincipalId,AlertName,AlertDescription,Description,AlertSeverity,AlertStatus,AlertRemediationSteps,RemediationSteps,AlertRemediationSteps,PrincipalId,AlertName,AlertDescription,Description,AlertSeverity,AlertStatus,AlertRemediationSteps,RemediationSteps,AlertRemediationSteps,PrincipalId,hint.remote,arg,securityresources,type,microsoft.security/assessments/subassessments,PrincipalId,properties.displayName,AssessmentDescription,properties.description,AssessmentRemediation,properties.remediation,AssessmentSeverity,properties.status.severity,AssessmentStatus,properties.status.code,AssessmentAdditionalData,properties.additionalData,AssessmentScopeResourceId,properties.resourceDetails.id,PrincipalId1

Operators

whereagomv-expandparse_jsonextenddistinctjoinhint.remoteargprojecttolower=~tostringproject-away

Actions