Search code examples
kqlazure-resource-graph

Kusto query "Where not equals to any of the elements contained in list"


I have this Kusto query that I use to find untagged resources:

// Find_Untagged_Resources
resources
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
    // Use this for testing
//| where type =~ 'Microsoft.Compute/virtualMachines' or type =~ "microsoft.sql/servers/databases" 
| where name != "master"
//| where type !in~(!contains("alert", "extensions"))
| where type !contains "alert" 
| where type !contains "extensions"
| where  isnull(tags.application) 
or isnull(tags.boundary) 
or isnull(tags.costCenter) 
or isnull(tags.createdBy) 
or isnull(tags.customerCode) 
or isnull(tags.env) 
or isnull(tags.managedBy) 
or isnull(tags.ownedBy)
| project subscriptionName, resourceGroup, name, type,location,tags.application,tags.boundary,tags.costCenter,tags.createdBy,tags.customerCode,tags.env,tags.managedBy,tags.ownedBy,tags.shared,tags.version
| order by ['resourceGroup'] asc

I would like to skip all resources that contains the words "alert" or "extensions"

I would like to do something elegant like this:

//| where type !in~(!contains("alert", "extensions"))

but the only way to make this work is to use the ugly:

| where type !contains "alert" 
| where type !contains "extensions"

Is there anyway I can skip a list of words?


Solution

  • Azure Resource Graph supports only a very limited subset of KQL, so the best options are gone out the window, but still there is a way.

    | where isempty(extract("alert|extensions", 0, type))