resolve kql "Let" clause for Azure Resource Graph Explorer - Stack Overflow

admin管理员组

文章数量:1123858

I'm working on getting this kql query to work but unable to get the joins to work and it appears that you can't use "let" in Azure resource graph. I am able to pull a seperate query that give the category along with the amount of unhealthy resources and then I can get a query that give the max score and how much it can increase by but can't seem to get everything in a single query. I used copilot for this code but unfortunately ARGE does not allow to use the "let" code.

// Summarize control data
let controlsAssessmentsData = 
    securityresources
    | where type == "microsoft.security/securescores/securescorecontrols"
    | extend secureScoreName = extract("/providers/Microsoft.Security/secureScores/([^/]*)/", 1, id)
    | where secureScoreName == "ascScore"
    | extend environment = tostring(properties.environment)
    | extend scope = extract("(.*)/providers/Microsoft.Security/secureScores/", 1, id)
    | where environment in ("AWS", "Azure", "AzureDevOps", "DockerHub", "GCP", "GitHub", "GitLab", "JFrog")
    | extend controlDisplayName = tostring(properties.displayName)
    | extend controlName = name
    | extend assessmentKeys = extract_all("\"id\":\".*?/assessmentMetadata/([^\"]*)\"", tostring(properties.definition.properties.assessmentDefinitions))
    | extend notApplicableResourceCount = toint(properties.notApplicableResourceCount)
    | extend unhealthyResourceCount = toint(properties.unhealthyResourceCount)
    | extend healthyResourceCount = toint(properties.healthyResourceCount)
    | extend controlMaxScore = toint(properties.score.max)
    | extend currentScore = todouble(properties.score.current)
    | join kind=leftouter (
        securityresources 
        | where type == "microsoft.security/securescores" 
        | where name == "ascScore"
        | extend environment = tostring(properties.environment)
        | extend scopeMaxScore = toint(properties.score.max)
        | extend scopeWeight = toint(properties.weight)
        | parse id with scope "/providers/Microsoft.Security/secureScores" * 
        | where environment in ("AWS", "Azure", "AzureDevOps", "DockerHub", "GCP", "GitHub", "GitLab", "JFrog")
        | project scope, scopeWeight, scopeMaxScore, joinOn = 1
    ) on scope
    | extend potentialScoreIncrease = controlMaxScore - currentScore
    | project subscriptionId, resourceGroup, controlDisplayName, controlName, maxScore = controlMaxScore, currentScore, potentialScoreIncrease, status = properties.status.code, unhealthyResourceCount, healthyResourceCount, assessmentKeys, controlData = pack_all()
    | summarize assessmentKeys = any(assessmentKeys),
                controlDisplayName = any(controlDisplayName),
                notApplicableResourceCount = sum(notApplicableResourceCount),
                unhealthyResourceCount = sum(unhealthyResourceCount),
                healthyResourceCount = sum(healthyResourceCount),
                controlMaxScore = max(controlMaxScore),
                sumParentScopeWeight = max(sumParentScopeWeight),
                impactRatio = sum(iff(scopeMaxScore == 0, todouble(0), scopeWeight  / scopeMaxScore)),
                controlAggregatedCurrentScoreSum = sum(iff(scopeMaxScore == 0, todouble(0), scopeWeight * currentScore / scopeMaxScore)) by controlName
    | extend controlAggregatedMaxScoreSum = impactRatio * controlMaxScore
    | extend controlAggregatedCurrentScore = iff(controlAggregatedMaxScoreSum == 0, todouble(0), controlAggregatedCurrentScoreSum /  controlAggregatedMaxScoreSum) * controlMaxScore
    | extend potentialScoreIncrease = iff(sumParentScopeWeight == 0, todouble(0), (controlAggregatedMaxScoreSum - controlAggregatedCurrentScoreSum) / sumParentScopeWeight) * 100
    | project controlsAssessmentsData = pack_all(), controlMaxScore
    | extend assessmentKeys = controlsAssessmentsData.assessmentKeys
    | extend controlData = pack(
        "controlDisplayName", controlsAssessmentsData.controlDisplayName, 
        "controlName", controlsAssessmentsData.controlName, 
        "assessmentKeys", controlsAssessmentsData.assessmentKeys,
        "notApplicableResourceCount", controlsAssessmentsData.notApplicableResourceCount,
        "unhealthyResourceCount", controlsAssessmentsData.unhealthyResourceCount,
        "healthyResourceCount", controlsAssessmentsData.healthyResourceCount,
        "totalResourceCount", toint(controlsAssessmentsData.notApplicableResourceCount) + toint(controlsAssessmentsData.unhealthyResourceCount) + toint(controlsAssessmentsData.healthyResourceCount),
        "maxScore", controlsAssessmentsData.controlMaxScore,
        "currentScore", controlsAssessmentsData.controlAggregatedCurrentScore,
        "potentialScoreIncrease", controlsAssessmentsData.potentialScoreIncrease);

// Combine with security assessments
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
| join kind=leftouter (controlsAssessmentsData | project controlData, controlName) on controlName
| project 
    subscription,
    resourceGroup,
    resourceName,
    resourceType=tostring(properties.resourceDetails.ResourceType), 
    SystemID=rtags,
    recommendationSeverity=properties.metadata.severity, 
    recommendationStatus =properties.status.code,
    recommendationName=properties.displayName, 
    description=properties.metadata.description, 
    remediationDescription=properties.metadata.remediationDescription,    
    portalLink=properties.links.azurePortal,
    resourceId,
    controlData,
    unhealthyResourceCount,
    healthyResourceCount,
    controlMaxScore,
    currentScore

Hoping to get the categories along with the individual unhealthy resources. Please advise, Serge

If I could get this query to show all the tags per resource, I could alter the csv to present what I need in a pivot table

securityresources 
| where type == 'microsoft.security/assessments' 
//above section will provide all information regarding on resources with status on recommendation.
| join kind=leftouter  (resourcecontainers 
            | where type == 'microsoft.resources/subscriptions'
            | project subscriptionId, subscription=name)
        on subscriptionId
//above section will pull all the subscriptions along with the subID and create a column called subscription that will hold the actual name and not the ID
| extend resourceId = tolower(tostring(properties.resourceDetails.ResourceId))
| extend resourceTag = tags
//above will create a column all resourceId that will be mae of the resource ID from the assessments table
| join kind=leftouter  (resources | project resourceId=tolower(id), tags, resourceName=name) on resourceId
//above join pulls from the resources table and collects tags based on resource and resource ID and then does a join wiht assessments.
| project 
    subscription,
    resourceGroup,
    resourceName,
    resourceType=tostring(properties.resourceDetails.ResourceType), 
    tags,
    recommendationSeverity=properties.metadata.severity, 
    recommendationStatus =properties.status.code,
    recommendationName=properties.displayName, 
    description=properties.metadata.description, 
    remediationDescription=properties.metadata.remediationDescription,    
    portalLink=properties.links.azurePortal,
    resourceId

Appreciate any assistance...This query is for Azure Resource Graph.

I'm working on getting this kql query to work but unable to get the joins to work and it appears that you can't use "let" in Azure resource graph. I am able to pull a seperate query that give the category along with the amount of unhealthy resources and then I can get a query that give the max score and how much it can increase by but can't seem to get everything in a single query. I used copilot for this code but unfortunately ARGE does not allow to use the "let" code.

// Summarize control data
let controlsAssessmentsData = 
    securityresources
    | where type == "microsoft.security/securescores/securescorecontrols"
    | extend secureScoreName = extract("/providers/Microsoft.Security/secureScores/([^/]*)/", 1, id)
    | where secureScoreName == "ascScore"
    | extend environment = tostring(properties.environment)
    | extend scope = extract("(.*)/providers/Microsoft.Security/secureScores/", 1, id)
    | where environment in ("AWS", "Azure", "AzureDevOps", "DockerHub", "GCP", "GitHub", "GitLab", "JFrog")
    | extend controlDisplayName = tostring(properties.displayName)
    | extend controlName = name
    | extend assessmentKeys = extract_all("\"id\":\".*?/assessmentMetadata/([^\"]*)\"", tostring(properties.definition.properties.assessmentDefinitions))
    | extend notApplicableResourceCount = toint(properties.notApplicableResourceCount)
    | extend unhealthyResourceCount = toint(properties.unhealthyResourceCount)
    | extend healthyResourceCount = toint(properties.healthyResourceCount)
    | extend controlMaxScore = toint(properties.score.max)
    | extend currentScore = todouble(properties.score.current)
    | join kind=leftouter (
        securityresources 
        | where type == "microsoft.security/securescores" 
        | where name == "ascScore"
        | extend environment = tostring(properties.environment)
        | extend scopeMaxScore = toint(properties.score.max)
        | extend scopeWeight = toint(properties.weight)
        | parse id with scope "/providers/Microsoft.Security/secureScores" * 
        | where environment in ("AWS", "Azure", "AzureDevOps", "DockerHub", "GCP", "GitHub", "GitLab", "JFrog")
        | project scope, scopeWeight, scopeMaxScore, joinOn = 1
    ) on scope
    | extend potentialScoreIncrease = controlMaxScore - currentScore
    | project subscriptionId, resourceGroup, controlDisplayName, controlName, maxScore = controlMaxScore, currentScore, potentialScoreIncrease, status = properties.status.code, unhealthyResourceCount, healthyResourceCount, assessmentKeys, controlData = pack_all()
    | summarize assessmentKeys = any(assessmentKeys),
                controlDisplayName = any(controlDisplayName),
                notApplicableResourceCount = sum(notApplicableResourceCount),
                unhealthyResourceCount = sum(unhealthyResourceCount),
                healthyResourceCount = sum(healthyResourceCount),
                controlMaxScore = max(controlMaxScore),
                sumParentScopeWeight = max(sumParentScopeWeight),
                impactRatio = sum(iff(scopeMaxScore == 0, todouble(0), scopeWeight  / scopeMaxScore)),
                controlAggregatedCurrentScoreSum = sum(iff(scopeMaxScore == 0, todouble(0), scopeWeight * currentScore / scopeMaxScore)) by controlName
    | extend controlAggregatedMaxScoreSum = impactRatio * controlMaxScore
    | extend controlAggregatedCurrentScore = iff(controlAggregatedMaxScoreSum == 0, todouble(0), controlAggregatedCurrentScoreSum /  controlAggregatedMaxScoreSum) * controlMaxScore
    | extend potentialScoreIncrease = iff(sumParentScopeWeight == 0, todouble(0), (controlAggregatedMaxScoreSum - controlAggregatedCurrentScoreSum) / sumParentScopeWeight) * 100
    | project controlsAssessmentsData = pack_all(), controlMaxScore
    | extend assessmentKeys = controlsAssessmentsData.assessmentKeys
    | extend controlData = pack(
        "controlDisplayName", controlsAssessmentsData.controlDisplayName, 
        "controlName", controlsAssessmentsData.controlName, 
        "assessmentKeys", controlsAssessmentsData.assessmentKeys,
        "notApplicableResourceCount", controlsAssessmentsData.notApplicableResourceCount,
        "unhealthyResourceCount", controlsAssessmentsData.unhealthyResourceCount,
        "healthyResourceCount", controlsAssessmentsData.healthyResourceCount,
        "totalResourceCount", toint(controlsAssessmentsData.notApplicableResourceCount) + toint(controlsAssessmentsData.unhealthyResourceCount) + toint(controlsAssessmentsData.healthyResourceCount),
        "maxScore", controlsAssessmentsData.controlMaxScore,
        "currentScore", controlsAssessmentsData.controlAggregatedCurrentScore,
        "potentialScoreIncrease", controlsAssessmentsData.potentialScoreIncrease);

// Combine with security assessments
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
| join kind=leftouter (controlsAssessmentsData | project controlData, controlName) on controlName
| project 
    subscription,
    resourceGroup,
    resourceName,
    resourceType=tostring(properties.resourceDetails.ResourceType), 
    SystemID=rtags,
    recommendationSeverity=properties.metadata.severity, 
    recommendationStatus =properties.status.code,
    recommendationName=properties.displayName, 
    description=properties.metadata.description, 
    remediationDescription=properties.metadata.remediationDescription,    
    portalLink=properties.links.azurePortal,
    resourceId,
    controlData,
    unhealthyResourceCount,
    healthyResourceCount,
    controlMaxScore,
    currentScore

Hoping to get the categories along with the individual unhealthy resources. Please advise, Serge

If I could get this query to show all the tags per resource, I could alter the csv to present what I need in a pivot table

securityresources 
| where type == 'microsoft.security/assessments' 
//above section will provide all information regarding on resources with status on recommendation.
| join kind=leftouter  (resourcecontainers 
            | where type == 'microsoft.resources/subscriptions'
            | project subscriptionId, subscription=name)
        on subscriptionId
//above section will pull all the subscriptions along with the subID and create a column called subscription that will hold the actual name and not the ID
| extend resourceId = tolower(tostring(properties.resourceDetails.ResourceId))
| extend resourceTag = tags
//above will create a column all resourceId that will be mae of the resource ID from the assessments table
| join kind=leftouter  (resources | project resourceId=tolower(id), tags, resourceName=name) on resourceId
//above join pulls from the resources table and collects tags based on resource and resource ID and then does a join wiht assessments.
| project 
    subscription,
    resourceGroup,
    resourceName,
    resourceType=tostring(properties.resourceDetails.ResourceType), 
    tags,
    recommendationSeverity=properties.metadata.severity, 
    recommendationStatus =properties.status.code,
    recommendationName=properties.displayName, 
    description=properties.metadata.description, 
    remediationDescription=properties.metadata.remediationDescription,    
    portalLink=properties.links.azurePortal,
    resourceId

Appreciate any assistance...This query is for Azure Resource Graph.

• azure
• kql

Share Improve this question Follow edited 19 hours ago SergeT asked yesterday SergeTSergeT 1966 bronze badges 1

• 1 Are you trying to run it in Azure Resource Graph right! – Jahnavi Commented yesterday

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

let expression is a part of the KQL query statements and it is mainly used to simplify a complex expression into multiple parts, each of which is represented by a variable to continue with the query operations.

Refer MSDoc for more relevant information.

And as it is thrown an error, there are some limitations on working with the let expression in Azure Resource Graph explorer.

Firstly, when using joins operator with let expression to create the variables and combine the queries based on their type and sub resource type as a single query. This operation is not allowed to perform in graph explorer.

As a workaround to this, you can use Union operator available in KQL to combine the queries.

Once it's done, now use Summarise operator which,

Produces a table that aggregates the content of the input table.

You can also remove some parts of the query which is not required mainly to reduce the query partitions.

Saying that, I've modified your query accordingly to execute in my environment as shown below with join and summarise operators and worked as expected.

securityresources
| where type == "microsoft.security/assessments"
| extend controlName = name
| extend unhealthyResourceCount = toint(properties.unhealthyResourceCount)
| project controlName, unhealthyResourceCount
| join kind=leftouter (
    securityresources
    | where type == "microsoft.security/assessments/subassessments"
    | extend resourceId = tolower(tostring(properties.resourceDetails.ResourceId))
    | extend recommendationSeverity = tostring(properties.metadata.severity)
    | project resourceId, recommendationSeverity
) on $left.controlName == $right.resourceId
| summarize 
    totalUnhealthyResources = sum(unhealthyResourceCount),
    severities = make_list(recommendationSeverity)
    by controlName
| project controlName, totalUnhealthyResources, severities

Output:

本文标签： resolve kql quotLetquot clause for Azure Resource Graph ExplorerStack Overflow