KQL Search

Query Details

HomeAI ToolsDevice Query

3 EPM Insights

Query

securityresources
| where type == "microsoft.security/assessments"
| extend source = trim(' ', tolower(tostring(properties.resourceDetails.Source)))
                                        | extend resourceId = trim(' ', tolower(tostring(case(
                                                                                source =~ "azure", properties.resourceDetails.Id,
                                                                                source =~ "aws" and isnotempty(tostring(properties.resourceDetails.ConnectorId)), properties.resourceDetails.Id,
                                                                                source =~ "gcp" and isnotempty(tostring(properties.resourceDetails.ConnectorId)), properties.resourceDetails.Id,
                                                                                source =~ 'aws', properties.resourceDetails.AzureResourceId,
                                                                                source =~ 'gcp', properties.resourceDetails.AzureResourceId,
                                                                                extract('^(.+)/providers/Microsoft.Security/assessments/.+$',1,id)
                                                                                ))))
| extend status = trim(" ", tostring(properties.status.code))
| extend cause = trim(" ", tostring(properties.status.cause))
| extend resourcetype = trim(" ", tostring(properties.additionalData.ResourceType))
| extend assessmentKey = tostring(name)
| where assessmentKey == "d19d5a12-41e9-44e2-b7f5-ee2160f62d62" or assessmentKey == "8b0bd683-bcfe-4ab1-96b9-f15a60eaa89d"
| extend resourceId = tostring(properties.resourceDetails.Id)
| extend identityId = tostring(properties.additionalData.ResourceName)
| extend identityType = tostring(properties.additionalData.ResourceType)
| extend assessmentTitle = tostring(properties.displayName)        
| extend assessmentSev = tostring(properties.metadata.severity)
| extend portalUrl = tostring(properties.links.azurePortal)
| extend status=tostring(properties.status.code), resourceType = tostring(properties.additionalData.ResourceType)
| project assessmentKey, assessmentTitle, assessmentSev, resourceId, identityId, identityType, status, tenantId, portalUrl
| sort by identityId

Explanation

This query is designed to extract and organize specific security assessment information from a dataset of security resources. Here's a simplified breakdown of what it does:

  1. Filter for Assessments: It starts by filtering the dataset to only include resources of the type "microsoft.security/assessments".

  2. Normalize and Extract Data:

    • It normalizes the source field by converting it to lowercase and trimming spaces.
    • It determines the resourceId based on the source type (Azure, AWS, GCP) and other conditions.
    • It extracts and trims various properties like status, cause, resourcetype, and assessmentKey.

  3. Filter by Assessment Key: It further filters the data to include only assessments with specific keys: "d19d5a12-41e9-44e2-b7f5-ee2160f62d62" or "8b0bd683-bcfe-4ab1-96b9-f15a60eaa89d".

  4. Extract Additional Information: It extracts additional details such as:

    • identityId and identityType from the resource's additional data.
    • assessmentTitle and assessmentSev from the assessment's properties.
    • portalUrl for accessing the assessment in the Azure portal.

  5. Project Relevant Fields: It selects and organizes the relevant fields to display: assessmentKey, assessmentTitle, assessmentSev, resourceId, identityId, identityType, status, tenantId, and portalUrl.

  6. Sort the Results: Finally, it sorts the results by identityId.

In essence, this query is used to gather and display detailed information about specific security assessments, focusing on their identity, status, and severity, and providing a link to view them in the Azure portal.

Details

Thomas Naunheim profile picture

Thomas Naunheim

Released: June 4, 2025

Tables

securityresources

Keywords

SecurityResourcesAssessmentsPropertiesResourceDetailsSourceResourceIdStatusCodeCauseAdditionalDataResourceTypeAssessmentKeyNameDisplayNameMetadataSeverityLinksAzurePortalTenantId

Operators

securityresourceswhere==extendtrimtolowertostringcase=~andisnotemptyextractorprojectsort by

Actions