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

Question

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.

I'm not familiar with the professional term "bombing everywhere across all columns". What exactly is the issue / what exactly are you looking for? — David דודו Markovitz, Aug 25 '22 at 08:59

David דודו Markovitz · Accepted Answer · 2022-08-25T11:05:17.760

1

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

edited Aug 25 '22 at 11:05

answered Aug 25 '22 at 09:20

David דודו Markovitz

42,900
6
64
88

1

Ah, regex on KQL, I'm definitely go to use that. Thank you @David – Francesco Mantovani Aug 25 '22 at 11:26

score 0 · Answer 2 · answered Aug 25 '22 at 11:25

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

answered Aug 25 '22 at 11:25

Francesco Mantovani

10,216
13
73
113

As I wrote in my answer, "AS of today, Azure Resource Graph supports a quite limited subset of KQL. E.g. `has_any` is currently not supported" – David דודו Markovitz Aug 25 '22 at 11:40
I don't get the saying: "My code now works this way". You already got better options, that actually answer your request (looking for suffixes). – David דודו Markovitz Aug 25 '22 at 11:43
My problem was that my code was using * and therefore was searching for the word in all columns. I now can target the column "name" and this was the goal – Francesco Mantovani Aug 25 '22 at 12:03
It seems you were also looking for suffixes – David דודו Markovitz Aug 25 '22 at 12:20
1

Sorry @DavidדודוMarkovitz, my question was misleading. It's true that I was looking for suffix but the word sometimes is also inside the name – Francesco Mantovani Aug 25 '22 at 12:25

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

2 Answers2