SQL Server Health Checks or any report with PowerShell Scripts

Here is another good PowerShell script that can make a SQL Admin’s life easier. This script can be used to check the health of SQL servers and retrieve information about them. The collected data will be saved in both CSV and HTML formats. Additionally, an error log file is generated, allowing you to manually review any failed servers.

To implement this, follow these steps:

Create a folder named “CommandTree” in the C:\ drive of the server.
Place the “ServerList.txt” and “TsqlCommand.sql” files in the CommandTree folder.
The PowerShell script will connect to each server listed in the ServerList.txt file and execute the T-SQL command provided in the TsqlCommand.sql file.
The output files (CSV and HTML formats) will be generated in the same location.

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103# Define paths on WINSRVR1
$basePath = "C:\commandTree"
$sqlCommandPath = "$basePath\tsqlcommand.sql"
$serverListPath = "$basePath\serverlist.txt"
$logPath = "$basePath\connectionlog.txt"  # Renamed from errorLogPath to logPath
$consolidatedCsvPath = "$basePath\consolidated_results.csv"
$consolidatedHtmlPath = "$basePath\consolidated_results.html"

# Function to log messages
function Write-Log {
    param (
        [string]$Message,
        [string]$Status
    )
    $logMessage = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - $Status - $Message"
    Add-Content -Path $logPath -Value $logMessage
    if ($Status -eq "Error") {
        Write-Host $logMessage -ForegroundColor Red
    } else {
        Write-Host $logMessage -ForegroundColor Green
    }
}

# Function to connect, execute query, and return results
function Get-SqlQueryResults {
    param(
        [string] $ServerInstance
    )
    try {
        # Read SQL command
        $sql = Get-Content $sqlCommandPath -Raw
        
        # Execute query and capture results
        $results = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $sql
        
        # Add a column for the server name
        $results | Add-Member -MemberType NoteProperty -Name "ServerName" -Value $ServerInstance -Force
        
        Write-Log -Message "Successfully processed $ServerInstance" -Status "Success"
        return $results
    }
    catch {
        $errorMessage = $_.Exception.Message
        Write-Log -Message "Error processing $($ServerInstance): $errorMessage" -Status "Error"
        return $null
    }
}

# Main script execution
try {
    # Check if required files exist
    if (-not (Test-Path $sqlCommandPath) -or -not (Test-Path $serverListPath)) {
        throw "Required files not found. Please ensure tsqlcommand.sql and serverlist.txt exist in $basePath"
    }

    # Read server list
    $servers = Get-Content $serverListPath

    # Initialize an array to store all results
    $allResults = @()

    # Process each server
    foreach ($server in $servers) {
        Write-Host "Processing $server..." -ForegroundColor Cyan
        $serverResults = Get-SqlQueryResults -ServerInstance $server
        if ($serverResults) {
            $allResults += $serverResults
        }
    }

    # Save consolidated results to CSV
    if ($allResults.Count -gt 0) {
        $allResults | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Csv -NoTypeInformation -Path $consolidatedCsvPath
        Write-Log -Message "Consolidated CSV results saved to $consolidatedCsvPath" -Status "Success"

        # Generate consolidated HTML report
        $htmlHeader = @"
        
        
            
        
        
        Consolidated Results for All Servers
"@
        $htmlFooter = ""
        $htmlBody = $allResults | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | ConvertTo-Html -Fragment
        $htmlHeader + $htmlBody + $htmlFooter | Out-File -FilePath $consolidatedHtmlPath -Encoding UTF8
        Write-Log -Message "Consolidated HTML results saved to $consolidatedHtmlPath" -Status "Success"
    } else {
        Write-Log -Message "No results to save." -Status "Warning"
    }

    Write-Log -Message "Script completed. Consolidated results saved to CSV and HTML files in $basePath on WINSRVR1." -Status "Success"
}
catch {
    Write-Log -Message "An error occurred: $_" -Status "Error"
}

Results shown as below in the server.

If the above code is not copied / not worked you can use below one.

# Define paths on WINSRVR1

$basePath = "C:\commandTree"

$sqlCommandPath = "$basePath\tsqlcommand.sql"

$serverListPath = "$basePath\serverlist.txt"

$logPath = "$basePath\connectionlog.txt" # Renamed from errorLogPath to logPath

$consolidatedCsvPath = "$basePath\consolidated_results.csv"

$consolidatedHtmlPath = "$basePath\consolidated_results.html"

# Function to log messages

function Write-Log {

param (

[string]$Message,

[string]$Status

)

$logMessage = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') - $Status - $Message"

Add-Content -Path $logPath -Value $logMessage

if ($Status -eq "Error") {

Write-Host $logMessage -ForegroundColor Red

} else {

Write-Host $logMessage -ForegroundColor Green

}

# Function to connect, execute query, and return results

function Get-SqlQueryResults {

param(

[string] $ServerInstance

)

try {

# Read SQL command

$sql = Get-Content $sqlCommandPath -Raw

# Execute query and capture results

$results = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $sql

# Add a column for the server name

$results | Add-Member -MemberType NoteProperty -Name "ServerName" -Value $ServerInstance -Force

Write-Log -Message "Successfully processed $ServerInstance" -Status "Success"

return $results

}

catch {

$errorMessage = $_.Exception.Message

Write-Log -Message "Error processing $($ServerInstance): $errorMessage" -Status "Error"

return $null

}

# Main script execution

try {

# Check if required files exist

if (-not (Test-Path $sqlCommandPath) -or -not (Test-Path $serverListPath)) {

throw "Required files not found. Please ensure tsqlcommand.sql and serverlist.txt exist in $basePath"

}

# Read server list

$servers = Get-Content $serverListPath

# Initialize an array to store all results

$allResults = @()

# Process each server

foreach ($server in $servers) {

Write-Host "Processing $server..." -ForegroundColor Cyan

$serverResults = Get-SqlQueryResults -ServerInstance $server

if ($serverResults) {

$allResults += $serverResults

}

# Save consolidated results to CSV

if ($allResults.Count -gt 0) {

$allResults | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | Export-Csv -NoTypeInformation -Path $consolidatedCsvPath

Write-Log -Message "Consolidated CSV results saved to $consolidatedCsvPath" -Status "Success"

# Generate consolidated HTML report

$htmlHeader = @"

<html>

<head>

<style>

table { border-collapse: collapse; width: 100%; }

th, td { border: 1px solid black; padding: 8px; text-align: left; }

th { background-color: #f2f2f2; }

</style>

</head>

<body>