✨ KQL query collection

calendar Nov 6, 2024 · 11 min read · kql  ·
Share on: linkedin copy

Table of content

If you want to create your own or to contribute to an existing GitHub project you are on the right page.

Query Language (KQL) Query Collection

Query Language (KQL) is the language used across Azure Resource Graph, Azure Monitor, Azure Data Explorer, and Azure Log Analytics.

Here is a collection of queries: Resource Graph Queries

⭐✨ Azure Advisor

Service retirement

1advisorresources
2| project id, properties.impact, properties.shortDescription.problem

⭐ AKS

List node pools

1Resources
2| where type == "microsoft.containerservice/managedclusters"
3| extend nodepools = properties.agentPoolProfiles
4| mv-expand nodepools
5| project name, nodepools.name, nodepools.vmSize, nodepools.minCount, nodepools.maxCount, nodepools.powerState.code, nodeCount = tostring(nodepools['count'])
6| sort by name

List nodes pools2

1Resources
2| where type == "microsoft.containerservice/managedclusters"
3| extend properties.agentPoolProfiles
4| project subscriptionId, name, pool = (properties.agentPoolProfiles)
5| mv-expand pool
6| project subscription = subscriptionId, cluster = name, size = pool.vmSize, count = pool.['count']

Get Node pools information

1resources
2| where type == "microsoft.containerservice/managedclusters"
3| extend properties.agentPoolProfiles
4| project subscriptionId, name, nodePool = properties.agentPoolProfiles
5| mv-expand nodePool
6| project subscriptionId, name, sku = nodePool.vmSize, count = nodePool.['count'], powerState = nodePool.powerState.code

⭐ Disks

Get Premium Disks

 1resources
 2| where type =~ 'microsoft.compute/disks'
 3| extend skuName=tostring(sku.name)
 4| extend accountType=case(skuName =~ 'Standard_LRS', 'Standard HDD LRS',
 5                          skuName =~ 'StandardSSD_LRS', 'Standard SSD LRS',
 6                          skuName =~ 'UltraSSD_LRS', 'Ultra disk LRS',
 7                          skuName =~ 'Premium_LRS', 'Premium SSD LRS',
 8                          skuName =~ 'Standard_ZRS', 'Zone-redundant',
 9                          skuName =~ 'Premium_ZRS', 'Premium SSD ZRS',
10                          skuName =~ 'StandardSSD_ZRS', 'Standard SSD ZRS',
11                          skuName)
12| where accountType contains "Premium"

Get SSD Disks

 1resources
 2| where type =~ 'microsoft.compute/disks'
 3| extend skuName=tostring(sku.name)
 4| extend accountType=case(skuName =~ 'Standard_LRS', 'Standard HDD LRS',
 5                          skuName =~ 'StandardSSD_LRS', 'Standard SSD LRS',
 6                          skuName =~ 'UltraSSD_LRS', 'Ultra disk LRS',
 7                          skuName =~ 'Premium_LRS', 'Premium SSD LRS',
 8                          skuName =~ 'Standard_ZRS', 'Zone-redundant',
 9                          skuName =~ 'Premium_ZRS', 'Premium SSD ZRS',
10                          skuName =~ 'StandardSSD_ZRS', 'Standard SSD ZRS',
11                          skuName)
12| where accountType contains "SSD"

Get ZRS Disks

 1resources
 2| where type =~ 'microsoft.compute/disks'
 3| extend skuName=tostring(sku.name)
 4| extend accountType=case(skuName =~ 'Standard_LRS', 'Standard HDD LRS',
 5                          skuName =~ 'StandardSSD_LRS', 'Standard SSD LRS',
 6                          skuName =~ 'UltraSSD_LRS', 'Ultra disk LRS',
 7                          skuName =~ 'Premium_LRS', 'Premium SSD LRS',
 8                          skuName =~ 'Standard_ZRS', 'Zone-redundant',
 9                          skuName =~ 'Premium_ZRS', 'Premium SSD ZRS',
10                          skuName =~ 'StandardSSD_ZRS', 'Standard SSD ZRS',
11                          skuName)
12| where accountType contains "ZRS"

Sort Disks By Size IOPS

1resources
2| where type == "microsoft.compute/disks"
3| project Disk_name=name, SKU=sku.name, Size=strcat(properties['diskSizeGB'],"GB"), IOPS=tolong(properties['diskIOPSReadWrite'])
4| sort by Size

⭐ Frontdoor

Routing rules and accepted protocols

1resources
2| where type == "microsoft.network/frontdoors"
3| project subscriptionId, frontDoorName=name, routingRules = (properties.routingRules)
4| mv-expand routingRules
5| project subscriptionId, frontDoorName, routingRuleName=routingRules.name, protocols = routingRules.properties

⭐ Network

List all subnets with delegation

1resources
2| where type == "microsoft.network/virtualnetworks"
3| project vnetName = name, subnets = (properties.subnets)
4| mvexpand subnets
5| extend subnetName = (subnets.name)
6| extend isDelegated = isnotnull(subnets.properties.delegations) and array_length(subnets.properties.delegations) != 0
7| where isDelegated == 1
8| project vnetName, subnetName

List subnets without NSG

1resources
2| where type == "microsoft.network/virtualnetworks"
3| project vnetName = name, subnets = (properties.subnets)
4| mvexpand subnets
5| extend subnetName = (subnets.name)
6| extend hasNSG = isnotnull(subnets.properties.networkSecurityGroup)
7| where hasNSG == 0
8| project vnetName, subnetName

List subnets with service endpoint

1resources
2| where type == "microsoft.network/virtualnetworks"
3| project vnetName = name, subnets = (properties.subnets)
4| mvexpand subnets
5| extend subnetName = (subnets.name)
6| extend hasServiceEndpoints = isnotnull(subnets.properties.serviceEndpoints) and array_length(subnets.properties.serviceEndpoints) != 0
7| where hasServiceEndpoints == 1
8| project vnetName, subnetName

List subnet with UDR

1resources
2| where type == "microsoft.network/virtualnetworks"
3| project vnetName = name, subnets = (properties.subnets)
4| mvexpand subnets
5| extend subnetName = (subnets.name)
6| extend hasRouteTable = isnotnull(subnets.properties.routeTable)
7| where hasRouteTable == 1
8| project vnetName, subnetName

Subnet IP usage

 1resources
 2| where type == "microsoft.network/virtualnetworks"
 3| project vnetName = name, subnets = (properties.subnets)
 4| mvexpand subnets
 5| extend subnetName = (subnets.name)
 6| extend mask = split(subnets.properties.addressPrefix, '/', 1)[0]
 7| extend usedIp = array_length(subnets.properties.ipConfigurations)
 8| extend totalIp = case(mask == 29, 3,
 9                        mask == 28, 11,
10                        mask == 27, 27,
11                        mask == 26, 59,
12                        mask == 25, 123,
13                        mask == 24, 251,
14                        mask == 23, 507,
15                        mask == 22, 1019,
16                        mask == 21, 2043,
17                        mask == 20, 4091,
18                        mask == 19, 8187,
19                        mask == 18, 16379,
20                        mask == 17, 32763,
21                        mask == 16, 65531,
22                        mask == 15, 131067,
23                        mask == 14, 262139,
24                        mask == 13, 524283,
25                        mask == 12, 1048571,
26                        mask == 11, 2097147,
27                        mask == 10, 4194299,
28                        mask == 9, 8388603,
29                        mask == 8, 16777211,
30                        -1)
31| extend availableIp = totalIp - usedIp
32| project vnetName, subnetName, mask, usedIp, totalIp, availableIp, subnets
33| order by toint(mask) desc

⭐ Resources

Display last resources changes since the last week

 1resourcechanges
 2| extend changeTime = todatetime(properties.changeAttributes.timestamp),
 3targetResourceId = tostring(properties.targetResourceId),
 4changeType = tostring(properties.changeType), changedBy = tostring(properties.changeAttributes.changedBy),
 5changedByType = properties.changeAttributes.changedByType,
 6clientType = tostring(properties.changeAttributes.clientType)
 7| where changeTime > ago(7d)
 8| project changeType, changedBy, changedByType, clientType
 9| summarize count() by changedBy, changeType, clientType
10| order by count_ desc

⭐ Resources Groups

Display Empty Resource Groups

1ResourceContainers
2| where type == 'microsoft.resources/subscriptions/resourcegroups'
3| extend rgAndSub = strcat(resourceGroup, '--', subscriptionId)
4| join kind=leftouter (
5    Resources
6    | extend rgAndSub = strcat(resourceGroup, '--', subscriptionId)
7    | summarize count() by rgAndSub
8) on rgAndSub
9| where isnull(count_)

⭐ Policies

Find unused custom policies

 1policyresources
 2| where type == "microsoft.authorization/policydefinitions"
 3| extend policyType = tostring(properties.policyType)
 4| where policyType == "Custom"
 5| join kind=leftouter (
 6    policyresources
 7    | where type == "microsoft.authorization/policysetdefinitions"
 8    | extend policyType = tostring(properties.policyType)
 9    | extend  policyDefinitions = properties.policyDefinitions
10    | where policyType == "Custom"
11    | mv-expand policyDefinitions
12    | extend policyDefinitionId = tostring(policyDefinitions.policyDefinitionId)
13    | project associedIdToInitiative=policyDefinitionId
14    | distinct associedIdToInitiative) on $left.id == $right.associedIdToInitiative
15| where associedIdToInitiative == ""
16| join kind=leftouter(
17    policyresources
18    | where type == "microsoft.authorization/policyassignments"
19    | extend policyDefinitionId = tostring(properties.policyDefinitionId)
20    | project associatedDefinitionId=policyDefinitionId
21    | distinct associatedDefinitionId
22) on $left.id == $right.associatedDefinitionId
23| where associatedDefinitionId == ""
24| extend displayName = tostring(properties.displayName)
25| project id, displayName

Count policies assignment by scope

1policyresources
2| where type == "microsoft.authorization/policyassignments"
3| extend scope = tostring(properties.scope)
4| summarize count() by scope
5| order by count_ desc

Count custom policies assignments by scope

1policyresources
2| where type == "microsoft.authorization/policydefinitions"
3| extend policyType = tostring(properties.policyType)
4| where policyType == "Custom"
5| project id
6| extend scope = tostring(split(id, "/providers/Microsoft.Authorization/policyDefinitions/", 0)[0])
7| summarize count() by scope
8| order by count_ desc

⭐ RBAC

List user direct assignment at management group level

1authorizationresources
2| extend scope = tostring(properties.scope)
3| join kind = inner (
4    resourcecontainers
5    | where type == "microsoft.management/managementgroups"
6    | project  managementGroupId=id, managementGroupName=properties.displayName
7) on $right.managementGroupId == $left.scope
8| where properties.principaltype == "User"
9| project properties.createdOn, managementGroupName, scope, managementGroupId

List user assignment at subscription level

1authorizationresources
2| join kind = inner (
3    resourcecontainers
4    | where type == "microsoft.resources/subscriptions"
5    | project  subscriptionName=name, subscriptionId) on subscriptionId
6| where type == "microsoft.authorization/roleassignments"
7| where properties.principalType == "User"
8| project properties.createdOn, properties.scope, properties.principalId, subscriptionId

All RBAC Assignments

 1authorizationresources
 2| where type =~ 'microsoft.authorization/roleassignments'
 3| extend roleDefinitionId= tolower(tostring(properties.roleDefinitionId))
 4| extend principalType = properties.principalType
 5| extend principalId = properties.principalId
 6| extend description = properties.description
 7| extend scope = properties.scope
 8| extend createdBy  = properties.createdBy
 9| join kind = inner (
10authorizationresources
11| where type =~ 'microsoft.authorization/roledefinitions'
12| extend roleDefinitionId = tolower(id)
13| extend roleName = tostring(properties.roleName)
14| extend roleType = tostring(properties.type)
15| project roleDefinitionId,roleName,roleType
16) on roleDefinitionId
17| project principalId,principalType,createdBy,description,roleName,roleType,scope,roleDefinitionId

⭐ Resource Groups

Display Empty Resource Groups

1ResourceContainers
2| where type == 'microsoft.resources/subscriptions/resourcegroups'
3| extend rgAndSub = strcat(resourceGroup, '--', subscriptionId)
4| join kind=leftouter (
5    Resources
6    | extend rgAndSub = strcat(resourceGroup, '--', subscriptionId)
7    | summarize count() by rgAndSub
8) on rgAndSub
9| where isnull(count_)

⭐ Resource changes

Display last resources changes since the last week

 1resourcechanges
 2| extend changeTime = todatetime(properties.changeAttributes.timestamp),
 3targetResourceId = tostring(properties.targetResourceId),
 4changeType = tostring(properties.changeType), changedBy = tostring(properties.changeAttributes.changedBy),
 5changedByType = properties.changeAttributes.changedByType,
 6clientType = tostring(properties.changeAttributes.clientType)
 7| where changeTime > ago(7d)
 8| project changeType, changedBy, changedByType, clientType
 9| summarize count() by changedBy, changeType, clientType
10| order by count_ desc

⭐ Service Health

Display by Impacted resource Id

1servicehealthresources
2| where type == "microsoft.resourcehealth/events/impactedresources"
3| extend TrackingId = split(split(id, "/events/", 1)[0], "/impactedResources", 0)[0]
4| extend p = parse_json(properties)
5| project subscriptionId, TrackingId,  targetResourceId= tostring(p.targetResourceId), details = p
6| join kind=inner (
7    resources
8    )
9    on $left.targetResourceId == $right.id

Display by Events (TrackingId)

1ServiceHealthResources
2| where type =~ 'Microsoft.ResourceHealth/events'
3| 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,
4EventSubType = properties.EventSubType
5| mv-expand Impact = properties.Impact
6| extend ImpactedService = Impact.ImpactedService
7| where eventType == 'HealthAdvisory' and status == 'Active' and  EventSubType == 'Retirement'
8| summarize count(subscriptionId) by name
9| order by ['count_subscriptionId'] desc

Display by Events (TrackingId) and Resource Id impacted

 1// Filter Specific Tracking IDs and Combine Health Advisory with Impacted Resources
 2servicehealthresources
 3| where type =~ 'Microsoft.ResourceHealth/events'
 4| extend
 5    eventType = properties.EventType,
 6    EventSubType = properties.EventSubType,
 7    status = properties.Status,
 8    description = properties.Title,
 9    trackingId = tostring(properties.TrackingId),  // Explicitly cast TrackingId to string
10    summary = properties.Summary,
11    priority = properties.Priority,
12    impactStartTime = properties.ImpactStartTime,
13    impactMitigationTime = properties.ImpactMitigationTime
14| mv-expand Impact = properties.Impact
15| extend ImpactedService = Impact.ImpactedService
16//| where eventType == 'HealthAdvisory' and status == 'Active' and  EventSubType == 'Retirement'
17| where eventType == 'PlannedMaintenance' and status == 'Active'
18| join kind=inner (
19    servicehealthresources
20    | where type == "microsoft.resourcehealth/events/impactedresources"
21    | extend TrackingId = tostring(split(split(id, "/events/", 1)[0], "/impactedResources", 0)[0]) // Explicitly cast TrackingId to string
22    | extend p = parse_json(properties)
23    | project subscriptionId, TrackingId, targetResourceId = tostring(p.targetResourceId), details = p
24) on $left.trackingId == $right.TrackingId
25| project
26    trackingId,
27    subscriptionId,
28    ImpactedService,
29    targetResourceId,
30    description,
31    summary,
32    priority,
33    impactStartTime,
34    impactMitigationTime,
35    details

⭐ Storage Accounts

Count Storage accounts by sku

1resources
2| where type == "microsoft.storage/storageaccounts"
3| extend sku = sku.name
4| summarize count(name) by tostring(sku)

⭐ Subscriptions

List subscriptions part of an EA

1resourcecontainers
2| where type == "microsoft.resources/subscriptions"
3| where properties.state == "Enabled"
4| mv-expand subscriptionPolicies = properties.subscriptionPolicies
5| where name !contains "Visual Studio" and subscriptionPolicies.quotaId startswith "MSDNDevTest" or subscriptionPolicies.quotaId startswith "EnterpriseAgreement"

List subscriptions by MG

1ResourceContainers
2| where type =~ 'microsoft.resources/subscriptions'
3| extend  mgParent = properties.managementGroupAncestorsChain
4| mv-expand with_itemindex=MGHierarchy mgParent
5| project subscriptionId, name, mgParent, MGHierarchy, mgParent.name

Count subscriptions by MG

1ResourceContainers
2| where type =~ 'microsoft.management/managementgroups'
3| project mgname = name
4| join kind=leftouter (resourcecontainers | where type=~ 'microsoft.resources/subscriptions'
5| extend  mgParent = properties.managementGroupAncestorsChain | project id, mgname = tostring(mgParent[0].name)) on mgname
6| summarize count() by mgname

Count all subscriptions by tenant

1ResourceContainers
2| where type =~ 'microsoft.resources/subscriptions'
3| project SubscriptionName=name, subscriptionId, tenantId
4| summarize count() by tenantId
5| order by ['count_'] desc

List resources part of a list of subscriptions

1resources
2| where subscriptionId in ("subid1-xxx-xxx-xxx-xxx", "subid2-xxx-xxx-xxx-xxx", "subid3-xxx-xxx-xxx-xxx", "subid4-xxx-xxx-xxx-xxx")

Check subscription naming convention

1resourcecontainers
2| where type == "microsoft.resources/subscriptions"
3| where properties.state == 'Enabled'
4| extend NamingCheck = iff((name startswith 'sub-'),"Naming is OK","Naming is not OK")
5| summarize count() by NamingCheck

Check subscription naming convention on a specific management group

1resourcecontainers
2| where type == "microsoft.resources/subscriptions"
3| where properties.state == 'Enabled'
4| where properties.managementGroupAncestorsChain contains 'Production'
5| extend NamingCheck = iff((name startswith 'sub-'),"Naming is OK","Naming is not OK")
6| summarize count() by NamingCheck

List subscriptions in a specific management group

1resourcecontainers
2 | where type == "microsoft.resources/subscriptions"
3 | where (properties.managementGroupAncestorsChain) contains "Sandbox"

Count resources type in a subscription

1resources
2| join kind=leftouter
3   (resourcecontainers
4   | where type == 'microsoft.resources/subscriptions'
5   | project subscriptionName=name, subscriptionId) on subscriptionId
6| where subscriptionName  == "<your-sub-name-here>"
7| summarize count() by type, subscriptionName

Count subscriptions by management groups

1resourcecontainers
2| where type == 'microsoft.resources/subscriptions'
3| project subscriptionName = name, managementgroups = (properties.managementGroupAncestorsChain)
4| mv-expand managementgroups
5| summarize count() by tostring(managementgroups.displayName)
6| order by count_ desc

⭐ Tags

Resources without tag

1resourcecontainers
2| where type != "microsoft.management/managementgroups"
3| mv-expand bagexpansion=array tags
4| where isempty(tags)

Resources with specific tags and expand tag names/values to individual rows

 1resourcecontainers
 2| where type != "microsoft.management/managementgroups"
 3| mvexpand parsejson(tags)
 4| extend tagname = tostring(bag_keys(tags)[0])
 5| extend tagvalue = tostring(tags[tagname])
 6| project  name,id,type,location,subscriptionId,tagname,tagvalue
 7| union (resources
 8| mvexpand parsejson(tags)
 9| extend tagname = tostring(bag_keys(tags)[0])
10| extend tagvalue = tostring(tags[tagname])
11| project name,id,type,location,subscriptionId,tagname,tagvalue)
12| where tagname == "Environment" or tagname == "Owner"

Resources not containing a specific tag

1resourcecontainers
2| where tags !contains 'Environment'

Resources not containing a tag and count

1resourcecontainers
2| where tags !contains 'Environment'
3| project name, resourceGroup, subscriptionId, location, tags
4| summarize count () by subscriptionId

All tags for resources

1resourcecontainers
2| project  name,type,location,subscriptionId,tags
3| union (resources | project name,type,location,subscriptionId,tags)

Count for a specific tag key

1ResourceContainers
2| where type =~ 'microsoft.resources/subscriptions/resourcegroups'
3| mvexpand tags
4| extend tagKey = tostring(bag_keys(tags)[0])
5| extend tagValue = tostring(tags[tagKey])
6| where tagKey == "Environment"
7| summarize count() by tagValue
8| order by ['count_'] desc

TEST

1resources
2| where type =~ 'Microsoft.Compute/virtualMachines'
3| mvexpand tags
4| extend tagKey = tostring(bag_keys(tags)[0])
5| extend tagValue = tostring(tags[tagKey])
6| where tagKey hasprefix "creat"  and tagKey hasprefix "cr"
7| project name, tags, tagKey, tagValue