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.
[code type="SQL"]
# 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"
}
[/code]
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>
<h2>Consolidated Results for All Servers</h2>
"@
$htmlFooter = "</body></html>"
$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"
}
-- We are done.
