CODEWITHSUNDEEP
kqlazure-resource-graph
Francesco Mantovani
Francesco Mantovani

Reputation: 12227

KQL :: return only tags with more than 4 records

I have created a Kusto query that allows me to return all our database park. The query only takes 10 lines of code:

Resources
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
| where subscriptionName in~ ('Subscription1','Subscription2')
| where type =~ 'microsoft.sql/servers/databases'
| where name != 'master'
| project  subscriptionName, resourceGroup, name, type, location,sku.tier, properties.requestedServiceObjectiveName, tags.customerCode

By contract we are supposed to give only 4 Azure SQL Database per customer but sometimes developers take a copy of them and they rename it _old or _backup and suddenly a customer can have 5 or 6 databases.

This increase the overall costs of the Cloud and I would like to have a list of all customers that have more than 4 databases.

In order to do so I can use the tag tags.customerCode which has the 3 letters identifier for each customer.

The code should work like this: if a customer is called ABC and there are 4 Azure SQL Databases with tags.customerCode ABC the query should return nothing. If there are 5 or 6 databases with tags.customerCode ABC the query should return all of them.

Not sure if Kusto can be that flexible.

Upvotes: 1

Views: 372

Answers (1)

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

Reputation: 44941

Here is a possible solution.
It should be noted that Azure resource graph supports only a limited subset of KQL.

resourcecontainers 
| where     type == 'microsoft.resources/subscriptions'
        //and name in~ ('Subscription1','Subscription2')
| project subscriptionId, subscriptionName = name
| join kind=inner  
  (
    resources
    | where     type =~ 'microsoft.sql/servers/databases'
            and name != 'master'
  )
  on  subscriptionId            
| project   subscriptionId, subscriptionName, resourceGroup, name, type, location
           ,tier                            = sku.tier
           ,requestedServiceObjectiveName   = properties.requestedServiceObjectiveName
           ,customerCode                    = tostring(tags.customerCode)    
| summarize dbs = count(), details = make_list(pack_all()) by customerCode
| where dbs > 4
| mv-expand with_itemindex=db_seq ['details']
| project   customerCode
           ,dbs
           ,db_seq = db_seq + 1
           ,subscriptionId                  = details.subscriptionId
           ,subscriptionName                = details.subscriptionName
           ,resourceGroup                   = details.resourceGroup
           ,name                            = details.name
           ,type                            = details.type
           ,location                        = details.location
           ,tier                            = details.tier
           ,requestedServiceObjectiveName   = details.requestedServiceObjectiveName

Upvotes: 1

Related Questions