Kusto :: find all SQL Server and the number of databases within each one

Question

I need to find a list of SQL Servers that are good candidates for SQL Elastic Pool.

I have this Kusto query but it just list all the databases once:

resources
| where type == 'microsoft.sql/servers/databases'
| summarize TotalDatabases = count() by name

From my understanding I should maybe join:

• | where type == 'microsoft.sql/servers/databases'
• | where type == 'microsoft.sql/servers'

Any idea how to do it?

The result should look like this:

serverName	NumberOfDatabases
Server1	16
Server2	3
Server3	5

Answer 1

You can get the list of Server Name and Database Name by using the below Kusto query

resources
| where type == 'microsoft.sql/servers/databases'
| summarize NumberOfDatabases = count() by ServerName = tolower(split(id, '/')[8])
| project ServerName, NumberOfDatabases

Output-