DEV Community

greenmtnsun
greenmtnsun

Posted on

Version 2 of the Audit Script for SQL Server

#automation #database #sql #tooling

<#
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
Enter fullscreen mode Exit fullscreen mode

)

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
}
Enter fullscreen mode Exit fullscreen mode

}

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))
Enter fullscreen mode Exit fullscreen mode

}

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
})
Enter fullscreen mode Exit fullscreen mode

}

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' }
}
Enter fullscreen mode Exit fullscreen mode

}

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()
Enter fullscreen mode Exit fullscreen mode

}

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
}
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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)"
}
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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%"
}
Enter fullscreen mode Exit fullscreen mode

}

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)
}
Enter fullscreen mode Exit fullscreen mode

}

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
}
Enter fullscreen mode Exit fullscreen mode

}

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)
}
Enter fullscreen mode Exit fullscreen mode

}

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 @"
Enter fullscreen mode Exit fullscreen mode

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 @"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

}

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)
Enter fullscreen mode Exit fullscreen mode

}

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' }))
Enter fullscreen mode Exit fullscreen mode

}

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)
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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
}
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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
Enter fullscreen mode Exit fullscreen mode

}

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' })
Enter fullscreen mode Exit fullscreen mode

}

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)"
}
Enter fullscreen mode Exit fullscreen mode

}

Top comments (0)