Azure Resource Graph sample queries by category
This page is a collection of Azure Resource Graph sample queries grouped by general and service categories. To jump to a specific category, use the links on the top of the page. Otherwise, use Ctrl-F to use your browser's search feature.
Azure Advisor
Get cost savings summary from Azure Advisor
This query summarizes the cost savings of each Azure Advisor recommendation.
AdvisorResources
| where type == 'microsoft.advisor/recommendations'
| where properties.category == 'Cost'
| extend
resources = tostring(properties.resourceMetadata.resourceId),
savings = todouble(properties.extendedProperties.savingsAmount),
solution = tostring(properties.shortDescription.solution),
currency = tostring(properties.extendedProperties.savingsCurrency)
| summarize
dcount(resources),
bin(sum(savings), 0.01)
by solution, currency
| project solution, dcount_resources, sum_savings, currency
| order by sum_savings desc
az graph query -q "AdvisorResources | where type == 'microsoft.advisor/recommendations' | where properties.category == 'Cost' | extend resources = tostring(properties.resourceMetadata.resourceId), savings = todouble(properties.extendedProperties.savingsAmount), solution = tostring(properties.shortDescription.solution), currency = tostring(properties.extendedProperties.savingsCurrency) | summarize dcount(resources), bin(sum(savings), 0.01) by solution, currency | project solution, dcount_resources, sum_savings, currency | order by sum_savings desc"
Azure App Service
List Azure App Service TLS version
List an Azure App Service's minimum Transport Layer Security (TLS) version for incoming requests to a web app.
AppServiceResources
| where type =~ 'microsoft.web/sites/config'
| project id, name, properties.MinTlsVersion
az graph query -q "AppServiceResources | where type =~ 'microsoft.web/sites/config' | project id, name, properties.MinTlsVersion"
Azure Arc
Get enabled resource types for Azure Arc-enabled custom locations
Provides a list of enabled resource types for Azure Arc-enabled custom locations.
ExtendedLocationResources
| where type == 'microsoft.extendedlocation/customlocations/enabledresourcetypes'
az graph query -q "ExtendedLocationResources | where type == 'microsoft.extendedlocation/customlocations/enabledresourcetypes'"
List Azure Arc-enabled custom locations with VMware or SCVMM enabled
Provides a list of all Azure Arc-enabled custom locations that have either VMware or SCVMM resource types enabled.
Resources
| where type =~ 'microsoft.extendedlocation/customlocations' and properties.provisioningState =~ 'succeeded'
| extend clusterExtensionIds=properties.clusterExtensionIds
| mvexpand clusterExtensionIds
| extend clusterExtensionId = tolower(clusterExtensionIds)
| join kind=leftouter(
ExtendedLocationResources
| where type =~ 'microsoft.extendedlocation/customLocations/enabledResourcetypes'
| project clusterExtensionId = tolower(properties.clusterExtensionId), extensionType = tolower(properties.extensionType)
| where extensionType in~ ('microsoft.scvmm','microsoft.vmware')
) on clusterExtensionId
| where extensionType in~ ('microsoft.scvmm','microsoft.vmware')
| summarize virtualMachineKindsEnabled=make_set(extensionType) by id,name,location
| sort by name asc
az graph query -q "Resources | where type =~ 'microsoft.extendedlocation/customlocations' and properties.provisioningState =~ 'succeeded' | extend clusterExtensionIds=properties.clusterExtensionIds | mvexpand clusterExtensionIds | extend clusterExtensionId = tolower(clusterExtensionIds) | join kind=leftouter( ExtendedLocationResources | where type =~ 'microsoft.extendedlocation/customLocations/enabledResourcetypes' | project clusterExtensionId = tolower(properties.clusterExtensionId), extensionType = tolower(properties.extensionType) | where extensionType in~ ('microsoft.scvmm','microsoft.vmware') ) on clusterExtensionId | where extensionType in~ ('microsoft.scvmm','microsoft.vmware') | summarize virtualMachineKindsEnabled=make_set(extensionType) by id,name,location | sort by name asc"
Azure Arc-enabled Kubernetes
List all Azure Arc-enabled Kubernetes clusters with Azure Monitor extension
Returns the connected cluster ID of each Azure Arc-enabled Kubernetes cluster that has the Azure Monitor extension installed.
KubernetesConfigurationResources
| where type == 'microsoft.kubernetesconfiguration/extensions'
| where properties.ExtensionType == 'microsoft.azuremonitor.containers'
| parse id with connectedClusterId '/providers/Microsoft.KubernetesConfiguration/Extensions' *
| project connectedClusterId
az graph query -q "KubernetesConfigurationResources | where type == 'microsoft.kubernetesconfiguration/extensions' | where properties.ExtensionType == 'microsoft.azuremonitor.containers' | parse id with connectedClusterId '/providers/Microsoft.KubernetesConfiguration/Extensions' * | project connectedClusterId"
List all Azure Arc-enabled Kubernetes clusters without Azure Monitor extension
Returns the connected cluster ID of each Azure Arc-enabled Kubernetes cluster that is missing the Azure Monitor extension.
Resources
| where type =~ 'Microsoft.Kubernetes/connectedClusters' | extend connectedClusterId = tolower(id) | project connectedClusterId
| join kind = leftouter
(KubernetesConfigurationResources
| where type == 'microsoft.kubernetesconfiguration/extensions'
| where properties.ExtensionType == 'microsoft.azuremonitor.containers'
| parse tolower(id) with connectedClusterId '/providers/microsoft.kubernetesconfiguration/extensions' *
| project connectedClusterId
) on connectedClusterId
| where connectedClusterId1 == ''
| project connectedClusterId
az graph query -q "Resources | where type =~ 'Microsoft.Kubernetes/connectedClusters' | extend connectedClusterId = tolower(id) | project connectedClusterId | join kind = leftouter (KubernetesConfigurationResources | where type == 'microsoft.kubernetesconfiguration/extensions' | where properties.ExtensionType == 'microsoft.azuremonitor.containers' | parse tolower(id) with connectedClusterId '/providers/microsoft.kubernetesconfiguration/extensions' * | project connectedClusterId ) on connectedClusterId | where connectedClusterId1 == '' | project connectedClusterId"
List all Azure Arc-enabled Kubernetes resources
Returns a list of each Azure Arc-enabled Kubernetes cluster and relevant metadata for each cluster.
Resources
| project id, subscriptionId, location, type, properties.agentVersion, properties.kubernetesVersion, properties.distribution, properties.infrastructure, properties.totalNodeCount, properties.totalCoreCount
| where type =~ 'Microsoft.Kubernetes/connectedClusters'
az graph query -q "Resources | project id, subscriptionId, location, type, properties.agentVersion, properties.kubernetesVersion, properties.distribution, properties.infrastructure, properties.totalNodeCount, properties.totalCoreCount | where type =~ 'Microsoft.Kubernetes/connectedClusters'"
List all ConnectedClusters and ManagedClusters that contain a Flux Configuration
Returns the connectedCluster and managedCluster Ids for clusters that contain at least one fluxConfiguration.
resources
| where type =~ 'Microsoft.Kubernetes/connectedClusters' or type =~ 'Microsoft.ContainerService/managedClusters' | extend clusterId = tolower(id) | project clusterId
| join
( kubernetesconfigurationresources
| where type == 'microsoft.kubernetesconfiguration/fluxconfigurations'
| parse tolower(id) with clusterId '/providers/microsoft.kubernetesconfiguration/fluxconfigurations' *
| project clusterId
) on clusterId
| project clusterId
az graph query -q "resources | where type =~ 'Microsoft.Kubernetes/connectedClusters' or type =~ 'Microsoft.ContainerService/managedClusters' | extend clusterId = tolower(id) | project clusterId | join ( kubernetesconfigurationresources | where type == 'microsoft.kubernetesconfiguration/fluxconfigurations' | parse tolower(id) with clusterId '/providers/microsoft.kubernetesconfiguration/fluxconfigurations' * | project clusterId ) on clusterId | project clusterId"
List All Flux Configurations that Are in a Non-Compliant State
Returns the fluxConfiguration Ids of configurations that are failing to sync resources on the cluster.
kubernetesconfigurationresources
| where type == 'microsoft.kubernetesconfiguration/fluxconfigurations'
| where properties.complianceState == 'Non-Compliant'
| project id
az graph query -q "kubernetesconfigurationresources | where type == 'microsoft.kubernetesconfiguration/fluxconfigurations' | where properties.complianceState == 'Non-Compliant' | project id"
Azure Arc-enabled servers
Get count and percentage of Arc-enabled servers by domain
This query summarizes the domainName property on Azure Arc-enabled servers and uses a calculation with bin to create a Pct column for the percent of Arc-enabled servers per domain.
Resources
| where type == 'microsoft.hybridcompute/machines'
| project domain=tostring(properties.domainName)
| summarize Domains=make_list(domain), TotalMachineCount=sum(1)
| mvexpand EachDomain = Domains
| summarize PerDomainMachineCount = count() by tostring(EachDomain), TotalMachineCount
| extend Pct = 100 * bin(todouble(PerDomainMachineCount) / todouble(TotalMachineCount), 0.001)
az graph query -q "Resources | where type == 'microsoft.hybridcompute/machines' | project domain=tostring(properties.domainName) | summarize Domains=make_list(domain), TotalMachineCount=sum(1) | mvexpand EachDomain = Domains | summarize PerDomainMachineCount = count() by tostring(EachDomain), TotalMachineCount | extend Pct = 100 * bin(todouble(PerDomainMachineCount) / todouble(TotalMachineCount), 0.001)"
List all extensions installed on an Azure Arc-enabled server
First, this query uses project on the hybrid machine resource type to get the ID in uppercase (toupper()), get the computer name, and the operating system running on the machine. Getting the resource ID in uppercase is a good way to prepare to join to another property. Then, the query uses join with kind as leftouter to get extensions by matching an uppercase substring of the extension ID. The portion of the ID before /extensions/<ExtensionName> is the same format as the hybrid machine ID, so we use this property for the join. summarize is then used with make_list on the name of the virtual machine extension to combine the name of each extension where id, OSName, and ComputerName are the same into a single array property. Lastly, we order by lowercase OSName with asc. By default, order by is descending.
Resources
| where type == 'microsoft.hybridcompute/machines'
| project
id,
JoinID = toupper(id),
ComputerName = tostring(properties.osProfile.computerName),
OSName = tostring(properties.osName)
| join kind=leftouter(
Resources
| where type == 'microsoft.hybridcompute/machines/extensions'
| project
MachineId = toupper(substring(id, 0, indexof(id, '/extensions'))),
ExtensionName = name
) on $left.JoinID == $right.MachineId
| summarize Extensions = make_list(ExtensionName) by id, ComputerName, OSName
| order by tolower(OSName) asc
az graph query -q "Resources | where type == 'microsoft.hybridcompute/machines' | project id, JoinID = toupper(id), ComputerName = tostring(properties.osProfile.computerName), OSName = tostring(properties.osName) | join kind=leftouter( Resources | where type == 'microsoft.hybridcompute/machines/extensions' | project MachineId = toupper(substring(id, 0, indexof(id, '/extensions'))), ExtensionName = name ) on \$left.JoinID == \$right.MachineId | summarize Extensions = make_list(ExtensionName) by id, ComputerName, OSName | order by tolower(OSName) asc"
List Arc-enabled servers not running latest released agent version
This query returns all Arc-enabled servers running an outdated version of the Connected Machine agent. Agents with a status of Expired are excluded from the results. The query uses leftouter join to bring together the Advisor recommendations raised about any Connected Machine agents identified as out of date, and Hybrid Computer machines to filter out any agent that haven't communicated with Azure over a period of time.
AdvisorResources
| where type == 'microsoft.advisor/recommendations'
| where properties.category == 'HighAvailability'
| where properties.shortDescription.solution == 'Upgrade to the latest version of the Azure Connected Machine agent'
| project
id,
JoinId = toupper(properties.resourceMetadata.resourceId),
machineName = tostring(properties.impactedValue),
agentVersion = tostring(properties.extendedProperties.installedVersion),
expectedVersion = tostring(properties.extendedProperties.latestVersion)
| join kind=leftouter(
Resources
| where type == 'microsoft.hybridcompute/machines'
| project
machineId = toupper(id),
status = tostring (properties.status)
) on $left.JoinId == $right.machineId
| where status != 'Expired'
| summarize by id, machineName, agentVersion, expectedVersion
| order by tolower(machineName) asc
az graph query -q "AdvisorResources | where type == 'microsoft.advisor/recommendations' | where properties.category == 'HighAvailability' | where properties.shortDescription.solution == 'Upgrade to the latest version of the Azure Connected Machine agent' | project id, JoinId = toupper(properties.resourceMetadata.resourceId), machineName = tostring(properties.impactedValue), agentVersion = tostring(properties.extendedProperties.installedVersion), expectedVersion = tostring(properties.extendedProperties.latestVersion) | join kind=leftouter( Resources | where type == 'microsoft.hybridcompute/machines' | project machineId = toupper(id), status = tostring (properties.status) ) on \$left.JoinId == \$right.machineId | where status != 'Expired' | summarize by id, machineName, agentVersion, expectedVersion | order by tolower(machineName) asc"
Azure Center for SAP solutions
Current health of virtual machines in Virtual Instance for SAP
This query fetches the current availability health of all virtual machines of an SAP system given the SID of a Virtual Instance for SAP. Replace mySubscriptionId with your subscription ID, and replace myResourceId with the resource ID of your Virtual Instance for SAP.
Resources
| where subscriptionId == 'mySubscriptionId'
| where type startswith 'microsoft.workloads/sapvirtualinstances/'
| where id startswith 'myResourceId'
| mv-expand d = properties.vmDetails
| project VmId = tolower(d.virtualMachineId)
| join kind = inner (
HealthResources
| where subscriptionId == 'mySubscriptionId'
| where type == 'microsoft.resourcehealth/availabilitystatuses'
| where properties contains 'Microsoft.Compute/virtualMachines'
| extend VmId = tolower(tostring(properties['targetResourceId']))
| extend AvailabilityState = tostring(properties['availabilityState']))
on $left.VmId == $right.VmId
| project VmId, todatetime(properties['occurredTime']), AvailabilityState
| project-rename ['Virtual Machine ID'] = VmId, UTCTimeStamp = properties_occurredTime, ['Availability State'] = AvailabilityState
az graph query -q "Resources | where subscriptionId == 'mySubscriptionId' | where type startswith 'microsoft.workloads/sapvirtualinstances/' | where id startswith 'myResourceId' | mv-expand d = properties.vmDetails | project VmId = tolower(d.virtualMachineId) | join kind = inner (HealthResources | where subscriptionId == 'mySubscriptionId' | where type == 'microsoft.resourcehealth/availabilitystatuses' | where properties contains 'Microsoft.Compute/virtualMachines' | extend VmId = tolower(tostring(properties['targetResourceId'])) | extend AvailabilityState = tostring(properties['availabilityState'])) on \$left.VmId == \$right.VmId | project VmId, todatetime(properties['occurredTime']), AvailabilityState | project-rename ['Virtual Machine ID'] = VmId, UTCTimeStamp = properties_occurredTime, ['Availability State'] = AvailabilityState"
Currently unhealthy virtual machines and annotations in Virtual Instance for SAP
This query fetches list of virtual machines of an SAP system that are unhealthy and corresponding annotations given the SID of a Virtual Instance for SAP. Replace mySubscriptionId with your subscription ID, and replace myResourceId with the resource ID of your Virtual Instance for SAP.
HealthResources
| where subscriptionId == 'mySubscriptionId'
| where type == 'microsoft.resourcehealth/availabilitystatuses'
| where properties contains 'Microsoft.Compute/virtualMachines'
| extend VmId = tolower(tostring(properties['targetResourceId']))
| extend AvailabilityState = tostring(properties['availabilityState'])
| where AvailabilityState != 'Available'
| project VmId, todatetime(properties['occurredTime']), AvailabilityState
| join kind = inner (
HealthResources
| where subscriptionId == 'mySubscriptionId'
| where type == 'microsoft.resourcehealth/resourceannotations'
| where properties contains 'Microsoft.Compute/virtualMachines'
| extend VmId = tolower(tostring(properties['targetResourceId']))) on $left.VmId == $right.VmId
| join kind = inner (Resources
| where subscriptionId == 'mySubscriptionId'
| where type startswith 'microsoft.workloads/sapvirtualinstances/'
| where id startswith 'myResourceId'
| mv-expand d = properties.vmDetails
| project VmId = tolower(d.virtualMachineId))
on $left.VmId1 == $right.VmId
| extend AnnotationName = tostring(properties['annotationName']), ImpactType = tostring(properties['impactType']), Context = tostring(properties['context']), Summary = tostring(properties['summary']), Reason = tostring(properties['reason']), OccurredTime = todatetime(properties['occurredTime'])
| project VmId, OccurredTime, AvailabilityState, AnnotationName, ImpactType, Context, Summary, Reason
| project-rename ['Virtual Machine ID'] = VmId, ['Time Since Not Available'] = OccurredTime, ['Availability State'] = AvailabilityState, ['Annotation Name'] = AnnotationName, ['Impact Type'] = ImpactType
az graph query -q "HealthResources | where subscriptionId == 'mySubscriptionId' | where type == 'microsoft.resourcehealth/availabilitystatuses' | where properties contains 'Microsoft.Compute/virtualMachines' | extend VmId = tolower(tostring(properties['targetResourceId'])) | extend AvailabilityState = tostring(properties['availabilityState']) | where AvailabilityState != 'Available' | project VmId, todatetime(properties['occurredTime']), AvailabilityState | join kind = inner (HealthResources | where subscriptionId == 'mySubscriptionId' | where type == 'microsoft.resourcehealth/resourceannotations' | where properties contains 'Microsoft.Compute/virtualMachines' | extend VmId = tolower(tostring(properties['targetResourceId']))) on \$left.VmId == \$right.VmId | join kind = inner (Resources | where subscriptionId == 'mySubscriptionId' | where type startswith 'microsoft.workloads/sapvirtualinstances/' | where id startswith 'myResourceId' | mv-expand d = properties.vmDetails | project VmId = tolower(d.virtualMachineId)) on \$left.VmId1 == \$right.VmId | extend AnnotationName = tostring(properties['annotationName']), ImpactType = tostring(properties['impactType']), Context = tostring(properties['context']), Summary = tostring(properties['summary']), Reason = tostring(properties['reason']), OccurredTime = todatetime(properties['occurredTime']) | project VmId, OccurredTime, AvailabilityState, AnnotationName, ImpactType, Context, Summary, Reason | project-rename ['Virtual Machine ID'] = VmId, ['Time Since Not Available'] = OccurredTime, ['Availability State'] = AvailabilityState, ['Annotation Name'] = AnnotationName, ['Impact Type'] = ImpactType"
Changes in health of virtual machines and annotations in Virtual Instance for SAP
This query fetches the historical changes in availability health and corresponding annotations of all virtual machines of an SAP system given the SID of a Virtual Instance for SAP. Replace mySubscriptionId with your subscription ID, and replace myResourceId with the resource ID of your Virtual Instance for SAP.
Resources
| where subscriptionId == 'mySubscriptionId'
| where type startswith 'microsoft.workloads/sapvirtualinstances/'
| where id startswith 'myResourceId'
| mv-expand d = properties.vmDetails
| project VmId = tolower(d.virtualMachineId)
| join kind = leftouter (
HealthResourceChanges
| where subscriptionId == 'mySubscriptionId'
| where id !has '/virtualMachineScaleSets/'
| where id has '/virtualMachines/'
| extend timestamp = todatetime(properties.changeAttributes.timestamp)
| extend VmId = tolower(tostring(split(id, '/providers/Microsoft.ResourceHealth/')[0]))
| where properties has 'properties.availabilityState' or properties has 'properties.annotationName'
| extend HealthChangeType = iff(properties has 'properties.availabilityState', 'Availability', 'Annotation')
| extend ChangeType = tostring(properties.changeType)
| where ChangeType == 'Update' or ChangeType == 'Delete')
on $left.VmId == $right.VmId
| extend Changes = parse_json(tostring(properties.changes))
| extend AvailabilityStateJson = parse_json(tostring(Changes['properties.availabilityState']))
| extend AnnotationNameJson = parse_json(tostring(Changes['properties.annotationName']))
| extend AnnotationSummary = parse_json(tostring(Changes['properties.summary']))
| extend AnnotationReason = parse_json(tostring(Changes['properties.reason']))
| extend AnnotationImpactType = parse_json(tostring(Changes['properties.impactType']))
| extend AnnotationContext = parse_json(tostring(Changes['properties.context']))
| extend AnnotationCategory = parse_json(tostring(Changes['properties.category']))
| extend AvailabilityStatePreviousValue = tostring(AvailabilityStateJson.previousValue)
| extend AvailabilityStateCurrentValue = tostring(AvailabilityStateJson.newValue)
| extend AnnotationNamePreviousValue = tostring(AnnotationNameJson.previousValue)
| extend AnnotationNameCurrentValue = tostring(AnnotationNameJson.newValue)
| extend AnnotationSummaryCurrentValue = tostring(AnnotationSummary.newValue)
| extend AnnotationReasonCurrentValue = tostring(AnnotationReason.newValue)
| extend AnnotationImpactTypeCurrentValue = tostring(AnnotationImpactType.newValue)
| extend AnnotationContextCurrentValue = tostring(AnnotationContext.newValue)
| extend AnnotationCategoryCurrentValue = tostring(AnnotationCategory.newValue)
| project id = VmId, timestamp, ChangeType, AvailabilityStateCurrentValue, AnnotationNameCurrentValue, AnnotationSummaryCurrentValue, AnnotationReasonCurrentValue, AnnotationImpactTypeCurrentValue, AnnotationContextCurrentValue, AnnotationCategoryCurrentValue, Changes
| order by id, timestamp asc
| project-rename ['Virtual Machine ID'] = id, UTCTimeStamp = timestamp, ['Change Type'] = ChangeType, ['Availability State'] = AvailabilityStateCurrentValue, ['Summary'] = AnnotationSummaryCurrentValue, ['Reason'] = AnnotationReasonCurrentValue, ['Impact Type'] = AnnotationImpactTypeCurrentValue, Category = AnnotationCategoryCurrentValue, Context = AnnotationContextCurrentValue
az graph query -q "Resources | where subscriptionId == 'mySubscriptionId' | where type startswith 'microsoft.workloads/sapvirtualinstances/' | where id startswith 'myResourceId' | mv-expand d = properties.vmDetails | project VmId = tolower(d.virtualMachineId) | join kind = leftouter (HealthResourceChanges | where subscriptionId == 'mySubscriptionId' | where id !has '/virtualMachineScaleSets/' | where id has '/virtualMachines/' | extend timestamp = todatetime(properties.changeAttributes.timestamp) | extend VmId = tolower(tostring(split(id, '/providers/Microsoft.ResourceHealth/')[0])) | where properties has 'properties.availabilityState' or properties has 'properties.annotationName' | extend HealthChangeType = iff(properties has 'properties.availabilityState', 'Availability', 'Annotation') | extend ChangeType = tostring(properties.changeType) | where ChangeType == 'Update' or ChangeType == 'Delete') on \$left.VmId == \$right.VmId | extend Changes = parse_json(tostring(properties.changes)) | extend AvailabilityStateJson = parse_json(tostring(Changes['properties.availabilityState'])) | extend AnnotationNameJson = parse_json(tostring(Changes['properties.annotationName'])) | extend AnnotationSummary = parse_json(tostring(Changes['properties.summary'])) | extend AnnotationReason = parse_json(tostring(Changes['properties.reason'])) | extend AnnotationImpactType = parse_json(tostring(Changes['properties.impactType'])) | extend AnnotationContext = parse_json(tostring(Changes['properties.context'])) | extend AnnotationCategory = parse_json(tostring(Changes['properties.category'])) | extend AvailabilityStatePreviousValue = tostring(AvailabilityStateJson.previousValue) | extend AvailabilityStateCurrentValue = tostring(AvailabilityStateJson.newValue) | extend AnnotationNamePreviousValue = tostring(AnnotationNameJson.previousValue) | extend AnnotationNameCurrentValue = tostring(AnnotationNameJson.newValue) | extend AnnotationSummaryCurrentValue = tostring(AnnotationSummary.newValue) | extend AnnotationReasonCurrentValue = tostring(AnnotationReason.newValue) | extend AnnotationImpactTypeCurrentValue = tostring(AnnotationImpactType.newValue) | extend AnnotationContextCurrentValue = tostring(AnnotationContext.newValue) | extend AnnotationCategoryCurrentValue = tostring(AnnotationCategory.newValue) | project id = VmId, timestamp, ChangeType, AvailabilityStateCurrentValue, AnnotationNameCurrentValue, AnnotationSummaryCurrentValue, AnnotationReasonCurrentValue, AnnotationImpactTypeCurrentValue, AnnotationContextCurrentValue, AnnotationCategoryCurrentValue, Changes | order by id, timestamp asc | project-rename ['Virtual Machine ID'] = id, UTCTimeStamp = timestamp, ['Change Type'] = ChangeType, ['Availability State'] = AvailabilityStateCurrentValue, ['Summary'] = AnnotationSummaryCurrentValue, ['Reason'] = AnnotationReasonCurrentValue, ['Impact Type'] = AnnotationImpactTypeCurrentValue, Category = AnnotationCategoryCurrentValue, Context = AnnotationContextCurrentValue"
Azure Container Registry
List Container Registry vulnerability assessment results
Returns all vulnerabilities found on container images. Microsoft Defender for Containers has to be enabled in order to view these security findings.
SecurityResources
| where type == 'microsoft.security/assessments'
| where properties.displayName contains 'Container registry images should have vulnerability findings resolved'
| summarize by assessmentKey=name //the ID of the assessment
| join kind=inner (
securityresources
| where type == 'microsoft.security/assessments/subassessments'
| extend assessmentKey = extract('.*assessments/(.+?)/.*',1, id)
) on assessmentKey
| project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
| extend description = properties.description,
displayName = properties.displayName,
resourceId = properties.resourceDetails.id,
resourceSource = properties.resourceDetails.source,
category = properties.category,
severity = properties.status.severity,
code = properties.status.code,
timeGenerated = properties.timeGenerated,
remediation = properties.remediation,
impact = properties.impact,
vulnId = properties.id,
additionalData = properties.additionalData
az graph query -q "SecurityResources | where type == 'microsoft.security/assessments' | where properties.displayName contains 'Container registry images should have vulnerability findings resolved' | summarize by assessmentKey=name //the ID of the assessment | join kind=inner ( securityresources | where type == 'microsoft.security/assessments/subassessments' | extend assessmentKey = extract('.*assessments/(.+?)/.*',1, id) ) on assessmentKey | project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId | extend description = properties.description, displayName = properties.displayName, resourceId = properties.resourceDetails.id, resourceSource = properties.resourceDetails.source, category = properties.category, severity = properties.status.severity, code = properties.status.code, timeGenerated = properties.timeGenerated, remediation = properties.remediation, impact = properties.impact, vulnId = properties.id, additionalData = properties.additionalData"
Azure Cosmos DB
List Azure Cosmos DB with specific write locations
The following query limits to Azure Cosmos DB resources, uses mv-expand to expand the property bag for properties.writeLocations, then project specific fields and limit the results further to properties.writeLocations.locationName values matching either 'East US' or 'West US'.
Resources
| where type =~ 'microsoft.documentdb/databaseaccounts'
| project id, name, writeLocations = (properties.writeLocations)
| mv-expand writeLocations
| project id, name, writeLocation = tostring(writeLocations.locationName)
| where writeLocation in ('East US', 'West US')
| summarize by id, name
az graph query -q "Resources | where type =~ 'microsoft.documentdb/databaseaccounts' | project id, name, writeLocations = (properties.writeLocations) | mv-expand writeLocations | project id, name, writeLocation = tostring(writeLocations.locationName) | where writeLocation in ('East US', 'West US') | summarize by id, name"
Azure Key Vault
Count key vault resources
This query uses count instead of summarize to count the number of records returned. Only key vaults are included in the count.
Resources
| where type =~ 'microsoft.keyvault/vaults'
| count
az graph query -q "Resources | where type =~ 'microsoft.keyvault/vaults' | count"
Key vaults with subscription name
The following query shows a complex use of join with kind as leftouter. The query limits the joined table to subscriptions resources and with project to include only the original field subscriptionId and the name field renamed to SubName. The field rename avoids join adding it as name1 since the field already exists in resources. The original table is filtered with where and the following project includes columns from both tables. The query result is all key vaults displaying type, the name of the key vault, and the name of the subscription it's in.
Resources
| join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
| where type == 'microsoft.keyvault/vaults'
| project type, name, SubName
az graph query -q "Resources | join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId | where type == 'microsoft.keyvault/vaults' | project type, name, SubName"
Azure Monitor
View recent Azure Monitor alerts
This sample query gets all Azure Monitor alerts that were fired in the last 12 hours and extracts commonly used properties.
alertsmanagementresources
| where properties.essentials.startDateTime > ago(12h)
| project
alertId = id,
name,
monitorCondition = tostring(properties.essentials.monitorCondition),
severity = tostring(properties.essentials.severity),
monitorService = tostring(properties.essentials.monitorService),
alertState = tostring(properties.essentials.alertState),
targetResourceType = tostring(properties.essentials.targetResourceType),
targetResource = tostring(properties.essentials.targetResource),
subscriptionId,
startDateTime = todatetime(properties.essentials.startDateTime),
lastModifiedDateTime = todatetime(properties.essentials.lastModifiedDateTime),
dimensions = properties.context.context.condition.allOf[0].dimensions, properties
az graph query -q "alertsmanagementresources | where properties.essentials.startDateTime > ago(12h) | project alertId = id, name, monitorCondition = tostring(properties.essentials.monitorCondition), severity = tostring(properties.essentials.severity), monitorService = tostring(properties.essentials.monitorService), alertState = tostring(properties.essentials.alertState), targetResourceType = tostring(properties.essentials.targetResourceType), targetResource = tostring(properties.essentials.targetResource), subscriptionId, startDateTime = todatetime(properties.essentials.startDateTime), lastModifiedDateTime = todatetime(properties.essentials.lastModifiedDateTime), dimensions = properties.context.context.condition.allOf[0].dimensions, properties"
View recent Azure Monitor alerts enriched with resource tags
This example query gets all Azure Monitor alerts that were fired in the last 12 hours, extracts commonly used properties, and adds the tags of the target resource.
alertsmanagementresources
| where properties.essentials.startDateTime > ago(12h)
| where tostring(properties.essentials.monitorService) <> "ActivityLog Administrative"
| project // converting extracted fields to string / datetime to allow grouping
alertId = id,
name,
monitorCondition = tostring(properties.essentials.monitorCondition),
severity = tostring(properties.essentials.severity),
monitorService = tostring(properties.essentials.monitorService),
alertState = tostring(properties.essentials.alertState),
targetResourceType = tostring(properties.essentials.targetResourceType),
targetResource = tostring(properties.essentials.targetResource),
subscriptionId,
startDateTime = todatetime(properties.essentials.startDateTime),
lastModifiedDateTime = todatetime(properties.essentials.lastModifiedDateTime),
dimensions = properties.context.context.condition.allOf[0].dimensions, // usefor metric alerts and log search alerts
properties
| extend targetResource = tolower(targetResource)
| join kind=leftouter
( resources | project targetResource = tolower(id), targetResourceTags = tags) on targetResource
| project-away targetResource1
az graph query -q "alertsmanagementresources | where properties.essentials.startDateTime > ago(12h) | where tostring(properties.essentials.monitorService) <> "ActivityLog Administrative" | project // converting extracted fields to string / datetime to allow grouping alertId = id, name, monitorCondition = tostring(properties.essentials.monitorCondition), severity = tostring(properties.essentials.severity), monitorService = tostring(properties.essentials.monitorService), alertState = tostring(properties.essentials.alertState), targetResourceType = tostring(properties.essentials.targetResourceType), targetResource = tostring(properties.essentials.targetResource), subscriptionId, startDateTime = todatetime(properties.essentials.startDateTime), lastModifiedDateTime = todatetime(properties.essentials.lastModifiedDateTime), dimensions = properties.context.context.condition.allOf[0].dimensions, // usefor metric alerts and log search alerts properties | extend targetResource = tolower(targetResource) | join kind=leftouter ( resources | project targetResource = tolower(id), targetResourceTags = tags) on targetResource | project-away targetResource1"
List all Azure Arc-enabled Kubernetes clusters with the Azure Monitor extension
Returns the connected cluster ID of each Azure Arc-enabled Kubernetes cluster that has the Azure Monitor extension installed.
KubernetesConfigurationResources
| where type == 'microsoft.kubernetesconfiguration/extensions'
| where properties.ExtensionType == 'microsoft.azuremonitor.containers'
| parse id with connectedClusterId '/providers/Microsoft.KubernetesConfiguration/Extensions' *
| project connectedClusterId
az graph query -q "KubernetesConfigurationResources | where type == 'microsoft.kubernetesconfiguration/extensions' | where properties.ExtensionType == 'microsoft.azuremonitor.containers' | parse id with connectedClusterId '/providers/Microsoft.KubernetesConfiguration/Extensions' * | project connectedClusterId"
List all Azure Arc-enabled Kubernetes clusters without the Azure Monitor extension
Returns the connected cluster ID of each Azure Arc-enabled Kubernetes cluster that's missing the Azure Monitor extension.
Resources
| where type =~ 'Microsoft.Kubernetes/connectedClusters' | extend connectedClusterId = tolower(id) | project connectedClusterId
| join kind = leftouter
(KubernetesConfigurationResources
| where type == 'microsoft.kubernetesconfiguration/extensions'
| where properties.ExtensionType == 'microsoft.azuremonitor.containers'
| parse tolower(id) with connectedClusterId '/providers/microsoft.kubernetesconfiguration/extensions' *
| project connectedClusterId
) on connectedClusterId
| where connectedClusterId1 == ''
| project connectedClusterId
az graph query -q "Resources | where type =~ 'Microsoft.Kubernetes/connectedClusters' | extend connectedClusterId = tolower(id) | project connectedClusterId | join kind = leftouter (KubernetesConfigurationResources | where type == 'microsoft.kubernetesconfiguration/extensions' | where properties.ExtensionType == 'microsoft.azuremonitor.containers' | parse tolower(id) with connectedClusterId '/providers/microsoft.kubernetesconfiguration/extensions' * | project connectedClusterId ) on connectedClusterId | where connectedClusterId1 == '' | project connectedClusterId"
Returns all Azure Monitor alerts in a subscription in the last day
{
"subscriptions": [
<subscriptionId>
],
"query": "alertsmanagementresources | where properties.essentials.lastModifiedDateTime > ago(1d) | project alertInstanceId = id, parentRuleId = tolower(tostring(properties['essentials']['alertRule'])), sourceId = properties['essentials']['sourceCreatedId'], alertName = name, severity = properties.essentials.severity, status = properties.essentials.monitorCondition, state = properties.essentials.alertState, affectedResource = properties.essentials.targetResourceName, monitorService = properties.essentials.monitorService, signalType = properties.essentials.signalType, firedTime = properties['essentials']['startDateTime'], lastModifiedDate = properties.essentials.lastModifiedDateTime, lastModifiedBy = properties.essentials.lastModifiedUserName"
}
Count Azure Monitor data collection rules by subscription and resource group
This query groups Azure Monitor data collection rules by subscription and resource group.
resources
| where type == 'microsoft.insights/datacollectionrules'
| summarize dcrCount = count() by subscriptionId, resourceGroup, location
| sort by dcrCount desc
az graph query -q "resources | where type == 'microsoft.insights/datacollectionrules' | summarize dcrCount = count() by subscriptionId, resourceGroup, location | sort by dcrCount desc"
Count Azure Monitor data collection rules by location
This query groups Azure Monitor data collection rules by location.
resources
| where type == 'microsoft.insights/datacollectionrules'
| summarize dcrCount=count() by location
| sort by dcrCount desc
az graph query -q "resources | where type == 'microsoft.insights/datacollectionrules' | summarize dcrCount=count() by location | sort by dcrCount desc"
List Azure Monitor data collection rules with Log Analytics workspace as destination
Get the list of Log Analytics workspaces, and for each of the workspaces, list data collection rules that specify the workspace as one of the destinations.
resources
| where type == 'microsoft.insights/datacollectionrules'
| extend destinations = properties['destinations']
| extend logAnalyticsWorkspaces = destinations['logAnalytics']
| where isnotnull(logAnalyticsWorkspaces)
| mv-expand logAnalyticsWorkspace = logAnalyticsWorkspaces
| extend logAnalyticsWorkspaceResourceId = tolower(tostring(logAnalyticsWorkspace['workspaceResourceId']))
| summarize dcrList = make_list(id), dcrCount = count() by logAnalyticsWorkspaceResourceId
| sort by dcrCount desc
az graph query -q "resources | where type == 'microsoft.insights/datacollectionrules' | extend destinations = properties['destinations'] | extend logAnalyticsWorkspaces = destinations['logAnalytics'] | where isnotnull(logAnalyticsWorkspaces) | mv-expand logAnalyticsWorkspace = logAnalyticsWorkspaces | extend logAnalyticsWorkspaceResourceId = tolower(tostring(logAnalyticsWorkspace['workspaceResourceId'])) | summarize dcrList = make_list(id), dcrCount = count() by logAnalyticsWorkspaceResourceId | sort by dcrCount desc"
List data collection rules that use Azure Monitor Metrics as one of its destinations
This query lists data collection rules that use Azure Monitor Metrics as one of its destinations.
resources
| where type == 'microsoft.insights/datacollectionrules'
| extend destinations = properties['destinations']
| extend azureMonitorMetrics = destinations['azureMonitorMetrics']
| where isnotnull(azureMonitorMetrics)
| project-away destinations, azureMonitorMetrics
az graph query -q "resources | where type == 'microsoft.insights/datacollectionrules' | extend destinations = properties['destinations'] | extend azureMonitorMetrics = destinations['azureMonitorMetrics'] | where isnotnull(azureMonitorMetrics) | project-away destinations, azureMonitorMetrics"
List VMs with data collection rule associations
This query lists virtual machines with data collection rule associations. For each virtual machine, lists data collection rules associated with it.
insightsresources
| where type == 'microsoft.insights/datacollectionruleassociations'
| where id contains 'microsoft.compute/virtualmachines/'
| project id = trim_start('/', tolower(id)), properties
| extend idComponents = split(id, '/')
| extend subscription = tolower(tostring(idComponents[1])), resourceGroup = tolower(tostring(idComponents[3])), vmName = tolower(tostring(idComponents[7]))
| extend dcrId = properties['dataCollectionRuleId']
| where isnotnull(dcrId)
| extend dcrId = tostring(dcrId)
| summarize dcrList = make_list(dcrId), dcrCount = count() by subscription, resourceGroup, vmName
| sort by dcrCount desc
az graph query -q "insightsresources | where type == 'microsoft.insights/datacollectionruleassociations' | where id contains 'microsoft.compute/virtualmachines/' | project id = trim_start('/', tolower(id)), properties | extend idComponents = split(id, '/') | extend subscription = tolower(tostring(idComponents[1])), resourceGroup = tolower(tostring(idComponents[3])), vmName = tolower(tostring(idComponents[7])) | extend dcrId = properties['dataCollectionRuleId'] | where isnotnull(dcrId) | extend dcrId = tostring(dcrId) | summarize dcrList = make_list(dcrId), dcrCount = count() by subscription, resourceGroup, vmName | sort by dcrCount desc"
Azure Orbital Ground Station
List upcoming contacts
This query helps customers track all the upcoming contacts sorted by reservation start time.
OrbitalResources
| where type == 'microsoft.orbital/spacecrafts/contacts' and todatetime(properties.reservationStartTime) >= now()
| sort by todatetime(properties.reservationStartTime)
| extend Contact_Profile = tostring(split(properties.contactProfile.id, "/")[-1])
| extend Spacecraft = tostring(split(id, "/")[-3])
| project Contact = tostring(name), Groundstation = tostring(properties.groundStationName), Spacecraft, Contact_Profile, Status=properties.status, Provisioning_Status=properties.provisioningState
az graph query -q "OrbitalResources | where type == 'microsoft.orbital/spacecrafts/contacts' and todatetime(properties.reservationStartTime) >= now() | sort by todatetime(properties.reservationStartTime) | extend Contact_Profile = tostring(split(properties.contactProfile.id, '/')[-1]) | extend Spacecraft = tostring(split(id, '/')[-3]) | project Contact = tostring(name), Groundstation = tostring(properties.groundStationName), Spacecraft, Contact_Profile, Status=properties.status, Provisioning_Status=properties.provisioningState"
List past 'x' days contacts on specified ground station
This query helps customers track all the past x days contacts sorted by reservation start time for a specified ground station. The function now(-1d) specifies the number of past days.
OrbitalResources
| where type == 'microsoft.orbital/spacecrafts/contacts' and todatetime(properties.reservationStartTime) >= now(-1d) and properties.groundStationName == 'Microsoft_Gavle'
| sort by todatetime(properties.reservationStartTime)
| extend Contact_Profile = tostring(split(properties.contactProfile.id, '/')[-1])
| extend Spacecraft = tostring(split(id, '/')[-3])
| project Contact = tostring(name), Groundstation = tostring(properties.groundStationName), Spacecraft, Contact_Profile, Reservation_Start_Time = todatetime(properties.reservationStartTime), Reservation_End_Time = todatetime(properties.reservationEndTime), Status=properties.status, Provisioning_Status=properties.provisioningState
az graph query -q "OrbitalResources | where type == 'microsoft.orbital/spacecrafts/contacts' and todatetime(properties.reservationStartTime) >= now(-1d) and properties.groundStationName == 'Microsoft_Gavle' | sort by todatetime(properties.reservationStartTime) | extend Contact_Profile = tostring(split(properties.contactProfile.id, '/')[-1]) | extend Spacecraft = tostring(split(id, '/')[-3]) | project Contact = tostring(name), Groundstation = tostring(properties.groundStationName), Spacecraft, Contact_Profile, Reservation_Start_Time = todatetime(properties.reservationStartTime), Reservation_End_Time = todatetime(properties.reservationEndTime), Status=properties.status, Provisioning_Status=properties.provisioningState"
List past 'x' days contacts on specified contact profile
This query helps customers track all the past x days contacts sorted by reservation start time for a specified contact profile. The function now(-1d) specifies the number of past days.
OrbitalResources
| where type == 'microsoft.orbital/spacecrafts/contacts'
| extend Contact_Profile = tostring(split(properties.contactProfile.id, '/')[-1])
| where todatetime(properties.reservationStartTime) >= now(-1d) and Contact_Profile == 'test-CP'
| sort by todatetime(properties.reservationStartTime)
| extend Spacecraft = tostring(split(id, '/')[-3])
| project Contact = tostring(name), Groundstation = tostring(properties.groundStationName), Spacecraft, Contact_Profile, Reservation_Start_Time = todatetime(properties.reservationStartTime), Reservation_End_Time = todatetime(properties.reservationEndTime), Status=properties.status, Provisioning_Status=properties.provisioningState
az graph query -q "OrbitalResources | where type == 'microsoft.orbital/spacecrafts/contacts' | extend Contact_Profile = tostring(split(properties.contactProfile.id, '/')[-1]) | where todatetime(properties.reservationStartTime) >= now(-1d) and Contact_Profile == 'test-CP' | sort by todatetime(properties.reservationStartTime) | extend Spacecraft = tostring(split(id, '/')[-3]) | project Contact = tostring(name), Groundstation = tostring(properties.groundStationName), Spacecraft, Contact_Profile, Reservation_Start_Time = todatetime(properties.reservationStartTime), Reservation_End_Time = todatetime(properties.reservationEndTime), Status=properties.status, Provisioning_Status=properties.provisioningState"
Azure Policy
Compliance by policy assignment
Provides compliance state, compliance percentage, and counts of resources for each Azure Policy assignment.
PolicyResources
| where type =~ 'Microsoft.PolicyInsights/PolicyStates'
| extend complianceState = tostring(properties.complianceState)
| extend
resourceId = tostring(properties.resourceId),
policyAssignmentId = tostring(properties.policyAssignmentId),
policyAssignmentScope = tostring(properties.policyAssignmentScope),
policyAssignmentName = tostring(properties.policyAssignmentName),
policyDefinitionId = tostring(properties.policyDefinitionId),
policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),
stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0)))))
| summarize max(stateWeight) by resourceId, policyAssignmentId, policyAssignmentScope, policyAssignmentName
| summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName
| summarize overallStateWeight = max(max_stateWeight),
nonCompliantCount = sumif(counts, max_stateWeight == 300),
compliantCount = sumif(counts, max_stateWeight == 200),
conflictCount = sumif(counts, max_stateWeight == 100),
exemptCount = sumif(counts, max_stateWeight == 50) by policyAssignmentId, policyAssignmentScope, policyAssignmentName
| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)
| extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources)
| project policyAssignmentName, scope = policyAssignmentScope,
complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),
compliancePercentage,
compliantCount,
nonCompliantCount,
conflictCount,
exemptCount
az graph query -q "PolicyResources | where type =~ 'Microsoft.PolicyInsights/PolicyStates' | extend complianceState = tostring(properties.complianceState) | extend resourceId = tostring(properties.resourceId), policyAssignmentId = tostring(properties.policyAssignmentId), policyAssignmentScope = tostring(properties.policyAssignmentScope), policyAssignmentName = tostring(properties.policyAssignmentName), policyDefinitionId = tostring(properties.policyDefinitionId), policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId), stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0))))) | summarize max(stateWeight) by resourceId, policyAssignmentId, policyAssignmentScope, policyAssignmentName | summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName | summarize overallStateWeight = max(max_stateWeight), nonCompliantCount = sumif(counts, max_stateWeight == 300), compliantCount = sumif(counts, max_stateWeight == 200), conflictCount = sumif(counts, max_stateWeight == 100), exemptCount = sumif(counts, max_stateWeight == 50) by policyAssignmentId, policyAssignmentScope, policyAssignmentName | extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount) | extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources) | project policyAssignmentName, scope = policyAssignmentScope, complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))), compliancePercentage, compliantCount, nonCompliantCount, conflictCount, exemptCount"
Compliance by resource type
Provides compliance state, compliance percentage, and counts of resources for each resource type.
PolicyResources
| where type =~ 'Microsoft.PolicyInsights/PolicyStates'
| extend complianceState = tostring(properties.complianceState)
| extend
resourceId = tostring(properties.resourceId),
resourceType = tolower(tostring(properties.resourceType)),
policyAssignmentId = tostring(properties.policyAssignmentId),
policyDefinitionId = tostring(properties.policyDefinitionId),
policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),
stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0)))))
| summarize max(stateWeight) by resourceId, resourceType
| summarize counts = count() by resourceType, max_stateWeight
| summarize overallStateWeight = max(max_stateWeight),
nonCompliantCount = sumif(counts, max_stateWeight == 300),
compliantCount = sumif(counts, max_stateWeight == 200),
conflictCount = sumif(counts, max_stateWeight == 100),
exemptCount = sumif(counts, max_stateWeight == 50) by resourceType
| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)
| extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources)
| project resourceType,
overAllComplianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))),
compliancePercentage,
compliantCount,
nonCompliantCount,
conflictCount,
exemptCount
az graph query -q "PolicyResources | where type =~ 'Microsoft.PolicyInsights/PolicyStates' | extend complianceState = tostring(properties.complianceState) | extend resourceId = tostring(properties.resourceId), resourceType = tolower(tostring(properties.resourceType)), policyAssignmentId = tostring(properties.policyAssignmentId), policyDefinitionId = tostring(properties.policyDefinitionId), policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId), stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0))))) | summarize max(stateWeight) by resourceId, resourceType | summarize counts = count() by resourceType, max_stateWeight | summarize overallStateWeight = max(max_stateWeight), nonCompliantCount = sumif(counts, max_stateWeight == 300), compliantCount = sumif(counts, max_stateWeight == 200), conflictCount = sumif(counts, max_stateWeight == 100), exemptCount = sumif(counts, max_stateWeight == 50) by resourceType | extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount) | extend compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources) | project resourceType, overAllComplianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted')))), compliancePercentage, compliantCount, nonCompliantCount, conflictCount, exemptCount"
List all non-compliant resources
Provides a list of all resources types that are in a NonCompliant state.
PolicyResources
| where type == 'microsoft.policyinsights/policystates'
| where properties.complianceState == 'NonCompliant'
| extend NonCompliantResourceId = properties.resourceId, PolicyAssignmentName = properties.policyAssignmentName
az graph query -q "PolicyResources | where type == 'microsoft.policyinsights/policystates' | where properties.complianceState == 'NonCompliant' | extend NonCompliantResourceId = properties.resourceId, PolicyAssignmentName = properties.policyAssignmentName"
Summarize resource compliance by state
Details the number of resources in each compliance state.
PolicyResources
| where type == 'microsoft.policyinsights/policystates'
| extend complianceState = tostring(properties.complianceState)
| summarize count() by complianceState
az graph query -q "PolicyResources | where type == 'microsoft.policyinsights/policystates' | extend complianceState = tostring(properties.complianceState) | summarize count() by complianceState"
Summarize resource compliance by state per location
Details the number of resources in each compliance state per location.
PolicyResources
| where type == 'microsoft.policyinsights/policystates'
| extend complianceState = tostring(properties.complianceState)
| extend resourceLocation = tostring(properties.resourceLocation)
| summarize count() by resourceLocation, complianceState
az graph query -q "PolicyResources | where type == 'microsoft.policyinsights/policystates' | extend complianceState = tostring(properties.complianceState) | extend resourceLocation = tostring(properties.resourceLocation) | summarize count() by resourceLocation, complianceState"
Policy exemptions per assignment
Lists the number of exemptions for each assignment.
PolicyResources
| where type == 'microsoft.authorization/policyexemptions'
| summarize count() by tostring(properties.policyAssignmentId)
For more information about using scopes with Azure CLI or Azure PowerShell, go to Count Azure resources.
Use the --management-groups parameter with an Azure management group ID or tenant ID. In this example, the tenantid variable stores the tenant ID.
tenantid="$(az account show --query tenantId --output tsv)"
az graph query -q "policyresources | where type == 'microsoft.authorization/policyexemptions' | summarize count() by tostring(properties.policyAssignmentId)" --management-groups $tenantid
Policy exemptions that expire within 90 days
Lists the name and expiration date.
PolicyResources
| where type == 'microsoft.authorization/policyexemptions'
| extend expiresOnC = todatetime(properties.expiresOn)
| where isnotnull(expiresOnC)
| where expiresOnC >= now() and expiresOnC < now(+90d)
| project name, expiresOnC
az graph query -q "policyresources | where type == 'microsoft.authorization/policyexemptions' | extend expiresOnC = todatetime(properties.expiresOn) | where isnotnull(expiresOnC) | where expiresOnC >= now() and expiresOnC < now(+90d) | project name, expiresOnC"
Azure Policy guest configuration
Count machines in scope of guest configuration policies
Displays the count of Azure virtual machines and Arc connected servers in scope for Azure Policy guest configuration assignments.
GuestConfigurationResources
| where type =~ 'microsoft.guestconfiguration/guestconfigurationassignments'
| extend vmid = split(properties.targetResourceId,'/')
| mvexpand properties.latestAssignmentReport.resources
| where properties_latestAssignmentReport_resources.resourceId != 'Invalid assignment package.'
| project machine = tostring(vmid[(-1)]),type = tostring(vmid[(-3)])
| distinct machine, type
| summarize count() by type
az graph query -q "GuestConfigurationResources | where type =~ 'microsoft.guestconfiguration/guestconfigurationassignments' | extend vmid = split(properties.targetResourceId,'/') | mvexpand properties.latestAssignmentReport.resources | where properties_latestAssignmentReport_resources.resourceId != 'Invalid assignment package.' | project machine = tostring(vmid[(-1)]),type = tostring(vmid[(-3)]) | distinct machine, type | summarize count() by type"
Count of non-compliant guest configuration assignments
Displays a count of non-compliant machines per guest configuration assignment reason. Limits results to first 100 for performance.
GuestConfigurationResources
| where type =~ 'microsoft.guestconfiguration/guestconfigurationassignments'
| project id, name, resources = properties.latestAssignmentReport.resources, vmid = split(properties.targetResourceId,'/')[(-1)], status = tostring(properties.complianceStatus)
| extend resources = iff(isnull(resources[0]), dynamic([{}]), resources)
| mvexpand resources
| extend reasons = resources.reasons
| extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons)
| mvexpand reasons
| project id, vmid, name, status, resource = tostring(resources.resourceId), reason = reasons.phrase
| summarize count() by resource, name
| order by count_
| limit 100
az graph query -q "GuestConfigurationResources | where type =~ 'microsoft.guestconfiguration/guestconfigurationassignments' | project id, name, resources = properties.latestAssignmentReport.resources, vmid = split(properties.targetResourceId,'/')[(-1)], status = tostring(properties.complianceStatus) | extend resources = iff(isnull(resources[0]), dynamic([{}]), resources) | mvexpand resources | extend reasons = resources.reasons | extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons) | mvexpand reasons | project id, vmid, name, status, resource = tostring(resources.resourceId), reason = reasons.phrase | summarize count() by resource, name | order by count_ | limit 100"
Find all reasons a machine is non-compliant for guest configuration assignments
Display all guest configuration assignment reasons for a specific machine. Remove the first where clause to also include audits where the machine is compliant.
GuestConfigurationResources
| where type =~ 'microsoft.guestconfiguration/guestconfigurationassignments'
| where properties.complianceStatus == 'NonCompliant'
| project id, name, resources = properties.latestAssignmentReport.resources, machine = split(properties.targetResourceId,'/')[(-1)], status = tostring(properties.complianceStatus)
| extend resources = iff(isnull(resources[0]), dynamic([{}]), resources)
| mvexpand resources
| extend reasons = resources.reasons
| extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons)
| mvexpand reasons
| where machine == 'MACHINENAME'
| project id, machine, name, status, resource = resources.resourceId, reason = reasons.phrase
az graph query -q "GuestConfigurationResources | where type =~ 'microsoft.guestconfiguration/guestconfigurationassignments' | where properties.complianceStatus == 'NonCompliant' | project id, name, resources = properties.latestAssignmentReport.resources, machine = split(properties.targetResourceId,'/')[(-1)], status = tostring(properties.complianceStatus) | extend resources = iff(isnull(resources[0]), dynamic([{}]), resources) | mvexpand resources | extend reasons = resources.reasons | extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons) | mvexpand reasons | where machine == 'MACHINENAME' | project id, machine, name, status, resource = resources.resourceId, reason = reasons.phrase"
List machines and status of pending reboot
Provides a list of machines with configuration details about whether they have a pending reboot.
GuestConfigurationResources
| where name in ('WindowsPendingReboot')
| project id, name, resources = properties.latestAssignmentReport.resources, vmid = split(properties.targetResourceId,'/'), status = tostring(properties.complianceStatus)
| extend resources = iff(isnull(resources[0]), dynamic([{}]), resources)
| mvexpand resources
| extend reasons = resources.reasons
| extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons)
| mvexpand reasons
| project id, vmid, name, status, resource = resources.resourceId, reason = reasons.phrase
| summarize name = any(name), status = any(status), vmid = any(vmid), resources = make_list_if(resource, isnotnull(resource)), reasons = make_list_if(reason, isnotnull(reason)) by id = tolower(id)
| project id, machine = tostring(vmid[(-1)]), type = tostring(vmid[(-3)]), name, status, reasons
az graph query -q "GuestConfigurationResources | where name in ('WindowsPendingReboot') | project id, name, resources = properties.latestAssignmentReport.resources, vmid = split(properties.targetResourceId,'/'), status = tostring(properties.complianceStatus) | extend resources = iff(isnull(resources[0]), dynamic([{}]), resources) | mvexpand resources | extend reasons = resources.reasons | extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons) | mvexpand reasons | project id, vmid, name, status, resource = resources.resourceId, reason = reasons.phrase | summarize name = any(name), status = any(status), vmid = any(vmid), resources = make_list_if(resource, isnotnull(resource)), reasons = make_list_if(reason, isnotnull(reason)) by id = tolower(id) | project id, machine = tostring(vmid[(-1)]), type = tostring(vmid[(-3)]), name, status, reasons"
List machines that are not running and the last compliance status
Provides a list of machines that aren't powered on with their configuration assignments and the last reported compliance status.
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| where properties.extended.instanceView.powerState.code != 'PowerState/running'
| project vmName = name, power = properties.extended.instanceView.powerState.code
| join kind = leftouter (GuestConfigurationResources
| extend vmName = tostring(split(properties.targetResourceId,'/')[(-1)])
| project vmName, name, compliance = properties.complianceStatus) on vmName | project-away vmName1
az graph query -q "Resources | where type =~ 'Microsoft.Compute/virtualMachines' | where properties.extended.instanceView.powerState.code != 'PowerState/running' | project vmName = name, power = properties.extended.instanceView.powerState.code | join kind = leftouter (GuestConfigurationResources | extend vmName = tostring(split(properties.targetResourceId,'/')[(-1)]) | project vmName, name, compliance = properties.complianceStatus) on vmName | project-away vmName1"
Query details of guest configuration assignment reports
Display report from guest configuration assignment reason details. In the following example, the query returns only results where the Guest Assignment name is installed_application_linux and the output contains the string Chrome to list all Linux machines where a package is installed that includes the name Chrome.
GuestConfigurationResources
| where name in ('installed_application_linux')
| project id, name, resources = properties.latestAssignmentReport.resources, vmid = split(properties.targetResourceId,'/')[(-1)], status = tostring(properties.complianceStatus)
| extend resources = iff(isnull(resources[0]), dynamic([{}]), resources)
| mvexpand resources
| extend reasons = resources.reasons
| extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons)
| mvexpand reasons
| where reasons.phrase contains 'chrome'
| project id, vmid, name, status, resource = resources.resourceId, reason = reasons.phrase
az graph query -q "GuestConfigurationResources | where name in ('installed_application_linux') | project id, name, resources = properties.latestAssignmentReport.resources, vmid = split(properties.targetResourceId,'/')[(-1)], status = tostring(properties.complianceStatus) | extend resources = iff(isnull(resources[0]), dynamic([{}]), resources) | mvexpand resources | extend reasons = resources.reasons | extend reasons = iff(isnull(reasons[0]), dynamic([{}]), reasons) | mvexpand reasons | where reasons.phrase contains 'chrome' | project id, vmid, name, status, resource = resources.resourceId, reason = reasons.phrase"
Azure RBAC
Troubleshoot Azure RBAC limits
The authorizationresources table can be used to troubleshoot Azure role-based access control (Azure RBAC) if you exceed limits. For more information, go to Troubleshoot Azure RBAC limits.
Get role assignments with key properties
Provides a sample of role assignments and some of the resources relevant properties.
authorizationresources
| where type =~ 'microsoft.authorization/roleassignments'
| extend roleDefinitionId = properties.roleDefinitionId
| extend principalType = properties.principalType
| extend principalId = properties.principalId
| extend scope = properties.scope
| take 5
az graph query -q "authorizationresources | where type =~ 'microsoft.authorization/roleassignments' | extend roleDefinitionId = properties.roleDefinitionId | extend principalType = properties.principalType | extend principalId = properties.principalId | extend scope = properties.scope | take 5"
Get role definitions with key properties
Provides a sample of role definitions and some of the resources relevant properties.
authorizationresources
| where type =~ 'microsoft.authorization/roledefinitions'
| extend assignableScopes = properties.assignableScopes
| extend permissionsList = properties.permissions
| extend isServiceRole = properties.isServiceRole
| take 5
az graph query -q "authorizationresources | where type =~ 'microsoft.authorization/roledefinitions' | extend assignableScopes = properties.assignableScopes | extend permissionsList = properties.permissions | extend isServiceRole = properties.isServiceRole | take 5"
Get role definitions with actions
Displays a sample of role definitions with an expanded list of actions and not actions for each role definition's permissions list.
authorizationresources
| where type =~ 'microsoft.authorization/roledefinitions'
| extend assignableScopes = properties.assignableScopes
| extend permissionsList = properties.permissions
| extend isServiceRole = properties.isServiceRole
| mv-expand permissionsList
| extend Actions = permissionsList.Actions
| extend notActions = permissionsList.notActions
| extend DataActions = permissionsList.DataActions
| extend notDataActions = permissionsList.notDataActions
| take 5
az graph query -q "authorizationresources | where type =~ 'microsoft.authorization/roledefinitions' | extend assignableScopes = properties.assignableScopes | extend permissionsList = properties.permissions | extend isServiceRole = properties.isServiceRole | mv-expand permissionsList | extend Actions = permissionsList.Actions | extend notActions = permissionsList.notActions | extend DataActions = permissionsList.DataActions | extend notDataActions = permissionsList.notDataActions | take 5"
Get role definitions with permissions listed out
Displays a summary of the Actions and notActions for each unique role definition.
authorizationresources
| where type =~ 'microsoft.authorization/roledefinitions'
| extend assignableScopes = properties.assignableScopes
| extend permissionsList = properties.permissions
| extend isServiceRole = properties.isServiceRole
| mv-expand permissionsList
| extend Actions = permissionsList.Actions
| extend notActions = permissionsList.notActions
| extend DataActions = permissionsList.DataActions
| extend notDataActions = permissionsList.notDataActions
| summarize make_set(Actions), make_set(notActions), make_set(DataActions), make_set(notDataActions), any(assignableScopes, isServiceRole) by id
az graph query -q "authorizationresources | where type =~ 'microsoft.authorization/roledefinitions' | extend assignableScopes = properties.assignableScopes | extend permissionsList = properties.permissions | extend isServiceRole = properties.isServiceRole | mv-expand permissionsList | extend Actions = permissionsList.Actions | extend notActions = permissionsList.notActions | extend DataActions = permissionsList.DataActions | extend notDataActions = permissionsList.notDataActions | summarize make_set(Actions), make_set(notActions), make_set(DataActions), make_set(notDataActions), any(assignableScopes, isServiceRole) by id"
Get classic administrators with key properties
Provides a sample of classic administrators and some of the resources relevant properties.
authorizationresources
| where type =~ 'microsoft.authorization/classicadministrators'
| extend state = properties.adminState
| extend roles = split(properties.role, ';')
| take 5
az graph query -q "authorizationresources | where type =~ 'microsoft.authorization/classicadministrators' | extend state = properties.adminState | extend roles = split(properties.role, ';') | take 5"
Azure Service Health
Active Service Health event subscription impact
Returns all active Service Health events - including service issues, planned maintenance, health advisories, and security advisories – grouped by event type and including count of impacted subscriptions.
ServiceHealthResources
| where type =~ 'Microsoft.ResourceHealth/events'
| extend eventType = tostring(properties.EventType), status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = properties.ImpactMitigationTime
| where eventType == 'ServiceIssue' and status == 'Active'
| summarize count(subscriptionId) by name
az graph query -q "ServiceHealthResources | where type =~ 'Microsoft.ResourceHealth/events' | extend eventType = tostring(properties.EventType), status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = properties.ImpactMitigationTime | where eventType == 'ServiceIssue' and status == 'Active' | summarize count(subscriptionId) by name"
All active health advisory events
Returns all active health advisory Service Health events across all subscriptions to which the user has access.
ServiceHealthResources
| where type =~ 'Microsoft.ResourceHealth/events'
| extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = todatetime(tolong(properties.ImpactMitigationTime))
| where eventType == 'HealthAdvisory' and impactMitigationTime > now()
az graph query -q "ServiceHealthResources | where type =~ 'Microsoft.ResourceHealth/events' | extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = todatetime(tolong(properties.ImpactMitigationTime)) | where eventType == 'HealthAdvisory' and impactMitigationTime > now()"
All active planned maintenance events
Returns all active planned maintenance Service Health events across all subscriptions to which the user has access.
ServiceHealthResources
| where type =~ 'Microsoft.ResourceHealth/events'
| extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = todatetime(tolong(properties.ImpactMitigationTime))
| where eventType == 'PlannedMaintenance' and impactMitigationTime > now()
az graph query -q "ServiceHealthResources | where type =~ 'Microsoft.ResourceHealth/events' | extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = todatetime(tolong(properties.ImpactMitigationTime)) | where eventType == 'PlannedMaintenance' and impactMitigationTime > now()"
All active Service Health events
Returns all active Service Health events across all subscriptions to which the user has access including service issues, planned maintenance, health advisories, and security advisories.
ServiceHealthResources
| where type =~ 'Microsoft.ResourceHealth/events'
| extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = properties.ImpactMitigationTime
| where (eventType in ('HealthAdvisory', 'SecurityAdvisory', 'PlannedMaintenance') and impactMitigationTime > now()) or (eventType == 'ServiceIssue' and status == 'Active')
az graph query -q "ServiceHealthResources | where type =~ 'Microsoft.ResourceHealth/events' | extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = properties.ImpactMitigationTime | where (eventType in ('HealthAdvisory', 'SecurityAdvisory', 'PlannedMaintenance') and impactMitigationTime > now()) or (eventType == 'ServiceIssue' and status == 'Active')"
All active service issue events
Returns all active service issue (outage) Service Health events across all subscriptions to which the user has access.
ServiceHealthResources
| where type =~ 'Microsoft.ResourceHealth/events'
| extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = properties.ImpactMitigationTime
| where eventType == 'ServiceIssue' and status == 'Active'
az graph query -q "ServiceHealthResources | where type =~ 'Microsoft.ResourceHealth/events' | extend eventType = properties.EventType, status = properties.Status, description = properties.Title, trackingId = properties.TrackingId, summary = properties.Summary, priority = properties.Priority, impactStartTime = properties.ImpactStartTime, impactMitigationTime = properties.ImpactMitigationTime | where eventType == 'ServiceIssue' and status == 'Active'"
Azure SQL
List SQL Databases and their elastic pools
The following query uses leftouter join to bring together SQL Database resources and their related elastic pools if any exist.
Resources
| where type =~ 'microsoft.sql/servers/databases'
| project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId))
| join kind=leftouter (
Resources
| where type =~ 'microsoft.sql/servers/elasticpools'
| project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state)
on elasticPoolId
| project-away elasticPoolId1
az graph query -q "Resources | where type =~ 'microsoft.sql/servers/databases' | project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId)) | join kind=leftouter ( Resources | where type =~ 'microsoft.sql/servers/elasticpools' | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state) on elasticPoolId | project-away elasticPoolId1"
Azure Storage
Find storage accounts with a specific case-insensitive tag on the resource group
Similar to the 'Find storage accounts with a specific case-sensitive tag on the resource group' query, but when it's necessary to look for a case insensitive tag name and tag value, use mv-expand with the bagexpansion parameter. This query uses more quota than the original query, so use mv-expand only if necessary.
Resources
| where type =~ 'microsoft.storage/storageaccounts'
| join kind=inner (
ResourceContainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| mv-expand bagexpansion=array tags
| where isnotempty(tags)
| where tags[0] =~ 'key1' and tags[1] =~ 'value1'
| project subscriptionId, resourceGroup)
on subscriptionId, resourceGroup
| project-away subscriptionId1, resourceGroup1
az graph query -q "Resources | where type =~ 'microsoft.storage/storageaccounts' | join kind=inner ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | mv-expand bagexpansion=array tags | where isnotempty(tags) | where tags[0] =~ 'key1' and tags[1] =~ 'value1' | project subscriptionId, resourceGroup) on subscriptionId, resourceGroup | project-away subscriptionId1, resourceGroup1"
Find storage accounts with a specific case-sensitive tag on the resource group
The following query uses an inner join to connect storage accounts with resource groups that have a specified case-sensitive tag name and tag value.
Resources
| where type =~ 'microsoft.storage/storageaccounts'
| join kind=inner (
ResourceContainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where tags['Key1'] =~ 'Value1'
| project subscriptionId, resourceGroup)
on subscriptionId, resourceGroup
| project-away subscriptionId1, resourceGroup1
az graph query -q "Resources | where type =~ 'microsoft.storage/storageaccounts' | join kind=inner ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | where tags['Key1'] =~ 'Value1' | project subscriptionId, resourceGroup) on subscriptionId, resourceGroup | project-away subscriptionId1, resourceGroup1"
List all storage accounts with specific tag value
Combine the filter functionality of the previous example and filter Azure resource type by type property. This query also limits our search for specific types of Azure resources with a specific tag name and value.
Resources
| where type =~ 'Microsoft.Storage/storageAccounts'
| where tags['tag with a space']=='Custom value'
az graph query -q "Resources | where type =~ 'Microsoft.Storage/storageAccounts' | where tags['tag with a space']=='Custom value'"
Show resources that contain storage
Instead of explicitly defining the type to match, this example query will find any Azure resource that contains the word storage.
Resources
| where type contains 'storage' | distinct type
az graph query -q "Resources | where type contains 'storage' | distinct type"
Azure Virtual Machines
Count of OS update installation done
Returns a list of status of OS update installation runs done for your machines in last 7 days.
PatchAssessmentResources
| where type !has 'softwarepatches'
| extend machineName = tostring(split(id, '/', 8)), resourceType = tostring(split(type, '/', 0)), tostring(rgName = split(id, '/', 4))
| extend prop = parse_json(properties)
| extend lTime = todatetime(prop.lastModifiedDateTime), OS = tostring(prop.osType), installedPatchCount = tostring(prop.installedPatchCount), failedPatchCount = tostring(prop.failedPatchCount), pendingPatchCount = tostring(prop.pendingPatchCount), excludedPatchCount = tostring(prop.excludedPatchCount), notSelectedPatchCount = tostring(prop.notSelectedPatchCount)
| where lTime > ago(7d)
| project lTime, RunID=name,machineName, rgName, resourceType, OS, installedPatchCount, failedPatchCount, pendingPatchCount, excludedPatchCount, notSelectedPatchCount
az graph query -q "PatchAssessmentResources | where type !has 'softwarepatches' | extend machineName = tostring(split(id, '/', 8)), resourceType = tostring(split(type, '/', 0)), tostring(rgName = split(id, '/', 4)) | extend prop = parse_json(properties) | extend lTime = todatetime(prop.lastModifiedDateTime), OS = tostring(prop.osType), installedPatchCount = tostring(prop.installedPatchCount), failedPatchCount = tostring(prop.failedPatchCount), pendingPatchCount = tostring(prop.pendingPatchCount), excludedPatchCount = tostring(prop.excludedPatchCount), notSelectedPatchCount = tostring(prop.notSelectedPatchCount) | where lTime > ago(7d) | project lTime, RunID=name,machineName, rgName, resourceType, OS, installedPatchCount, failedPatchCount, pendingPatchCount, excludedPatchCount, notSelectedPatchCount"
Count of virtual machines by availability state and Subscription Id
Returns the count of virtual machines (type Microsoft.Compute/virtualMachines) aggregated by their availability state across each of your subscriptions.
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| summarize count() by subscriptionId, AvailabilityState = tostring(properties.availabilityState)
az graph query -q "HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | summarize count() by subscriptionId, AvailabilityState = tostring(properties.availabilityState)"
Count of virtual machines by power state
Returns count of virtual machines (type Microsoft.Compute/virtualMachines) categorized according to their power state. For more information on power states, please see Power states overview.
Resources
| where type == 'microsoft.compute/virtualmachines'
| summarize count() by PowerState = tostring(properties.extended.instanceView.powerState.code)
az graph query -q "Resources | where type == 'microsoft.compute/virtualmachines' | summarize count() by PowerState = tostring(properties.extended.instanceView.powerState.code)"
Count virtual machines by OS type
Building on the previous query, we're still limiting by Azure resources of type Microsoft.Compute/virtualMachines, but are no longer limiting the number of records returned. Instead, we used summarize and count() to define how to group and aggregate the values by property, which in this example is properties.storageProfile.osDisk.osType. For an example of how this string looks in the full object, see explore resources - virtual machine discovery.
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| summarize count() by tostring(properties.storageProfile.osDisk.osType)
az graph query -q "Resources | where type =~ 'Microsoft.Compute/virtualMachines' | summarize count() by tostring(properties.storageProfile.osDisk.osType)"
Count virtual machines by OS type with extend
A different way to write the 'Count virtual machines by OS type' query is to extend a property and give it a temporary name for use within the query, in this case os. os is then used by summarize and count() as in the referenced example.
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| extend os = properties.storageProfile.osDisk.osType
| summarize count() by tostring(os)
az graph query -q "Resources | where type =~ 'Microsoft.Compute/virtualMachines' | extend os = properties.storageProfile.osDisk.osType | summarize count() by tostring(os)"
Get all New alerts from the past 30 days
This query provides a list of all the user's New alerts, from the past 30 days.
iotsecurityresources
| where type == 'microsoft.iotsecurity/locations/devicegroups/alerts'
| where todatetime(properties.startTimeUtc) > ago(30d) and properties.status == 'New'
az graph query -q "iotsecurityresources | where type == 'microsoft.iotsecurity/locations/devicegroups/alerts' | where todatetime(properties.startTimeUtc) > ago(30d) and properties.status == 'New'"
Get virtual machine scale set capacity and size
This query looks for virtual machine scale set resources and gets various details including the virtual machine size and the capacity of the scale set. The query uses the toint() function to cast the capacity to a number so that it can be sorted. Finally, the columns are renamed into custom named properties.
Resources
| where type=~ 'microsoft.compute/virtualmachinescalesets'
| where name contains 'contoso'
| project subscriptionId, name, location, resourceGroup, Capacity = toint(sku.capacity), Tier = sku.name
| order by Capacity desc
az graph query -q "Resources | where type=~ 'microsoft.compute/virtualmachinescalesets' | where name contains 'contoso' | project subscriptionId, name, location, resourceGroup, Capacity = toint(sku.capacity), Tier = sku.name | order by Capacity desc"
List all extensions installed on a virtual machine
First, this query uses extend on the virtual machines resource type to get the ID in uppercase (toupper()) the ID, get the operating system name and type, and get the virtual machine size. Getting the resource ID in uppercase is a good way to prepare to join to another property. Then, the query uses join with kind as leftouter to get virtual machine extensions by matching an uppercase substring of the extension ID. The portion of the ID before "/extensions/<ExtensionName>" is the same format as the virtual machines ID, so we use this property for the join. summarize is then used with make_list on the name of the virtual machine extension to combine the name of each extension where id, OSName, OSType, and VMSize are the same into a single array property. Lastly, we order by lowercase OSName with asc. By default, order by is descending.
Resources
| where type == 'microsoft.compute/virtualmachines'
| extend
JoinID = toupper(id),
OSName = tostring(properties.osProfile.computerName),
OSType = tostring(properties.storageProfile.osDisk.osType),
VMSize = tostring(properties.hardwareProfile.vmSize)
| join kind=leftouter(
Resources
| where type == 'microsoft.compute/virtualmachines/extensions'
| extend
VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
ExtensionName = name
) on $left.JoinID == $right.VMId
| summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize
| order by tolower(OSName) asc
az graph query -q "Resources | where type == 'microsoft.compute/virtualmachines' | extend JoinID = toupper(id), OSName = tostring(properties.osProfile.computerName), OSType = tostring(properties.storageProfile.osDisk.osType), VMSize = tostring(properties.hardwareProfile.vmSize) | join kind=leftouter( Resources | where type == 'microsoft.compute/virtualmachines/extensions' | extend VMId = toupper(substring(id, 0, indexof(id, '/extensions'))), ExtensionName = name ) on \$left.JoinID == \$right.VMId | summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize | order by tolower(OSName) asc"
List available OS updates for all your machines grouped by update category
Returns a list of pending OS for your machines.
PatchAssessmentResources
| where type !has 'softwarepatches'
| extend prop = parse_json(properties)
| extend lastTime = properties.lastModifiedDateTime
| extend updateRollupCount = prop.availablePatchCountByClassification.updateRollup, featurePackCount = prop.availablePatchCountByClassification.featurePack, servicePackCount = prop.availablePatchCountByClassification.servicePack, definitionCount = prop.availablePatchCountByClassification.definition, securityCount = prop.availablePatchCountByClassification.security, criticalCount = prop.availablePatchCountByClassification.critical, updatesCount = prop.availablePatchCountByClassification.updates, toolsCount = prop.availablePatchCountByClassification.tools, otherCount = prop.availablePatchCountByClassification.other, OS = prop.osType
| project lastTime, id, OS, updateRollupCount, featurePackCount, servicePackCount, definitionCount, securityCount, criticalCount, updatesCount, toolsCount, otherCount
az graph query -q "PatchAssessmentResources | where type !has 'softwarepatches' | extend prop = parse_json(properties) | extend lastTime = properties.lastModifiedDateTime | extend updateRollupCount = prop.availablePatchCountByClassification.updateRollup, featurePackCount = prop.availablePatchCountByClassification.featurePack, servicePackCount = prop.availablePatchCountByClassification.servicePack, definitionCount = prop.availablePatchCountByClassification.definition, securityCount = prop.availablePatchCountByClassification.security, criticalCount = prop.availablePatchCountByClassification.critical, updatesCount = prop.availablePatchCountByClassification.updates, toolsCount = prop.availablePatchCountByClassification.tools, otherCount = prop.availablePatchCountByClassification.other, OS = prop.osType | project lastTime, id, OS, updateRollupCount, featurePackCount, servicePackCount, definitionCount, securityCount, criticalCount, updatesCount, toolsCount, otherCount"
List of Linux OS update installation done
Returns a list of status of Linux Server - OS update installation runs done for your machines in last 7 days.
PatchAssessmentResources
| where type has 'softwarepatches' and properties has 'version'
| extend machineName = tostring(split(id, '/', 8)), resourceType = tostring(split(type, '/', 0)), tostring(rgName = split(id, '/', 4)), tostring(RunID = split(id, '/', 10))
| extend prop = parse_json(properties)
| extend lTime = todatetime(prop.lastModifiedDateTime), patchName = tostring(prop.patchName), version = tostring(prop.version), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications)
| where lTime > ago(7d)
| project lTime, RunID, machineName, rgName, resourceType, patchName, version, classifications, installationState
| sort by RunID
az graph query -q "PatchAssessmentResources | where type has 'softwarepatches' and properties has 'version' | extend machineName = tostring(split(id, '/', 8)), resourceType = tostring(split(type, '/', 0)), tostring(rgName = split(id, '/', 4)), tostring(RunID = split(id, '/', 10)) | extend prop = parse_json(properties) | extend lTime = todatetime(prop.lastModifiedDateTime), patchName = tostring(prop.patchName), version = tostring(prop.version), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications) | where lTime > ago(7d) | project lTime, RunID, machineName, rgName, resourceType, patchName, version, classifications, installationState | sort by RunID"
List of virtual machines and associated availability states by Resource Ids
Returns the latest list of virtual machines (type Microsoft.Compute/virtualMachines) aggregated by availability state. The query also provides the associated Resource Id based on properties.targetResourceId, for easy debugging and mitigation. Availability states can be one of four values: Available, Unavailable, Degraded and Unknown. For more details on what each of the availability states mean, please see Azure Resource Health overview.
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)
az graph query -q "HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)"
List of virtual machines by availability state and power state with Resource Ids and resource Groups
Returns list of virtual machines (type Microsoft.Compute/virtualMachines) aggregated on their power state and availability state to provide a cohesive state of health for your virtual machines. The query also provides details on the resource group and resource Id associated with each entry for detailed visibility into your resources.
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| project resourceGroup, Id = tolower(id), PowerState = tostring( properties.extended.instanceView.powerState.code)
| join kind=leftouter (
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| where tostring(properties.targetResourceType) =~ 'microsoft.compute/virtualmachines'
| project targetResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState))
on $left.Id == $right.targetResourceId
| project-away targetResourceId
| where PowerState != 'PowerState/deallocated'
az graph query -q "Resources | where type =~ 'microsoft.compute/virtualmachines' | project resourceGroup, Id = tolower(id), PowerState = tostring( properties.extended.instanceView.powerState.code) | join kind=leftouter ( HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | where tostring(properties.targetResourceType) =~ 'microsoft.compute/virtualmachines' | project targetResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)) on \$left.Id == \$right.targetResourceId | project-away targetResourceId | where PowerState != 'PowerState/deallocated'"
List of virtual machines that are not Available by Resource Ids
Returns the latest list of virtual machines (type Microsoft.Compute/virtualMachines) aggregated by their availability state. The populated list only highlights virtual machines whose availability state is not "Available" to ensure you are aware of all the concerning states your virtual machines are in. When all your virtual machines are Available, you can expect to receive no results.
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| where tostring(properties.availabilityState) != 'Available'
| summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)
az graph query -q "HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | where tostring(properties.availabilityState) != 'Available' | summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)"
List of Windows Server OS update installation done
Returns a list of status of Windows Server - OS update installation runs done for your machines in last 7 days.
PatchAssessmentResources
| where type has 'softwarepatches' and properties !has 'version'
| extend machineName = tostring(split(id, '/', 8)), resourceType = tostring(split(type, '/', 0)), tostring(rgName = split(id, '/', 4)), tostring(RunID = split(id, '/', 10))
| extend prop = parse_json(properties)
| extend lTime = todatetime(prop.lastModifiedDateTime), patchName = tostring(prop.patchName), kbId = tostring(prop.kbId), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications)
| where lTime > ago(7d)
| project lTime, RunID, machineName, rgName, resourceType, patchName, kbId, classifications, installationState
| sort by RunID
az graph query -q "PatchAssessmentResources | where type has 'softwarepatches' and properties !has 'version' | extend machineName = tostring(split(id, '/', 8)), resourceType = tostring(split(type, '/', 0)), tostring(rgName = split(id, '/', 4)), tostring(RunID = split(id, '/', 10)) | extend prop = parse_json(properties) | extend lTime = todatetime(prop.lastModifiedDateTime), patchName = tostring(prop.patchName), kbId = tostring(prop.kbId), installationState = tostring(prop.installationState), classifications = tostring(prop.classifications) | where lTime > ago(7d) | project lTime, RunID, machineName, rgName, resourceType, patchName, kbId, classifications, installationState | sort by RunID"
List virtual machines with their network interface and public IP
This query uses two leftouter join commands to bring together virtual machines created with the Resource Manager deployment model, their related network interfaces, and any public IP address related to those network interfaces.
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend nics=array_length(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| where nics == 1 or nic.properties.primary =~ 'true' or isempty(nic)
| project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id)
| join kind=leftouter (
Resources
| where type =~ 'microsoft.network/networkinterfaces'
| extend ipConfigsCount=array_length(properties.ipConfigurations)
| mv-expand ipconfig=properties.ipConfigurations
| where ipConfigsCount == 1 or ipconfig.properties.primary =~ 'true'
| project nicId = id, publicIpId = tostring(ipconfig.properties.publicIPAddress.id))
on nicId
| project-away nicId1
| summarize by vmId, vmName, vmSize, nicId, publicIpId
| join kind=leftouter (
Resources
| where type =~ 'microsoft.network/publicipaddresses'
| project publicIpId = id, publicIpAddress = properties.ipAddress)
on publicIpId
| project-away publicIpId1
az graph query -q "Resources | where type =~ 'microsoft.compute/virtualmachines' | extend nics=array_length(properties.networkProfile.networkInterfaces) | mv-expand nic=properties.networkProfile.networkInterfaces | where nics == 1 or nic.properties.primary =~ 'true' or isempty(nic) | project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id) | join kind=leftouter ( Resources | where type =~ 'microsoft.network/networkinterfaces' | extend ipConfigsCount=array_length(properties.ipConfigurations) | mv-expand ipconfig=properties.ipConfigurations | where ipConfigsCount == 1 or ipconfig.properties.primary =~ 'true' | project nicId = id, publicIpId = tostring(ipconfig.properties.publicIPAddress.id)) on nicId | project-away nicId1 | summarize by vmId, vmName, vmSize, nicId, publicIpId | join kind=leftouter ( Resources | where type =~ 'microsoft.network/publicipaddresses' | project publicIpId = id, publicIpAddress = properties.ipAddress) on publicIpId | project-away publicIpId1"
Show all virtual machines ordered by name in descending order
To list only virtual machines (which are type Microsoft.Compute/virtualMachines), we can match the property type in the results. Similar to the previous query, desc changes the order by to be descending. The =~ in the type match tells Resource Graph to be case insensitive.
Resources
| project name, location, type
| where type =~ 'Microsoft.Compute/virtualMachines'
| order by name desc
az graph query -q "Resources | project name, location, type | where type =~ 'Microsoft.Compute/virtualMachines' | order by name desc"
Show first five virtual machines by name and their OS type
This query uses top to only retrieve five matching records that are ordered by name. The type of the Azure resource is Microsoft.Compute/virtualMachines. project tells Azure Resource Graph which properties to include.
Resources
| where type =~ 'Microsoft.Compute/virtualMachines'
| project name, properties.storageProfile.osDisk.osType
| top 5 by name desc
az graph query -q "Resources | where type =~ 'Microsoft.Compute/virtualMachines' | project name, properties.storageProfile.osDisk.osType | top 5 by name desc"
Summarize virtual machine by the power states extended property
This query uses the extended properties on virtual machines to summarize by power states.
Resources
| where type == 'microsoft.compute/virtualmachines'
| summarize count() by tostring(properties.extended.instanceView.powerState.code)
az graph query -q "Resources | where type == 'microsoft.compute/virtualmachines' | summarize count() by tostring(properties.extended.instanceView.powerState.code)"
Virtual machines matched by regex
This query looks for virtual machines that match a regular expression (known as regex). The matches regex @ allows us to define the regex to match, which is ^Contoso(.*)[0-9]+$. That regex definition is explained as:
^- Match must start at the beginning of the string.Contoso- The case-sensitive string.(.*)- A subexpression match:.- Matches any single character (except a new line).*- Matches previous element zero or more times.
[0-9]- Character group match for numbers 0 through 9.+- Matches previous element one or more times.$- Match of the previous element must occur at the end of the string.
After matching by name, the query projects the name and orders by name ascending.
Resources
| where type =~ 'microsoft.compute/virtualmachines' and name matches regex @'^Contoso(.*)[0-9]+$'
| project name
| order by name asc
az graph query -q "Resources | where type =~ 'microsoft.compute/virtualmachines' and name matches regex @'^Contoso(.*)[0-9]+\$' | project name | order by name asc"
General
Count Azure resources
This query returns number of Azure resources that exist in the subscriptions that you have access to. It's also a good query to validate your shell of choice has the appropriate Azure Resource Graph components installed and in working order.
Resources
| summarize count()
az graph query -q "Resources | summarize count()"
List impacted resources when transferring an Azure subscription
Returns some of the Azure resources that are impacted when you transfer a subscription to a different Azure Active Directory (Azure AD) directory. You will need to re-create some of the resources that existed prior to the subscription transfer.
Resources
| where type in (
'microsoft.managedidentity/userassignedidentities',
'microsoft.keyvault/vaults',
'microsoft.sql/servers/databases',
'microsoft.datalakestore/accounts',
'microsoft.containerservice/managedclusters')
or identity has 'SystemAssigned'
or (type =~ 'microsoft.storage/storageaccounts' and properties['isHnsEnabled'] == true)
| summarize count() by type
az graph query -q "Resources | where type in ( 'microsoft.managedidentity/userassignedidentities', 'microsoft.keyvault/vaults', 'microsoft.sql/servers/databases', 'microsoft.datalakestore/accounts', 'microsoft.containerservice/managedclusters') or identity has 'SystemAssigned' or (type =~ 'microsoft.storage/storageaccounts' and properties['isHnsEnabled'] == true) | summarize count() by type"
List resources sorted by name
This query returns any type of resource, but only the name, type, and location properties. It uses order by to sort the properties by the name property in ascending (asc) order.
Resources
| project name, type, location
| order by name asc
az graph query -q "Resources | project name, type, location | order by name asc"
Remove columns from results
The following query uses summarize to count resources by subscription, join to combine it with subscription details from ResourceContainers table, then project-away to remove some of the columns.
Resources
| summarize resourceCount=count() by subscriptionId
| join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
| project-away subscriptionId, subscriptionId1
az graph query -q "Resources | summarize resourceCount=count() by subscriptionId | join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId | project-away subscriptionId, subscriptionId1"
Show resource types and API versions
Resource Graph primarily uses the most recent non-preview version of a Resource Provider's API to GET resource properties during an update. In some cases, the API version used has been overridden to provide more current or widely used properties in the results. The following query details the API version used for gathering properties on each resource type:
Resources
| distinct type, apiVersion
| where isnotnull(apiVersion)
| order by type asc
az graph query -q "Resources | distinct type, apiVersion | where isnotnull(apiVersion) | order by type asc"
IoT Defender
Count all sensors by type
This query summarizes all sensors by their type (OT, EIoT).
iotsecurityresources
| where type == 'microsoft.iotsecurity/sensors'
| summarize count() by tostring(properties.sensorType)
az graph query -q "iotsecurityresources | where type == 'microsoft.iotsecurity/sensors' | summarize count() by tostring(properties.sensorType)"
Count how many IoT Devices there are in your network, by operation system
This query summarizes all IoT Devices by their operation system's platform.
iotsecurityresources
| where type == 'microsoft.iotsecurity/locations/devicegroups/devices'
| summarize count() by tostring(properties.operatingSystem.platform)
az graph query -q "iotsecurityresources | where type == 'microsoft.iotsecurity/locations/devicegroups/devices' | summarize count() by tostring(properties.operatingSystem.platform)"
Get all High severity recommendations
This query provides a list of all the user's High severity recommendations.
iotsecurityresources
| where type == 'microsoft.iotsecurity/locations/devicegroups/recommendations'
| where properties.severity == 'High'
az graph query -q "iotsecurityresources | where type == 'microsoft.iotsecurity/locations/devicegroups/recommendations' | where properties.severity == 'High'"
List sites with a specific tag value
This query provides a list of all sites with specific tag value.
iotsecurityresources
| where type == 'microsoft.iotsecurity/sites'
| where properties.tags['key'] =~ 'value1'
az graph query -q "iotsecurityresources | where type == 'microsoft.iotsecurity/sites' | where properties.tags['key'] =~ 'value1'"
Management groups
Count of subscriptions per management group
Summarizes the count of subscriptions in each management group.
ResourceContainers
| where type =~ 'microsoft.management/managementgroups'
| project mgname = name
| join kind=leftouter (resourcecontainers | where type=~ 'microsoft.resources/subscriptions'
| extend mgParent = properties.managementGroupAncestorsChain | project id, mgname = tostring(mgParent[0].name)) on mgname
| summarize count() by mgname
az graph query -q "ResourceContainers | where type =~ 'microsoft.management/managementgroups' | project mgname = name | join kind=leftouter (resourcecontainers | where type=~ 'microsoft.resources/subscriptions' | extend mgParent = properties.managementGroupAncestorsChain | project id, mgname = tostring(mgParent[0].name)) on mgname | summarize count() by mgname"
List all management group ancestors for a specified management group
Provides the management group hierarchy details for the management group specified in the query scope. In this example, the management group is named Application.
ResourceContainers
| where type =~ 'microsoft.management/managementgroups'
| extend mgParent = properties.details.managementGroupAncestorsChain
| mv-expand with_itemindex=MGHierarchy mgParent
| project name, properties.displayName, mgParent, MGHierarchy, mgParent.name
az graph query -q "ResourceContainers | where type =~ 'microsoft.management/managementgroups' | extend mgParent = properties.details.managementGroupAncestorsChain | mv-expand with_itemindex=MGHierarchy mgParent | project name, properties.displayName, mgParent, MGHierarchy, mgParent.name" --management-groups Application
List all management group ancestors for a specified subscription
Provides the management group hierarchy details for the subscription specified in the query scope. In this example, the subscription GUID is 11111111-1111-1111-1111-111111111111.
ResourceContainers
| where type =~ 'microsoft.resources/subscriptions'
| extend mgParent = properties.managementGroupAncestorsChain
| mv-expand with_itemindex=MGHierarchy mgParent
| project subscriptionId, name, mgParent, MGHierarchy, mgParent.name
az graph query -q "ResourceContainers | where type =~ 'microsoft.resources/subscriptions' | extend mgParent = properties.managementGroupAncestorsChain | mv-expand with_itemindex=MGHierarchy mgParent | project subscriptionId, name, mgParent, MGHierarchy, mgParent.name" --subscriptions 11111111-1111-1111-1111-111111111111
List all subscriptions under a specified management group
Provides the name and subscription ID of all subscriptions under the management group specified in the query scope. In this example, the management group is named Application.
ResourceContainers
| where type =~ 'microsoft.resources/subscriptions'
| project subscriptionId, name
az graph query -q "ResourceContainers | where type =~ 'microsoft.resources/subscriptions' | project subscriptionId, name" --management-groups Application
Secure score per management group
Returns secure score per management group.
SecurityResources
| where type == 'microsoft.security/securescores'
| project subscriptionId,
subscriptionTotal = iff(properties.score.max == 0, 0.00, round(tolong(properties.weight) * todouble(properties.score.current)/tolong(properties.score.max),2)),
weight = tolong(iff(properties.weight == 0, 1, properties.weight))
| join kind=leftouter (
ResourceContainers
| where type == 'microsoft.resources/subscriptions' and properties.state == 'Enabled'
| project subscriptionId, mgChain=properties.managementGroupAncestorsChain )
on subscriptionId
| mv-expand mg=mgChain
| summarize sumSubs = sum(subscriptionTotal), sumWeight = sum(weight), resultsNum = count() by tostring(mg.displayName), mgId = tostring(mg.name)
| extend secureScore = iff(tolong(resultsNum) == 0, 404.00, round(sumSubs/sumWeight*100,2))
| project mgName=mg_displayName, mgId, sumSubs, sumWeight, resultsNum, secureScore
| order by mgName asc
az graph query -q "SecurityResources | where type == 'microsoft.security/securescores' | project subscriptionId, subscriptionTotal = iff(properties.score.max == 0, 0.00, round(tolong(properties.weight) * todouble(properties.score.current)/tolong(properties.score.max),2)), weight = tolong(iff(properties.weight == 0, 1, properties.weight)) | join kind=leftouter ( ResourceContainers | where type == 'microsoft.resources/subscriptions' and properties.state == 'Enabled' | project subscriptionId, mgChain=properties.managementGroupAncestorsChain ) on subscriptionId | mv-expand mg=mgChain | summarize sumSubs = sum(subscriptionTotal), sumWeight = sum(weight), resultsNum = count() by tostring(mg.displayName), mgId = tostring(mg.name) | extend secureScore = iff(tolong(resultsNum) == 0, 404.00, round(sumSubs/sumWeight*100,2)) | project mgName=mg_displayName, mgId, sumSubs, sumWeight, resultsNum, secureScore | order by mgName asc"
Microsoft Defender
Display all active Microsoft Defender for Cloud alerts
Returns a list of all active alerts in your Microsoft Defender for Cloud tenant.
securityresources
| where type =~ 'microsoft.security/locations/alerts'
| where properties.Status in ('Active')
| where properties.Severity in ('Low', 'Medium', 'High')
| project alert_type = tostring(properties.AlertType), SystemAlertId = tostring(properties.SystemAlertId), ResourceIdentifiers = todynamic(properties.ResourceIdentifiers)
az graph query -q "securityresources | where type =~ 'microsoft.security/locations/alerts' | where properties.Status in ('Active') | where properties.Severity in ('Low', 'Medium', 'High') | project alert_type = tostring(properties AlertType), SystemAlertId = tostring(properties.SystemAlertId), ResourceIdentifiers = todynamic(properties ResourceIdentifiers)"
Controls secure score per subscription
Returns controls secure score per subscription.
SecurityResources
| where type == 'microsoft.security/securescores/securescorecontrols'
| extend controlName=properties.displayName,
controlId=properties.definition.name,
notApplicableResourceCount=properties.notApplicableResourceCount,
unhealthyResourceCount=properties.unhealthyResourceCount,
healthyResourceCount=properties.healthyResourceCount,
percentageScore=properties.score.percentage,
currentScore=properties.score.current,
maxScore=properties.definition.properties.maxScore,
weight=properties.weight,
controlType=properties.definition.properties.source.sourceType,
controlRecommendationIds=properties.definition.properties.assessmentDefinitions
| project tenantId, subscriptionId, controlName, controlId, unhealthyResourceCount, healthyResourceCount, notApplicableResourceCount, percentageScore, currentScore, maxScore, weight, controlType, controlRecommendationIds
az graph query -q "SecurityResources | where type == 'microsoft.security/securescores/securescorecontrols' | extend controlName=properties.displayName, controlId=properties.definition.name, notApplicableResourceCount=properties.notApplicableResourceCount, unhealthyResourceCount=properties.unhealthyResourceCount, healthyResourceCount=properties.healthyResourceCount, percentageScore=properties.score.percentage, currentScore=properties.score.current, maxScore=properties.definition.properties.maxScore, weight=properties.weight, controlType=properties.definition.properties.source.sourceType, controlRecommendationIds=properties.definition.properties.assessmentDefinitions | project tenantId, subscriptionId, controlName, controlId, unhealthyResourceCount, healthyResourceCount, notApplicableResourceCount, percentageScore, currentScore, maxScore, weight, controlType, controlRecommendationIds"
Count healthy, unhealthy, and not applicable resources per recommendation
Returns count of healthy, unhealthy, and not applicable resources per recommendation. Use summarize and count to define how to group and aggregate the values by property.
SecurityResources
| where type == 'microsoft.security/assessments'
| extend resourceId=id,
recommendationId=name,
resourceType=type,
recommendationName=properties.displayName,
source=properties.resourceDetails.Source,
recommendationState=properties.status.code,
description=properties.metadata.description,
assessmentType=properties.metadata.assessmentType,
remediationDescription=properties.metadata.remediationDescription,
policyDefinitionId=properties.metadata.policyDefinitionId,
implementationEffort=properties.metadata.implementationEffort,
recommendationSeverity=properties.metadata.severity,
category=properties.metadata.categories,
userImpact=properties.metadata.userImpact,
threats=properties.metadata.threats,
portalLink=properties.links.azurePortal
| summarize numberOfResources=count(resourceId) by tostring(recommendationName), tostring(recommendationState)
az graph query -q "SecurityResources | where type == 'microsoft.security/assessments' | extend resourceId=id, recommendationId=name, resourceType=type, recommendationName=properties.displayName, source=properties.resourceDetails.Source, recommendationState=properties.status.code, description=properties.metadata.description, assessmentType=properties.metadata.assessmentType, remediationDescription=properties.metadata.remediationDescription, policyDefinitionId=properties.metadata.policyDefinitionId, implementationEffort=properties.metadata.implementationEffort, recommendationSeverity=properties.metadata.severity, category=properties.metadata.categories, userImpact=properties.metadata.userImpact, threats=properties.metadata.threats, portalLink=properties.links.azurePortal | summarize numberOfResources=count(resourceId) by tostring(recommendationName), tostring(recommendationState)"
Get all IoT alerts on hub, filtered by type
Returns all IoT alerts for a specific hub (replace placeholder {hub_id}) and alert type (replace placeholder {alert_type}).
SecurityResources
| where type =~ 'microsoft.security/iotalerts' and id contains '{hub_id}' and properties.alertType contains '{alert_type}'
az graph query -q "SecurityResources | where type =~ 'microsoft.security/iotalerts' and id contains '{hub_id}' and properties.alertType contains '{alert_type}'"
Get sensitivity insight of a specific resource
Returns sensitivity insight of a specific resource (replace placeholder {resource_id}).
SecurityResources
| where type == 'microsoft.security/insights/classification'
| where properties.associatedResource contains '$resource_id'
| project SensitivityInsight = properties.insightProperties.purviewCatalogs[0].sensitivity
az graph query -q "SecurityResources | where type == 'microsoft.security/insights/classification' | where properties.associatedResource contains '\$resource_id' | project SensitivityInsight = properties.insightProperties.purviewCatalogs[0].sensitivity"
Get specific IoT alert
Returns specific IoT alert by a provided system alert ID (replace placeholder {system_Alert_Id}).
SecurityResources
| where type =~ 'microsoft.security/iotalerts' and properties.systemAlertId contains '{system_Alert_Id}'
az graph query -q "SecurityResources | where type =~ 'microsoft.security/iotalerts' and properties.systemAlertId contains '{system_Alert_Id}'"
List Container Registry vulnerability assessment results
Returns all vulnerabilities found on container images. Microsoft Defender for Containers has to be enabled in order to view these security findings.
SecurityResources
| where type == 'microsoft.security/assessments'
| where properties.displayName contains 'Container registry images should have vulnerability findings resolved'
| summarize by assessmentKey=name //the ID of the assessment
| join kind=inner (
securityresources
| where type == 'microsoft.security/assessments/subassessments'
| extend assessmentKey = extract('.*assessments/(.+?)/.*',1, id)
) on assessmentKey
| project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
| extend description = properties.description,
displayName = properties.displayName,
resourceId = properties.resourceDetails.id,
resourceSource = properties.resourceDetails.source,
category = properties.category,
severity = properties.status.severity,
code = properties.status.code,
timeGenerated = properties.timeGenerated,
remediation = properties.remediation,
impact = properties.impact,
vulnId = properties.id,
additionalData = properties.additionalData
az graph query -q "SecurityResources | where type == 'microsoft.security/assessments' | where properties.displayName contains 'Container registry images should have vulnerability findings resolved' | summarize by assessmentKey=name //the ID of the assessment | join kind=inner ( securityresources | where type == 'microsoft.security/assessments/subassessments' | extend assessmentKey = extract('.*assessments/(.+?)/.*',1, id) ) on assessmentKey | project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId | extend description = properties.description, displayName = properties.displayName, resourceId = properties.resourceDetails.id, resourceSource = properties.resourceDetails.source, category = properties.category, severity = properties.status.severity, code = properties.status.code, timeGenerated = properties.timeGenerated, remediation = properties.remediation, impact = properties.impact, vulnId = properties.id, additionalData = properties.additionalData"
List Microsoft Defender recommendations
Returns all Microsoft Defender assessments, organized in tabular manner with field per property.
SecurityResources
| where type == 'microsoft.security/assessments'
| extend resourceId=id,
recommendationId=name,
recommendationName=properties.displayName,
source=properties.resourceDetails.Source,
recommendationState=properties.status.code,
description=properties.metadata.description,
assessmentType=properties.metadata.assessmentType,
remediationDescription=properties.metadata.remediationDescription,
policyDefinitionId=properties.metadata.policyDefinitionId,
implementationEffort=properties.metadata.implementationEffort,
recommendationSeverity=properties.metadata.severity,
category=properties.metadata.categories,
userImpact=properties.metadata.userImpact,
threats=properties.metadata.threats,
portalLink=properties.links.azurePortal
| project tenantId, subscriptionId, resourceId, recommendationName, recommendationId, recommendationState, recommendationSeverity, description, remediationDescription, assessmentType, policyDefinitionId, implementationEffort, userImpact, category, threats, source, portalLink
az graph query -q "SecurityResources | where type == 'microsoft.security/assessments' | extend resourceId=id, recommendationId=name, recommendationName=properties.displayName, source=properties.resourceDetails.Source, recommendationState=properties.status.code, description=properties.metadata.description, assessmentType=properties.metadata.assessmentType, remediationDescription=properties.metadata.remediationDescription, policyDefinitionId=properties.metadata.policyDefinitionId, implementationEffort=properties.metadata.implementationEffort, recommendationSeverity=properties.metadata.severity, category=properties.metadata.categories, userImpact=properties.metadata.userImpact, threats=properties.metadata.threats, portalLink=properties.links.azurePortal | project tenantId, subscriptionId, resourceId, recommendationName, recommendationId, recommendationState, recommendationSeverity, description, remediationDescription, assessmentType, policyDefinitionId, implementationEffort, userImpact, category, threats, source, portalLink"
List Qualys vulnerability assessment results
Returns all the vulnerabilities found on virtual machines that have a Qualys agent installed.
SecurityResources
| where type == 'microsoft.security/assessments'
| where * contains 'vulnerabilities in your virtual machines'
| summarize by assessmentKey=name //the ID of the assessment
| join kind=inner (
securityresources
| where type == 'microsoft.security/assessments/subassessments'
| extend assessmentKey = extract('.*assessments/(.+?)/.*',1, id)
) on assessmentKey
| project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId
| extend description = properties.description,
displayName = properties.displayName,
resourceId = properties.resourceDetails.id,
resourceSource = properties.resourceDetails.source,
category = properties.category,
severity = properties.status.severity,
code = properties.status.code,
timeGenerated = properties.timeGenerated,
remediation = properties.remediation,
impact = properties.impact,
vulnId = properties.id,
additionalData = properties.additionalData
az graph query -q "SecurityResources | where type == 'microsoft.security/assessments' | where * contains 'vulnerabilities in your virtual machines' | summarize by assessmentKey=name //the ID of the assessment | join kind=inner ( securityresources | where type == 'microsoft.security/assessments/subassessments' | extend assessmentKey = extract('.*assessments/(.+?)/.*',1, id) ) on assessmentKey | project assessmentKey, subassessmentKey=name, id, parse_json(properties), resourceGroup, subscriptionId, tenantId | extend description = properties.description, displayName = properties.displayName, resourceId = properties.resourceDetails.id, resourceSource = properties.resourceDetails.source, category = properties.category, severity = properties.status.severity, code = properties.status.code, timeGenerated = properties.timeGenerated, remediation = properties.remediation, impact = properties.impact, vulnId = properties.id, additionalData = properties.additionalData"
Regulatory compliance assessments state
Returns regulatory compliance assessments state per compliance standard and control.
SecurityResources
| where type == 'microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols/regulatorycomplianceassessments'
| extend assessmentName=properties.description,
complianceStandard=extract(@'/regulatoryComplianceStandards/(.+)/regulatoryComplianceControls',1,id),
complianceControl=extract(@'/regulatoryComplianceControls/(.+)/regulatoryComplianceAssessments',1,id),
skippedResources=properties.skippedResources,
passedResources=properties.passedResources,
failedResources=properties.failedResources,
state=properties.state
| project tenantId, subscriptionId, id, complianceStandard, complianceControl, assessmentName, state, skippedResources, passedResources, failedResources
az graph query -q "SecurityResources | where type == 'microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols/regulatorycomplianceassessments' | extend assessmentName=properties.description, complianceStandard=extract(@'/regulatoryComplianceStandards/(.+)/regulatoryComplianceControls',1,id), complianceControl=extract(@'/regulatoryComplianceControls/(.+)/regulatoryComplianceAssessments',1,id), skippedResources=properties.skippedResources, passedResources=properties.passedResources, failedResources=properties.failedResources, state=properties.state | project tenantId, subscriptionId, id, complianceStandard, complianceControl, assessmentName, state, skippedResources, passedResources, failedResources"
Regulatory compliance state per compliance standard
Returns regulatory compliance state per compliance standard per subscription.
SecurityResources
| where type == 'microsoft.security/regulatorycompliancestandards'
| extend complianceStandard=name,
state=properties.state,
passedControls=properties.passedControls,
failedControls=properties.failedControls,
skippedControls=properties.skippedControls,
unsupportedControls=properties.unsupportedControls
| project tenantId, subscriptionId, complianceStandard, state, passedControls, failedControls, skippedControls, unsupportedControls
az graph query -q "SecurityResources | where type == 'microsoft.security/regulatorycompliancestandards' | extend complianceStandard=name, state=properties.state, passedControls=properties.passedControls, failedControls=properties.failedControls, skippedControls=properties.skippedControls, unsupportedControls=properties.unsupportedControls | project tenantId, subscriptionId, complianceStandard, state, passedControls, failedControls, skippedControls, unsupportedControls"
Secure score per management group
Returns secure score per management group.
SecurityResources
| where type == 'microsoft.security/securescores'
| project subscriptionId,
subscriptionTotal = iff(properties.score.max == 0, 0.00, round(tolong(properties.weight) * todouble(properties.score.current)/tolong(properties.score.max),2)),
weight = tolong(iff(properties.weight == 0, 1, properties.weight))
| join kind=leftouter (
ResourceContainers
| where type == 'microsoft.resources/subscriptions' and properties.state == 'Enabled'
| project subscriptionId, mgChain=properties.managementGroupAncestorsChain )
on subscriptionId
| mv-expand mg=mgChain
| summarize sumSubs = sum(subscriptionTotal), sumWeight = sum(weight), resultsNum = count() by tostring(mg.displayName), mgId = tostring(mg.name)
| extend secureScore = iff(tolong(resultsNum) == 0, 404.00, round(sumSubs/sumWeight*100,2))
| project mgName=mg_displayName, mgId, sumSubs, sumWeight, resultsNum, secureScore
| order by mgName asc
az graph query -q "SecurityResources | where type == 'microsoft.security/securescores' | project subscriptionId, subscriptionTotal = iff(properties.score.max == 0, 0.00, round(tolong(properties.weight) * todouble(properties.score.current)/tolong(properties.score.max),2)), weight = tolong(iff(properties.weight == 0, 1, properties.weight)) | join kind=leftouter ( ResourceContainers | where type == 'microsoft.resources/subscriptions' and properties.state == 'Enabled' | project subscriptionId, mgChain=properties.managementGroupAncestorsChain ) on subscriptionId | mv-expand mg=mgChain | summarize sumSubs = sum(subscriptionTotal), sumWeight = sum(weight), resultsNum = count() by tostring(mg.displayName), mgId = tostring(mg.name) | extend secureScore = iff(tolong(resultsNum) == 0, 404.00, round(sumSubs/sumWeight*100,2)) | project mgName=mg_displayName, mgId, sumSubs, sumWeight, resultsNum, secureScore | order by mgName asc"
Secure score per subscription
Returns secure score per subscription.
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
az graph query -q "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"
Show Defender for Cloud plan pricing tier per subscription
Returns Defender for Cloud pricing tier plan per subscription.
SecurityResources
| where type == 'microsoft.security/pricings'
| project Subscription= subscriptionId, Azure_Defender_plan= name, Status= properties.pricingTier
az graph query -q "SecurityResources | where type == 'microsoft.security/pricings' | project Subscription= subscriptionId, Azure_Defender_plan= name, Status= properties.pricingTier"
Networking
Count resources that have IP addresses configured by subscription
Using the 'List all public IP addresses' example query and adding summarize and count(), we can get a list by subscription of resources with configured IP addresses.
Resources
| where type contains 'publicIPAddresses' and isnotempty(properties.ipAddress)
| summarize count () by subscriptionId
az graph query -q "Resources | where type contains 'publicIPAddresses' and isnotempty(properties.ipAddress) | summarize count () by subscriptionId"
Get virtual networks and subnets of network interfaces
Use a regular expression parse to get the virtual network and subnet names from the resource ID property. While parse enables getting data from a complex field, it's optimal to access properties directly if they exist instead of using parse.
Resources
| where type =~ 'microsoft.network/networkinterfaces'
| project id, ipConfigurations = properties.ipConfigurations
| mvexpand ipConfigurations
| project id, subnetId = tostring(ipConfigurations.properties.subnet.id)
| parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
| project id, virtualNetwork, subnet
az graph query -q "Resources | where type =~ 'microsoft.network/networkinterfaces' | project id, ipConfigurations = properties.ipConfigurations | mvexpand ipConfigurations | project id, subnetId = tostring(ipConfigurations.properties.subnet.id) | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet | project id, virtualNetwork, subnet"
List all public IP addresses
Similar to the 'Show resources that contain storage' query, find everything that is a type with the word publicIPAddresses. This query expands on that pattern to only include results where properties.ipAddress isnotempty, to only return the properties.ipAddress, and to limit the results by the top 100. You may need to escape the quotes depending on your chosen shell.
Resources
| where type contains 'publicIPAddresses' and isnotempty(properties.ipAddress)
| project properties.ipAddress
| limit 100
az graph query -q "Resources | where type contains 'publicIPAddresses' and isnotempty(properties.ipAddress) | project properties.ipAddress | limit 100"
Show unassociated network security groups
This query returns network security groups (NSGs) that aren't associated to a network interface or subnet.
Resources
| where type =~ 'microsoft.network/networksecuritygroups' and isnull(properties.networkInterfaces) and isnull(properties.subnets)
| project name, resourceGroup
| sort by name asc
az graph query -q "Resources | where type =~ 'microsoft.network/networksecuritygroups' and isnull(properties.networkInterfaces) and isnull(properties.subnets) | project name, resourceGroup | sort by name asc"
Show virtual machines with Basic SKU public IP addresses
This query returns a list of virtual machine IDs with Basic SKU public IP addresses attached.
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| project vmId = tolower(id), vmNics = properties.networkProfile.networkInterfaces
| join (
Resources |
where type =~ 'microsoft.network/networkinterfaces' |
project nicVMId = tolower(tostring(properties.virtualMachine.id)), allVMNicID = tolower(id), nicIPConfigs = properties.ipConfigurations)
on $left.vmId == $right.nicVMId
| join (
Resources
| where type =~ 'microsoft.network/publicipaddresses' and isnotnull(properties.ipConfiguration.id)
| where sku.name == 'Basic' // exclude to find all VMs with Public IPs
| project pipId = id, pipSku = sku.name, pipAssociatedNicId = tolower(tostring(split(properties.ipConfiguration.id, '/ipConfigurations/')[0])))
on $left.allVMNicID == $right.pipAssociatedNicId
| project vmId, pipId, pipSku
az graph query -q "Resources | where type =~ 'microsoft.compute/virtualmachines' | project vmId = tolower(id), vmNics = properties.networkProfile.networkInterfaces | join (Resources | where type =~ 'microsoft.network/networkinterfaces' | project nicVMId = tolower(tostring(properties.virtualMachine.id)), allVMNicID = tolower(id), nicIPConfigs = properties.ipConfigurations) on \$left.vmId == \$right.nicVMId | join ( Resources | where type =~ 'microsoft.network/publicipaddresses' and isnotnull(properties.ipConfiguration.id) | where sku.name == 'Basic' | project pipId = id, pipSku = sku.name, pipAssociatedNicId = tolower(tostring(split(properties.ipConfiguration.id, '/ipConfigurations/')[0]))) on \$left.allVMNicID == \$right.pipAssociatedNicId | project vmId, pipId, pipSku"
Resource health
Count of virtual machines by availability state and Subscription Id
Returns the count of virtual machines (type Microsoft.Compute/virtualMachines) aggregated by their availability state across each of your subscriptions.
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| summarize count() by subscriptionId, AvailabilityState = tostring(properties.availabilityState)
az graph query -q "HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | summarize count() by subscriptionId, AvailabilityState = tostring(properties.availabilityState)"
List of virtual machines and associated availability states by Resource Ids
Returns the latest list of virtual machines (type Microsoft.Compute/virtualMachines) aggregated by availability state. The query also provides the associated Resource Id based on properties.targetResourceId, for easy debugging and mitigation. Availability states can be one of four values: Available, Unavailable, Degraded, and Unknown. For more details on what each of the availability states mean, see Azure Resource Health overview.
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)
az graph query -q "HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)"
List of virtual machines by availability state and power state with Resource Ids and resource Groups
Returns list of virtual machines (type Microsoft.Compute/virtualMachines) aggregated on their power state and availability state to provide a cohesive state of health for your virtual machines. The query also provides details on the resource group and resource Id associated with each entry for detailed visibility into your resources.
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| project resourceGroup, Id = tolower(id), PowerState = tostring( properties.extended.instanceView.powerState.code)
| join kind=leftouter (
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| where tostring(properties.targetResourceType) =~ 'microsoft.compute/virtualmachines'
| project targetResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState))
on $left.Id == $right.targetResourceId
| project-away targetResourceId
| where PowerState != 'PowerState/deallocated'
az graph query -q "Resources | where type =~ 'microsoft.compute/virtualmachines' | project resourceGroup, Id = tolower(id), PowerState = tostring( properties.extended.instanceView.powerState.code) | join kind=leftouter ( HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | where tostring(properties.targetResourceType) =~ 'microsoft.compute/virtualmachines' | project targetResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)) on \$left.Id == \$right.targetResourceId | project-away targetResourceId | where PowerState != 'PowerState/deallocated'"
List of virtual machines not available by Resource Ids
Returns the latest list of virtual machines (type Microsoft.Compute/virtualMachines) aggregated by their availability state. The populated list only highlights virtual machines whose availability state isn't "Available" to ensure you're aware of all the concerning states your virtual machines are in. When all your virtual machines are Available, you can expect to receive no results.
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| where tostring(properties.availabilityState) != 'Available'
| summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)
az graph query -q "HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | where tostring(properties.availabilityState) != 'Available' | summarize by ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)"
Tags
Find storage accounts with a specific case-insensitive tag on the resource group
Similar to the 'Find storage accounts with a specific case-sensitive tag on the resource group' query, but when it's necessary to look for a case insensitive tag name and tag value, use mv-expand with the bagexpansion parameter. This query uses more quota than the original query, so use mv-expand only if necessary.
Resources
| where type =~ 'microsoft.storage/storageaccounts'
| join kind=inner (
ResourceContainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| mv-expand bagexpansion=array tags
| where isnotempty(tags)
| where tags[0] =~ 'key1' and tags[1] =~ 'value1'
| project subscriptionId, resourceGroup)
on subscriptionId, resourceGroup
| project-away subscriptionId1, resourceGroup1
az graph query -q "Resources | where type =~ 'microsoft.storage/storageaccounts' | join kind=inner ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | mv-expand bagexpansion=array tags | where isnotempty(tags) | where tags[0] =~ 'key1' and tags[1] =~ 'value1' | project subscriptionId, resourceGroup) on subscriptionId, resourceGroup | project-away subscriptionId1, resourceGroup1"
Find storage accounts with a specific case-sensitive tag on the resource group
The following query uses an inner join to connect storage accounts with resource groups that have a specified case-sensitive tag name and tag value.
Resources
| where type =~ 'microsoft.storage/storageaccounts'
| join kind=inner (
ResourceContainers
| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
| where tags['Key1'] =~ 'Value1'
| project subscriptionId, resourceGroup)
on subscriptionId, resourceGroup
| project-away subscriptionId1, resourceGroup1
az graph query -q "Resources | where type =~ 'microsoft.storage/storageaccounts' | join kind=inner ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | where tags['Key1'] =~ 'Value1' | project subscriptionId, resourceGroup) on subscriptionId, resourceGroup | project-away subscriptionId1, resourceGroup1"
List all tag names
This query starts with the tag and builds a JSON object listing all unique tag names and their corresponding types.
Resources
| project tags
| summarize buildschema(tags)
az graph query -q "Resources | project tags | summarize buildschema(tags)"
List all tags and their values
This query lists tags on management groups, subscriptions, and resources along with their values. The query first limits to resources where tags isnotempty(), limits the included fields by only including tags in the project, and mvexpand and extend to get the paired data from the property bag. It then uses union to combine the results from ResourceContainers to the same results from Resources, giving broad coverage to which tags are fetched. Last, it limits the results to distinct paired data and excludes system-hidden tags.
ResourceContainers
| where isnotempty(tags)
| project tags
| mvexpand tags
| extend tagKey = tostring(bag_keys(tags)[0])
| extend tagValue = tostring(tags[tagKey])
| union (
resources
| where isnotempty(tags)
| project tags
| mvexpand tags
| extend tagKey = tostring(bag_keys(tags)[0])
| extend tagValue = tostring(tags[tagKey])
)
| distinct tagKey, tagValue
| where tagKey !startswith "hidden-"
az graph query -q "ResourceContainers | where isnotempty(tags) | project tags | mvexpand tags | extend tagKey = tostring(bag_keys(tags)[0]) | extend tagValue = tostring(tags[tagKey]) | union ( resources | where isnotempty(tags) | project tags | mvexpand tags | extend tagKey = tostring(bag_keys(tags)[0]) | extend tagValue = tostring(tags[tagKey]) ) | distinct tagKey, tagValue | where tagKey !startswith "hidden-""
List resources with a specific tag value
We can limit the results by properties other than the Azure resource type, such as a tag. In this example, we're filtering for Azure resources with a tag name of Environment that have a value of Internal. To also provide what tags the resource has and their values, add the property tags to the project keyword.
Resources
| where tags.environment=~'internal'
| project name, tags
az graph query -q "Resources | where tags.environment=~'internal' | project name, tags"
Virtual Machine Scale Sets
Virtual Machine Scale Set Uniform orchestration
Get virtual machines in the Virtual Machine Scale Sets (VMSS) Uniform orchestration mode categorized according to their power state. This table contains the model view and powerState in the instance view properties for the virtual machines part of Virtual Machine Scale Set Uniform mode.
The model view and powerState in the instance view properties for the virtual machines part of Virtual Machine Scale Set Flexible mode can be queried through Resources table.
ComputeResources
| where type =~ 'microsoft.compute/virtualmachinescalesets/virtualmachines'
| extend powerState = properties.extended.instanceView.powerState.code
| project name, powerState, id
az graph query -q "ComputeResources | where type =~ 'microsoft.compute/virtualmachinescalesets/virtualmachines' | extend powerState = properties.extended.instanceView.powerState.code | project name, powerState, id"
Virtual Machine Scale Set Flexible orchestration
Get Virtual Machine Scale Sets (VMSS) Flexible orchestration mode VMs categorized according to their power state. This table contains the model view and powerState in the instance view properties for the virtual machines part of Virtual Machine Scale Set Flexible mode.
Resources
| where type == 'microsoft.compute/virtualmachines'
| extend powerState = tostring(properties.extended.instanceView.powerState.code)
| extend VMSS = tostring(properties.virtualMachineScaleSet.id)
| where isnotempty(VMSS)
| project name, powerState, id
az graph query -q "Resources | where type == 'microsoft.compute/virtualmachines' | extend powerState = tostring(properties.extended.instanceView.powerState.code) | extend VMSS = tostring(properties.virtualMachineScaleSet.id) | where isnotempty(VMSS) | project name, powerState, id"
Next steps
- Learn more about the query language.
- Learn more about how to explore resources.