CODEWITHSUNDEEP
kqlazure-resource-graph
Detsawm
Detsawm

Reputation: 81

Azure Resource Graph - Database sizes

I have recently begun using Azure Resource Graph to query and pull reports across our entire estate regarding our azure databases. I've managed to pull a lot of useful data out from the resources table but I'm brand new to KQL and graph in general and am unsure of where else to look for the monitoring data such as used space, remaining space etc.

The query I'm running currently is:

resources
| where type =~ 'microsoft.sql/servers'
| project serverName = name, serverLocation = location, serverResourceGroup = resourceGroup, serverFQDN = properties.fullyQualifiedDomainName, serverLocalAdminAccount = properties.administratorLogin, serverAdminGroup = properties.administrators.login, serverTags = tags
| join (resources
| where type =~ 'microsoft.sql/servers/databases' and name != 'master'
| extend serverName = extract("servers/(.*)/databases", 1, id)
| project serverName, databaseName = name, databaseCreationDate = format_datetime(todatetime(properties.creationDate), 'yyyy-MM-dd HH:mm'), databaseStatus = properties.status, databaseBackupStorageRedundancy = properties.requestedBackupStorageRedundancy, databaseZoneRedundant = properties.zoneRedundant, databaseCollation = properties.collation, databaseEarliestRestoreDate = format_datetime(todatetime(properties.earliestRestoreDate), 'yyyy-MM-dd HH:mm'), elasticPoolId = tolower(properties.elasticPoolId)) on serverName
| project-away serverName1
| join kind=leftouter (resources
  | where type =~ 'microsoft.sql/servers/elasticpools'
  | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state) on elasticPoolId
| project-away elasticPoolId, elasticPoolId1

I'll admit that at first glance, I thought I'd find everything I needed within those 3 types but evidently there must be something else I'm missing?

Upvotes: 1

Views: 290

Answers (1)

decius
decius

Reputation: 1445

Maybe this query could help you:

resources 
| where type == "microsoft.sql/servers/databases" 
| project
    resourceGroup,
    serverName = split(id, "/")[8],
    databaseName = name,
    sku = properties.sku.name,
    status = properties.status,
    maxSizeBytes = toint(properties.maxSizeBytes),
    maxSizeGB = maxSizeBytes / (1024 * 1024 * 1024),
    usedSpaceMB = properties.currentUsedStorageBytes / (1024 * 1024) 
| order by databaseName asc

Upvotes: 1

Related Questions