CODEWITHSUNDEEP
azurekqlazure-resource-graph
Francesco Mantovani
Francesco Mantovani

Reputation: 12227

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:

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

Upvotes: 1

Views: 904

Answers (2)

Francesco Mantovani
Francesco Mantovani

Reputation: 12227

Thank you @PeterBonz, the name is what I was missing.

My code now works this way:

// Find Unused Virtual Machines
Resources
| where type has "microsoft.compute/virtualmachines"
| where name contains "old"
    or name contains "dev"
    or name contains "test"
| project resourceName = name,type,sku.name,sku.tier,tags.createdBy,tags.customerCode,tags.managedBy, resourceGroup, subscriptionId, location

I also found the clause has_any but I couldn't make it work:

let ComputerTerms = pack_array('old', 'dev', 'test');
// Find Unused Virtual Machines
Resources
| where type has "microsoft.compute/virtualmachines"
| where name has_any (ComputerTerms) 
| project resourceName = name,type,sku.name,sku.tier,tags.createdBy,tags.customerCode,tags.managedBy, resourceGroup, subscriptionId, location

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

  • 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")

Upvotes: 1

Related Questions