Azure Resource Graph - Database sizes

Question

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?

Answer 1

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