0

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
Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113

1 Answers1

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-

enter image description here

Ikhtesam Afrin
  • 897
  • 1
  • 1
  • 6