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.

CodePudding user response：

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

CodePudding user response：

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