Awesome KQL

Get in touch

Twitter:@coder_au
LinkedIn:@JesseLoudon
Web:jloudon
GitHub:@JesseLoudon

Azure Monitor KQL queries

⭐ Key Vault

Search for expiring Key Vault secrets and calculate Days till Expiry

AzureDiagnostics
|whereResourceProvider =='MICROSOFT.KEYVAULT'
|whereOperationName =='SecretNearExpiryEventGridNotification'
|extendSecretExpire = unixtime_seconds_todatetime(eventGridEventProperties_data_EXP_d)
|extendSecretName = eventGridEventProperties_data_ObjectName_s
|extendDaysTillExpire =datetime_diff("Day",SecretExpire,now())
|projectResource,SecretName,DaysTillExpire

Azure Resource Graph KQL queries

Azure Portal KQL

Current Scope:

resource groups
virtual machines
public ip addresses
load balancers
sql databases
expressroute
web server farms / app services
network security group rules
disks
security assessments
azure policy

⭐ All Resources

Count`all resources`summarizing by`count`and ordering by`count`

Resources
|summarizecount()bytype
|orderbycount_

⭐ Resource Groups

Count`resource groups`missing the`costcentre`tag

resourcecontainers
|wheretype =="microsoft.resources/subscriptions/resourcegroups"
|wheretags!contains'costcentre'
|projectname, resourceGroup, subscriptionId, location, tags
|summarizecount()bysubscriptionId

Count`resource groups`missing the`application`tag

resourcecontainers
|wheretype =="microsoft.resources/subscriptions/resourcegroups"
|wheretags!contains'application'
|projectname, resourceGroup, subscriptionId, location, tags
|summarizecount()bysubscriptionId

Query all tags for`resource groups`and`resources`

resourcecontainers
|wheretype =="microsoft.resources/subscriptions/resourcegroups"
|projectname,type,location,subscriptionId,tags
|union(resources |projectname,type,location,subscriptionId,tags)

Query all tags for`resource groups`and`resources`and expand tag names/values to individual rows

resourcecontainers
|wheretype =="microsoft.resources/subscriptions/resourcegroups"
|mvexpandparsejson(tags)
|extendtagname =tostring(bag_keys(tags)[0])
|extendtagvalue =tostring(tags[tagname])
|projectname,id,type,location,subscriptionId,tagname,tagvalue
|union(resources
|mvexpandparsejson(tags)
|extendtagname =tostring(bag_keys(tags)[0])
|extendtagvalue =tostring(tags[tagname])
|projectname,id,type,location,subscriptionId,tagname,tagvalue)

⭐ Virtual Machines

Query`virtual machines`and return`VM size`

Resources
|wheretype =~'Microsoft.Compute/virtualMachines'
|projectvmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), vmId = id

⭐ Public IP Addresses

Query resources for a specific`publicIPAddress`

Resources
|wheretypecontains'publicIPAddresses'andproperties.ipAddress =="12.345.678.910"

⭐ Load Balancers

Query`load balancers`that are`Standard`SKU

resources
|wheretype =="microsoft.network/loadbalancers"andsku.name =="Standard"

⭐ SQL Databases

Query`sql databases`that do not contain the name`Master`

resources
|wheretype =="microsoft.sql/servers/databases"andname notcontains"master"

⭐ ExpressRoute

Query`virtual network gateways`that are`ExpressRoute`type

resources
|wheretype =="microsoft.network/virtualnetworkgateways"andproperties.gatewayType =="ExpressRoute"

Query`network connections`that are`ExpressRoute`type

resources
|wheretype =="microsoft.network/connections"andproperties.connectionType =="ExpressRoute"

⭐ Web Server Farms / App Services

Count`numberOfWorkers`for`web server farms`

resources
|wheretype =="microsoft.web/serverfarms"
|summarizecount()bytostring(properties.numberOfWorkers)

Query`web sites`that are not`functionapp`

resources
|wheretype =="microsoft.web/sites"andkindnotcontains"functionapp"

⭐ Network Security Group Rules

Query`network security groups`across all subscriptions expanding`securityRules`

Resources
|wheretype =~"microsoft.network/networksecuritygroups"
|joinkind=leftouter(ResourceContainers |wheretype=='microsoft.resources/subscriptions'|projectSubcriptionName=name, subscriptionId)onsubscriptionId
|mv-expandrules=properties.securityRules
|extendrule_name =tostring(rules.name)
|extenddirection =tostring(rules.properties.direction)
|extendpriority =toint(rules.properties.priority)
|extendaccess= rules.properties.access
|extenddescription = rules.properties.description
|extendprotocol = rules.properties.protocol
|extendsourceprefix = rules.properties.sourceAddressPrefix
|extendsourceport = rules.properties.sourcePortRange
|extendsourceApplicationSecurityGroups =split((split(tostring(rules.properties.sourceApplicationSecurityGroups),'/'))[8],' "')[0]
|extenddestprefix = rules.properties.destinationAddressPrefix
|extenddestport = rules.properties.destinationPortRange
|extenddestinationApplicationSecurityGroups =split((split(tostring(rules.properties.destinationApplicationSecurityGroups),'/'))[8],' "')[0]
|extendsubnet_name =split((split(tostring(properties.subnets),'/'))[10],' "')[0]
|projectSubcriptionName, resourceGroup, subnet_name, name, rule_name, direction, priority,access,description, protocol, sourceport, sourceprefix, sourceApplicationSecurityGroups, destport, destprefix, destinationApplicationSecurityGroups
|sortbySubcriptionName, resourceGroup, nameasc,directionasc,priorityasc

⭐ Disks

Query`microsoft pute/disks`

resources
| where type == "microsoft pute/disks"
| extend diskSizeGB = tostring(properties.diskSizeGB)
| extend timeCreated = tostring(properties.timeCreated)
| extend diskState = tostring(properties.diskState)
| project name,type,location,resourceGroup,diskState,diskSizeGB,timeCreated,managedBy

⭐ Security Assessments

Query`microsoft.security/assessments`and show distinct values

securityresources
| where type == "microsoft.security/assessments"
| extend description = tostring(properties.metadata.description)
| extend displayName = tostring(properties.displayName)
| extend severity = tostring(properties.metadata.severity)
| extend remediationDescription = tostring(properties.metadata.remediationDescription)
| extend policyDefinitionId = tostring(properties.metadata.policyDefinitionId)
| extend implementationEffort = tostring(properties.metadata.implementationEffort)
| extend userImpact = tostring(properties.metadata.userImpact)
| distinct name, description, displayName, severity, remediationDescription, policyDefinitionId, implementationEffort, userImpact

⭐ Azure Policy

Query`policy states`filtering on`'NonCompliant'`results

policyresources
| where type == "microsoft.policyinsights/policystates"
| where properties plianceState == 'NonCompliant'
| extend policyAssignmentParameters = todynamic(properties.policyAssignmentParameters),
policyDefinitionAction = tostring(properties.policyDefinitionAction),
policyAssignmentScope = tostring(properties.policyAssignmentScope),
policyAssignmentName = tostring(properties.policyAssignmentName),
policyDefinitionName = tostring(properties.policyDefinitionName),
policyDefinitionId = tostring(properties.policyDefinitionId),
policyAssignmentId = tostring(properties.policyAssignmentId),
managementGroupIds = tostring(properties.managementGroupIds),
policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),
complianceState = tostring(properties plianceState),
policySetDefinitionCategory = tostring(properties.policySetDefinitionCategory),
subscriptionId = tostring(properties.subscriptionId),
policySetDefinitionName = tostring(properties.policySetDefinitionName),
policySetDefinitionId = tostring(properties.policySetDefinitionId),
resourceType = tostring(properties.resourceType),
policyDefinitionGroupNames = todynamic(properties.policyDefinitionGroupNames),
stateWeight = toint(properties.stateWeight),
resourceId = tostring(properties.resourceId),
isDeleted = tobool(properties.isDeleted),
timestamp = tostring(properties.timestamp)
| project timestamp,resourceId,resourceGroup,resourceType,complianceState,stateWeight,policyAssignmentName,policyAssignmentScope,policyAssignmentParameters,policySetDefinitionId,policySetDefinitionName,policySetDefinitionCategory,policyDefinitionId,policyDefinitionName,policyDefinitionAction,policyDefinitionReferenceId,policyDefinitionGroupNames,managementGroupIds,subscriptionId

Query`Azure Security Benchmark`compliance across all subscriptions

// Regulatory compliance CSV report query for standard "Azure Security Benchmark"
// Change the 'complianceStandardId' column condition to select a different standard
securityresources
| where type == "microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols/regulatorycomplianceassessments"
| extend complianceStandardId = replace( "-", "", extract(@'/regulatoryComplianceStandards/([^/]*)', 1, id))
| where complianceStandardId == "Azure Security Benchmark"
| extend failedResources = toint(properties.failedResources), passedResources = toint(properties.passedResources),skippedResources = toint(properties.skippedResources)
| where failedResources + passedResources + skippedResources > 0 or properties.assessmentType == "MicrosoftManaged"
| join kind = leftouter(
securityresources
| where type == "microsoft.security/assessments" ) on subscriptionId, name
| extend complianceState = tostring(properties.state)
| extend resourceSource = tolower(tostring(properties1.resourceDetails.Source))
| extend recommendationId = iff(isnull(id1) or isempty(id1), id, id1)
| extend resourceId = trim(' ', tolower(tostring(case(resourceSource =~ 'azure', properties1.resourceDetails.Id,
resourceSource =~ 'gcp', properties1.resourceDetails.GcpResourceId,
resourceSource =~ 'aws' and isnotempty(tostring(properties1.resourceDetails.ConnectorId)), properties1.resourceDetails.Id,
resourceSource =~ 'aws', properties1.resourceDetails.AwsResourceId,
extract('^(.+)/providers/Microsoft.Security/assessments/.+$',1,recommendationId)))))
| extend regexResourceId = extract_all(@ "/providers/[^/]+(?:/([^/]+)/[^/]+(?:/[^/]+/[^/]+)?)?/([^/]+)/([^/]+)$", resourceId)[0]
| extend resourceType = iff(resourceSource =~ "aws" and isnotempty(tostring(properties1.resourceDetails.ConnectorId)), tostring(properties1.additionalData.ResourceType), iff(regexResourceId[1]!= "", regexResourceId[1], iff(regexResourceId[0]!= "", regexResourceId[0], "subscriptions" )))
| extend resourceName = tostring(regexResourceId[2])
| extend recommendationName = name
| extend recommendationDisplayName = tostring(iff(isnull(properties1.displayName) or isempty(properties1.displayName), properties.description, properties1.displayName))
| extend description = tostring(properties1.metadata.description)
| extend remediationSteps = tostring(properties1.metadata.remediationDescription)
| extend severity = tostring(properties1.metadata.severity)
| extend azurePortalRecommendationLink = tostring(properties1.links.azurePortal)
| extend complianceStandardId = replace( "-", "", extract(@'/regulatoryComplianceStandards/([^/]*)', 1, id))
| extend complianceControlId = extract(@ "/regulatoryComplianceControls/([^/]*)", 1, id)
| mvexpand statusPerInitiative = properties1.statusPerInitiative
| extend expectedInitiative = statusPerInitiative.policyInitiativeName =~ "ASC Default"
| summarize arg_max(expectedInitiative, *) by complianceControlId, recommendationId
| extend state = iff(expectedInitiative, tolower(statusPerInitiative.assessmentStatus.code), tolower(properties1.status.code))
| extend notApplicableReason = iff(expectedInitiative, tostring(statusPerInitiative.assessmentStatus.cause), tostring(properties1.status.cause))
| project-away expectedInitiative
| project complianceStandardId, complianceControlId, complianceState, subscriptionId, resourceGroup = resourceGroup1,resourceType, resourceName, resourceId, recommendationId, recommendationName, recommendationDisplayName, description, remediationSteps, severity, state, notApplicableReason, azurePortalRecommendationLink
| join kind = leftouter (securityresources
| where type == "microsoft.security/regulatorycompliancestandards/regulatorycompliancecontrols"
| extend complianceStandardId = replace( "-", "", extract(@'/regulatoryComplianceStandards/([^/]*)', 1, id))
| where complianceStandardId == "Azure Security Benchmark"
| extend controlName = tostring(properties.description)
| project controlId = name, controlName
| distinct *) on $right.controlId == $left plianceControlId
| project-away controlId
| distinct *
| order by complianceControlId asc, recommendationId asc

PowerShell KQL

Run your first Resource Graph query using Azure PowerShell

⭐ AzGraph Queries w/ export to JSON

Current Scope:

resource groups
virtual networks
redis cache
availability sets
disks
virtual machines
virtual machine extensions
virtual machine scale sets
managed clusters
data factories
key vaults
application security groups
load balancers
network interfaces
public IP addresses
search services
service bus namespaces
sql managed instances
storage accounts
web server farms
websites
notification hubs

#Install the Resource Graph module from PowerShell Gallery
Install-Module-Name Az.ResourceGraph

#Set a local File Path for JSON export
$FilePath="C:\Temp\"

#Query resources, project away columns, and convert/export to JSON
$resourceGroups=Search-AzGraph-Query"ResourceContainers | project-away resourceGroup,managedBy,tenantId,identity,zones,extendedLocation,sku,plan,properties,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\ResourceGroups.json

$virtualNetworks=Search-AzGraph-Query"Resources | where type == 'microsoft.network/virtualnetworks' | project-away managedBy,tenantId,identity,zones,extendedLocation,sku,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\VirtualNetworks.json

$redis=Search-AzGraph-Query"Resources | where type == 'microsoft.cache/redis' | project-away managedBy,tenantId,identity,zones,extendedLocation,sku,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\Redis.json

$availabilitySets=Search-AzGraph-Query"Resources | where type == 'microsoft pute/availabilitysets' | project-away managedBy,tenantId,identity,zones,extendedLocation,sku,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\AvailabilitySets.json

$disks=Search-AzGraph-Query"Resources | where type == 'microsoft pute/disks' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\Disks.json

$virtualMachines=Search-AzGraph-Query"Resources | where type == 'microsoft pute/virtualmachines' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,sku,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\VirtualMachines.json

$virtualMachinesExtensions=Search-AzGraph-Query"Resources | where type == 'microsoft pute/virtualmachines/extensions' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,sku,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\VirtualMachinesExtensions.json

$virtualMachinesScaleSets=Search-AzGraph-Query"Resources | where type == 'microsoft pute/virtualmachinescalesets' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\VirtualMachinesScaleSets.json

$managedClusters=Search-AzGraph-Query"Resources | where type == 'microsoft.containerservice/managedclusters' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\ManagedClusters.json

$dataFactories=Search-AzGraph-Query"Resources | where type == 'microsoft.datafactory/factories' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,sku,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\DataFactories.json

$appInsights=Search-AzGraph-Query"Resources | where type == 'microsoft.insights/components' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,sku,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\AppInsights.json

$keyVaults=Search-AzGraph-Query"Resources | where type == 'microsoft.keyvault/vaults' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,sku,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\KeyVaults.json

$applicationSecurityGroups=Search-AzGraph-Query"Resources | where type == 'microsoft.network/applicationsecuritygroups' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,sku,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\ApplicationSecurityGroups.json

$loadBalancers=Search-AzGraph-Query"Resources | where type == 'microsoft.network/loadbalancers' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\LoadBalancers.json

$networkInterfaces=Search-AzGraph-Query"Resources | where type == 'microsoft.network/networkinterfaces' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,sku,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\NetworkInterfaces.json

$publicIPAddresses=Search-AzGraph-Query"Resources | where type == 'microsoft.network/publicipaddresses' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\PublicIPAddresses.json

$searchServices=Search-AzGraph-Query"Resources | where type == 'microsoft.search/searchservices' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\SearchServices.json

$serviceBusNamespaces=Search-AzGraph-Query"Resources | where type == 'microsoft.servicebus/namespaces' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\ServiceBusNamespaces.json

$sqlManagedInstances=Search-AzGraph-Query"Resources | where type == 'microsoft.sql/managedinstances' | project-away managedBy,tenantId,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\SqlManagedInstances.json

$storageAccounts=Search-AzGraph-Query"Resources | where type == 'microsoft.storage/storageaccounts' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\StorageAccounts.json

$webServerFarms=Search-AzGraph-Query"Resources | where type == 'microsoft.web/serverfarms' | project-away managedBy,tenantId,identity,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\WebServerFarms.json

$webSites=Search-AzGraph-Query"Resources | where type == 'microsoft.web/sites' | project-away managedBy,tenantId,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\WebSites.json

$notificationHubs=Search-AzGraph-Query"Resources | where type == 'microsoft.notificationhubs/namespaces/notificationhubs' or type == 'microsoft.notificationhubs/namespaces' | project-away managedBy,tenantId,zones,extendedLocation,plan,kind,type,subscriptionId"|ConvertTo-Json-Depth100|Out-File$FilePath\NotificationHubs.json

Name		Name	Last commit message	Last commit date
Latest commit History 25 Commits
LICENSE		LICENSE
README.md		README.md

License

globalbao/awesome-kql

Folders and files

Latest commit

History

Repository files navigation

Awesome KQL

Azure Monitor KQL queries

⭐ Key Vault

Search for expiring Key Vault secrets and calculate Days till Expiry

Azure Resource Graph KQL queries

Azure Portal KQL

⭐ All Resources

Countall resourcessummarizing bycountand ordering bycount

⭐ Resource Groups

Countresource groupsmissing thecostcentretag

Countresource groupsmissing theapplicationtag

Query all tags forresource groupsandresources

Query all tags forresource groupsandresourcesand expand tag names/values to individual rows

⭐ Virtual Machines

Queryvirtual machinesand returnVM size

⭐ Public IP Addresses

Query resources for a specificpublicIPAddress

⭐ Load Balancers

Queryload balancersthat areStandardSKU

⭐ SQL Databases

Querysql databasesthat do not contain the nameMaster

⭐ ExpressRoute

Queryvirtual network gatewaysthat areExpressRoutetype

Querynetwork connectionsthat areExpressRoutetype

⭐ Web Server Farms / App Services

CountnumberOfWorkersforweb server farms

Queryweb sitesthat are notfunctionapp

⭐ Network Security Group Rules

Querynetwork security groupsacross all subscriptions expandingsecurityRules

⭐ Disks

Querymicrosoft pute/disks

⭐ Security Assessments

Querymicrosoft.security/assessmentsand show distinct values

⭐ Azure Policy

Querypolicy statesfiltering on'NonCompliant'results

QueryAzure Security Benchmarkcompliance across all subscriptions

PowerShell KQL

⭐ AzGraph Queries w/ export to JSON

About

Topics

Resources

License

Stars

Watchers

Forks

Count`all resources`summarizing by`count`and ordering by`count`

Count`resource groups`missing the`costcentre`tag

Count`resource groups`missing the`application`tag

Query all tags for`resource groups`and`resources`

Query all tags for`resource groups`and`resources`and expand tag names/values to individual rows

Query`virtual machines`and return`VM size`

Query resources for a specific`publicIPAddress`

Query`load balancers`that are`Standard`SKU

Query`sql databases`that do not contain the name`Master`

Query`virtual network gateways`that are`ExpressRoute`type

Query`network connections`that are`ExpressRoute`type

Count`numberOfWorkers`for`web server farms`

Query`web sites`that are not`functionapp`

Query`network security groups`across all subscriptions expanding`securityRules`

Query`microsoft pute/disks`

Query`microsoft.security/assessments`and show distinct values

Query`policy states`filtering on`'NonCompliant'`results

Query`Azure Security Benchmark`compliance across all subscriptions