0

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.

Lasse.37846
  • 5
  • 2

1 Answers1

0

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"
rony l
  • 5,798
  • 5
  • 35
  • 56
  • Thanks so much, it worked with the databases not showing the "master" I got one follow-up question for the servers, i tried the `resources | where type has "servers"` but then it also showed the databases which "server" was included in the name and i dont want that because i olso want to filter out actual servers, do you have an idea for that ? – Lasse.37846 Nov 24 '20 at 08:27
  • you can add a filter for that like this: `resources | where type has "servers" | where type != 'microsoft.sql/servers/databases'` – rony l Nov 24 '20 at 18:27