display only specific resources by type with kusto in Resource Graph Explorer

Question

I have an issue with showing specific resources with azure kusto query.

what i want is to write a kusto query that show only database resources and server resources in azure.

i have written following query regarding Databases:

resources
| where type in ("microsoft.sql/servers/databases","microsoft.dbforpostgresql/servers","microsoft.azuredata/postgresinstances","microsoft.dbformariadb/servers","microsoft.dbformysql/flexibleservers","microsoft.dbformysql/servers","microsoft.dbforpostgresql/flexibleservers","microsoft.dbforpostgresql/servergroups","microsoft.kusto/clusters/databases","microsoft.sql/managedinstances/databases","microsoft.synapse/workspaces/sqldatabases","ravenhq.db/databases","microsoft.documentdb/databaseaccounts")
| summarize Amount=count() by type

But when i execute the query it shows me two Databases even though i only have create one, the extra one is a "master" which should not be included because there is only one resource in the resource group

i have also tried with the following query:

resources
| where type contains "database" | distinct type
| summarize Amount=count() by type

But then the issue is that it doesnt include all the db's that doesnt have the word "database" in the type name for example "microsoft.azuredata/postgresinstances"

so the question is, how do i write a query that shows ALL the databases on my dashboard.

The second part of the question which is similar to the previous with databases is how i show all the Servers. I have tried with the following queries:

resources
| where split(type,"/")[array_length(split(type,"/"))] contains "servers"

it gave me no result even though i had a server. then i tried:

resources
| where type contains "/server" | distinct type
| summarize Amount=count() by type

that didnt work because it also returned all the database resources cuntaining the work "server"

i have tried to look through microsofts documentation, but cannot figure out what to do.

Answer 1

If you don't want the master databases (which are the databases that store system level data in SQL databases, you can simply filter them out:

resources
| where type in ("microsoft.sql/servers/databases","microsoft.dbforpostgresql/servers","microsoft.azuredata/postgresinstances","microsoft.dbformariadb/servers","microsoft.dbformysql/flexibleservers","microsoft.dbformysql/servers","microsoft.dbforpostgresql/flexibleservers","microsoft.dbforpostgresql/servergroups","microsoft.kusto/clusters/databases","microsoft.sql/managedinstances/databases","microsoft.synapse/workspaces/sqldatabases","ravenhq.db/databases","microsoft.documentdb/databaseaccounts")
| where name type != "microsoft.sql/servers/databases" or name != "master"
| summarize Amount=count() by type

Regarding the 2nd question, this should work since the has operator will only match whole tokens (and a slash separates tokens):

resources | where type has "servers"