Question 105188 in Azure Question posted in
The official documentation to get you started can be found here.

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0
    • 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")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search