Azure Resource Graph sample queries for Azure Advisor

This page is a collection of Azure Resource Graph sample queries for Azure Advisor.

Sample queries

Potential cost savings from recommendations

This query calculates the monthly cost savings of Azure Advisor recommendations.

advisorresources
| where type =~ "microsoft.advisor/metadata"
| where tostring(properties.language)=="en"
| where tostring(properties.recommendationCategory)=="Cost"
| extend recommendationTypeId=tostring(properties.recommendationTypeId)
| project recommendationTypeId,
    metadataSolution=tostring(properties.label)
| join kind=inner (
    advisorresources
    | where type =~ "microsoft.advisor/recommendations"
    | extend recommendationTypeId=tostring(properties.recommendationTypeId)
    | extend
        resources=tostring(properties.resourceMetadata.resourceId),
        savings=todouble(properties.extendedProperties.savingsAmount),
        tracked=tobool(properties.tracked),
        recommendationSolution=tostring(properties.label),
        currency=tostring(properties.extendedProperties.savingsCurrency)
    | project recommendationTypeId,resources,savings,tracked,recommendationSolution,currency
) on recommendationTypeId
| extend solution = iff(tracked == true, recommendationSolution, metadataSolution)
| summarize
    dcount_resources=dcount(resources),
    sum_savings=bin(sum(savings),0.01)
    by solution,currency
| where sum_savings > 0
| project solution,dcount_resources,sum_savings,currency
| order by sum_savings desc

Azure CLI

az graph query -q "advisorresources | where type =~ "microsoft.advisor/metadata" | where tostring(properties.language)=="en" | where tostring(properties.recommendationCategory)=="Cost" | extend recommendationTypeId=tostring(properties.recommendationTypeId) | project recommendationTypeId,     metadataSolution=tostring(properties.label) | join kind=inner (     advisorresources | where type =~ "microsoft.advisor/recommendations" | extend recommendationTypeId=tostring(properties.recommendationTypeId) | extend         resources=tostring(properties.resourceMetadata.resourceId),         savings=todouble(properties.extendedProperties.savingsAmount),         tracked=tobool(properties.tracked),        recommendationSolution=tostring(properties.label),         currency=tostring(properties.extendedProperties.savingsCurrency) | project recommendationTypeId,resources,savings,tracked,recommendationSolution,currency ) on recommendationTypeId | extend solution = iff(tracked == true, recommendationSolution, metadataSolution) | summarize     dcount_resources=dcount(resources),     sum_savings=bin(sum(savings),0.01)     by solution,currency | where sum_savings > 0 | project solution,dcount_resources,sum_savings,currency | order by sum_savings desc"

Azure PowerShell

Search-AzGraph -Query "advisorresources | where type =~ "microsoft.advisor/metadata" | where tostring(properties.language)=="en" | where tostring(properties.recommendationCategory)=="Cost" | extend recommendationTypeId=tostring(properties.recommendationTypeId) | project recommendationTypeId,     metadataSolution=tostring(properties.label) | join kind=inner (     advisorresources | where type =~ "microsoft.advisor/recommendations" | extend recommendationTypeId=tostring(properties.recommendationTypeId) | extend         resources=tostring(properties.resourceMetadata.resourceId),         savings=todouble(properties.extendedProperties.savingsAmount),         tracked=tobool(properties.tracked),        recommendationSolution=tostring(properties.label),         currency=tostring(properties.extendedProperties.savingsCurrency) | project recommendationTypeId,resources,savings,tracked,recommendationSolution,currency ) on recommendationTypeId | extend solution = iff(tracked == true, recommendationSolution, metadataSolution) | summarize     dcount_resources=dcount(resources),     sum_savings=bin(sum(savings),0.01)     by solution,currency | where sum_savings > 0 | project solution,dcount_resources,sum_savings,currency | order by sum_savings desc"

Portal

• Azure portal: portal.azure.com
• Azure Government portal: portal.azure.us
• Microsoft Azure operated by 21Vianet portal: portal.azure.cn

Next steps

• Learn more about the query language.
• Learn more about how to explore resources.