Search code examples
azurekqlazure-resource-graph

KQL :: Find Azure SQL Databases where name like "_old"

I'm new to KQL and I'm trying to list all Azure SQL Databases with the word "_old" in their name.

My protoquery is this and it works:

// Find "_old" Databases
Resources
| where type =~ 'microsoft.sql/servers/databases'
| where *  contains  "old"
| project  resourceName = name,type,sku.name,sku.tier,tags.createdBy,tags.customerCode,tags.managedBy, resourceGroup, subscriptionId, location

But here the WHERE clause is bombing everywhere across all columns.

Is there a more fashionable way to search into Azure ideally with more words like:

  • _old
  • .old
  • _test
  • .test
  • _dev
  • .dev

I have to clean up unused resources and I have to search per resource name.

Solution
    • AS of today, Azure Resource Graph supports a quite limited subset of KQL. E.g. has_any is currently not supported.
    • If needed, you can uncomment the commented line to improve performance.
    Resources
| where type == 'microsoft.sql/servers/databases'
// | where name has_cs "old" or name has_cs "dev" or name has_cs "test"
| parse-where kind=regex name with ".*[._]" suffix
| where suffix in ("old", "dev", "test")