DEV Community: greenmtnsun

Health: Module psm1

greenmtnsun — Thu, 26 Mar 2026 20:22:25 +0000

requires -Version 5.1

Set-StrictMode -Version 2.0

$script:ModuleRoot = Split-Path -Parent $MyInvocation.MyCommand.Path

Get-ChildItem -Path (Join-Path $script:ModuleRoot 'Private') -Filter '*.ps1' -Recurse -ErrorAction SilentlyContinue |
Sort-Object FullName |
ForEach-Object { . $_.FullName }

Get-ChildItem -Path (Join-Path $script:ModuleRoot 'Public') -Filter '*.ps1' -Recurse -ErrorAction SilentlyContinue |
Sort-Object FullName |
ForEach-Object { . $_.FullName }

Export-ModuleMember -Function @(
'Invoke-SqlTechnicalSanity',
'Get-SqlTechnicalSanityCollector',
'Get-SqlTechnicalSanityCheck',
'ConvertTo-SqlTechnicalSanityHtml',
'ConvertTo-SqlTechnicalSanityJson',
'Export-SqlTechnicalSanityReport'
)

Health: JSON for Report

greenmtnsun — Thu, 26 Mar 2026 20:22:08 +0000

{
"schemaVersion": "3.0",
"run": {
"RunId": "mock-run-003",
"Instances": [
"PROD-SQL01\SQL2019"
],
"GeneratedAt": "2026-03-26T10:00:00-07:00",
"DbatoolsVersion": "2.x"
},
"score": {
"OverallScore": 81.4,
"DomainScores": [
{
"Collector": "Instance",
"Score": 95,
"Coverage": 100,
"Weight": 1.0,
"Findings": 2
},
{
"Collector": "Databases",
"Score": 78,
"Coverage": 100,
"Weight": 1.3,
"Findings": 4
},
{
"Collector": "Backups",
"Score": 68,
"Coverage": 100,
"Weight": 1.6,
"Findings": 4
},
{
"Collector": "Jobs",
"Score": 65,
"Coverage": 100,
"Weight": 1.0,
"Findings": 3
},
{
"Collector": "HaDr",
"Score": 92,
"Coverage": 100,
"Weight": 1.6,
"Findings": 3
},
{
"Collector": "Storage",
"Score": 88,
"Coverage": 100,
"Weight": 1.3,
"Findings": 2
},
{
"Collector": "Security",
"Score": 60,
"Coverage": 100,
"Weight": 1.2,
"Findings": 2
},
{
"Collector": "Performance",
"Score": 80,
"Coverage": 100,
"Weight": 1.2,
"Findings": 2
},
{
"Collector": "Replication",
"Score": 100,
"Coverage": 100,
"Weight": 1.1,
"Findings": 1
},
{
"Collector": "TempDb",
"Score": 70,
"Coverage": 100,
"Weight": 1.0,
"Findings": 1
},
{
"Collector": "ErrorLog",
"Score": 75,
"Coverage": 100,
"Weight": 0.9,
"Findings": 1
},
{
"Collector": "Shares",
"Score": 100,
"Coverage": 100,
"Weight": 0.9,
"Findings": 1
}
],
"TotalFindings": 26,
"CriticalCount": 3,
"WarningCount": 8,
"UnknownCount": 0,
"HealthyCount": 9,
"InfoCount": 6
},
"findings": [
{
"CheckId": "BACKUP-LAST-LOG",
"TargetName": "SalesDB",
"State": "Critical",
"Message": "No log backup found for FULL recovery database.",
"Evidence": {
"LastLogBackup": "",
"WarnMinutes": 90
}
},
{
"CheckId": "JOB-FAILURES",
"TargetName": "LogBackupJob",
"State": "Critical",
"Message": "Last status for job 'LogBackupJob' is 0.",
"Evidence": {
"Enabled": true,
"LastRun": "2026-03-26T08:55:00",
"Message": "The step failed."
}
},
{
"CheckId": "CERT-EXPIRY",
"TargetName": "AGEndpointCert",
"State": "Critical",
"Message": "Certificate 'AGEndpointCert' expires in 9 day(s).",
"Evidence": {
"ExpiryDate": "2026-04-04",
"DaysRemaining": 9
}
},
{
"CheckId": "TEMPDB-FILES",
"TargetName": "tempdb",
"State": "Warning",
"Message": "TempDB data file count is below the warning threshold.",
"Evidence": {
"FileCount": 2,
"WarnThreshold": 4
}
},
{
"CheckId": "ERRORLOG-SCAN",
"TargetName": "PROD-SQL01\SQL2019",
"State": "Warning",
"Message": "Potentially important patterns found in SQL error log.",
"Evidence": {
"Sample": "I/O requests taking longer than 15 seconds | deadlock encountered | backup failed"
}
}
]
}

Health: Module psd1

greenmtnsun — Thu, 26 Mar 2026 19:39:21 +0000

@{
RootModule = 'SqlTechnicalSanity.psm1'
ModuleVersion = '3.0.0'
GUID = '14552ff8-6ab0-4fef-bd66-d6901c0b6a5e'
Author = 'OpenAI'
CompanyName = 'OpenAI'
Copyright = '(c) OpenAI. All rights reserved.'
Description = 'Fail-soft, dbatools-first SQL Server technical sanity reporting with Outlook-safe HTML output.'
PowerShellVersion = '5.1'

FunctionsToExport = @(
    'Invoke-SqlTechnicalSanity',
    'Get-SqlTechnicalSanityCollector',
    'Get-SqlTechnicalSanityCheck',
    'ConvertTo-SqlTechnicalSanityHtml',
    'ConvertTo-SqlTechnicalSanityJson',
    'Export-SqlTechnicalSanityReport'
)

CmdletsToExport   = @()
VariablesToExport = @()
AliasesToExport   = @()

PrivateData = @{
    PSData = @{
        Tags = @('SQLServer','dbatools','HealthCheck','HTML','Outlook','FailSoft','Operations')
        ReleaseNotes = 'v3 scaffold with expanded collectors: replication, tempdb, error log, and share/path checks.'
    }
}

}

Health: Example Call

greenmtnsun — Thu, 26 Mar 2026 19:30:18 +0000

Import-Module "$PSScriptRoot\SqlTechnicalSanity.psd1" -Force

$result = Invoke-SqlTechnicalSanity -SqlInstance @('localhost')
-OutputDirectory "$PSScriptRoot\out" `
-PassThru

$result.Score | Format-List
$result.HtmlPath
$result.JsonPath

Health: Example call.

greenmtnsun — Thu, 26 Mar 2026 19:29:08 +0000

FunctionsToExport = @(
    'Invoke-SqlTechnicalSanity',
    'Get-SqlTechnicalSanityCollector',
    'Get-SqlTechnicalSanityCheck',
    'ConvertTo-SqlTechnicalSanityHtml',
    'ConvertTo-SqlTechnicalSanityJson',
    'Export-SqlTechnicalSanityReport'
)

CmdletsToExport   = @()
VariablesToExport = @()
AliasesToExport   = @()

PrivateData = @{
    PSData = @{
        Tags = @('SQLServer','dbatools','HealthCheck','HTML','Outlook','FailSoft','Operations')
        ReleaseNotes = 'v3 scaffold with expanded collectors: replication, tempdb, error log, and share/path checks.'
    }
}

}

Version 2 of the Audit Script for SQL Server

greenmtnsun — Thu, 26 Mar 2026 02:15:19 +0000

<#
ways to call it.
.\Invoke-SqlServerNewInstallAudit_dbatools_v3.ps1 -SqlInstance "MyServer\SQL2022"
-OutputPath "C:\Temp\SqlAudit.html"

$cred = Get-Credential

.\Invoke-SqlServerNewInstallAudit_dbatools_v3.ps1 -SqlInstance "MyServer\SQL2022"
-SqlCredential $cred `
-OutputPath "C:\Temp\SqlAudit.html"

$cred = Get-Credential

.\Invoke-SqlServerNewInstallAudit_dbatools_v3.ps1 -SqlInstance "MyServer\SQL2022"
-SqlCredential $cred `
-OutputPath "C:\Temp\SqlAudit.html"

>

[CmdletBinding()]
param(
[Parameter(Mandatory)]
[string]$SqlInstance,

[PSCredential]$SqlCredential,

[string]$OutputPath = ".\SqlServer-NewInstall-Audit.html",

[int]$TopDbRows = 25,

[switch]$TrustServerCertificate,

[switch]$EmailReport,
[string]$SmtpServer,
[int]$SmtpPort = 587,
[switch]$UseSsl,
[PSCredential]$SmtpCredential,
[string]$EmailFrom,
[string[]]$EmailTo,
[string[]]$EmailCc,
[string]$EmailSubject

)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

region helpers

function Write-Log {
param([string]$Message)
Write-Host ("[{0}] {1}" -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss'), $Message)
}

function Test-CommandExists {
param([string]$Name)
return bool
}

function Convert-BytesToNiceString {
param([Nullable[decimal]]$Bytes)
if ($null -eq $Bytes) { return '' }
$sizes = @('B','KB','MB','GB','TB','PB')
$order = 0
$value = [decimal]$Bytes
while ($value -ge 1024 -and $order -lt ($sizes.Count - 1)) {
$value = $value / 1024
$order++
}
return '{0:N2} {1}' -f $value, $sizes[$order]
}

function Convert-ToSafeString {
param([AllowNull()][object]$Value)
if ($null -eq $Value) { return '' }
if ($Value -is [datetime]) { return $Value.ToString('yyyy-MM-dd HH:mm:ss') }
if ($Value -is [System.Array]) { return (($Value | ForEach-Object { Convert-ToSafeString $_ }) -join ', ') }
return [string]$Value
}

function New-Metric {
[CmdletBinding()]
param(
[Parameter(Mandatory)][string]$Category,
[Parameter(Mandatory)][string]$Metric,
[AllowNull()][object]$Value,
[ValidateSet('Info','Pass','Warn','Fail')][string]$Status = 'Info',
[string]$Notes = ''
)

[pscustomobject]@{
    Category = $Category
    Metric   = $Metric
    Value    = Convert-ToSafeString $Value
    Status   = $Status
    Notes    = $Notes
}

}

function Add-Metric {
[CmdletBinding()]
param(
[Parameter(Mandatory)][System.Collections.Generic.List[object]]$List,
[Parameter(Mandatory)][string]$Category,
[Parameter(Mandatory)][string]$Metric,
[AllowNull()][object]$Value,
[ValidateSet('Info','Pass','Warn','Fail')][string]$Status = 'Info',
[string]$Notes = ''
)

if ($null -eq $List) {
    throw "Metric list is null."
}

[void]$List.Add((New-Metric -Category $Category -Metric $Metric -Value $Value -Status $Status -Notes $Notes))

}

function Add-SectionError {
[CmdletBinding()]
param(
[Parameter(Mandatory)][System.Collections.Generic.List[object]]$List,
[Parameter(Mandatory)][string]$Section,
[Parameter(Mandatory)][string]$Message
)

[void]$List.Add([pscustomobject]@{
    Section = $Section
    Error   = $Message
})

}

function Get-StatusFromRule {
[CmdletBinding()]
param(
[string]$Metric,
[AllowNull()][object]$Value
)

$valueString = Convert-ToSafeString $Value
switch -Regex ($Metric) {
    '^SQL Server service status$'               { if ($valueString -eq 'Running') { return 'Pass' } else { return 'Fail' } }
    '^SQL Server Agent service status$'         { if ($valueString -eq 'Running') { return 'Pass' } else { return 'Warn' } }
    '^xp_cmdshell$'                             { if ($valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^Ole Automation Procedures$'               { if ($valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^ad hoc distributed queries$'              { if ($valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^cross db ownership chaining$'             { if ($valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^scan for startup procs$'                  { if ($valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^priority boost$'                          { if ($valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^lightweight pooling$'                     { if ($valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^default trace enabled$'                   { if ($valueString -eq '1') { return 'Pass' } else { return 'Warn' } }
    '^optimize for ad hoc workloads$'           { if ($valueString -eq '1') { return 'Pass' } else { return 'Warn' } }
    '^backup compression default$'              { if ($valueString -eq '1') { return 'Pass' } else { return 'Warn' } }
    '^remote admin connections$'                { if ($valueString -eq '1') { return 'Pass' } else { return 'Warn' } }
    '^cost threshold for parallelism$'          {
        try { if ([int]$valueString -ge 25) { return 'Pass' } else { return 'Warn' } } catch { return 'Info' }
    }
    '^DB \[.+\] auto_close$'                    { if ($valueString -eq 'False' -or $valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^DB \[.+\] auto_shrink$'                   { if ($valueString -eq 'False' -or $valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^DB \[.+\] trustworthy$'                   { if ($valueString -eq 'False' -or $valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^DB \[.+\] page verify$'                   { if ($valueString -eq 'Checksum') { return 'Pass' } else { return 'Warn' } }
    '^File \[.+\] percent growth$'              { if ($valueString -eq 'False' -or $valueString -eq '0') { return 'Pass' } else { return 'Warn' } }
    '^TempDB data files equal size$'            { if ($valueString -eq 'Yes') { return 'Pass' } else { return 'Warn' } }
    '^Max server memory review$'                {
        try {
            if ([int]$valueString -eq 2147483647 -or [int]$valueString -eq 0) { return 'Fail' } else { return 'Info' }
        } catch { return 'Info' }
    }
    default                                     { return 'Info' }
}

}

function Convert-TableToHtml {
[CmdletBinding()]
param(
[Parameter(Mandatory)][System.Collections.IEnumerable]$Data,
[string]$Title,
[string[]]$Columns
)

$sb = [System.Text.StringBuilder]::new()
if ($Title) { [void]$sb.AppendLine("<h3>$([System.Net.WebUtility]::HtmlEncode($Title))</h3>") }

$rows = @($Data)
if ($rows.Count -eq 0) {
    [void]$sb.AppendLine("<div class='empty'>No data returned.</div>")
    return $sb.ToString()
}

if (-not $Columns) {
    $Columns = $rows[0].PSObject.Properties.Name
}

[void]$sb.AppendLine("<table>")
[void]$sb.AppendLine("<thead><tr>")
foreach ($c in $Columns) {
    [void]$sb.AppendLine("<th>$([System.Net.WebUtility]::HtmlEncode($c))</th>")
}
[void]$sb.AppendLine("</tr></thead><tbody>")

foreach ($row in $rows) {
    [void]$sb.AppendLine("<tr>")
    foreach ($c in $Columns) {
        $value = ''
        try { $value = Convert-ToSafeString $row.$c } catch {}
        $class = ''
        if ($c -eq 'Status') {
            switch ($value) {
                'Pass' { $class = 'status-pass' }
                'Warn' { $class = 'status-warn' }
                'Fail' { $class = 'status-fail' }
                default { $class = 'status-info' }
            }
        }
        [void]$sb.AppendLine("<td class='$class'>$([System.Net.WebUtility]::HtmlEncode($value))</td>")
    }
    [void]$sb.AppendLine("</tr>")
}

[void]$sb.AppendLine("</tbody></table>")
return $sb.ToString()

}

function Invoke-AuditSection {
[CmdletBinding()]
param(
[Parameter(Mandatory)][string]$SectionName,
[Parameter(Mandatory)][scriptblock]$ScriptBlock,
[Parameter(Mandatory)][System.Collections.Generic.List[object]]$SectionErrors
)

try {
    Write-Log "Starting section: $SectionName"
    & $ScriptBlock
    Write-Log "Completed section: $SectionName"
}
catch {
    $msg = $_.Exception.Message
    Write-Warning "Section [$SectionName] failed: $msg"
    Add-SectionError -List $SectionErrors -Section $SectionName -Message $msg
}

}

function Get-PropertyValue {
[CmdletBinding()]
param(
[Parameter(Mandatory)][AllowNull()]$InputObject,
[Parameter(Mandatory)][string]$PropertyName,
[AllowNull()]$Default = $null
)

if ($null -eq $InputObject) { return $Default }
if ($InputObject.PSObject.Properties.Name -contains $PropertyName) {
    return $InputObject.$PropertyName
}
return $Default

}

function Connect-AuditInstance {
[CmdletBinding()]
param(
[Parameter(Mandatory)][string]$SqlInstance,
[PSCredential]$SqlCredential,
[switch]$TrustServerCertificate
)

$connectParams = @{
    SqlInstance = $SqlInstance
    NonPooledConnection = $true
    Encrypt = $true
    EnableException = $true
}
if ($SqlCredential) { $connectParams.SqlCredential = $SqlCredential }
if ($TrustServerCertificate) { $connectParams.TrustServerCertificate = $true }

return Connect-DbaInstance @connectParams

}

function Invoke-SqlSafeQuery {
[CmdletBinding()]
param(
[Parameter(Mandatory)]$SqlInstance,
[Parameter(Mandatory)][string]$Query,
[Parameter(Mandatory)][string]$QueryName
)

try {
    return Invoke-DbaQuery -SqlInstance $SqlInstance -Query $Query -EnableException
}
catch {
    throw "Query failed [$QueryName]: $($_.Exception.Message)"
}

}

function Get-WindowsData {
[CmdletBinding()]
param()

$result = [ordered]@{
    ComputerSystem = $null
    OperatingSystem = $null
    Bios = $null
    Processor = @()
    LogicalDisks = @()
}

try { $result.ComputerSystem = Get-CimInstance -ClassName Win32_ComputerSystem -ErrorAction Stop } catch {}
try { $result.OperatingSystem = Get-CimInstance -ClassName Win32_OperatingSystem -ErrorAction Stop } catch {}
try { $result.Bios = Get-CimInstance -ClassName Win32_BIOS -ErrorAction Stop } catch {}
try { $result.Processor = @(Get-CimInstance -ClassName Win32_Processor -ErrorAction Stop) } catch {}
try { $result.LogicalDisks = @(Get-CimInstance -ClassName Win32_LogicalDisk -Filter "DriveType = 3" -ErrorAction Stop) } catch {}

return [pscustomobject]$result

}

function Send-ReportEmail {
[CmdletBinding()]
param(
[Parameter(Mandatory)][string]$AttachmentPath,
[Parameter(Mandatory)][string]$SmtpServer,
[int]$SmtpPort = 587,
[switch]$UseSsl,
[PSCredential]$SmtpCredential,
[Parameter(Mandatory)][string]$From,
[Parameter(Mandatory)][string[]]$To,
[string[]]$Cc,
[Parameter(Mandatory)][string]$Subject
)

if (-not (Test-Path -LiteralPath $AttachmentPath)) {
    throw "Attachment path does not exist: $AttachmentPath"
}

$mailParams = @{
    From        = $From
    To          = ($To -join ',')
    Subject     = $Subject
    Body        = "SQL Server audit completed. See attached HTML report."
    SmtpServer  = $SmtpServer
    Port        = $SmtpPort
    Attachments = $AttachmentPath
}
if ($Cc) { $mailParams.Cc = ($Cc -join ',') }
if ($UseSsl) { $mailParams.UseSsl = $true }
if ($SmtpCredential) { $mailParams.Credential = $SmtpCredential }

Send-MailMessage @mailParams

}

endregion helpers

if (-not (Get-Module -ListAvailable -Name dbatools)) {
throw "The dbatools module is required for this script. Install it first, for example: Install-Module dbatools -Scope CurrentUser"
}

Import-Module dbatools -ErrorAction Stop

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

$dbSummary = [System.Collections.Generic.List[object]]::new()
$fileSummary = [System.Collections.Generic.List[object]]::new()
$jobSummary = [System.Collections.Generic.List[object]]::new()
$loginSummary = [System.Collections.Generic.List[object]]::new()
$backupSummary = [System.Collections.Generic.List[object]]::new()
$driveSummary = [System.Collections.Generic.List[object]]::new()
$waitSummary = [System.Collections.Generic.List[object]]::new()
$tempdbSummary = [System.Collections.Generic.List[object]]::new()
$agSummary = [System.Collections.Generic.List[object]]::new()
$svcSummary = [System.Collections.Generic.List[object]]::new()

Write-Log "Connecting to $SqlInstance"
$instance = Connect-AuditInstance -SqlInstance $SqlInstance -SqlCredential $SqlCredential -TrustServerCertificate:$TrustServerCertificate
$generatedOn = Get-Date

Invoke-AuditSection -SectionName 'OS and Storage' -SectionErrors $sectionErrors -ScriptBlock {
$windows = Get-WindowsData

$comp = $windows.ComputerSystem
$os = $windows.OperatingSystem
$bios = $windows.Bios
$procs = @($windows.Processor)
$disks = @($windows.LogicalDisks)

if ($comp) {
    Add-Metric -List $metrics -Category 'OS' -Metric 'Computer name' -Value $comp.Name
    Add-Metric -List $metrics -Category 'OS' -Metric 'Domain' -Value $comp.Domain
    Add-Metric -List $metrics -Category 'OS' -Metric 'Manufacturer' -Value $comp.Manufacturer
    Add-Metric -List $metrics -Category 'OS' -Metric 'Model' -Value $comp.Model
    Add-Metric -List $metrics -Category 'OS' -Metric 'Physical memory total' -Value (Convert-BytesToNiceString $comp.TotalPhysicalMemory)
    Add-Metric -List $metrics -Category 'OS' -Metric 'Logical processors (WMI)' -Value $comp.NumberOfLogicalProcessors
    Add-Metric -List $metrics -Category 'OS' -Metric 'Processor sockets (WMI)' -Value $comp.NumberOfProcessors
}

if ($os) {
    Add-Metric -List $metrics -Category 'OS' -Metric 'OS caption' -Value $os.Caption
    Add-Metric -List $metrics -Category 'OS' -Metric 'OS version' -Value $os.Version
    Add-Metric -List $metrics -Category 'OS' -Metric 'OS build number' -Value $os.BuildNumber
    Add-Metric -List $metrics -Category 'OS' -Metric 'OS architecture' -Value $os.OSArchitecture
    Add-Metric -List $metrics -Category 'OS' -Metric 'Last boot time' -Value $os.LastBootUpTime
    Add-Metric -List $metrics -Category 'OS' -Metric 'System drive' -Value $os.SystemDrive
    Add-Metric -List $metrics -Category 'OS' -Metric 'Windows directory' -Value $os.WindowsDirectory
    Add-Metric -List $metrics -Category 'OS' -Metric 'Free physical memory' -Value (Convert-BytesToNiceString ([decimal]$os.FreePhysicalMemory * 1KB))
    Add-Metric -List $metrics -Category 'OS' -Metric 'Free virtual memory' -Value (Convert-BytesToNiceString ([decimal]$os.FreeVirtualMemory * 1KB))
}

if ($bios) {
    Add-Metric -List $metrics -Category 'OS' -Metric 'BIOS version' -Value ((@($bios.SMBIOSBIOSVersion, $bios.Version) | Where-Object { $_ }) -join ' | ')
    Add-Metric -List $metrics -Category 'OS' -Metric 'BIOS serial' -Value $bios.SerialNumber
}

if ($procs.Count -gt 0) {
    Add-Metric -List $metrics -Category 'OS' -Metric 'CPU count (rows)' -Value $procs.Count
    Add-Metric -List $metrics -Category 'OS' -Metric 'CPU name' -Value ($procs | Select-Object -First 1 -ExpandProperty Name)
    Add-Metric -List $metrics -Category 'OS' -Metric 'CPU max clock MHz' -Value (($procs | Measure-Object -Property MaxClockSpeed -Maximum).Maximum)
    Add-Metric -List $metrics -Category 'OS' -Metric 'CPU cores total' -Value (($procs | Measure-Object -Property NumberOfCores -Sum).Sum)
    Add-Metric -List $metrics -Category 'OS' -Metric 'CPU logical total' -Value (($procs | Measure-Object -Property NumberOfLogicalProcessors -Sum).Sum)
}

foreach ($d in $disks) {
    $freePct = if ($d.Size -gt 0) { '{0:N1}' -f (($d.FreeSpace / $d.Size) * 100) } else { '' }
    [void]$driveSummary.Add([pscustomobject]@{
        Drive      = $d.DeviceID
        Label      = $d.VolumeName
        FileSystem = $d.FileSystem
        Size       = Convert-BytesToNiceString $d.Size
        Free       = Convert-BytesToNiceString $d.FreeSpace
        FreePct    = $freePct
    })

    Add-Metric -List $metrics -Category 'Storage' -Metric "$($d.DeviceID) filesystem" -Value $d.FileSystem
    Add-Metric -List $metrics -Category 'Storage' -Metric "$($d.DeviceID) label" -Value $d.VolumeName
    Add-Metric -List $metrics -Category 'Storage' -Metric "$($d.DeviceID) size" -Value (Convert-BytesToNiceString $d.Size)
    Add-Metric -List $metrics -Category 'Storage' -Metric "$($d.DeviceID) free" -Value (Convert-BytesToNiceString $d.FreeSpace)
    Add-Metric -List $metrics -Category 'Storage' -Metric "$($d.DeviceID) free percent" -Value "$freePct%"
}

}

Invoke-AuditSection -SectionName 'Server Identity and Build' -SectionErrors $sectionErrors -ScriptBlock {
$build = Get-DbaBuildReference -SqlInstance $instance -ErrorAction SilentlyContinue

Add-Metric -List $metrics -Category 'Instance' -Metric 'Server name' -Value $instance.ServerName
Add-Metric -List $metrics -Category 'Instance' -Metric 'Computer name physical netbios' -Value (Get-PropertyValue $instance Information.ComputerNamePhysicalNetBIOS)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Domain instance name' -Value (Get-PropertyValue $instance DomainInstanceName)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Edition' -Value (Get-PropertyValue $instance Edition)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Version string' -Value (Get-PropertyValue $instance VersionString)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Product level' -Value (Get-PropertyValue $instance ProductLevel)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Engine edition' -Value (Get-PropertyValue $instance EngineEdition)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Collation' -Value (Get-PropertyValue $instance Collation)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Default file path' -Value (Get-PropertyValue $instance DefaultFile)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Default log path' -Value (Get-PropertyValue $instance DefaultLog)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Backup directory' -Value (Get-PropertyValue $instance BackupDirectory)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Master DB path' -Value (Get-PropertyValue $instance.MasterDBPath)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Master DB log path' -Value (Get-PropertyValue $instance.MasterDBLogPath)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Error log path' -Value (Get-PropertyValue $instance ErrorLogPath)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Is clustered' -Value (Get-PropertyValue $instance IsClustered)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Is HADR enabled' -Value (Get-PropertyValue $instance IsHadrEnabled)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Login mode' -Value (Get-PropertyValue $instance LoginMode)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Audit level' -Value (Get-PropertyValue $instance AuditLevel)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Case sensitive' -Value (Get-PropertyValue $instance IsCaseSensitive)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Filestream level' -Value (Get-PropertyValue $instance FilestreamLevel)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Resource version' -Value (Get-PropertyValue $instance ResourceVersion)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Host platform' -Value (Get-PropertyValue $instance HostPlatform)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Host distribution' -Value (Get-PropertyValue $instance HostDistribution)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Process ID' -Value (Get-PropertyValue $instance ProcessID)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Status' -Value (Get-PropertyValue $instance Status)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Service account' -Value (Get-PropertyValue $instance ServiceAccount)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Start mode' -Value (Get-PropertyValue $instance StartMode)
Add-Metric -List $metrics -Category 'Instance' -Metric 'Language' -Value (Get-PropertyValue $instance Language)

if ($build) {
    $firstBuild = $build | Select-Object -First 1
    Add-Metric -List $metrics -Category 'Instance' -Metric 'Build reference matched name' -Value (Get-PropertyValue $firstBuild NameLevel)
    Add-Metric -List $metrics -Category 'Instance' -Metric 'Build reference supported until' -Value (Get-PropertyValue $firstBuild SupportedUntil)
    Add-Metric -List $metrics -Category 'Instance' -Metric 'Build reference CU' -Value (Get-PropertyValue $firstBuild CULevel)
    Add-Metric -List $metrics -Category 'Instance' -Metric 'Build reference SP' -Value (Get-PropertyValue $firstBuild SPLevel)
}

}

Invoke-AuditSection -SectionName 'Connectivity and Services' -SectionErrors $sectionErrors -ScriptBlock {
$connection = Test-DbaConnection -SqlInstance $instance -EnableException
$services = @(Get-DbaService -ComputerName $env:COMPUTERNAME -EnableException | Where-Object {
$_.DisplayName -match 'SQL Server|SQL Server Agent|SQL Full-text|SQL Browser|Launchpad'
})

Add-Metric -List $metrics -Category 'Connectivity' -Metric 'Connect success' -Value (Get-PropertyValue $connection ConnectSuccess) -Status (if ((Get-PropertyValue $connection ConnectSuccess) -eq $true) { 'Pass' } else { 'Fail' })
Add-Metric -List $metrics -Category 'Connectivity' -Metric 'Auth type' -Value (Get-PropertyValue $connection AuthType)
Add-Metric -List $metrics -Category 'Connectivity' -Metric 'TCP port' -Value (Get-PropertyValue $connection Port)
Add-Metric -List $metrics -Category 'Connectivity' -Metric 'IP address' -Value (Get-PropertyValue $connection IPAddress)
Add-Metric -List $metrics -Category 'Connectivity' -Metric 'Local version' -Value (Get-PropertyValue $connection LocalVersion)
Add-Metric -List $metrics -Category 'Connectivity' -Metric 'Remote version' -Value (Get-PropertyValue $connection.SqlVersion)
Add-Metric -List $metrics -Category 'Connectivity' -Metric 'Is pingable' -Value (Get-PropertyValue $connection IsPingable)
Add-Metric -List $metrics -Category 'Connectivity' -Metric 'TCP enabled' -Value (Get-PropertyValue $connection TcpEnabled)

foreach ($svc in $services) {
    [void]$svcSummary.Add([pscustomobject]@{
        Name         = $svc.DisplayName
        ServiceName  = $svc.ServiceName
        State        = $svc.State
        StartMode    = $svc.StartMode
        StartName    = $svc.StartName
        PathName     = $svc.PathName
    })

    $metricName = switch -Regex ($svc.DisplayName) {
        '^SQL Server Agent' { 'SQL Server Agent service status'; break }
        '^SQL Server \('    { 'SQL Server service status'; break }
        default             { "$($svc.DisplayName) status" }
    }

    Add-Metric -List $metrics -Category 'Services' -Metric $metricName -Value $svc.State -Status (Get-StatusFromRule -Metric $metricName -Value $svc.State) -Notes "Startup: $($svc.StartMode); Account: $($svc.StartName)"
    Add-Metric -List $metrics -Category 'Services' -Metric "$($svc.DisplayName) start mode" -Value $svc.StartMode
    Add-Metric -List $metrics -Category 'Services' -Metric "$($svc.DisplayName) service account" -Value $svc.StartName
    Add-Metric -List $metrics -Category 'Services' -Metric "$($svc.DisplayName) binary path" -Value $svc.PathName
}

}

Invoke-AuditSection -SectionName 'sp_configure' -SectionErrors $sectionErrors -ScriptBlock {
$configs = @(Get-DbaSpConfigure -SqlInstance $instance -EnableException)
$cfgIndex = @{}
foreach ($cfg in $configs) {
$cfgIndex[$cfg.ConfigName] = $cfg
$status = Get-StatusFromRule -Metric $cfg.ConfigName -Value $cfg.RunningValue
Add-Metric -List $metrics -Category 'Configuration' -Metric $cfg.ConfigName -Value $cfg.RunningValue -Status $status -Notes "Configured: $($cfg.ConfiguredValue); Dynamic: $($cfg.IsDynamic)"
}

if ($cfgIndex.ContainsKey('max server memory (MB)')) {
    Add-Metric -List $metrics -Category 'BestPractice' -Metric 'Max server memory review' -Value $cfgIndex['max server memory (MB)'].RunningValue -Status (Get-StatusFromRule -Metric 'Max server memory review' -Value $cfgIndex['max server memory (MB)'].RunningValue)
}
if ($cfgIndex.ContainsKey('min server memory (MB)')) {
    Add-Metric -List $metrics -Category 'BestPractice' -Metric 'Min server memory review' -Value $cfgIndex['min server memory (MB)'].RunningValue
}
if ($cfgIndex.ContainsKey('max degree of parallelism')) {
    Add-Metric -List $metrics -Category 'BestPractice' -Metric 'MAXDOP review' -Value $cfgIndex['max degree of parallelism'].RunningValue
}
if ($cfgIndex.ContainsKey('cost threshold for parallelism')) {
    Add-Metric -List $metrics -Category 'BestPractice' -Metric 'CTFP review' -Value $cfgIndex['cost threshold for parallelism'].RunningValue -Status (Get-StatusFromRule -Metric 'cost threshold for parallelism' -Value $cfgIndex['cost threshold for parallelism'].RunningValue)
}

}

Invoke-AuditSection -SectionName 'SQL dynamic management views' -SectionErrors $sectionErrors -ScriptBlock {
$sysInfo = Invoke-SqlSafeQuery -SqlInstance $instance -Query @"
SELECT
cpu_count,
scheduler_count,
hyperthread_ratio,
numa_node_count,
softnuma_configuration_desc,
max_workers_count,
physical_memory_kb,
committed_kb,
committed_target_kb,
visible_target_kb,
sql_memory_model_desc,
stack_size_in_bytes,
virtual_machine_type_desc,
sqlserver_start_time
FROM sys.dm_os_sys_info;
"@ -QueryName 'sys.dm_os_sys_info' | Select-Object -First 1

$sysMem = Invoke-SqlSafeQuery -SqlInstance $instance -Query @"

SELECT
total_physical_memory_kb,
available_physical_memory_kb,
total_page_file_kb,
available_page_file_kb,
system_memory_state_desc
FROM sys.dm_os_sys_memory;
"@ -QueryName 'sys.dm_os_sys_memory' | Select-Object -First 1

$procMem = Invoke-SqlSafeQuery -SqlInstance $instance -Query @"

SELECT
physical_memory_in_use_kb,
large_page_allocations_kb,
locked_page_allocations_kb,
virtual_address_space_reserved_kb,
virtual_address_space_committed_kb,
page_fault_count,
memory_utilization_percentage,
available_commit_limit_kb,
process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;
"@ -QueryName 'sys.dm_os_process_memory' | Select-Object -First 1

Add-Metric -List $metrics -Category 'Performance' -Metric 'cpu_count' -Value $sysInfo.cpu_count
Add-Metric -List $metrics -Category 'Performance' -Metric 'scheduler_count' -Value $sysInfo.scheduler_count
Add-Metric -List $metrics -Category 'Performance' -Metric 'hyperthread_ratio' -Value $sysInfo.hyperthread_ratio
Add-Metric -List $metrics -Category 'Performance' -Metric 'numa_node_count' -Value $sysInfo.numa_node_count
Add-Metric -List $metrics -Category 'Performance' -Metric 'softnuma_configuration_desc' -Value $sysInfo.softnuma_configuration_desc
Add-Metric -List $metrics -Category 'Performance' -Metric 'max_workers_count' -Value $sysInfo.max_workers_count
Add-Metric -List $metrics -Category 'Performance' -Metric 'physical_memory_kb' -Value (Convert-BytesToNiceString ([decimal]$sysInfo.physical_memory_kb * 1KB))
Add-Metric -List $metrics -Category 'Performance' -Metric 'committed_kb' -Value (Convert-BytesToNiceString ([decimal]$sysInfo.committed_kb * 1KB))
Add-Metric -List $metrics -Category 'Performance' -Metric 'committed_target_kb' -Value (Convert-BytesToNiceString ([decimal]$sysInfo.committed_target_kb * 1KB))
Add-Metric -List $metrics -Category 'Performance' -Metric 'visible_target_kb' -Value (Convert-BytesToNiceString ([decimal]$sysInfo.visible_target_kb * 1KB))
Add-Metric -List $metrics -Category 'Performance' -Metric 'sql_memory_model_desc' -Value $sysInfo.sql_memory_model_desc
Add-Metric -List $metrics -Category 'Performance' -Metric 'stack_size_in_bytes' -Value (Convert-BytesToNiceString ([decimal]$sysInfo.stack_size_in_bytes))
Add-Metric -List $metrics -Category 'Performance' -Metric 'virtual_machine_type_desc' -Value $sysInfo.virtual_machine_type_desc
Add-Metric -List $metrics -Category 'Performance' -Metric 'sqlserver_start_time' -Value $sysInfo.sqlserver_start_time

Add-Metric -List $metrics -Category 'Memory' -Metric 'OS total physical memory' -Value (Convert-BytesToNiceString ([decimal]$sysMem.total_physical_memory_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'OS available physical memory' -Value (Convert-BytesToNiceString ([decimal]$sysMem.available_physical_memory_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'OS total page file' -Value (Convert-BytesToNiceString ([decimal]$sysMem.total_page_file_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'OS available page file' -Value (Convert-BytesToNiceString ([decimal]$sysMem.available_page_file_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'system_memory_state_desc' -Value $sysMem.system_memory_state_desc

Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL physical_memory_in_use' -Value (Convert-BytesToNiceString ([decimal]$procMem.physical_memory_in_use_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL large_page_allocations' -Value (Convert-BytesToNiceString ([decimal]$procMem.large_page_allocations_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL locked_page_allocations' -Value (Convert-BytesToNiceString ([decimal]$procMem.locked_page_allocations_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL VA reserved' -Value (Convert-BytesToNiceString ([decimal]$procMem.virtual_address_space_reserved_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL VA committed' -Value (Convert-BytesToNiceString ([decimal]$procMem.virtual_address_space_committed_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL page_fault_count' -Value $procMem.page_fault_count
Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL memory_utilization_percentage' -Value $procMem.memory_utilization_percentage
Add-Metric -List $metrics -Category 'Memory' -Metric 'SQL available_commit_limit' -Value (Convert-BytesToNiceString ([decimal]$procMem.available_commit_limit_kb * 1KB))
Add-Metric -List $metrics -Category 'Memory' -Metric 'process_physical_memory_low' -Value $procMem.process_physical_memory_low
Add-Metric -List $metrics -Category 'Memory' -Metric 'process_virtual_memory_low' -Value $procMem.process_virtual_memory_low

}

Invoke-AuditSection -SectionName 'Databases' -SectionErrors $sectionErrors -ScriptBlock {
$databases = @(Get-DbaDatabase -SqlInstance $instance -IncludeLastUsed -EnableException)
$lastBackups = @{}
foreach ($lb in @(Get-DbaLastBackup -SqlInstance $instance -EnableException)) {
$lastBackups[$lb.Database] = $lb
}

foreach ($db in $databases) {
    $dbName = $db.Name
    $lastBackup = if ($lastBackups.ContainsKey($dbName)) { $lastBackups[$dbName] } else { $null }

    [void]$dbSummary.Add([pscustomobject]@{
        Database             = $dbName
        Status               = $db.Status
        RecoveryModel        = $db.RecoveryModel
        CompatibilityLevel   = $db.CompatibilityLevel
        DataSizeMB           = [math]::Round(([decimal]($db.Size)), 2)
        Owner                = $db.Owner
        AutoClose            = $db.AutoClose
        AutoShrink           = $db.AutoShrink
        ReadOnly             = $db.ReadOnly
        Encrypted            = $db.EncryptionEnabled
        Trustworthy          = $db.Trustworthy
        QueryStore           = (Get-PropertyValue $db QueryStoreOptions).ActualState
        PageVerify           = $db.PageVerify
        LastFullBackup       = Get-PropertyValue $lastBackup LastFullBackup
        LastLogBackup        = Get-PropertyValue $lastBackup LastLogBackup
    })

    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] status" -Value $db.Status -Status (if ($db.Status -eq 'Normal') { 'Pass' } else { 'Warn' })
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] owner" -Value $db.Owner
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] recovery model" -Value $db.RecoveryModel
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] compatibility level" -Value $db.CompatibilityLevel
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] collation" -Value $db.Collation
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] auto_close" -Value $db.AutoClose -Status (Get-StatusFromRule -Metric "DB [$dbName] auto_close" -Value $db.AutoClose)
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] auto_shrink" -Value $db.AutoShrink -Status (Get-StatusFromRule -Metric "DB [$dbName] auto_shrink" -Value $db.AutoShrink)
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] auto_create_stats" -Value $db.AutoCreateStatisticsEnabled
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] auto_update_stats" -Value $db.AutoUpdateStatisticsEnabled
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] auto_update_stats_async" -Value $db.AutoUpdateStatisticsAsync
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] read_only" -Value $db.ReadOnly
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] encrypted" -Value $db.EncryptionEnabled
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] trustworthy" -Value $db.Trustworthy -Status (Get-StatusFromRule -Metric "DB [$dbName] trustworthy" -Value $db.Trustworthy)
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] query_store" -Value ((Get-PropertyValue $db QueryStoreOptions).ActualState)
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] snapshot isolation" -Value $db.SnapshotIsolationState
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] RCSI" -Value $db.ReadCommittedSnapshotIsolation
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] page verify" -Value $db.PageVerify -Status (Get-StatusFromRule -Metric "DB [$dbName] page verify" -Value $db.PageVerify)
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] size MB" -Value ([math]::Round(([decimal]$db.Size),2))
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] space available KB" -Value $db.SpaceAvailable
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] create date" -Value $db.CreateDate
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] last full backup" -Value (Get-PropertyValue $lastBackup LastFullBackup) -Status (if (Get-PropertyValue $lastBackup LastFullBackup) { 'Pass' } else { 'Warn' })
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] last diff backup" -Value (Get-PropertyValue $lastBackup LastDiffBackup)
    Add-Metric -List $metrics -Category 'Databases' -Metric "DB [$dbName] last log backup" -Value (Get-PropertyValue $lastBackup LastLogBackup)

    [void]$backupSummary.Add([pscustomobject]@{
        Database      = $dbName
        RecoveryModel = $db.RecoveryModel
        LastFull      = Get-PropertyValue $lastBackup LastFullBackup
        LastDiff      = Get-PropertyValue $lastBackup LastDiffBackup
        LastLog       = Get-PropertyValue $lastBackup LastLogBackup
    })
}

Add-Metric -List $metrics -Category 'Databases' -Metric 'Database count total' -Value $databases.Count
Add-Metric -List $metrics -Category 'Databases' -Metric 'Database count online/normal' -Value (@($databases | Where-Object Status -eq 'Normal').Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Database count not normal' -Value (@($databases | Where-Object Status -ne 'Normal').Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Database count FULL recovery' -Value (@($databases | Where-Object RecoveryModel -eq 'Full').Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Database count SIMPLE recovery' -Value (@($databases | Where-Object RecoveryModel -eq 'Simple').Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Database count BULK_LOGGED recovery' -Value (@($databases | Where-Object RecoveryModel -eq 'BulkLogged').Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Databases with AUTO_CLOSE ON' -Value (@($databases | Where-Object AutoClose).Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Databases with AUTO_SHRINK ON' -Value (@($databases | Where-Object AutoShrink).Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Databases with TRUSTWORTHY ON' -Value (@($databases | Where-Object Trustworthy).Count)
Add-Metric -List $metrics -Category 'Databases' -Metric 'Databases encrypted' -Value (@($databases | Where-Object EncryptionEnabled).Count)

}

Invoke-AuditSection -SectionName 'Database Files and TempDB' -SectionErrors $sectionErrors -ScriptBlock {
$dbFiles = @(Get-DbaDbFile -SqlInstance $instance -EnableException)

foreach ($file in $dbFiles) {
    [void]$fileSummary.Add([pscustomobject]@{
        Database      = $file.Database
        LogicalName   = $file.LogicalName
        Type          = $file.TypeDescription
        PhysicalName  = $file.PhysicalName
        SizeMB        = [math]::Round(([decimal]$file.Size / 1MB), 2)
        MaxSizeMB     = if ($file.MaxSize -lt 0) { 'Unlimited' } else { [math]::Round(([decimal]$file.MaxSize / 1MB),2) }
        Growth        = $file.Growth
        PercentGrowth = $file.IsPercentGrowth
    })

    Add-Metric -List $metrics -Category 'Files' -Metric "File [$($file.Database):$($file.LogicalName)] type" -Value $file.TypeDescription
    Add-Metric -List $metrics -Category 'Files' -Metric "File [$($file.Database):$($file.LogicalName)] physical path" -Value $file.PhysicalName
    Add-Metric -List $metrics -Category 'Files' -Metric "File [$($file.Database):$($file.LogicalName)] size MB" -Value ([math]::Round(([decimal]$file.Size / 1MB),2))
    Add-Metric -List $metrics -Category 'Files' -Metric "File [$($file.Database):$($file.LogicalName)] max size MB" -Value (if ($file.MaxSize -lt 0) { 'Unlimited' } else { [math]::Round(([decimal]$file.MaxSize / 1MB),2) })
    Add-Metric -List $metrics -Category 'Files' -Metric "File [$($file.Database):$($file.LogicalName)] growth" -Value $file.Growth
    Add-Metric -List $metrics -Category 'Files' -Metric "File [$($file.Database):$($file.LogicalName)] percent growth" -Value $file.IsPercentGrowth -Status (Get-StatusFromRule -Metric "File [$($file.Database):$($file.LogicalName)] percent growth" -Value $file.IsPercentGrowth)
}

$tempdbFiles = @($dbFiles | Where-Object Database -eq 'tempdb')
foreach ($tf in $tempdbFiles) {
    [void]$tempdbSummary.Add([pscustomobject]@{
        Name          = $tf.LogicalName
        Type          = $tf.TypeDescription
        PhysicalName  = $tf.PhysicalName
        SizeMB        = [math]::Round(([decimal]$tf.Size / 1MB), 2)
        Growth        = $tf.Growth
        PercentGrowth = $tf.IsPercentGrowth
    })
}

$tempdbData = @($tempdbFiles | Where-Object TypeDescription -match 'ROWS|DATA|Data')
$tempdbDataCount = $tempdbData.Count
$tempdbLogCount = @($tempdbFiles | Where-Object TypeDescription -match 'LOG|Log').Count
$tempdbSizes = @($tempdbData | ForEach-Object { [math]::Round(([decimal]$_.Size / 1MB),2) })
$equalSize = if ($tempdbSizes.Count -le 1) { $true } else { (($tempdbSizes | Select-Object -Unique).Count -eq 1) }

Add-Metric -List $metrics -Category 'TempDB' -Metric 'TempDB data file count' -Value $tempdbDataCount
Add-Metric -List $metrics -Category 'TempDB' -Metric 'TempDB log file count' -Value $tempdbLogCount
Add-Metric -List $metrics -Category 'TempDB' -Metric 'TempDB data files equal size' -Value (if ($equalSize) { 'Yes' } else { 'No' }) -Status (Get-StatusFromRule -Metric 'TempDB data files equal size' -Value (if ($equalSize) { 'Yes' } else { 'No' }))

}

Invoke-AuditSection -SectionName 'Security and Logins' -SectionErrors $sectionErrors -ScriptBlock {
$logins = @(Get-DbaLogin -SqlInstance $instance -EnableException)
$dbOwners = @(Get-DbaDatabase -SqlInstance $instance -EnableException | Select-Object Name, Owner)

foreach ($login in $logins) {
    [void]$loginSummary.Add([pscustomobject]@{
        Name            = $login.Name
        LoginType       = $login.LoginType
        IsDisabled      = $login.IsDisabled
        DefaultDatabase = $login.DefaultDatabase
        CreateDate      = $login.CreateDate
        HasAccess       = $login.HasAccess
    })

    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] type" -Value $login.LoginType
    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] disabled" -Value $login.IsDisabled
    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] default DB" -Value $login.DefaultDatabase
    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] create date" -Value $login.CreateDate
    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] has access" -Value $login.HasAccess
    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] password policy enforced" -Value (Get-PropertyValue $login PasswordPolicyEnforced)
    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] password expiration enabled" -Value (Get-PropertyValue $login PasswordExpirationEnabled)
    Add-Metric -List $metrics -Category 'Security' -Metric "Login [$($login.Name)] must change password" -Value (Get-PropertyValue $login MustChangePassword)
}

Add-Metric -List $metrics -Category 'Security' -Metric 'Login count total' -Value $logins.Count
Add-Metric -List $metrics -Category 'Security' -Metric 'Disabled login count' -Value (@($logins | Where-Object IsDisabled).Count)
Add-Metric -List $metrics -Category 'Security' -Metric 'SQL login count' -Value (@($logins | Where-Object LoginType -match 'Sql').Count)
Add-Metric -List $metrics -Category 'Security' -Metric 'Windows login count' -Value (@($logins | Where-Object LoginType -match 'Windows').Count)
Add-Metric -List $metrics -Category 'Security' -Metric 'Databases owned by sa' -Value (@($dbOwners | Where-Object Owner -eq 'sa').Count)

}

Invoke-AuditSection -SectionName 'Agent and Maintenance' -SectionErrors $sectionErrors -ScriptBlock {
$jobs = @(Get-DbaAgentJob -SqlInstance $instance -EnableException)
$operators = @()
try { $operators = @(Get-DbaAgentOperator -SqlInstance $instance -EnableException) } catch {}
$alerts = @()
try { $alerts = @(Get-DbaAgentAlert -SqlInstance $instance -EnableException) } catch {}

foreach ($job in $jobs) {
    [void]$jobSummary.Add([pscustomobject]@{
        JobName      = $job.Name
        Enabled      = $job.IsEnabled
        Owner        = $job.OwnerLoginName
        Category     = $job.Category
        LastRunDate  = $job.LastRunDate
        LastRunOutcome = $job.LastRunOutcome
        HasSchedule  = (Get-PropertyValue $job HasSchedule)
    })

    Add-Metric -List $metrics -Category 'Agent' -Metric "Job [$($job.Name)] enabled" -Value $job.IsEnabled
    Add-Metric -List $metrics -Category 'Agent' -Metric "Job [$($job.Name)] owner" -Value $job.OwnerLoginName
    Add-Metric -List $metrics -Category 'Agent' -Metric "Job [$($job.Name)] category" -Value $job.Category
    Add-Metric -List $metrics -Category 'Agent' -Metric "Job [$($job.Name)] last run date" -Value $job.LastRunDate
    Add-Metric -List $metrics -Category 'Agent' -Metric "Job [$($job.Name)] last run outcome" -Value $job.LastRunOutcome -Status (if ($job.LastRunOutcome -eq 'Succeeded' -or [string]::IsNullOrEmpty((Convert-ToSafeString $job.LastRunOutcome))) { 'Info' } elseif ($job.LastRunOutcome -eq 'Failed') { 'Warn' } else { 'Info' })
}

Add-Metric -List $metrics -Category 'Agent' -Metric 'Job count total' -Value $jobs.Count
Add-Metric -List $metrics -Category 'Agent' -Metric 'Enabled job count' -Value (@($jobs | Where-Object IsEnabled).Count)
Add-Metric -List $metrics -Category 'Agent' -Metric 'Disabled job count' -Value (@($jobs | Where-Object { -not $_.IsEnabled }).Count)
Add-Metric -List $metrics -Category 'Agent' -Metric 'Operator count' -Value $operators.Count
Add-Metric -List $metrics -Category 'Agent' -Metric 'Alert count' -Value $alerts.Count

}

Invoke-AuditSection -SectionName 'Backups and Backup History' -SectionErrors $sectionErrors -ScriptBlock {
$backupHistory = @(Get-DbaDbBackupHistory -SqlInstance $instance -Last -EnableException)
foreach ($item in $backupHistory) {
Add-Metric -List $metrics -Category 'Backups' -Metric "Backup chain [$($item.Database)] type" -Value $item.Type
Add-Metric -List $metrics -Category 'Backups' -Metric "Backup chain [$($item.Database)] finish date" -Value $item.End
Add-Metric -List $metrics -Category 'Backups' -Metric "Backup chain [$($item.Database)] path" -Value $item.Path
Add-Metric -List $metrics -Category 'Backups' -Metric "Backup chain [$($item.Database)] compressed" -Value $item.Compressed
}
Add-Metric -List $metrics -Category 'Backups' -Metric 'Backup history row count' -Value $backupHistory.Count
}

Invoke-AuditSection -SectionName 'Trace Flags and Error Logs' -SectionErrors $sectionErrors -ScriptBlock {
$traceFlags = @()
try { $traceFlags = @(Get-DbaTraceFlag -SqlInstance $instance -EnableException) } catch {}
foreach ($tf in $traceFlags) {
Add-Metric -List $metrics -Category 'Health' -Metric "Trace flag [$($tf.TraceFlag)] status" -Value $tf.Status
Add-Metric -List $metrics -Category 'Health' -Metric "Trace flag [$($tf.TraceFlag)] global" -Value $tf.Global
Add-Metric -List $metrics -Category 'Health' -Metric "Trace flag [$($tf.TraceFlag)] session" -Value $tf.Session
}

$elog = @()
try {
    $elog = @(Get-DbaErrorLog -SqlInstance $instance -LogNumber 0 -After (Get-Date).AddDays(-7) -EnableException | Select-Object -First 50)
} catch {}

Add-Metric -List $metrics -Category 'Health' -Metric 'Active trace flag count' -Value $traceFlags.Count
Add-Metric -List $metrics -Category 'Health' -Metric 'Current error log entries in last 7 days captured' -Value $elog.Count

}

Invoke-AuditSection -SectionName 'Wait Statistics' -SectionErrors $sectionErrors -ScriptBlock {
$waits = Invoke-SqlSafeQuery -SqlInstance $instance -Query @"
SELECT TOP (15)
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
AND wait_type NOT IN (
'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','BROKER_TO_FLUSH','BROKER_TASK_STOP',
'BROKER_EVENTHANDLER','FT_IFTS_SCHEDULER_IDLE_WAIT','BROKER_RECEIVE_WAITFOR',
'ONDEMAND_TASK_QUEUE','DBMIRROR_EVENTS_QUEUE','DBMIRRORING_CMD','SQLTRACE_BUFFER_FLUSH',
'REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE','CHECKPOINT_QUEUE','BROKER_TRANSMITTER',
'HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL','SP_SERVER_DIAGNOSTICS_SLEEP'
)
ORDER BY wait_time_ms DESC;
"@ -QueryName 'dm_os_wait_stats'

foreach ($w in $waits) {
    [void]$waitSummary.Add([pscustomobject]@{
        WaitType         = $w.wait_type
        WaitingTasks     = $w.waiting_tasks_count
        WaitTimeMs       = $w.wait_time_ms
        MaxWaitTimeMs    = $w.max_wait_time_ms
        SignalWaitTimeMs = $w.signal_wait_time_ms
    })

    Add-Metric -List $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] waiting tasks" -Value $w.waiting_tasks_count
    Add-Metric -List $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] wait time ms" -Value $w.wait_time_ms
    Add-Metric -List $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] signal wait ms" -Value $w.signal_wait_time_ms
}

}

Invoke-AuditSection -SectionName 'Availability Groups and HA' -SectionErrors $sectionErrors -ScriptBlock {
$ags = @()
$agDbs = @()
try { $ags = @(Get-DbaAvailabilityGroup -SqlInstance $instance -EnableException) } catch {}
try { $agDbs = @(Get-DbaAgDatabase -SqlInstance $instance -EnableException) } catch {}

foreach ($ag in $ags) {
    [void]$agSummary.Add([pscustomobject]@{
        Name                    = $ag.Name
        PrimaryReplicaServer    = $ag.PrimaryReplicaServerName
        ClusterType             = $ag.ClusterType
        AutomatedBackupPref     = $ag.AutomatedBackupPreference
        HealthCheckTimeout      = $ag.HealthCheckTimeout
    })

    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG [$($ag.Name)] primary replica" -Value $ag.PrimaryReplicaServerName
    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG [$($ag.Name)] cluster type" -Value $ag.ClusterType
    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG [$($ag.Name)] automated backup preference" -Value $ag.AutomatedBackupPreference
    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG [$($ag.Name)] health check timeout" -Value $ag.HealthCheckTimeout
}

foreach ($agd in $agDbs) {
    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AvailabilityGroup):$($agd.Name)] synchronization state" -Value $agd.SynchronizationState
    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AvailabilityGroup):$($agd.Name)] health state" -Value $agd.SynchronizationHealth
    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AvailabilityGroup):$($agd.Name)] is joined" -Value $agd.IsJoined
    Add-Metric -List $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AvailabilityGroup):$($agd.Name)] is suspended" -Value $agd.IsSuspended
}

Add-Metric -List $metrics -Category 'HA/DR' -Metric 'Availability Group count' -Value $ags.Count
Add-Metric -List $metrics -Category 'HA/DR' -Metric 'Availability Group database count' -Value $agDbs.Count

}

Invoke-AuditSection -SectionName 'Feature inventory' -SectionErrors $sectionErrors -ScriptBlock {
$features = Invoke-SqlSafeQuery -SqlInstance $instance -Query @"
SELECT
SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported,
SERVERPROPERTY('IsPolyBaseInstalled') AS IsPolyBaseInstalled,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled,
SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel,
SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel;
"@ -QueryName 'serverproperty feature inventory' | Select-Object -First 1

Add-Metric -List $metrics -Category 'Features' -Metric 'IsXTPSupported' -Value $features.IsXTPSupported
Add-Metric -List $metrics -Category 'Features' -Metric 'IsPolyBaseInstalled' -Value $features.IsPolyBaseInstalled
Add-Metric -List $metrics -Category 'Features' -Metric 'IsFullTextInstalled' -Value $features.IsFullTextInstalled
Add-Metric -List $metrics -Category 'Features' -Metric 'IsAdvancedAnalyticsInstalled' -Value $features.IsAdvancedAnalyticsInstalled
Add-Metric -List $metrics -Category 'Features' -Metric 'FilestreamConfiguredLevel' -Value $features.FilestreamConfiguredLevel
Add-Metric -List $metrics -Category 'Features' -Metric 'FilestreamEffectiveLevel' -Value $features.FilestreamEffectiveLevel

}

Invoke-AuditSection -SectionName 'Summary' -SectionErrors $sectionErrors -ScriptBlock {
$warnCount = @($metrics | Where-Object Status -eq 'Warn').Count
$failCount = @($metrics | Where-Object Status -eq 'Fail').Count
$passCount = @($metrics | Where-Object Status -eq 'Pass').Count
$infoCount = @($metrics | Where-Object Status -eq 'Info').Count

Add-Metric -List $metrics -Category 'Summary' -Metric 'Total metrics collected' -Value $metrics.Count
Add-Metric -List $metrics -Category 'Summary' -Metric 'Pass metric count' -Value $passCount
Add-Metric -List $metrics -Category 'Summary' -Metric 'Warn metric count' -Value $warnCount
Add-Metric -List $metrics -Category 'Summary' -Metric 'Fail metric count' -Value $failCount
Add-Metric -List $metrics -Category 'Summary' -Metric 'Info metric count' -Value $infoCount
Add-Metric -List $metrics -Category 'Summary' -Metric 'Section error count' -Value $sectionErrors.Count -Status (if ($sectionErrors.Count -eq 0) { 'Pass' } else { 'Warn' })

}

Write-Log "Generating HTML"
$warnCount = @($metrics | Where-Object Status -eq 'Warn').Count
$failCount = @($metrics | Where-Object Status -eq 'Fail').Count
$passCount = @($metrics | Where-Object Status -eq 'Pass').Count
$infoCount = @($metrics | Where-Object Status -eq 'Info').Count

$summaryCards = @(
[pscustomobject]@{ Label = 'Total Metrics'; Value = $metrics.Count; Css = 'card-neutral' },
[pscustomobject]@{ Label = 'Pass'; Value = $passCount; Css = 'card-pass' },
[pscustomobject]@{ Label = 'Warnings'; Value = $warnCount; Css = 'card-warn' },
[pscustomobject]@{ Label = 'Failures'; Value = $failCount; Css = 'card-fail' },
[pscustomobject]@{ Label = 'Databases'; Value = $dbSummary.Count; Css = 'card-neutral' },
[pscustomobject]@{ Label = 'Jobs'; Value = $jobSummary.Count; Css = 'card-neutral' }
)

$topWarnings = @($metrics | Where-Object { $_.Status -in @('Warn', 'Fail') } | Select-Object -First 50)
$metricsByCategory = @($metrics | Group-Object Category | Sort-Object Name)

$css = @"

body {
font-family: Segoe UI, Arial, sans-serif;
margin: 0;
background: #f4f7fb;
color: #1f2937;
}
.header {
background: linear-gradient(135deg, #0f172a, #1d4ed8);
color: white;
padding: 28px 32px;
}
.header h1 {
margin: 0 0 8px 0;
font-size: 30px;
}
.header .sub {
opacity: .95;
font-size: 14px;
}
.container { padding: 24px 28px 40px 28px; }
.card-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(160px, 1fr));
gap: 14px;
margin-bottom: 24px;
}
.card {
border-radius: 14px;
padding: 18px;
box-shadow: 0 6px 18px rgba(15, 23, 42, 0.08);
background: white;
border-left: 6px solid #64748b;
}
.card .label {
font-size: 12px;
text-transform: uppercase;
letter-spacing: .06em;
color: #475569;
}
.card .value {
font-size: 28px;
font-weight: 700;
margin-top: 8px;
}
.card-pass { border-left-color: #16a34a; }
.card-warn { border-left-color: #f59e0b; }
.card-fail { border-left-color: #dc2626; }
.card-neutral { border-left-color: #2563eb; }
.section {
background: white;
border-radius: 16px;
padding: 20px 20px 8px 20px;
margin-bottom: 22px;
box-shadow: 0 6px 18px rgba(15, 23, 42, 0.06);
}
.section h2 {
margin-top: 0;
font-size: 20px;
color: #0f172a;
}
.section h3 {
margin-top: 18px;
color: #1e293b;
}
table {
width: 100%;
border-collapse: collapse;
margin: 10px 0 18px 0;
font-size: 13px;
}
th, td {
border-bottom: 1px solid #e5e7eb;
padding: 8px 10px;
text-align: left;
vertical-align: top;
}
th {
background: #eff6ff;
color: #1e3a8a;
position: sticky;
top: 0;
}
tr:hover td { background: #f8fafc; }
.status-pass { color: #166534; font-weight: 600; }
.status-warn { color: #92400e; font-weight: 600; }
.status-fail { color: #991b1b; font-weight: 700; }
.status-info { color: #334155; }
.kv {
display: grid;
grid-template-columns: 240px 1fr;
gap: 8px 14px;
font-size: 14px;
margin-top: 10px;
}
.kv .k { font-weight: 600; color: #334155; }
.badge {
display: inline-block;
padding: 4px 8px;
border-radius: 999px;
background: #e2e8f0;
color: #334155;
font-size: 12px;
margin-right: 8px;
}
.empty {
padding: 14px;
border: 1px dashed #cbd5e1;
border-radius: 10px;
color: #64748b;
background: #f8fafc;
}
.small {
font-size: 12px;
color: #64748b;
}
.footer {
text-align: center;
font-size: 12px;
color: #64748b;
padding: 10px 0 20px 0;
}

$overviewInstance = [ordered]@{}
$instanceMetrics = @($metrics | Where-Object Category -eq 'Instance')
foreach ($m in $instanceMetrics) {
$overviewInstance[$m.Metric] = $m.Value
}

$sb = [System.Text.StringBuilder]::new()
[void]$sb.AppendLine("

SQL Server New Install Audit - $([System.Net.WebUtility]::HtmlEncode($SqlInstance))$css")
[void]$sb.AppendLine("

SQL Server New Install Audit

Instance: $([System.Net.WebUtility]::HtmlEncode($SqlInstance)) | Generated: $generatedOn")
[void]$sb.AppendLine("")
[void]$sb.AppendLine("")
foreach ($card in $summaryCards) {
[void]$sb.AppendLine("$($card.Label)$($card.Value)")
}
[void]$sb.AppendLine("")

[void]$sb.AppendLine("

Overview

")
foreach ($label in @('Server name','Edition','Version string','Product level','Is clustered','Is HADR enabled','Default file path','Default log path','Backup directory','Service account','Status')) {
if ($overviewInstance.Contains($label)) {
[void]$sb.AppendLine("$([System.Net.WebUtility]::HtmlEncode($label))$([System.Net.WebUtility]::HtmlEncode([string]$overviewInstance[$label]))")
}
}
[void]$sb.AppendLine("")

[void]$sb.AppendLine("

Section Errors

")
if ($sectionErrors.Count -gt 0) {
[void]$sb.AppendLine((Convert-TableToHtml -Data $sectionErrors -Columns @('Section','Error')))
} else {
[void]$sb.AppendLine("No section-level failures recorded.")
}
[void]$sb.AppendLine("")

[void]$sb.AppendLine("

Top Findings

")
if ($topWarnings.Count -gt 0) {
[void]$sb.AppendLine((Convert-TableToHtml -Data $topWarnings -Columns @('Category','Metric','Value','Status','Notes')))
} else {
[void]$sb.AppendLine("No warnings or failures were detected by the built-in checks.")
}
[void]$sb.AppendLine("")

[void]$sb.AppendLine("

Key Summaries

")
[void]$sb.AppendLine((Convert-TableToHtml -Data ($dbSummary | Select-Object -First $TopDbRows) -Title 'Databases' -Columns @('Database','Status','RecoveryModel','CompatibilityLevel','DataSizeMB','Owner','AutoClose','AutoShrink','Encrypted','Trustworthy','QueryStore','PageVerify','LastFullBackup','LastLogBackup')))
[void]$sb.AppendLine((Convert-TableToHtml -Data ($fileSummary | Select-Object -First 50) -Title 'Files' -Columns @('Database','Type','LogicalName','SizeMB','MaxSizeMB','PercentGrowth','PhysicalName')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $backupSummary -Title 'Backups' -Columns @('Database','RecoveryModel','LastFull','LastDiff','LastLog')))
[void]$sb.AppendLine((Convert-TableToHtml -Data ($jobSummary | Select-Object -First 50) -Title 'SQL Agent Jobs' -Columns @('JobName','Enabled','Owner','Category','HasSchedule','LastRunOutcome','LastRunDate')))
[void]$sb.AppendLine((Convert-TableToHtml -Data ($loginSummary | Select-Object -First 50) -Title 'Logins' -Columns @('Name','LoginType','IsDisabled','DefaultDatabase','CreateDate','HasAccess')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $driveSummary -Title 'Windows Volumes' -Columns @('Drive','Label','FileSystem','Size','Free','FreePct')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $tempdbSummary -Title 'TempDB Files' -Columns @('Name','Type','SizeMB','Growth','PercentGrowth','PhysicalName')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $waitSummary -Title 'Top Wait Stats' -Columns @('WaitType','WaitingTasks','WaitTimeMs','MaxWaitTimeMs','SignalWaitTimeMs')))
if ($agSummary.Count -gt 0) {
[void]$sb.AppendLine((Convert-TableToHtml -Data $agSummary -Title 'Availability Groups' -Columns @('Name','PrimaryReplicaServer','ClusterType','AutomatedBackupPref','HealthCheckTimeout')))
}
if ($svcSummary.Count -gt 0) {
[void]$sb.AppendLine((Convert-TableToHtml -Data $svcSummary -Title 'Services' -Columns @('Name','ServiceName','State','StartMode','StartName','PathName')))
}
[void]$sb.AppendLine("")

foreach ($group in $metricsByCategory) {
[void]$sb.AppendLine("

$([System.Net.WebUtility]::HtmlEncode($group.Name))

$($group.Count) metrics")
[void]$sb.AppendLine((Convert-TableToHtml -Data ($group.Group | Sort-Object Metric) -Columns @('Metric','Value','Status','Notes')))
[void]$sb.AppendLine("")
}

[void]$sb.AppendLine("

Generated by Invoke-SqlServerNewInstallAudit_dbatools_v3.ps1")
[void]$sb.AppendLine("")

$outDir = Split-Path -Path $OutputPath -Parent
if ($outDir -and -not (Test-Path -LiteralPath $outDir)) {
$null = New-Item -Path $outDir -ItemType Directory -Force
}
$sb.ToString() | Set-Content -Path $OutputPath -Encoding UTF8

Write-Log "Report written to: $OutputPath"

if ($EmailReport) {
if (-not $SmtpServer) { throw "EmailReport specified but SmtpServer was not provided." }
if (-not $EmailFrom) { throw "EmailReport specified but EmailFrom was not provided." }
if (-not $EmailTo) { throw "EmailReport specified but EmailTo was not provided." }
if (-not $EmailSubject) { $EmailSubject = "SQL Audit Report - $SqlInstance" }

try {
    Send-ReportEmail -AttachmentPath $OutputPath -SmtpServer $SmtpServer -SmtpPort $SmtpPort -UseSsl:$UseSsl -SmtpCredential $SmtpCredential -From $EmailFrom -To $EmailTo -Cc $EmailCc -Subject $EmailSubject
    Write-Log "Email sent to: $($EmailTo -join ', ')"
}
catch {
    Write-Warning "Report was created, but email failed: $($_.Exception.Message)"
}

}

SQL Server Audit

greenmtnsun — Wed, 25 Mar 2026 03:48:47 +0000

I have a new script I'd like to share

<# 
.SYNOPSIS
    Audits a newly installed SQL Server and generates a polished HTML report.

.DESCRIPTION
    Collects a broad set of SQL Server, OS, storage, security, database, backup,
    Agent, HA/DR, and health metrics. Intended for post-install/post-build validation.

.NOTES
    Author: OpenAI
    Requirements:
      - PowerShell 5.1+ or PowerShell 7+
      - Permissions to connect to SQL Server and read DMVs/catalog views
      - Optional: SqlServer PowerShell module for Invoke-Sqlcmd
    Example:
      .\Invoke-SqlServerNewInstallAudit.ps1 -SqlInstance "SERVER\INST1" -OutputPath "C:\Temp\SqlAudit.html"
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory)]
    [string]$SqlInstance,

    [string]$OutputPath = ".\SqlServer-NewInstall-Audit.html",

    [int]$TopDbRows = 25,

    [switch]$TrustServerCertificate
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

#region helpers
function Write-Log {
    param([string]$Message)
    Write-Host "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] $Message"
}

function Add-Metric {
    param(
        [Parameter(Mandatory)][System.Collections.Generic.List[object]]$MetricList,
        [Parameter(Mandatory)][string]$Category,
        [Parameter(Mandatory)][string]$Metric,
        [Parameter()][object]$Value,
        [Parameter()][string]$Status = 'Info',
        [Parameter()][string]$Notes = ''
    )
    $MetricList.Add([pscustomobject]@{
        Category = $Category
        Metric   = $Metric
        Value    = if ($null -eq $Value) { '' } else { [string]$Value }
        Status   = $Status
        Notes    = $Notes
    })
}

function Convert-BytesToNiceString {
    param([Nullable[decimal]]$Bytes)
    if ($null -eq $Bytes) { return '' }
    $sizes = "B","KB","MB","GB","TB","PB"
    $order = 0
    $value = [decimal]$Bytes
    while ($value -ge 1024 -and $order -lt ($sizes.Count - 1)) {
        $order++
        $value = $value / 1024
    }
    return "{0:N2} {1}" -f $value, $sizes[$order]
}

function New-SqlConnectionString {
    param(
        [string]$ServerInstance,
        [switch]$TrustServerCertificate
    )
    $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
    $builder["Data Source"] = $ServerInstance
    $builder["Initial Catalog"] = "master"
    $builder["Integrated Security"] = $true
    $builder["Application Name"] = "SQL New Install Audit"
    $builder["Connect Timeout"] = 15
    if ($TrustServerCertificate) {
        $builder["TrustServerCertificate"] = $true
    }
    return $builder.ConnectionString
}

function Invoke-SqlQuery {
    param(
        [string]$ServerInstance,
        [string]$Query,
        [switch]$TrustServerCertificate
    )

    if (Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue) {
        try {
            return Invoke-Sqlcmd -ServerInstance $ServerInstance -Database master -Query $Query -TrustServerCertificate:$TrustServerCertificate
        }
        catch {
            Write-Verbose "Invoke-Sqlcmd failed; falling back to SqlClient. $_"
        }
    }

    $connString = New-SqlConnectionString -ServerInstance $ServerInstance -TrustServerCertificate:$TrustServerCertificate
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString
    $cmd = $conn.CreateCommand()
    $cmd.CommandTimeout = 120
    $cmd.CommandText = $Query
    $da = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
    $dt = New-Object System.Data.DataTable
    $null = $da.Fill($dt)
    $conn.Close()
    return $dt
}

function Try-GetValue {
    param(
        [object]$Row,
        [string]$ColumnName
    )
    try {
        return $Row.$ColumnName
    }
    catch {
        return $null
    }
}

function Get-SafeWmi {
    param(
        [string]$ClassName,
        [string]$ComputerName = $env:COMPUTERNAME
    )
    try {
        if (Get-Command Get-CimInstance -ErrorAction SilentlyContinue) {
            return Get-CimInstance -ClassName $ClassName -ComputerName $ComputerName -ErrorAction Stop
        } else {
            return Get-WmiObject -Class $ClassName -ComputerName $ComputerName -ErrorAction Stop
        }
    }
    catch {
        return $null
    }
}

function Get-HtmlEncoded {
    param([string]$Text)
    if ($null -eq $Text) { return '' }
    return [System.Net.WebUtility]::HtmlEncode($Text)
}

function Convert-TableToHtml {
    param(
        [Parameter(Mandatory)][System.Collections.IEnumerable]$Data,
        [string]$Title = '',
        [string[]]$Columns
    )

    $sb = New-Object System.Text.StringBuilder
    if ($Title) {
        [void]$sb.AppendLine("<h2>$([System.Net.WebUtility]::HtmlEncode($Title))</h2>")
    }

    $rows = @($Data)
    if (-not $rows -or $rows.Count -eq 0) {
        [void]$sb.AppendLine("<div class='empty'>No data returned.</div>")
        return $sb.ToString()
    }

    if (-not $Columns) {
        $Columns = $rows[0].PSObject.Properties.Name
    }

    [void]$sb.AppendLine("<table>")
    [void]$sb.AppendLine("<thead><tr>")
    foreach ($c in $Columns) {
        [void]$sb.AppendLine("<th>$([System.Net.WebUtility]::HtmlEncode($c))</th>")
    }
    [void]$sb.AppendLine("</tr></thead>")
    [void]$sb.AppendLine("<tbody>")

    foreach ($row in $rows) {
        [void]$sb.AppendLine("<tr>")
        foreach ($c in $Columns) {
            $val = ''
            try { $val = $row.$c } catch {}
            $class = ''
            if ($c -eq 'Status') {
                switch -Regex ($val) {
                    '^Pass$'    { $class = 'status-pass' }
                    '^Warn$'    { $class = 'status-warn' }
                    '^Fail$'    { $class = 'status-fail' }
                    default     { $class = 'status-info' }
                }
            }
            [void]$sb.AppendLine("<td class='$class'>$([System.Net.WebUtility]::HtmlEncode([string]$val))</td>")
        }
        [void]$sb.AppendLine("</tr>")
    }

    [void]$sb.AppendLine("</tbody></table>")
    return $sb.ToString()
}

function Get-StatusFromRule {
    param(
        [string]$Metric,
        [object]$Value
    )

    # Light rules for common install validation checks
    switch -Regex ($Metric) {
        '^SQL Server service status$'               { if ($Value -eq 'Running') { 'Pass' } else { 'Fail' } ; break }
        '^SQL Server Agent service status$'         { if ($Value -eq 'Running') { 'Pass' } else { 'Warn' } ; break }
        '^xp_cmdshell$'                             { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^clr enabled$'                             { 'Info'; break }
        '^remote admin connections$'                { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
        '^cost threshold for parallelism$'          { if ([int]$Value -ge 25) { 'Pass' } else { 'Warn' } ; break }
        '^max degree of parallelism$'               { 'Info'; break }
        '^optimize for ad hoc workloads$'           { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
        '^backup compression default$'              { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
        '^default backup checksum$'                 { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
        '^Database Mail XPs$'                       { 'Info'; break }
        '^contained database authentication$'       { 'Info'; break }
        '^cross db ownership chaining$'             { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^default trace enabled$'                   { if ($Value -eq '1') { 'Pass' } else { 'Warn' } ; break }
        '^priority boost$'                          { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^scan for startup procs$'                  { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^show advanced options$'                   { 'Info'; break }
        '^Ole Automation Procedures$'               { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^SMO and DMO XPs$'                         { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^ad hoc distributed queries$'              { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^filestream access level$'                 { 'Info'; break }
        '^lightweight pooling$'                     { if ($Value -eq '0') { 'Pass' } else { 'Warn' } ; break }
        '^default data path$'                       { 'Info'; break }
        '^default log path$'                        { 'Info'; break }
        '^instant file initialization enabled$'     { if ($Value -eq 'Yes') { 'Pass' } else { 'Warn' } ; break }
        '^Lock pages in memory enabled$'            { 'Info'; break }
        '^Pending restart$'                         { if ($Value -eq 'No') { 'Pass' } else { 'Warn' } ; break }
        default { 'Info' }
    }
}
#endregion helpers

Write-Log "Starting audit for $SqlInstance"

# Storage objects
$metrics = New-Object 'System.Collections.Generic.List[object]'
$dbSummary = @()
$fileSummary = @()
$jobSummary = @()
$loginSummary = @()
$backupSummary = @()
$driveSummary = @()
$errorSummary = @()
$waitSummary = @()
$tempdbSummary = @()
$agSummary = @()

#region OS/WMI
Write-Log "Collecting OS/WMI details"
$comp = Get-SafeWmi -ClassName Win32_ComputerSystem
$os = Get-SafeWmi -ClassName Win32_OperatingSystem
$bios = Get-SafeWmi -ClassName Win32_BIOS
$proc = @(Get-SafeWmi -ClassName Win32_Processor)
$svc = @(Get-SafeWmi -ClassName Win32_Service)
$disks = @(Get-SafeWmi -ClassName Win32_LogicalDisk | Where-Object { $_.DriveType -eq 3 })

if ($comp) {
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Computer name' -Value $comp.Name
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Domain' -Value $comp.Domain
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Manufacturer' -Value $comp.Manufacturer
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Model' -Value $comp.Model
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Physical memory total' -Value (Convert-BytesToNiceString $comp.TotalPhysicalMemory)
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Logical processors (WMI)' -Value $comp.NumberOfLogicalProcessors
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Processor sockets' -Value $comp.NumberOfProcessors
}
if ($os) {
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS caption' -Value $os.Caption
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS version' -Value $os.Version
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS build number' -Value $os.BuildNumber
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'OS architecture' -Value $os.OSArchitecture
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Last boot time' -Value $os.LastBootUpTime
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'System drive' -Value $os.SystemDrive
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Windows directory' -Value $os.WindowsDirectory
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Free physical memory' -Value (Convert-BytesToNiceString ([decimal]$os.FreePhysicalMemory * 1KB))
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'Free virtual memory' -Value (Convert-BytesToNiceString ([decimal]$os.FreeVirtualMemory * 1KB))
}
if ($bios) {
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'BIOS version' -Value (($bios.SMBIOSBIOSVersion, $bios.Version -join ' | ').Trim(' |'))
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'BIOS serial' -Value $bios.SerialNumber
}
if ($proc) {
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU count (rows)' -Value $proc.Count
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU name' -Value (($proc | Select-Object -First 1).Name)
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU max clock MHz' -Value (($proc | Measure-Object -Property MaxClockSpeed -Maximum).Maximum)
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU cores total' -Value (($proc | Measure-Object -Property NumberOfCores -Sum).Sum)
    Add-Metric -MetricList $metrics -Category 'OS' -Metric 'CPU logical total' -Value (($proc | Measure-Object -Property NumberOfLogicalProcessors -Sum).Sum)
}
foreach ($d in $disks) {
    $driveSummary += [pscustomobject]@{
        Drive      = $d.DeviceID
        Label      = $d.VolumeName
        FileSystem = $d.FileSystem
        Size       = Convert-BytesToNiceString $d.Size
        Free       = Convert-BytesToNiceString $d.FreeSpace
        FreePct    = if ($d.Size -gt 0) { "{0:N1}" -f (($d.FreeSpace / $d.Size) * 100) } else { '' }
    }
    Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) filesystem" -Value $d.FileSystem
    Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) label" -Value $d.VolumeName
    Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) size" -Value (Convert-BytesToNiceString $d.Size)
    Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) free" -Value (Convert-BytesToNiceString $d.FreeSpace)
    Add-Metric -MetricList $metrics -Category 'Storage' -Metric "$($d.DeviceID) free percent" -Value (if ($d.Size -gt 0) { "{0:N1}%" -f (($d.FreeSpace / $d.Size) * 100) } else { '' })
}
#endregion OS/WMI

#region SQL queries
Write-Log "Collecting SQL details"

$serverPropsQuery = @"
SET NOCOUNT ON;
SELECT
    @@SERVERNAME AS ServerName,
    SERVERPROPERTY('MachineName') AS MachineName,
    SERVERPROPERTY('ServerName') AS FullServerName,
    SERVERPROPERTY('InstanceName') AS InstanceName,
    SERVERPROPERTY('IsClustered') AS IsClustered,
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS PhysicalNetBIOS,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,
    SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,
    SERVERPROPERTY('EngineEdition') AS EngineEdition,
    SERVERPROPERTY('HadrManagerStatus') AS HadrManagerStatus,
    SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,
    SERVERPROPERTY('Collation') AS Collation,
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,
    SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel,
    SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel,
    SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath,
    SERVERPROPERTY('InstanceDefaultLogPath') AS DefaultLogPath,
    SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion,
    SERVERPROPERTY('LCID') AS LCID,
    SERVERPROPERTY('SqlCharSet') AS SqlCharSet,
    SERVERPROPERTY('SqlSortOrder') AS SqlSortOrder,
    SERVERPROPERTY('SqlSortOrderName') AS SqlSortOrderName;
"@

$configQuery = @"
SET NOCOUNT ON;
SELECT name, value, value_in_use, description
FROM sys.configurations
ORDER BY name;
"@

$sysInfoQuery = @"
SET NOCOUNT ON;
SELECT
    cpu_count,
    scheduler_count,
    hyperthread_ratio,
    numa_node_count,
    softnuma_configuration_desc,
    sockets,
    cores_per_socket,
    max_workers_count,
    physical_memory_kb,
    committed_kb,
    committed_target_kb,
    visible_target_kb,
    sql_memory_model_desc,
    stack_size_in_bytes,
    virtual_machine_type_desc,
    sqlserver_start_time
FROM sys.dm_os_sys_info;
"@

$memoryQuery = @"
SET NOCOUNT ON;
SELECT
    total_physical_memory_kb,
    available_physical_memory_kb,
    total_page_file_kb,
    available_page_file_kb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;
"@

$processMemQuery = @"
SET NOCOUNT ON;
SELECT
    physical_memory_in_use_kb,
    large_page_allocations_kb,
    locked_page_allocations_kb,
    virtual_address_space_reserved_kb,
    virtual_address_space_committed_kb,
    page_fault_count,
    memory_utilization_percentage,
    available_commit_limit_kb,
    process_physical_memory_low,
    process_virtual_memory_low
FROM sys.dm_os_process_memory;
"@

$servicesQuery = @"
SET NOCOUNT ON;
SELECT servicename, startup_type_desc, status_desc, last_startup_time, service_account, is_clustered, cluster_nodename, filename
FROM sys.dm_server_services
ORDER BY servicename;
"@

$dbQuery = @"
SET NOCOUNT ON;
SELECT
    d.database_id,
    d.name,
    d.state_desc,
    d.user_access_desc,
    d.recovery_model_desc,
    d.compatibility_level,
    d.containment_desc,
    d.collation_name,
    d.is_auto_close_on,
    d.is_auto_shrink_on,
    d.is_auto_create_stats_on,
    d.is_auto_update_stats_on,
    d.is_auto_update_stats_async_on,
    d.is_read_only,
    d.is_encrypted,
    d.is_trustworthy_on,
    d.is_query_store_on,
    d.snapshot_isolation_state_desc,
    d.is_read_committed_snapshot_on,
    d.log_reuse_wait_desc,
    d.page_verify_option_desc,
    d.target_recovery_time_in_seconds,
    d.create_date,
    SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size END) * 8.0 / 1024 AS DataSizeMB,
    SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size END) * 8.0 / 1024 AS LogSizeMB
FROM sys.databases d
LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
GROUP BY
    d.database_id,d.name,d.state_desc,d.user_access_desc,d.recovery_model_desc,d.compatibility_level,
    d.containment_desc,d.collation_name,d.is_auto_close_on,d.is_auto_shrink_on,d.is_auto_create_stats_on,
    d.is_auto_update_stats_on,d.is_auto_update_stats_async_on,d.is_read_only,d.is_encrypted,d.is_trustworthy_on,
    d.is_query_store_on,d.snapshot_isolation_state_desc,d.is_read_committed_snapshot_on,d.log_reuse_wait_desc,
    d.page_verify_option_desc,d.target_recovery_time_in_seconds,d.create_date
ORDER BY d.database_id;
"@

$fileQuery = @"
SET NOCOUNT ON;
SELECT
    DB_NAME(mf.database_id) AS DatabaseName,
    mf.file_id,
    mf.type_desc,
    mf.name AS LogicalName,
    mf.physical_name,
    mf.state_desc,
    mf.size * 8.0 / 1024 AS SizeMB,
    CASE WHEN mf.max_size = -1 THEN -1 ELSE mf.max_size * 8.0 / 1024 END AS MaxSizeMB,
    mf.growth,
    mf.is_percent_growth,
    vs.volume_mount_point,
    vs.logical_volume_name,
    vs.file_system_type,
    CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS decimal(18,2)) AS VolumeSizeGB,
    CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS decimal(18,2)) AS VolumeFreeGB
FROM sys.master_files mf
OUTER APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
ORDER BY DB_NAME(mf.database_id), mf.type_desc, mf.file_id;
"@

$tempdbQuery = @"
SET NOCOUNT ON;
SELECT
    name,
    file_id,
    type_desc,
    physical_name,
    size * 8.0 / 1024 AS SizeMB,
    growth,
    is_percent_growth
FROM tempdb.sys.database_files
ORDER BY file_id;
"@

$backupQuery = @"
SET NOCOUNT ON;
WITH b AS (
    SELECT
        database_name,
        type,
        MAX(backup_finish_date) AS LastBackupFinishDate
    FROM msdb.dbo.backupset
    GROUP BY database_name, type
)
SELECT
    d.name,
    d.recovery_model_desc,
    MAX(CASE WHEN b.type = 'D' THEN b.LastBackupFinishDate END) AS LastFull,
    MAX(CASE WHEN b.type = 'I' THEN b.LastBackupFinishDate END) AS LastDiff,
    MAX(CASE WHEN b.type = 'L' THEN b.LastBackupFinishDate END) AS LastLog
FROM sys.databases d
LEFT JOIN b ON d.name = b.database_name
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;
"@

$jobQuery = @"
SET NOCOUNT ON;
SELECT
    j.name,
    j.enabled,
    j.description,
    SUSER_SNAME(j.owner_sid) AS OwnerName,
    c.name AS CategoryName,
    CASE WHEN js.job_id IS NULL THEN 0 ELSE 1 END AS HasSchedule
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT JOIN (
    SELECT DISTINCT job_id FROM msdb.dbo.sysjobschedules
) js ON j.job_id = js.job_id
ORDER BY j.name;
"@

$jobHistoryQuery = @"
SET NOCOUNT ON;
;WITH last_run AS (
    SELECT
        j.name,
        h.run_status,
        h.run_date,
        h.run_time,
        ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY h.instance_id DESC) AS rn
    FROM msdb.dbo.sysjobs j
    LEFT JOIN msdb.dbo.sysjobhistory h
        ON j.job_id = h.job_id
       AND h.step_id = 0
)
SELECT name, run_status, run_date, run_time
FROM last_run
WHERE rn = 1
ORDER BY name;
"@

$loginQuery = @"
SET NOCOUNT ON;
SELECT
    name,
    type_desc,
    is_disabled,
    default_database_name,
    create_date,
    modify_date
FROM sys.server_principals
WHERE type IN ('S','U','G','R','C','K')
ORDER BY type_desc, name;
"@

$sqlLoginPolicyQuery = @"
SET NOCOUNT ON;
SELECT
    name,
    is_policy_checked,
    is_expiration_checked
FROM sys.sql_logins
ORDER BY name;
"@

$linkQuery = @"
SET NOCOUNT ON;
SELECT name, product, provider, data_source, is_linked, is_remote_login_enabled, is_rpc_out_enabled, modify_date
FROM sys.servers
ORDER BY server_id;
"@

$endpointQuery = @"
SET NOCOUNT ON;
SELECT name, type_desc, state_desc, is_admin_endpoint, protocol_desc
FROM sys.endpoints
ORDER BY name;
"@

$permQuery = @"
SET NOCOUNT ON;
SELECT
    sp.name AS principal_name,
    sp.type_desc,
    perm.permission_name,
    perm.state_desc
FROM sys.server_permissions perm
JOIN sys.server_principals sp
    ON perm.grantee_principal_id = sp.principal_id
ORDER BY sp.name, perm.permission_name;
"@

$haQuery = @"
SET NOCOUNT ON;
SELECT
    ag.name AS AGName,
    ag.cluster_type_desc,
    ag.automated_backup_preference_desc,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.seeding_mode_desc,
    ar.secondary_role_allow_connections_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
    ON ag.group_id = ar.group_id
ORDER BY ag.name, ar.replica_server_name;
"@

$haDbQuery = @"
SET NOCOUNT ON;
SELECT
    ag.name AS AGName,
    adc.database_name,
    drs.is_local,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.is_suspended,
    drs.last_commit_time
FROM sys.availability_groups ag
JOIN sys.availability_databases_cluster adc
    ON ag.group_id = adc.group_id
LEFT JOIN sys.dm_hadr_database_replica_states drs
    ON adc.group_database_id = drs.group_database_id
ORDER BY ag.name, adc.database_name;
"@

$waitsQuery = @"
SET NOCOUNT ON;
SELECT TOP (15)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
  AND wait_type NOT IN (
      'CLR_AUTO_EVENT','CLR_MANUAL_EVENT','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
      'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','BROKER_TO_FLUSH','BROKER_TASK_STOP',
      'BROKER_EVENTHANDLER','FT_IFTS_SCHEDULER_IDLE_WAIT','BROKER_RECEIVE_WAITFOR',
      'ONDEMAND_TASK_QUEUE','DBMIRROR_EVENTS_QUEUE','DBMIRRORING_CMD','SQLTRACE_BUFFER_FLUSH',
      'REQUEST_FOR_DEADLOCK_SEARCH','LOGMGR_QUEUE','CHECKPOINT_QUEUE','BROKER_TRANSMITTER',
      'HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL','SP_SERVER_DIAGNOSTICS_SLEEP'
  )
ORDER BY wait_time_ms DESC;
"@

$errorLogCountQuery = @"
SET NOCOUNT ON;
DECLARE @ErrorLog TABLE (
    ArchiveNo int,
    [Date] datetime,
    LogFileSizeBytes bigint
);
INSERT INTO @ErrorLog
EXEC master.dbo.xp_enumerrorlogs;
SELECT * FROM @ErrorLog ORDER BY ArchiveNo;
"@

$traceFlagsQuery = @"
DBCC TRACESTATUS(-1) WITH NO_INFOMSGS;
"@

$dbccPageVerifyQuery = @"
SET NOCOUNT ON;
SELECT name, page_verify_option_desc
FROM sys.databases
ORDER BY name;
"@

$featureQuery = @"
SET NOCOUNT ON;
SELECT
    SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported,
    SERVERPROPERTY('IsPolyBaseInstalled') AS IsPolyBaseInstalled,
    SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS IsAdvancedAnalyticsInstalled,
    SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled;
"@

$resourceGovQuery = @"
SET NOCOUNT ON;
SELECT is_enabled, classifier_function_id
FROM sys.resource_governor_configuration;
"@

$serverAuditQuery = @"
SET NOCOUNT ON;
SELECT name, status_desc, audit_file_path, maxsize, max_rollover_files
FROM sys.server_audits
ORDER BY name;
"@

$credProxyMailQuery = @"
SET NOCOUNT ON;
SELECT 'Credential' AS ItemType, name, create_date, modify_date
FROM sys.credentials
UNION ALL
SELECT 'Proxy' AS ItemType, name, NULL, NULL
FROM msdb.dbo.sysproxies
UNION ALL
SELECT 'DBMailProfile' AS ItemType, name, NULL, NULL
FROM msdb.dbo.sysmail_profile
ORDER BY ItemType, name;
"@

$policyQuery = @"
SET NOCOUNT ON;
SELECT name, condition_id, root_condition_id, is_enabled, created_by, date_created
FROM msdb.dbo.syspolicy_policies
ORDER BY name;
"@

$operatorQuery = @"
SET NOCOUNT ON;
SELECT name, enabled, email_address, pager_address
FROM msdb.dbo.sysoperators
ORDER BY name;
"@

$queryStoreStateQuery = @"
SET NOCOUNT ON;
SELECT
    name,
    is_query_store_on,
    CASE WHEN is_query_store_on = 1 THEN CAST(DATABASEPROPERTYEX(name,'Updateability') AS nvarchar(60)) ELSE NULL END AS Updateability
FROM sys.databases
ORDER BY name;
"@

$instanceProps = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $serverPropsQuery -TrustServerCertificate:$TrustServerCertificate
$configs       = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $configQuery -TrustServerCertificate:$TrustServerCertificate
$sysInfo       = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $sysInfoQuery -TrustServerCertificate:$TrustServerCertificate
$osMem         = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $memoryQuery -TrustServerCertificate:$TrustServerCertificate
$procMem       = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $processMemQuery -TrustServerCertificate:$TrustServerCertificate
$services      = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $servicesQuery -TrustServerCertificate:$TrustServerCertificate
$dbs           = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $dbQuery -TrustServerCertificate:$TrustServerCertificate
$files         = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $fileQuery -TrustServerCertificate:$TrustServerCertificate
$tempdbFiles   = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $tempdbQuery -TrustServerCertificate:$TrustServerCertificate
$backups       = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $backupQuery -TrustServerCertificate:$TrustServerCertificate
$jobs          = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $jobQuery -TrustServerCertificate:$TrustServerCertificate
$jobHistory    = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $jobHistoryQuery -TrustServerCertificate:$TrustServerCertificate
$logins        = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $loginQuery -TrustServerCertificate:$TrustServerCertificate
$sqlLoginPol   = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $sqlLoginPolicyQuery -TrustServerCertificate:$TrustServerCertificate
$linkedServers = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $linkQuery -TrustServerCertificate:$TrustServerCertificate
$endpoints     = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $endpointQuery -TrustServerCertificate:$TrustServerCertificate
$permissions   = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $permQuery -TrustServerCertificate:$TrustServerCertificate
$agReplicas    = $null
$agDbs         = $null
try { $agReplicas = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $haQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
try { $agDbs = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $haDbQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
$waits         = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $waitsQuery -TrustServerCertificate:$TrustServerCertificate
$errorLogs     = $null
try { $errorLogs = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $errorLogCountQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
$traceFlags    = $null
try { $traceFlags = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $traceFlagsQuery -TrustServerCertificate:$TrustServerCertificate } catch {}
$pageVerify    = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $dbccPageVerifyQuery -TrustServerCertificate:$TrustServerCertificate
$features      = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $featureQuery -TrustServerCertificate:$TrustServerCertificate
$rgConfig      = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $resourceGovQuery -TrustServerCertificate:$TrustServerCertificate
$audits        = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $serverAuditQuery -TrustServerCertificate:$TrustServerCertificate
$otherObjects  = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $credProxyMailQuery -TrustServerCertificate:$TrustServerCertificate
$policies      = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $policyQuery -TrustServerCertificate:$TrustServerCertificate
$operators     = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $operatorQuery -TrustServerCertificate:$TrustServerCertificate
$qstore        = Invoke-SqlQuery -ServerInstance $SqlInstance -Query $queryStoreStateQuery -TrustServerCertificate:$TrustServerCertificate
#endregion SQL queries

#region summarize server properties
Write-Log "Building metric list"

$sp = $instanceProps | Select-Object -First 1
if ($sp) {
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ServerName' -Value $sp.ServerName
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'MachineName' -Value $sp.MachineName
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'FullServerName' -Value $sp.FullServerName
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'InstanceName' -Value $sp.InstanceName
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Edition' -Value $sp.Edition
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductVersion' -Value $sp.ProductVersion
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductLevel' -Value $sp.ProductLevel
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductUpdateLevel' -Value $sp.ProductUpdateLevel
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'ProductUpdateReference' -Value $sp.ProductUpdateReference
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'EngineEdition' -Value $sp.EngineEdition
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'IsClustered' -Value $sp.IsClustered
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'PhysicalNetBIOS' -Value $sp.PhysicalNetBIOS
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Collation' -Value $sp.Collation
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Windows auth only' -Value $sp.IsIntegratedSecurityOnly
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'FilestreamConfiguredLevel' -Value $sp.FilestreamConfiguredLevel
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'FilestreamEffectiveLevel' -Value $sp.FilestreamEffectiveLevel
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Default data path' -Value $sp.DefaultDataPath
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Default log path' -Value $sp.DefaultLogPath
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'Build CLR version' -Value $sp.BuildClrVersion
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'LCID' -Value $sp.LCID
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'SqlCharSet' -Value $sp.SqlCharSet
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'SqlSortOrder' -Value $sp.SqlSortOrder
    Add-Metric -MetricList $metrics -Category 'Instance' -Metric 'SqlSortOrderName' -Value $sp.SqlSortOrderName
    Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'IsHadrEnabled' -Value $sp.IsHadrEnabled
    Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'HadrManagerStatus' -Value $sp.HadrManagerStatus
}

$si = $sysInfo | Select-Object -First 1
if ($si) {
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'cpu_count' -Value $si.cpu_count
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'scheduler_count' -Value $si.scheduler_count
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'hyperthread_ratio' -Value $si.hyperthread_ratio
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'numa_node_count' -Value $si.numa_node_count
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'softnuma_configuration_desc' -Value $si.softnuma_configuration_desc
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'sockets' -Value $si.sockets
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'cores_per_socket' -Value $si.cores_per_socket
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'max_workers_count' -Value $si.max_workers_count
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'physical_memory_kb' -Value (Convert-BytesToNiceString ([decimal]$si.physical_memory_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'committed_kb' -Value (Convert-BytesToNiceString ([decimal]$si.committed_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'committed_target_kb' -Value (Convert-BytesToNiceString ([decimal]$si.committed_target_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'visible_target_kb' -Value (Convert-BytesToNiceString ([decimal]$si.visible_target_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'sql_memory_model_desc' -Value $si.sql_memory_model_desc
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'stack_size_in_bytes' -Value (Convert-BytesToNiceString $si.stack_size_in_bytes)
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'virtual_machine_type_desc' -Value $si.virtual_machine_type_desc
    Add-Metric -MetricList $metrics -Category 'Performance' -Metric 'sqlserver_start_time' -Value $si.sqlserver_start_time
}

$osm = $osMem | Select-Object -First 1
if ($osm) {
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS total physical memory' -Value (Convert-BytesToNiceString ([decimal]$osm.total_physical_memory_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS available physical memory' -Value (Convert-BytesToNiceString ([decimal]$osm.available_physical_memory_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS total page file' -Value (Convert-BytesToNiceString ([decimal]$osm.total_page_file_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'OS available page file' -Value (Convert-BytesToNiceString ([decimal]$osm.available_page_file_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'system_memory_state_desc' -Value $osm.system_memory_state_desc
}

$pm = $procMem | Select-Object -First 1
if ($pm) {
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL physical_memory_in_use' -Value (Convert-BytesToNiceString ([decimal]$pm.physical_memory_in_use_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL large_page_allocations' -Value (Convert-BytesToNiceString ([decimal]$pm.large_page_allocations_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL locked_page_allocations' -Value (Convert-BytesToNiceString ([decimal]$pm.locked_page_allocations_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL VA reserved' -Value (Convert-BytesToNiceString ([decimal]$pm.virtual_address_space_reserved_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL VA committed' -Value (Convert-BytesToNiceString ([decimal]$pm.virtual_address_space_committed_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL page_fault_count' -Value $pm.page_fault_count
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL memory_utilization_percentage' -Value ("{0}%" -f $pm.memory_utilization_percentage)
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'SQL available_commit_limit' -Value (Convert-BytesToNiceString ([decimal]$pm.available_commit_limit_kb * 1KB))
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'process_physical_memory_low' -Value $pm.process_physical_memory_low
    Add-Metric -MetricList $metrics -Category 'Memory' -Metric 'process_virtual_memory_low' -Value $pm.process_virtual_memory_low
}
#endregion summarize server properties

#region services
foreach ($s in $services) {
    $metricName = switch -Regex ($s.servicename) {
        'SQL Server \(' { 'SQL Server service status'; break }
        '^SQL Server Agent' { 'SQL Server Agent service status'; break }
        default { "$($s.servicename) status" }
    }

    $status = Get-StatusFromRule -Metric $metricName -Value $s.status_desc
    Add-Metric -MetricList $metrics -Category 'Services' -Metric $metricName -Value $s.status_desc -Status $status -Notes "Startup: $($s.startup_type_desc); Account: $($s.service_account)"
    Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) startup type" -Value $s.startup_type_desc
    Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) last startup" -Value $s.last_startup_time
    Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) service account" -Value $s.service_account
    Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) clustered" -Value $s.is_clustered
    Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) cluster node" -Value $s.cluster_nodename
    Add-Metric -MetricList $metrics -Category 'Services' -Metric "$($s.servicename) binary" -Value $s.filename
}
#endregion services

#region configurations
foreach ($cfg in $configs) {
    $metricName = $cfg.name
    $status = Get-StatusFromRule -Metric $metricName -Value ([string]$cfg.value_in_use)
    Add-Metric -MetricList $metrics -Category 'Configuration' -Metric $metricName -Value $cfg.value_in_use -Status $status -Notes "Configured value: $($cfg.value); $($cfg.description)"
}

# Derived config checks
$configHash = @{}
foreach ($c in $configs) { $configHash[$c.name] = $c }

$maxServerMemMB = if ($configHash.ContainsKey('max server memory (MB)')) { [int]$configHash['max server memory (MB)'].value_in_use } else { 0 }
$minServerMemMB = if ($configHash.ContainsKey('min server memory (MB)')) { [int]$configHash['min server memory (MB)'].value_in_use } else { 0 }
$maxDop         = if ($configHash.ContainsKey('max degree of parallelism')) { [int]$configHash['max degree of parallelism'].value_in_use } else { 0 }
$ctfp           = if ($configHash.ContainsKey('cost threshold for parallelism')) { [int]$configHash['cost threshold for parallelism'].value_in_use } else { 0 }

if ($comp -and $comp.TotalPhysicalMemory) {
    $totalMb = [math]::Round($comp.TotalPhysicalMemory / 1MB)
    Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Max server memory review' -Value "$maxServerMemMB MB" -Status (
        if ($maxServerMemMB -eq 2147483647 -or $maxServerMemMB -eq 0) { 'Warn' } else { 'Info' }
    ) -Notes "OS total memory: $totalMb MB"
}
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Min server memory review' -Value "$minServerMemMB MB" -Status 'Info'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'MAXDOP review' -Value $maxDop -Status 'Info'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'CTFP review' -Value $ctfp -Status (if ($ctfp -lt 25) { 'Warn' } else { 'Pass' })
#endregion configurations

#region security/logins
foreach ($l in $logins) {
    $loginSummary += [pscustomobject]@{
        Name            = $l.name
        Type            = $l.type_desc
        Disabled        = $l.is_disabled
        DefaultDatabase = $l.default_database_name
        CreateDate      = $l.create_date
        ModifyDate      = $l.modify_date
    }

    Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] type" -Value $l.type_desc
    Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] disabled" -Value $l.is_disabled
    Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] default DB" -Value $l.default_database_name
    Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] create date" -Value $l.create_date
    Add-Metric -MetricList $metrics -Category 'Security' -Metric "Login [$($l.name)] modify date" -Value $l.modify_date
}

foreach ($sl in $sqlLoginPol) {
    Add-Metric -MetricList $metrics -Category 'Security' -Metric "SQL Login [$($sl.name)] password policy checked" -Value $sl.is_policy_checked
    Add-Metric -MetricList $metrics -Category 'Security' -Metric "SQL Login [$($sl.name)] expiration checked" -Value $sl.is_expiration_checked
}

Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Login count total' -Value (@($logins).Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'SQL login count' -Value (@($logins | Where-Object type_desc -eq 'SQL_LOGIN').Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Windows login count' -Value (@($logins | Where-Object type_desc -eq 'WINDOWS_LOGIN').Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Windows group count' -Value (@($logins | Where-Object type_desc -eq 'WINDOWS_GROUP').Count)
Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Disabled login count' -Value (@($logins | Where-Object is_disabled -eq 1).Count)

foreach ($ls in $linkedServers) {
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] product" -Value $ls.product
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] provider" -Value $ls.provider
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] data source" -Value $ls.data_source
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] linked" -Value $ls.is_linked
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] remote login enabled" -Value $ls.is_remote_login_enabled
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] RPC out enabled" -Value $ls.is_rpc_out_enabled
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Linked server [$($ls.name)] modified" -Value $ls.modify_date
}

foreach ($ep in $endpoints) {
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] type" -Value $ep.type_desc
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] state" -Value $ep.state_desc
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] admin endpoint" -Value $ep.is_admin_endpoint
    Add-Metric -MetricList $metrics -Category 'Connectivity' -Metric "Endpoint [$($ep.name)] protocol" -Value $ep.protocol_desc
}

Add-Metric -MetricList $metrics -Category 'Security' -Metric 'Server-level explicit permissions count' -Value (@($permissions).Count)
#endregion security/logins

#region databases
foreach ($db in $dbs) {
    $dbSummary += [pscustomobject]@{
        Database            = $db.name
        State               = $db.state_desc
        RecoveryModel       = $db.recovery_model_desc
        CompatibilityLevel  = $db.compatibility_level
        DataSizeMB          = [math]::Round([decimal]$db.DataSizeMB,2)
        LogSizeMB           = [math]::Round([decimal]$db.LogSizeMB,2)
        AutoClose           = $db.is_auto_close_on
        AutoShrink          = $db.is_auto_shrink_on
        AutoCreateStats     = $db.is_auto_create_stats_on
        AutoUpdateStats     = $db.is_auto_update_stats_on
        AutoUpdateStatsAsync= $db.is_auto_update_stats_async_on
        ReadOnly            = $db.is_read_only
        Encrypted           = $db.is_encrypted
        Trustworthy         = $db.is_trustworthy_on
        QueryStore          = $db.is_query_store_on
        RCSI                = $db.is_read_committed_snapshot_on
        SnapshotIsolation   = $db.snapshot_isolation_state_desc
        PageVerify          = $db.page_verify_option_desc
        TargetRecoverySec   = $db.target_recovery_time_in_seconds
        LogReuseWait        = $db.log_reuse_wait_desc
        CreateDate          = $db.create_date
    }

    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] state" -Value $db.state_desc -Status (if ($db.state_desc -eq 'ONLINE') { 'Pass' } else { 'Warn' })
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] recovery model" -Value $db.recovery_model_desc
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] compatibility level" -Value $db.compatibility_level
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] containment" -Value $db.containment_desc
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] collation" -Value $db.collation_name
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_close" -Value $db.is_auto_close_on -Status (if ($db.is_auto_close_on -eq 0) { 'Pass' } else { 'Warn' })
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_shrink" -Value $db.is_auto_shrink_on -Status (if ($db.is_auto_shrink_on -eq 0) { 'Pass' } else { 'Warn' })
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_create_stats" -Value $db.is_auto_create_stats_on
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_update_stats" -Value $db.is_auto_update_stats_on
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] auto_update_stats_async" -Value $db.is_auto_update_stats_async_on
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] read_only" -Value $db.is_read_only
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] encrypted" -Value $db.is_encrypted
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] trustworthy" -Value $db.is_trustworthy_on -Status (if ($db.is_trustworthy_on -eq 0) { 'Pass' } else { 'Warn' })
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] query_store_on" -Value $db.is_query_store_on
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] snapshot isolation" -Value $db.snapshot_isolation_state_desc
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] RCSI" -Value $db.is_read_committed_snapshot_on
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] page verify" -Value $db.page_verify_option_desc -Status (if ($db.page_verify_option_desc -eq 'CHECKSUM') { 'Pass' } else { 'Warn' })
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] target recovery sec" -Value $db.target_recovery_time_in_seconds
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] log reuse wait" -Value $db.log_reuse_wait_desc
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] data size" -Value ("{0:N2} MB" -f [decimal]$db.DataSizeMB)
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] log size" -Value ("{0:N2} MB" -f [decimal]$db.LogSizeMB)
    Add-Metric -MetricList $metrics -Category 'Databases' -Metric "DB [$($db.name)] create date" -Value $db.create_date
}

Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count total' -Value (@($dbs).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count online' -Value (@($dbs | Where-Object state_desc -eq 'ONLINE').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count offline' -Value (@($dbs | Where-Object state_desc -ne 'ONLINE').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count FULL recovery' -Value (@($dbs | Where-Object recovery_model_desc -eq 'FULL').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count SIMPLE recovery' -Value (@($dbs | Where-Object recovery_model_desc -eq 'SIMPLE').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Database count BULK_LOGGED recovery' -Value (@($dbs | Where-Object recovery_model_desc -eq 'BULK_LOGGED').Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with AUTO_CLOSE ON' -Value (@($dbs | Where-Object is_auto_close_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with AUTO_SHRINK ON' -Value (@($dbs | Where-Object is_auto_shrink_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with TRUSTWORTHY ON' -Value (@($dbs | Where-Object is_trustworthy_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases encrypted' -Value (@($dbs | Where-Object is_encrypted -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with Query Store ON' -Value (@($dbs | Where-Object is_query_store_on -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Databases' -Metric 'Databases with RCSI ON' -Value (@($dbs | Where-Object is_read_committed_snapshot_on -eq 1).Count)
#endregion databases

#region files/tempdb
foreach ($f in $files) {
    $fileSummary += [pscustomobject]@{
        Database     = $f.DatabaseName
        FileId       = $f.file_id
        Type         = $f.type_desc
        LogicalName  = $f.LogicalName
        PhysicalName = $f.physical_name
        State        = $f.state_desc
        SizeMB       = [math]::Round([decimal]$f.SizeMB,2)
        MaxSizeMB    = $f.MaxSizeMB
        Growth       = $f.growth
        PercentGrowth= $f.is_percent_growth
        MountPoint   = $f.volume_mount_point
        VolumeLabel  = $f.logical_volume_name
        FileSystem   = $f.file_system_type
        VolumeSizeGB = $f.VolumeSizeGB
        VolumeFreeGB = $f.VolumeFreeGB
    }

    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] type" -Value $f.type_desc
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] state" -Value $f.state_desc
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] size" -Value ("{0:N2} MB" -f [decimal]$f.SizeMB)
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] max size" -Value (if ([decimal]$f.MaxSizeMB -lt 0) { 'Unlimited' } else { "{0:N2} MB" -f [decimal]$f.MaxSizeMB })
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] growth raw" -Value $f.growth
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] percent growth" -Value $f.is_percent_growth -Status (if ($f.is_percent_growth -eq 0) { 'Pass' } else { 'Warn' })
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] physical path" -Value $f.physical_name
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] mount point" -Value $f.volume_mount_point
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] volume label" -Value $f.logical_volume_name
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] filesystem" -Value $f.file_system_type
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] volume size" -Value ("{0:N2} GB" -f [decimal]$f.VolumeSizeGB)
    Add-Metric -MetricList $metrics -Category 'Files' -Metric "File [$($f.DatabaseName):$($f.LogicalName)] volume free" -Value ("{0:N2} GB" -f [decimal]$f.VolumeFreeGB)
}

foreach ($tf in $tempdbFiles) {
    $tempdbSummary += [pscustomobject]@{
        Name          = $tf.name
        FileId        = $tf.file_id
        Type          = $tf.type_desc
        PhysicalName  = $tf.physical_name
        SizeMB        = [math]::Round([decimal]$tf.SizeMB,2)
        Growth        = $tf.growth
        PercentGrowth = $tf.is_percent_growth
    }

    Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] type" -Value $tf.type_desc
    Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] path" -Value $tf.physical_name
    Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] size" -Value ("{0:N2} MB" -f [decimal]$tf.SizeMB)
    Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] growth raw" -Value $tf.growth
    Add-Metric -MetricList $metrics -Category 'TempDB' -Metric "TempDB file [$($tf.name)] percent growth" -Value $tf.is_percent_growth -Status (if ($tf.is_percent_growth -eq 0) { 'Pass' } else { 'Warn' })
}

$tempdbDataFileCount = @($tempdbFiles | Where-Object type_desc -eq 'ROWS').Count
$tempdbLogFileCount = @($tempdbFiles | Where-Object type_desc -eq 'LOG').Count
$tempdbDataSizes = @($tempdbFiles | Where-Object type_desc -eq 'ROWS' | Select-Object -ExpandProperty SizeMB)
$tempdbSameSize = $false
if ($tempdbDataSizes.Count -gt 1) {
    $tempdbSameSize = (($tempdbDataSizes | Select-Object -Unique).Count -eq 1)
}

Add-Metric -MetricList $metrics -Category 'TempDB' -Metric 'TempDB data file count' -Value $tempdbDataFileCount
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric 'TempDB log file count' -Value $tempdbLogFileCount
Add-Metric -MetricList $metrics -Category 'TempDB' -Metric 'TempDB data files equal size' -Value $(if ($tempdbSameSize) { 'Yes' } else { 'No' }) -Status $(if ($tempdbSameSize -or $tempdbDataFileCount -le 1) { 'Pass' } else { 'Warn' })
#endregion files/tempdb

#region backups
foreach ($b in $backups) {
    $backupSummary += [pscustomobject]@{
        Database      = $b.name
        RecoveryModel = $b.recovery_model_desc
        LastFull      = $b.LastFull
        LastDiff      = $b.LastDiff
        LastLog       = $b.LastLog
    }

    Add-Metric -MetricList $metrics -Category 'Backups' -Metric "Backup [$($b.name)] last full" -Value $b.LastFull -Status (if ($b.LastFull) { 'Pass' } else { 'Warn' })
    Add-Metric -MetricList $metrics -Category 'Backups' -Metric "Backup [$($b.name)] last diff" -Value $b.LastDiff
    Add-Metric -MetricList $metrics -Category 'Backups' -Metric "Backup [$($b.name)] last log" -Value $b.LastLog -Status (if ($b.recovery_model_desc -eq 'FULL' -and -not $b.LastLog -and $b.name -notin @('master','model','msdb','tempdb')) { 'Warn' } else { 'Info' })
}

Add-Metric -MetricList $metrics -Category 'Backups' -Metric 'Databases with full backup history' -Value (@($backups | Where-Object LastFull).Count)
Add-Metric -MetricList $metrics -Category 'Backups' -Metric 'Databases missing full backup history' -Value (@($backups | Where-Object { -not $_.LastFull }).Count)
Add-Metric -MetricList $metrics -Category 'Backups' -Metric 'FULL recovery DBs missing log backup history' -Value (@($backups | Where-Object { $_.recovery_model_desc -eq 'FULL' -and -not $_.LastLog -and $_.name -notin @('master','model','msdb','tempdb') }).Count)
#endregion backups

#region agent/jobs
foreach ($j in $jobs) {
    $hist = $jobHistory | Where-Object name -eq $j.name | Select-Object -First 1
    $lastRunStatus = switch ($hist.run_status) {
        0 { 'Failed' }
        1 { 'Succeeded' }
        2 { 'Retry' }
        3 { 'Canceled' }
        4 { 'In Progress' }
        default { 'Never Run' }
    }

    $jobSummary += [pscustomobject]@{
        JobName      = $j.name
        Enabled      = $j.enabled
        Owner        = $j.OwnerName
        Category     = $j.CategoryName
        HasSchedule  = $j.HasSchedule
        LastRun      = if ($hist.run_date) { "{0} {1:000000}" -f $hist.run_date, [int]$hist.run_time } else { '' }
        LastStatus   = $lastRunStatus
        Description  = $j.description
    }

    Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] enabled" -Value $j.enabled
    Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] owner" -Value $j.OwnerName
    Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] category" -Value $j.CategoryName
    Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] has schedule" -Value $j.HasSchedule
    Add-Metric -MetricList $metrics -Category 'Agent' -Metric "Job [$($j.name)] last status" -Value $lastRunStatus -Status (if ($lastRunStatus -eq 'Failed') { 'Warn' } elseif ($lastRunStatus -eq 'Succeeded' -or $lastRunStatus -eq 'Never Run') { 'Info' } else { 'Info' })
}

Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Job count total' -Value (@($jobs).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Enabled job count' -Value (@($jobs | Where-Object enabled -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Scheduled job count' -Value (@($jobs | Where-Object HasSchedule -eq 1).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Operator count' -Value (@($operators).Count)
Add-Metric -MetricList $metrics -Category 'Agent' -Metric 'Proxy count' -Value (@($otherObjects | Where-Object ItemType -eq 'Proxy').Count)
#endregion agent/jobs

#region feature/platform objects
$ft = $features | Select-Object -First 1
if ($ft) {
    Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsXTPSupported' -Value $ft.IsXTPSupported
    Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsPolyBaseInstalled' -Value $ft.IsPolyBaseInstalled
    Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsAdvancedAnalyticsInstalled' -Value $ft.IsAdvancedAnalyticsInstalled
    Add-Metric -MetricList $metrics -Category 'Features' -Metric 'IsFullTextInstalled' -Value $ft.IsFullTextInstalled
}

$rg = $rgConfig | Select-Object -First 1
if ($rg) {
    Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Resource Governor enabled' -Value $rg.is_enabled
    Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Resource Governor classifier_function_id' -Value $rg.classifier_function_id
}

Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Server audit count' -Value (@($audits).Count)
foreach ($a in $audits) {
    Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] status" -Value $a.status_desc
    Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] path" -Value $a.audit_file_path
    Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] maxsize" -Value $a.maxsize
    Add-Metric -MetricList $metrics -Category 'Features' -Metric "Server audit [$($a.name)] max_rollover_files" -Value $a.max_rollover_files
}

Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Credential count' -Value (@($otherObjects | Where-Object ItemType -eq 'Credential').Count)
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'Database Mail profile count' -Value (@($otherObjects | Where-Object ItemType -eq 'DBMailProfile').Count)
Add-Metric -MetricList $metrics -Category 'Features' -Metric 'PBM policy count' -Value (@($policies).Count)
#endregion feature/platform objects

#region AG/HA
if ($agReplicas) {
    foreach ($ag in $agReplicas) {
        $agSummary += [pscustomobject]@{
            AGName                = $ag.AGName
            ClusterType           = $ag.cluster_type_desc
            BackupPreference      = $ag.automated_backup_preference_desc
            ReplicaServer         = $ag.replica_server_name
            AvailabilityMode      = $ag.availability_mode_desc
            FailoverMode          = $ag.failover_mode_desc
            SeedingMode           = $ag.seeding_mode_desc
            SecondaryConnections  = $ag.secondary_role_allow_connections_desc
        }

        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] cluster type" -Value $ag.cluster_type_desc
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] backup preference" -Value $ag.automated_backup_preference_desc
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] availability mode" -Value $ag.availability_mode_desc
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] failover mode" -Value $ag.failover_mode_desc
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] seeding mode" -Value $ag.seeding_mode_desc
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG [$($ag.AGName)] replica [$($ag.replica_server_name)] secondary allow connections" -Value $ag.secondary_role_allow_connections_desc
    }
    Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'AG replica row count' -Value (@($agReplicas).Count)
} else {
    Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'AG replica row count' -Value 0
}

if ($agDbs) {
    foreach ($agd in $agDbs) {
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] is local" -Value $agd.is_local
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] sync state" -Value $agd.synchronization_state_desc
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] sync health" -Value $agd.synchronization_health_desc
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] suspended" -Value $agd.is_suspended
        Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric "AG DB [$($agd.AGName):$($agd.database_name)] last commit" -Value $agd.last_commit_time
    }
    Add-Metric -MetricList $metrics -Category 'HA/DR' -Metric 'AG database row count' -Value (@($agDbs).Count)
}
#endregion AG/HA

#region waits/errors/trace flags/page verify/qstore
foreach ($w in $waits) {
    $waitSummary += [pscustomobject]@{
        WaitType         = $w.wait_type
        WaitingTasks     = $w.waiting_tasks_count
        WaitTimeMs       = $w.wait_time_ms
        MaxWaitTimeMs    = $w.max_wait_time_ms
        SignalWaitTimeMs = $w.signal_wait_time_ms
    }

    Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] waiting tasks" -Value $w.waiting_tasks_count
    Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] wait_time_ms" -Value $w.wait_time_ms
    Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] max_wait_time_ms" -Value $w.max_wait_time_ms
    Add-Metric -MetricList $metrics -Category 'Health' -Metric "Top wait [$($w.wait_type)] signal_wait_time_ms" -Value $w.signal_wait_time_ms
}

if ($errorLogs) {
    Add-Metric -MetricList $metrics -Category 'Health' -Metric 'SQL error log archive count' -Value (@($errorLogs).Count)
    foreach ($e in $errorLogs) {
        $errorSummary += [pscustomobject]@{
            ArchiveNo        = $e.ArchiveNo
            Date             = $e.Date
            LogFileSizeBytes = $e.LogFileSizeBytes
        }
        Add-Metric -MetricList $metrics -Category 'Health' -Metric "Error log archive [$($e.ArchiveNo)] date" -Value $e.Date
        Add-Metric -MetricList $metrics -Category 'Health' -Metric "Error log archive [$($e.ArchiveNo)] size bytes" -Value $e.LogFileSizeBytes
    }
}

if ($traceFlags) {
    foreach ($tf in $traceFlags) {
        $traceNum = Try-GetValue -Row $tf -ColumnName 'TraceFlag'
        $traceStat = Try-GetValue -Row $tf -ColumnName 'Status'
        $traceGlob = Try-GetValue -Row $tf -ColumnName 'Global'
        $traceSess = Try-GetValue -Row $tf -ColumnName 'Session'
        Add-Metric -MetricList $metrics -Category 'Health' -Metric "Trace flag [$traceNum] status" -Value $traceStat
        Add-Metric -MetricList $metrics -Category 'Health' -Metric "Trace flag [$traceNum] global" -Value $traceGlob
        Add-Metric -MetricList $metrics -Category 'Health' -Metric "Trace flag [$traceNum] session" -Value $traceSess
    }
} else {
    Add-Metric -MetricList $metrics -Category 'Health' -Metric 'Trace flags returned' -Value 0
}

foreach ($pv in $pageVerify) {
    Add-Metric -MetricList $metrics -Category 'Health' -Metric "Page verify [$($pv.name)]" -Value $pv.page_verify_option_desc -Status (if ($pv.page_verify_option_desc -eq 'CHECKSUM') { 'Pass' } else { 'Warn' })
}

foreach ($qs in $qstore) {
    Add-Metric -MetricList $metrics -Category 'Health' -Metric "Query Store [$($qs.name)] enabled" -Value $qs.is_query_store_on
}
#endregion waits/errors/trace flags/page verify/qstore

#region extra derived checks
# Instant File Initialization / LPIM heuristic from service account privilege inspection is not straightforward in raw PowerShell.
# Placeholder metrics based on SQL process memory behavior and common validation notes.
$ifiEnabled = 'Unknown'
try {
    # crude heuristic: IFI commonly seen when data file growths are fast, but not deterministically queryable here
    # leave as manual review note
    $ifiEnabled = 'Manual verification recommended'
}
catch {}

Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'instant file initialization enabled' -Value 'Manual verification recommended' -Status 'Info' -Notes 'Check Perform Volume Maintenance Tasks right for SQL Server service account.'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Lock pages in memory enabled' -Value 'Manual verification recommended' -Status 'Info' -Notes 'Check local security policy and locked_page_allocations.'
Add-Metric -MetricList $metrics -Category 'BestPractice' -Metric 'Pending restart' -Value 'Manual verification recommended' -Status 'Info'

# Count/pass-fail style summaries
$warnCount = @($metrics | Where-Object Status -eq 'Warn').Count
$failCount = @($metrics | Where-Object Status -eq 'Fail').Count
$passCount = @($metrics | Where-Object Status -eq 'Pass').Count
$infoCount = @($metrics | Where-Object Status -eq 'Info').Count

Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Total metrics collected' -Value $metrics.Count
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Pass metric count' -Value $passCount
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Warn metric count' -Value $warnCount
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Fail metric count' -Value $failCount
Add-Metric -MetricList $metrics -Category 'Summary' -Metric 'Info metric count' -Value $infoCount
#endregion extra derived checks

# Re-evaluate summary after adding summary rows
$warnCount = @($metrics | Where-Object Status -eq 'Warn').Count
$failCount = @($metrics | Where-Object Status -eq 'Fail').Count
$passCount = @($metrics | Where-Object Status -eq 'Pass').Count
$infoCount = @($metrics | Where-Object Status -eq 'Info').Count

#region HTML generation
Write-Log "Generating HTML"

$generatedOn = Get-Date
$metricsByCategory = $metrics | Group-Object Category | Sort-Object Name

$topWarnings = $metrics |
    Where-Object { $_.Status -in @('Warn','Fail') } |
    Sort-Object Category, Metric |
    Select-Object -First 50

$summaryCards = @(
    [pscustomobject]@{ Label = 'Total Metrics'; Value = $metrics.Count; Css = 'card-neutral' },
    [pscustomobject]@{ Label = 'Pass';          Value = $passCount;     Css = 'card-pass' },
    [pscustomobject]@{ Label = 'Warnings';      Value = $warnCount;     Css = 'card-warn' },
    [pscustomobject]@{ Label = 'Failures';      Value = $failCount;     Css = 'card-fail' },
    [pscustomobject]@{ Label = 'Databases';     Value = @($dbs).Count;  Css = 'card-neutral' },
    [pscustomobject]@{ Label = 'Jobs';          Value = @($jobs).Count; Css = 'card-neutral' }
)

$css = @"
<style>
body {
    font-family: Segoe UI, Arial, sans-serif;
    margin: 0;
    background: #f4f7fb;
    color: #1f2937;
}
.header {
    background: linear-gradient(135deg, #0f172a, #1d4ed8);
    color: white;
    padding: 28px 32px;
}
.header h1 {
    margin: 0 0 8px 0;
    font-size: 30px;
}
.header .sub {
    opacity: 0.95;
    font-size: 14px;
}
.container {
    padding: 24px 28px 40px 28px;
}
.card-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(160px, 1fr));
    gap: 14px;
    margin-bottom: 24px;
}
.card {
    border-radius: 14px;
    padding: 18px;
    box-shadow: 0 6px 18px rgba(15, 23, 42, 0.08);
    background: white;
    border-left: 6px solid #64748b;
}
.card .label {
    font-size: 12px;
    text-transform: uppercase;
    letter-spacing: .06em;
    color: #475569;
}
.card .value {
    font-size: 28px;
    font-weight: 700;
    margin-top: 8px;
}
.card-pass { border-left-color: #16a34a; }
.card-warn { border-left-color: #f59e0b; }
.card-fail { border-left-color: #dc2626; }
.card-neutral { border-left-color: #2563eb; }

.section {
    background: white;
    border-radius: 16px;
    padding: 20px 20px 8px 20px;
    margin-bottom: 22px;
    box-shadow: 0 6px 18px rgba(15, 23, 42, 0.06);
}
.section h2 {
    margin-top: 0;
    font-size: 20px;
    color: #0f172a;
}
.section h3 {
    margin-top: 18px;
    color: #1e293b;
}
table {
    width: 100%;
    border-collapse: collapse;
    margin: 10px 0 18px 0;
    font-size: 13px;
}
th, td {
    border-bottom: 1px solid #e5e7eb;
    padding: 8px 10px;
    text-align: left;
    vertical-align: top;
}
th {
    background: #eff6ff;
    color: #1e3a8a;
    position: sticky;
    top: 0;
}
tr:hover td {
    background: #f8fafc;
}
.status-pass {
    color: #166534;
    font-weight: 600;
}
.status-warn {
    color: #92400e;
    font-weight: 600;
}
.status-fail {
    color: #991b1b;
    font-weight: 700;
}
.status-info {
    color: #334155;
}
.kv {
    display: grid;
    grid-template-columns: 220px 1fr;
    gap: 8px 14px;
    font-size: 14px;
    margin-top: 10px;
}
.kv .k {
    font-weight: 600;
    color: #334155;
}
.empty {
    padding: 14px;
    border: 1px dashed #cbd5e1;
    border-radius: 10px;
    color: #64748b;
    background: #f8fafc;
}
.small {
    font-size: 12px;
    color: #64748b;
}
.footer {
    text-align: center;
    font-size: 12px;
    color: #64748b;
    padding: 10px 0 20px 0;
}
.badge {
    display: inline-block;
    padding: 4px 8px;
    border-radius: 999px;
    background: #e2e8f0;
    color: #334155;
    font-size: 12px;
    margin-right: 8px;
}
</style>
"@

$sb = New-Object System.Text.StringBuilder
[void]$sb.AppendLine("<html><head><meta charset='utf-8'><title>SQL Server New Install Audit - $([System.Net.WebUtility]::HtmlEncode($SqlInstance))</title>$css</head><body>")
[void]$sb.AppendLine("<div class='header'>")
[void]$sb.AppendLine("<h1>SQL Server New Install Audit</h1>")
[void]$sb.AppendLine("<div class='sub'>Instance: <strong>$([System.Net.WebUtility]::HtmlEncode($SqlInstance))</strong> &nbsp; | &nbsp; Generated: <strong>$generatedOn</strong></div>")
[void]$sb.AppendLine("</div>")
[void]$sb.AppendLine("<div class='container'>")

# cards
[void]$sb.AppendLine("<div class='card-grid'>")
foreach ($card in $summaryCards) {
    [void]$sb.AppendLine("<div class='card $($card.Css)'><div class='label'>$($card.Label)</div><div class='value'>$($card.Value)</div></div>")
}
[void]$sb.AppendLine("</div>")

# overview
[void]$sb.AppendLine("<div class='section'>")
[void]$sb.AppendLine("<h2>Overview</h2>")
[void]$sb.AppendLine("<div class='kv'>")
[void]$sb.AppendLine("<div class='k'>SQL Instance</div><div>$([System.Net.WebUtility]::HtmlEncode($SqlInstance))</div>")
[void]$sb.AppendLine("<div class='k'>Edition</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.Edition))</div>")
[void]$sb.AppendLine("<div class='k'>Version</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.ProductVersion))</div>")
[void]$sb.AppendLine("<div class='k'>Patch Level</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.ProductLevel)) / $([System.Net.WebUtility]::HtmlEncode([string]$sp.ProductUpdateLevel))</div>")
[void]$sb.AppendLine("<div class='k'>Clustered</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.IsClustered))</div>")
[void]$sb.AppendLine("<div class='k'>Always On Enabled</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$sp.IsHadrEnabled))</div>")
[void]$sb.AppendLine("<div class='k'>OS</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$os.Caption)) $([System.Net.WebUtility]::HtmlEncode([string]$os.Version))</div>")
[void]$sb.AppendLine("<div class='k'>Physical Memory</div><div>$([System.Net.WebUtility]::HtmlEncode((Convert-BytesToNiceString $comp.TotalPhysicalMemory)))</div>")
[void]$sb.AppendLine("<div class='k'>CPU</div><div>$([System.Net.WebUtility]::HtmlEncode((($proc | Select-Object -First 1).Name)))</div>")
[void]$sb.AppendLine("<div class='k'>SQL Start Time</div><div>$([System.Net.WebUtility]::HtmlEncode([string]$si.sqlserver_start_time))</div>")
[void]$sb.AppendLine("</div>")
[void]$sb.AppendLine("</div>")

# findings
[void]$sb.AppendLine("<div class='section'>")
[void]$sb.AppendLine("<h2>Top Findings</h2>")
if ($topWarnings.Count -gt 0) {
    [void]$sb.AppendLine((Convert-TableToHtml -Data $topWarnings -Columns @('Category','Metric','Value','Status','Notes')))
} else {
    [void]$sb.AppendLine("<div class='empty'>No warnings or failures were detected by the built-in light rules.</div>")
}
[void]$sb.AppendLine("</div>")

# compact summaries
[void]$sb.AppendLine("<div class='section'>")
[void]$sb.AppendLine("<h2>Key Summaries</h2>")
[void]$sb.AppendLine((Convert-TableToHtml -Data ($dbSummary | Select-Object -First $TopDbRows) -Title 'Databases' -Columns @('Database','State','RecoveryModel','CompatibilityLevel','DataSizeMB','LogSizeMB','AutoClose','AutoShrink','Encrypted','Trustworthy','QueryStore','RCSI','PageVerify')))
[void]$sb.AppendLine((Convert-TableToHtml -Data ($fileSummary | Select-Object -First 50) -Title 'Files' -Columns @('Database','Type','LogicalName','SizeMB','MaxSizeMB','PercentGrowth','MountPoint','VolumeFreeGB','PhysicalName')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $backupSummary -Title 'Backups' -Columns @('Database','RecoveryModel','LastFull','LastDiff','LastLog')))
[void]$sb.AppendLine((Convert-TableToHtml -Data ($jobSummary | Select-Object -First 50) -Title 'SQL Agent Jobs' -Columns @('JobName','Enabled','Owner','Category','HasSchedule','LastStatus','LastRun')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $loginSummary -Title 'Logins' -Columns @('Name','Type','Disabled','DefaultDatabase','CreateDate')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $driveSummary -Title 'Windows Volumes' -Columns @('Drive','Label','FileSystem','Size','Free','FreePct')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $tempdbSummary -Title 'TempDB Files' -Columns @('Name','Type','SizeMB','Growth','PercentGrowth','PhysicalName')))
[void]$sb.AppendLine((Convert-TableToHtml -Data $waitSummary -Title 'Top Wait Stats' -Columns @('WaitType','WaitingTasks','WaitTimeMs','MaxWaitTimeMs','SignalWaitTimeMs')))
if ($agSummary.Count -gt 0) {
    [void]$sb.AppendLine((Convert-TableToHtml -Data $agSummary -Title 'Availability Groups' -Columns @('AGName','ClusterType','BackupPreference','ReplicaServer','AvailabilityMode','FailoverMode','SeedingMode','SecondaryConnections')))
}
[void]$sb.AppendLine("</div>")

# detailed sections by category
foreach ($group in $metricsByCategory) {
    [void]$sb.AppendLine("<div class='section'>")
    [void]$sb.AppendLine("<h2>$([System.Net.WebUtility]::HtmlEncode($group.Name))</h2>")
    [void]$sb.AppendLine("<div class='small'><span class='badge'>$($group.Count) metrics</span></div>")
    [void]$sb.AppendLine((Convert-TableToHtml -Data ($group.Group | Sort-Object Metric) -Columns @('Metric','Value','Status','Notes')))
    [void]$sb.AppendLine("</div>")
}

[void]$sb.AppendLine("<div class='footer'>Generated by Invoke-SqlServerNewInstallAudit.ps1</div>")
[void]$sb.AppendLine("</div></body></html>")

# Write output
$outDir = Split-Path -Path $OutputPath -Parent
if ($outDir -and -not (Test-Path $outDir)) {
    New-Item -Path $outDir -ItemType Directory -Force | Out-Null
}
$sb.ToString() | Set-Content -Path $OutputPath -Encoding UTF8

Write-Log "Audit complete. HTML report written to: $OutputPath"
#endregion HTML generation