Azure Resource Graph sample queries by table
Important
This article's query samples and content have been restored to the public ARG live site. The samples left on this site are no longer being updated or supported. The portal's Azure Resource Graph Explorer includes the supported Categories and Tables, and the Get started tab includes Example queries and Advanced queries that you can open and run in ARG Explorer.
This page is a collection of Azure Resource Graph sample queries grouped by table. To jump to a specific table, use the links on the top of the page. Otherwise, use Ctrl-F to use your browser's search feature. For a list of tables and related details, see Resource Graph tables.
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"
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"
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"
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.
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"
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"
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"
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"
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"
Get virtual machines in the Virtual Machine Scale Set 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"
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'"
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"
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"
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"
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"
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"
Provides a list of a 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"
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"
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)"
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'"
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 resources with availability states that have been impacted by unplanned, platform-initiated health events
Returns the latest list of virtual machines impacted by unplanned disruptions that were triggered unexpectedly by the Azure platform. This query returns all the impacted virtual machines aggregated by their ID property, along with the corresponding availability state and associated annotation (properties.reason) summarizing the specific disruption.
HealthResources
| where type == "microsoft.resourcehealth/resourceannotations"
| where properties.category == 'Unplanned' and properties.context != 'Customer Initiated'
| project ResourceId = tolower(tostring(properties.targetResourceId)), Annotation = tostring(properties.reason)
| join (
HealthResources
| where type == 'microsoft.resourcehealth/availabilitystatuses'
| project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)) on ResourceId
| project ResourceId, AvailabilityState, Annotation
az graph query -q "HealthResources | where type == "microsoft.resourcehealth/resourceannotations" | where properties.category == 'Unplanned' and properties.context != 'Customer Initiated' | project ResourceId = tolower(tostring(properties.targetResourceId)), Annotation = tostring(properties.reason) | join (HealthResources | where type == 'microsoft.resourcehealth/availabilitystatuses' | project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)) on ResourceId | project ResourceId, AvailabilityState, Annotation"
Returns a list of virtual machines currently not in an Available state, aggregated by their ID property. The query also shows the virtual machines' actual availability state as well as their associated details, including the reason for their unavailability.
HealthResources
| where type == 'microsoft.resourcehealth/availabilitystatuses'
| where properties.availabilityState != 'Available'
| project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState)
| join (
HealthResources
| where type == "microsoft.resourcehealth/resourceannotations"
| project ResourceId = tolower(tostring(properties.targetResourceId)), Reason = tostring(properties.reason), Context = tostring(properties.context), Category = tostring(properties.category)) on ResourceId
| project ResourceId, AvailabilityState, Reason, Context, Category
az graph query -q "HealthResources | where type == 'microsoft.resourcehealth/availabilitystatuses' | where properties.availabilityState != 'Available' | project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState) | join (HealthResources | where type == "microsoft.resourcehealth/resourceannotations" | project ResourceId = tolower(tostring(properties.targetResourceId)), Reason = tostring(properties.reason), Context = tostring(properties.context), Category = tostring(properties.category)) on ResourceId | project ResourceId, AvailabilityState, Reason, Context, Category"
Count of resources in a region that have been impacted by an availability disruption along with the type of impact
Returns the count of virtual machines that are currently not in an Available state, aggregated by their ID property. The query also shows the corresponding location and annotation details, including the cause for the VMs not being in an Available state.
HealthResources
| where type == 'microsoft.resourcehealth/availabilitystatuses'
| where properties.availabilityState != 'Available'
| project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState), Location = location
| join (
HealthResources
| where type == "microsoft.resourcehealth/resourceannotations"
| project ResourceId = tolower(tostring(properties.targetResourceId)), Context = tostring(properties.context), Category = tostring(properties.category), Location = location) on ResourceId
| summarize NumResources = count(ResourceId) by Location, Context, Category
az graph query -q "HealthResources | where type == 'microsoft.resourcehealth/availabilitystatuses' | where properties.availabilityState != 'Available' | project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState), Location = location | join (HealthResources | where type == "microsoft.resourcehealth/resourceannotations" | project ResourceId = tolower(tostring(properties.targetResourceId)), Context = tostring(properties.context), Category = tostring(properties.category), Location = location) on ResourceId | summarize NumResources = count(ResourceId) by Location, Context, Category"
List of resources impacted by a specific health event, along with impact time, impact details, availability state, and region
Returns a list of virtual machines impacted by the VirtualMachineHostRebootedForRepair
annotation, aggregated by their ID property. The query also returns the virtual machines' corresponding availability state, time of disruption, and annotation details, including the impact cause.
HealthResources
| where type == "microsoft.resourcehealth/resourceannotations"
| where properties.AnnotationName contains 'VirtualMachineHostRebootedForRepair'
| project ResourceId = tolower(tostring(properties.targetResourceId)), Reason = tostring(properties.reason), Context = tostring(properties.context), Category = tostring(properties.category), Location = location, Timestamp = tostring(properties.occurredTime)
| join (
HealthResources
| where type == 'microsoft.resourcehealth/availabilitystatuses'
| project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState), Location = location) on ResourceId
| project ResourceId, Reason, Context, Category, AvailabilityState, Timestamp
az graph query -q "HealthResources | where type == "microsoft.resourcehealth/resourceannotations" | where properties.AnnotationName contains 'VirtualMachineHostRebootedForRepair' | project ResourceId = tolower(tostring(properties.targetResourceId)), Reason = tostring(properties.reason), Context = tostring(properties.context), Category = tostring(properties.category), Location = location, Timestamp = tostring(properties.occuredTime) | join (HealthResources | where type == 'microsoft.resourcehealth/availabilitystatuses' | project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState), Location = location) on ResourceId | project ResourceId, Reason, Context, Category, AvailabilityState, Timestamp"
Returns a list of virtual machines impacted by planned maintenance or repair operations conducted by the Azure platform, aggregated by their ID property. The query also returns the virtual machines' corresponding availability state, time of disruption, location, and annotation details, including the impact cause.
HealthResources
| where type == "microsoft.resourcehealth/resourceannotations"
| where properties.category contains 'Planned'
| project ResourceId = tolower(tostring(properties.targetResourceId)), Reason = tostring(properties.reason), Location = location, Timestamp = tostring(properties.occuredTime)
| join (
HealthResources
| where type == 'microsoft.resourcehealth/availabilitystatuses'
| project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState), Location = location) on ResourceId
| project ResourceId, Reason, AvailabilityState, Timestamp, Location
az graph query -q "HealthResources | where type == "microsoft.resourcehealth/resourceannotations" | where properties.category contains 'Planned' | project ResourceId = tolower(tostring(properties.targetResourceId)), Reason = tostring(properties.reason), Location = location, Timestamp = tostring(properties.occuredTime) | join (HealthResources | where type == 'microsoft.resourcehealth/availabilitystatuses' | project ResourceId = tolower(tostring(properties.targetResourceId)), AvailabilityState = tostring(properties.availabilityState), Location = location) on ResourceId | project ResourceId, Reason, AvailabilityState, Timestamp, Location"
List of resources that have been impacted by unplanned platform disruptions, along with availability, power states and location
Returns a list of virtual machines impacted by planned maintenance or repair operations conducted by the Azure platform, aggregated by their ID property. The query also shows the virtual machines' corresponding availability state, power state, and location details.
HealthResources
| where type =~ 'microsoft.resourcehealth/resourceannotations'
| where tostring(properties.context) == 'Platform Initiated' and tostring(properties.category) == 'Planned'
| project ResourceId = tolower(properties.targetResourceId), Location = location
| join (
HealthResources
| where type =~ 'microsoft.resourcehealth/availabilitystatuses'
| project ResourceId = tolower(properties.targetResourceId), AvailabilityState = tostring(properties.availabilityState), Location = location) on ResourceId
| join (
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| project ResourceId = tolower(id), PowerState = properties.extended.instanceView.powerState.code, Location = location) on ResourceId
| project ResourceId, AvailabilityState, PowerState, Location
az graph query -q "HealthResources | where type =~ 'microsoft.resourcehealth/resourceannotations' | where tostring(properties.context) == 'Platform Initiated' and tostring(properties.category) == 'Planned' | project ResourceId = tolower(properties.targetResourceId), Location = location | join (HealthResources | where type =~ 'microsoft.resourcehealth/availabilitystatuses' | project ResourceId = tolower(properties.targetResourceId), AvailabilityState = tostring(properties.availabilityState), Location = location) on ResourceId | join (Resources | where type =~ 'microsoft.compute/virtualmachines' | project ResourceId = tolower(id), PowerState = properties.extended.instanceView.powerState.code, Location = location) on ResourceId | project ResourceId, AvailabilityState, PowerState, Location"
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"
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"
Returns a list of virtual machines (VMs) impacted by routine Azure-initiated maintenance operations in the past 14 days, along with the corresponding reason for impact.
HealthResourceChanges
| where properties.targetResourceType =~ 'microsoft.resourcehealth/resourceannotations'
| where properties['changes']['properties.category']['newValue'] =~ 'Planned'
| project Id = tostring(split(tolower(properties.targetResourceId), '/providers/microsoft.resourcehealth/resourceannotations')[0]), Reason = properties['changes']['properties.reason']['newValue']
az graph query -q "HealthResourceChanges | where properties.targetResourceType =~ 'microsoft.resourcehealth/resourceannotations' | where properties['changes']['properties.category']['newValue'] =~ 'Planned' | project Id = tostring(split(tolower(properties.targetResourceId), '/providers/microsoft.resourcehealth/resourceannotations')[0]), Reason = properties['changes']['properties.reason']['newValue']"
Returns a list of all changes to health annotations in the past 14 days. Nulls indicate there was no update to that specific field, corresponding to a change in the Annotation Name
field)
healthresourcechanges
| where type == "microsoft.resources/changes"
| where properties.targetResourceType =~ "microsoft.resourcehealth/resourceannotations"
| extend Id = tolower(split(properties.targetResourceId, '/providers/Microsoft.ResourceHealth/resourceAnnotations/current')[0]),
Timestamp = todatetime(properties.changeAttributes.timestamp), AnnotationName = properties['changes']['properties.annotationName']['newValue'], Reason = properties['changes']['properties.reason']['newValue'],
Context = properties['changes']['properties.context']['newValue'], Category = properties['changes']['properties.category']['newValue']
| where isnotempty(AnnotationName)
| project Timestamp, Id, PreviousAnnotation = properties['changes']['properties.annotationName']['previousValue'], AnnotationName, Reason, Context, Category
| order by Timestamp desc
az graph query -q "healthresourcechanges | where type == "microsoft.resources/changes" | where properties.targetResourceType =~ "microsoft.resourcehealth/resourceannotations" | extend Id = tolower(split(properties.targetResourceId, '/providers/Microsoft.ResourceHealth/resourceAnnotations/current')[0]), Timestamp = todatetime(properties.changeAttributes.timestamp), AnnotationName = properties['changes']['properties.annotationName']['newValue'], Reason = properties['changes']['properties.reason']['newValue'], Context = properties['changes']['properties.context']['newValue'], Category = properties['changes']['properties.category']['newValue'] | where isnotempty(AnnotationName) | project Timestamp, Id, PreviousAnnotation = properties['changes']['properties.annotationName']['previousValue'], AnnotationName, Reason, Context, Category | order by Timestamp desc"
Returns a list of all changes to VM availability states in the past 14 days.
healthresourcechanges
| where type == "microsoft.resources/changes"
| where properties.targetResourceType =~ "microsoft.resourcehealth/availabilityStatuses"
| extend Id = tolower(split(properties.targetResourceId, '/providers/Microsoft.ResourceHealth/availabilityStatuses/current')[0]),
Timestamp = todatetime(properties.changeAttributes.timestamp), PreviousAvailabilityState = properties['changes']['properties.availabilityState']['previousValue'],
LatestAvailabilityState = properties['changes']['properties.availabilityState']['newValue']
| where isnotempty(LatestAvailabilityState)
| project Timestamp, Id, PreviousAvailabilityState, LatestAvailabilityState
| order by Timestamp desc
az graph query -q "healthresourcechanges | where type == "microsoft.resources/changes" | where properties.targetResourceType =~ "microsoft.resourcehealth/availabilityStatuses" | extend Id = tolower(split(properties.targetResourceId, '/providers/Microsoft.ResourceHealth/availabilityStatuses/current')[0]), Timestamp = todatetime(properties.changeAttributes.timestamp), PreviousAvailabilityState = properties['changes']['properties.availabilityState']['previousValue'], LatestAvailabilityState = properties['changes']['properties.availabilityState']['newValue'] | where isnotempty(LatestAvailabilityState) | project Timestamp, Id, PreviousAvailabilityState, LatestAvailabilityState | order by Timestamp desc"
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"
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"
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'"
This query summarize 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)"
This query summarize 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)"
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'"
This query provide 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'"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
Provides a list of all resources types that are in a NonCompliant
state.
PolicyResources
| where type == 'microsoft.policyinsights/policystates'
| where properties.complianceState == 'NonCompliant'
az graph query -q "PolicyResources | where type == 'microsoft.policyinsights/policystates' | where properties.complianceState == 'NonCompliant'"
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"
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"
Lists the number of exemptions for each assignment.
PolicyResources
| where type == 'microsoft.authorization/policyexemptions'
| summarize count() by tostring(properties.policyAssignmentId)
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
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"
The following query uses union
to get results from the ResourceContainers table and add them to results from the Resources table.
ResourceContainers
| where type=='microsoft.resources/subscriptions/resourcegroups' | project name, type | limit 5
| union (Resources | project name, type | limit 5)
az graph query -q "ResourceContainers | where type=='microsoft.resources/subscriptions/resourcegroups' | project name, type | limit 5 | union (Resources | project name, type | limit 5)"
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"
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
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
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
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-""
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"
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"
The following query uses union
to get results from the ResourceContainers table and add them to results from the Resources table.
ResourceContainers
| where type=='microsoft.resources/subscriptions/resourcegroups' | project name, type | limit 5
| union (Resources | project name, type | limit 5)
az graph query -q "ResourceContainers | where type=='microsoft.resources/subscriptions/resourcegroups' | project name, type | limit 5 | union (Resources | project name, type | limit 5)"
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()"
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"
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)"
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"
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)"
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)"
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"
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"
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)"
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"
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"
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"
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"
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'"
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"
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"
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"
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"
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"
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'"
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)"
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-""
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"
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"
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"
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"
Provides a list of a 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"
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'"
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"
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"
The following query uses leftouter join
to bring together SQL Database resources and their related elastic pools, if they've any.
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"
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"
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"
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"
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"
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"
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"
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"
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)"
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"
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"
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"
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"
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"
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"
Get Virtual Machine Scale Set 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"
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"
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)"
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}'"
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"
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}'"
Returns all the all the 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"
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"
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"
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"
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"
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"
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"
Returns Defender for Cloud plan 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"
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"
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()"
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()"
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')"
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'"
- Learn more about the query language.
- Learn more about how to explore resources.