Listing Azure Databases

If you are a data professional and need to list all the databases in your organization, across all subscriptions and resource providers, then you can easily accomplish this task by using Azure Resource Graph. We will start with writing the Kusto query in the Azure portal and later we will use PowerShell to execute the query programatically.

Azure Resource Graph Explorer

The easiest way to write a Kusto query is using the explorer. In the Azure portal search box, type Resource Graph Explorer and click on it. On the left pane search for sql, and you will see all the resource types that contain sql in the name. Click on resources, then click on the resource type microsoft.sql/servers/databases, both will show in the query editor in the right like this:

resources
 | where type == "microsoft.sql/servers/databases"

Next click on Run query and that's it, you wrote your first Kusto query!

In this example you will notice that master shows as a third database, you can filter it out by adding another where operator:

 | where name != "master"

It is a good practice to only show the columns needed (less data travel over the wire), you can select the columns with the project operator:

 | project type, name, kind, subscriptionId

Since the query will search in all subscriptions, you can create another query to find the subscriptions in your organizations using resourcecontainers of type microsoft.resources/subscriptions, and you will use the project operator to select what is needed such as subscriptionId and susbcriptionName:

 resourcecontainers
 | where type == "microsoft.resources/subscriptions"
 | project subscriptionId, subscriptionName = name

Now you can join both queries using the join operator with the column subscriptionId, and selecting again only the columns needed with the project operator:

resources
 | where type == "microsoft.sql/servers/databases"
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName

Azure PowerShell

Now that you have the Kusto query under your belt, you can call it from a PowerShell script using the cmdlet Search-AzGraph. But first you will need the Az modules, then connect to Azure:

Connect-AzAccount

Next you can run the same query from above. In addition, you can add other database resource types (MySQL and PostgreSQL) using the in operator

$query = '
resources
 | where type in (
    "microsoft.sql/servers/databases"
    ,"microsoft.sqlvirtualmachine/sqlvirtualmachines"
    ,"microsoft.sql/managedinstances"
    ,"microsoft.dbforpostgresql/servers"
    ,"microsoft.dbformysql/servers"
   )
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName
'
Search-AzGraph -Query $query

This was just a peek of the capabilities that Azure Resource Graph provides and hopefully it sparked your interest to learn more about it.