Get-AzureSqlDatabaseUsageReport

<#
.SYNOPSIS
    Reports size, tier, and CPU usage for every Azure SQL database in a subscription.

.DESCRIPTION
    Loops through every Azure SQL server, or every server in one resource
    group if you provide one, and pulls the current size, max size,
    service tier, elastic pool membership, and average CPU percent over
    the last 24 hours for each database. Results are exported to a CSV
    and any database averaging 80 percent CPU or higher is printed out
    separately so you can spot compute problems before they cause
    timeouts for users.

.PARAMETER ResourceGroupName
    Optional. Limits the report to SQL servers in this resource group.
    If omitted, every resource group in the current subscription is checked.

.PARAMETER OutputPath
    Path for the CSV report. Defaults to .\AzureSqlDatabaseUsageReport.csv
    in the current folder.

.EXAMPLE
    .\Get-AzureSqlDatabaseUsageReport.ps1 -ResourceGroupName "rg-prod-data"

.AUTHOR
    Shehryar Hassan
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory = $false)]
    [string]$ResourceGroupName,

    [Parameter(Mandatory = $false)]
    [string]$OutputPath = ".\AzureSqlDatabaseUsageReport.csv"
)

if (-not (Get-AzContext)) {
    Connect-AzAccount | Out-Null
}

$servers = if ($ResourceGroupName) {
    Get-AzSqlServer -ResourceGroupName $ResourceGroupName
} else {
    Get-AzSqlServer
}

if (-not $servers) {
    Write-Warning "No Azure SQL servers found."
    return
}

$report = [System.Collections.Generic.List[object]]::new()

foreach ($server in $servers) {
    $databases = Get-AzSqlDatabase -ResourceGroupName $server.ResourceGroupName -ServerName $server.ServerName |
        Where-Object { $_.DatabaseName -ne "master" }

    foreach ($db in $databases) {
        $avgCpu = $null
        $cpuMetric = Get-AzMetric -ResourceId $db.ResourceId -MetricName "cpu_percent" `
            -TimeGrain 01:00:00 -StartTime (Get-Date).AddHours(-24) -EndTime (Get-Date) `
            -AggregationType Average -WarningAction SilentlyContinue

        if ($cpuMetric.Data) {
            $values = $cpuMetric.Data | Where-Object { $null -ne $_.Average } | Select-Object -ExpandProperty Average
            if ($values) {
                $avgCpu = [math]::Round(($values | Measure-Object -Average).Average, 1)
            }
        }

        $maxSizeGb = [math]::Round($db.MaxSizeBytes / 1GB, 1)

        $report.Add([PSCustomObject]@{
            ServerName       = $server.ServerName
            DatabaseName     = $db.DatabaseName
            Edition          = $db.Edition
            ServiceObjective = $db.CurrentServiceObjectiveName
            ElasticPool      = $db.ElasticPoolName
            MaxSizeGB        = $maxSizeGb
            Status           = $db.Status
            AvgCpuPercent24h = $avgCpu
            Location         = $server.Location
        })
    }
}

$report | Sort-Object AvgCpuPercent24h -Descending | Export-Csv -Path $OutputPath -NoTypeInformation

$highUsage = $report | Where-Object { $_.AvgCpuPercent24h -ge 80 }
if ($highUsage) {
    Write-Host "Databases averaging 80 percent CPU or higher over the last 24 hours:" -ForegroundColor Yellow
    $highUsage | Format-Table ServerName, DatabaseName, AvgCpuPercent24h -AutoSize
}

Write-Host "Report saved to $OutputPath ($($report.Count) databases)." -ForegroundColor Green