Below is the Disk information.
<#
.SYNOPSIS
Collects drive space details from a list of Windows servers and emails an HTML report.
#>
# ---- CONFIG ----
$serverListPath = "C:\Scripts\servers.txt"
$servers = Get-Content $serverListPath |
Where-Object { $_.Trim() -ne "" }
$smtpServer = "smtp.yourcompany.com"
$from = "monitoring@yourcompany.com"
$to = "you@yourcompany.com"
$subject = "Windows Servers - Drive Space Report - $(Get-Date -Format 'yyyy-MM-dd')"
$freeThreshold = 20 # percent free below which status = Check Now
# ---- COLLECT DATA ----
$results = foreach ($server in $servers) {
try {
$disks = Get-CimInstance -ClassName Win32_LogicalDisk -ComputerName $server -Filter "DriveType=3" -ErrorAction Stop
foreach ($disk in $disks) {
$sizeGB = [math]::Round($disk.Size / 1GB, 2)
$freeGB = [math]::Round($disk.FreeSpace / 1GB, 2)
$usedGB = [math]::Round($sizeGB - $freeGB, 2)
$pctFree = if ($sizeGB -gt 0) { [math]::Round(($freeGB / $sizeGB) * 100, 1) } else { 0 }
$status = if ($pctFree -lt $freeThreshold) { "Check Now" } else { "Healthy" }
[PSCustomObject]@{
Server = $server
DeviceID = $disk.DeviceID
SizeGB = $sizeGB
UsedGB = $usedGB
FreeGB = $freeGB
PercentFree = $pctFree
Status = $status
}
}
}
catch {
[PSCustomObject]@{
Server = $server
DeviceID = "-"
SizeGB = $null
UsedGB = $null
FreeGB = $null
PercentFree = $null
Status = "Server not reachable"
}
}
}
# ---- BUILD HTML OUTPUT WITH INLINE STYLES ----
$rows = foreach ($r in $results) {
$statusStyle = switch ($r.Status) {
"Healthy" { "color: #2e7d32; font-weight: bold;" }
"Check Now" { "color: #c62828; font-weight: bold;" }
"Server not reachable" { "color: #c62828; font-weight: bold; background-color: #ffebee;" }
default { "" }
}
"<tr>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($r.Server)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($r.DeviceID)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($r.SizeGB)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($r.UsedGB)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($r.FreeGB)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($r.PercentFree)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; $statusStyle'>$($r.Status)</td>
</tr>"
}
$html = @"
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
</head>
<body style="margin: 0; padding: 20px; background-color: #f4f6f8; font-family: 'Segoe UI', Arial, sans-serif;">
<!-- Main Container Table (1100px Width) -->
<table align="center" width="1100" border="0" cellpadding="0" cellspacing="0" style="width: 1100px; margin: 0 auto;">
<!-- HEADER SECTION (BLUE BACKGROUND) -->
<tr>
<td style="background-color: #1e40af; border-radius: 8px; padding: 18px 20px; text-align: center;">
<h2 style="color: #ffffff; margin: 0; font-size: 18px; font-weight: 600;">
Windows Server Drive Space Report - $(Get-Date -Format 'yyyy-MM-dd')
</h2>
</td>
</tr>
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- RESULTS TABLE SECTION -->
<tr>
<td align="center">
<table width="100%" border="0" cellpadding="0" cellspacing="0" style="width: 100%; border-collapse: collapse; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
<thead>
<!-- TABLE HEADER (BLUE BACKGROUND) -->
<tr style="background-color: #1e3a8a; color: #ffffff;">
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">Server</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">DeviceID</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">Size (GB)</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">Used (GB)</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">Free (GB)</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">% Free</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">Status</th>
</tr>
</thead>
<tbody>
$($rows -join "`n")
</tbody>
</table>
</td>
</tr>
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- SEPARATE AUTOMATED DISCLAIMER BOX -->
<tr>
<td style="background-color: #eff6ff; border: 1px solid #bfdbfe; border-left: 4px solid #1e40af; border-radius: 6px; padding: 10px 14px; text-align: center;">
<span style="font-size: 12px; color: #1e40af; font-weight: 600;">
Note: This is an automated report do not reply to this email.
</span>
</td>
</tr>
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- SIGNATURE SECTION (BLUE BACKGROUND) -->
<tr>
<td style="background-color: #1e40af; border-radius: 8px; padding: 14px 20px; text-align: left;">
<p style="color: #ffffff; margin: 0; font-size: 13px; line-height: 1.5;">
Regards,<br>
<strong style="color: #ffffff; font-size: 14px;">SQL DBA Team</strong><br>
<span style="color: #dbeafe;">Database Administration</span>
</p>
</td>
</tr>
</table>
</body>
</html>
"@
# ---- SEND EMAIL ----
Send-MailMessage -From $from -To $to -Subject $subject -Body $html -BodyAsHtml -SmtpServer $smtpServer
Below is the Backup information.
# ==========================================
# Configuration Parameters
# ==========================================
$ServerListFile = "C:\Data\Sqlserver.txt"
$SmtpServer = "smtp.yourcompany.com"
$SmtpPort = 25
$EmailFrom = "sqlmonitoring@yourcompany.com"
$EmailTo = "dba-team@yourcompany.com"
$EmailSubject = "SQL Backup Monitoring Report - $(Get-Date -Format 'dd-MMM-yyyy')"
$StartTime = Get-Date
# ==========================================
# SQL Query to Fetch Backup History
# ==========================================
$SqlQuery = @"
SELECT
d.name AS DatabaseName,
d.database_id,
CASE WHEN d.name IN ('master', 'model', 'msdb') THEN 'System DB' ELSE 'User DB' END AS DBType,
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name <> 'tempdb' AND d.state_desc = 'ONLINE'
GROUP BY d.name, d.database_id;
"@
# Data Collectors
$ServerResults = @()
$ExceptionList = @()
$TotalServers = 0
$ServersHealthy = 0
$ServersFailed = 0
$ServersUnreachable = 0
if (-not (Test-Path $ServerListFile)) {
Write-Error "Server list file not found at $ServerListFile"
exit
}
$SqlServerList = Get-Content $ServerListFile | Where-Object { $_ -and -not $_.StartsWith("#") }
foreach ($ServerInput in $SqlServerList) {
$ServerInput = $ServerInput.Trim()
$TotalServers++
$ServerHasIssues = $false
$ServerExceptionsCount = 0
$DisplayName = $ServerInput
try {
$ConnectionString = "Server=$ServerInput;Database=msdb;Integrated Security=SSPI;Connection Timeout=10;"
$Connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$Connection.Open()
$CmdInstance = $Connection.CreateCommand()
$CmdInstance.CommandText = "SELECT @@SERVERNAME AS SqlInstanceName;"
$DisplayName = $CmdInstance.ExecuteScalar()
$Command = $Connection.CreateCommand()
$Command.CommandText = $SqlQuery
$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter($Command)
$DataTable = New-Object System.Data.DataTable
[void]$Adapter.Fill($DataTable)
$Connection.Close()
foreach ($Row in $DataTable) {
$DbName = $Row.DatabaseName
$DbType = $Row.DBType
$LastFull = if ($Row.LastFullBackup -ne [DBNull]::Value) { [datetime]$Row.LastFullBackup } else { $null }
$LastDiff = if ($Row.LastDiffBackup -ne [DBNull]::Value) { [datetime]$Row.LastDiffBackup } else { $null }
$FullDaysOld = if ($LastFull) { ($StartTime - $LastFull).TotalDays } else { 9999 }
$DiffHoursOld = if ($LastDiff) { ($StartTime - $LastDiff).TotalHours } else { 9999 }
$IssueDescription = $null
if ($DbType -eq 'System DB') {
if ($FullDaysOld -gt 7) {
$IssueDescription = if ($null -eq $LastFull) { "Full backup NEVER performed" } else { "Full backup overdue (> 7 days)" }
}
} else {
if ($FullDaysOld -gt 7 -and $DiffHoursOld -gt 24) {
$IssueDescription = "Full (> 7 days) and Differential (> 24 hours) backups overdue"
} elseif ($FullDaysOld -gt 7) {
$IssueDescription = "Full backup overdue (> 7 days)"
} elseif ($DiffHoursOld -gt 24) {
$IssueDescription = "Differential backup overdue (> 24 hours)"
}
}
if ($IssueDescription) {
$ServerHasIssues = $true
$ServerExceptionsCount++
$ExceptionList += [PSCustomObject]@{
Server = $DisplayName
Database = $DbName
Type = $DbType
IssueDescription = $IssueDescription
LastFull = if ($LastFull) { $LastFull.ToString("dd-MMM HH:mm") } else { "NEVER" }
LastDiff = if ($DbType -eq 'System DB') { "N/A" } else { if ($LastDiff) { $LastDiff.ToString("dd-MMM HH:mm") } else { "NEVER" } }
}
}
}
if ($ServerHasIssues) {
$ServersFailed++
$ServerResults += [PSCustomObject]@{
ServerName = $DisplayName
Status = "ISSUES FOUND"
Details = "<span style='color: #c62828; font-weight: bold;'>$ServerExceptionsCount database(s) failing backup SLA</span>"
}
} else {
$ServersHealthy++
$ServerResults += [PSCustomObject]@{
ServerName = $DisplayName
Status = "ALL DB'S FINE"
Details = "<span style='color: #2e7d32;'>All databases met backup SLAs</span>"
}
}
} catch {
$ServersUnreachable++
$ErrorMessage = $_.Exception.Message.Trim()
$ServerResults += [PSCustomObject]@{
ServerName = "$ServerInput (Unreachable)"
Status = "CHECK FAILED"
Details = "<div style='color: #c62828; font-weight: bold;'>Connection / Execution Failure</div><div style='background-color: #ffebee; color: #c62828; font-family: Consolas, monospace; font-size: 12px; padding: 6px 10px; border-left: 3px solid #c62828; margin-top: 4px;'>$ErrorMessage</div>"
}
}
}
$EndTime = Get-Date
$Duration = "{0:hh}:{0:mm}:{0:ss}" -f ($EndTime - $StartTime)
$HostName = $env:COMPUTERNAME
# ==========================================
# HTML Email Generation (Matching 1100px Layout)
# ==========================================
$OverviewRows = foreach ($res in $ServerResults) {
$statusStyle = switch ($res.Status) {
"ALL DB'S FINE" { "color: #2e7d32; font-weight: bold;" }
"ISSUES FOUND" { "color: #c62828; font-weight: bold;" }
"CHECK FAILED" { "color: #c62828; font-weight: bold; background-color: #ffebee;" }
}
"<tr>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'><b>$($res.ServerName)</b></td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; $statusStyle'>$($res.Status)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($res.Details)</td>
</tr>"
}
$ExceptionSection = ""
if ($ExceptionList.Count -gt 0) {
$ExRows = foreach ($ex in $ExceptionList) {
"<tr>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'><b>$($ex.Server)</b></td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($ex.Database)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($ex.Type)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #c62828; font-weight: bold;'>$($ex.IssueDescription)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($ex.LastFull)</td>
<td style='border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;'>$($ex.LastDiff)</td>
</tr>"
}
$ExceptionSection = @"
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- DATABASE EXCEPTIONS SECTION -->
<tr>
<td align="center">
<table width="100%" border="0" cellpadding="0" cellspacing="0" style="width: 100%; border-collapse: collapse; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
<thead>
<tr style="background-color: #1e40af; color: #ffffff;">
<th colspan="6" style="padding: 8px 12px; text-align: left; font-size: 13px; font-weight: 600; background-color: #1e40af;">Database Backup Exceptions</th>
</tr>
<tr style="background-color: #1e3a8a; color: #ffffff;">
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 18%;">Server</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 18%;">Database</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 12%;">Type</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 34%;">Issue Description</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 9%;">Last Full</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 9%;">Last Diff</th>
</tr>
</thead>
<tbody>
$($ExRows -join "`n")
</tbody>
</table>
</td>
</tr>
"@
}
$HtmlBody = @"
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
</head>
<body style="margin: 0; padding: 20px; background-color: #f4f6f8; font-family: 'Segoe UI', Arial, sans-serif;">
<!-- Main Container Table (1100px Width) -->
<table align="center" width="1100" border="0" cellpadding="0" cellspacing="0" style="width: 1100px; margin: 0 auto;">
<!-- HEADER SECTION (BLUE BACKGROUND) -->
<tr>
<td style="background-color: #1e40af; border-radius: 8px; padding: 18px 20px; text-align: center;">
<h2 style="color: #ffffff; margin: 0; font-size: 18px; font-weight: 600;">
SQL Server Backup Monitoring Report - $(Get-Date -Format 'yyyy-MM-dd')
</h2>
</td>
</tr>
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- EXECUTION SUMMARY SECTION -->
<tr>
<td align="center">
<table width="100%" border="0" cellpadding="0" cellspacing="0" style="width: 100%; border-collapse: collapse; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
<thead>
<tr style="background-color: #1e40af; color: #ffffff;">
<th colspan="2" style="padding: 8px 12px; text-align: left; font-size: 13px; font-weight: 600; background-color: #1e40af;">Execution Summary</th>
</tr>
</thead>
<tbody>
<tr>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333; width: 40%;">Monitoring Host Name</td>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333; font-weight: bold;">$HostName</td>
</tr>
<tr>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;">Script Execution Duration</td>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333; font-weight: bold;">$Duration</td>
</tr>
<tr>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;">Total SQL Servers Checked</td>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333; font-weight: bold;">$TotalServers</td>
</tr>
<tr>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;">Servers - All DBs Healthy</td>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #2e7d32; font-weight: bold;">$ServersHealthy</td>
</tr>
<tr>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;">Servers - Backup Issues Found</td>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #c62828; font-weight: bold;">$ServersFailed</td>
</tr>
<tr>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;">Servers - Connection / Check Failed</td>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #c62828; font-weight: bold;">$ServersUnreachable</td>
</tr>
<tr>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #333333;">Total Database Backup Exceptions</td>
<td style="border: 1px solid #e0e0e0; padding: 8px 12px; font-size: 13px; color: #c62828; font-weight: bold;">$($ExceptionList.Count)</td>
</tr>
</tbody>
</table>
</td>
</tr>
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- SERVER STATUS OVERVIEW SECTION -->
<tr>
<td align="center">
<table width="100%" border="0" cellpadding="0" cellspacing="0" style="width: 100%; border-collapse: collapse; background-color: #ffffff; border-radius: 8px; overflow: hidden; box-shadow: 0 1px 3px rgba(0,0,0,0.1);">
<thead>
<tr style="background-color: #1e40af; color: #ffffff;">
<th colspan="3" style="padding: 8px 12px; text-align: left; font-size: 13px; font-weight: 600; background-color: #1e40af;">Server Status Overview</th>
</tr>
<tr style="background-color: #1e3a8a; color: #ffffff;">
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 25%;">Server Name</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600; width: 20%;">Status</th>
<th style="padding: 10px 12px; text-align: left; font-size: 12px; font-weight: 600;">Details</th>
</tr>
</thead>
<tbody>
$($OverviewRows -join "`n")
</tbody>
</table>
</td>
</tr>
$ExceptionSection
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- SEPARATE AUTOMATED DISCLAIMER BOX -->
<tr>
<td style="background-color: #eff6ff; border: 1px solid #bfdbfe; border-left: 4px solid #1e40af; border-radius: 6px; padding: 10px 14px; text-align: center;">
<span style="font-size: 12px; color: #1e40af; font-weight: 600;">
Note: This is an automated report do not reply to this email.
</span>
</td>
</tr>
<!-- SPACER -->
<tr><td height="16" style="height: 16px;"></td></tr>
<!-- SIGNATURE SECTION (BLUE BACKGROUND) -->
<tr>
<td style="background-color: #1e40af; border-radius: 8px; padding: 14px 20px; text-align: left;">
<p style="color: #ffffff; margin: 0; font-size: 13px; line-height: 1.5;">
Regards,<br>
<strong style="color: #ffffff; font-size: 14px;">SQL DBA Team</strong><br>
<span style="color: #dbeafe;">Database Administration</span>
</p>
</td>
</tr>
</table>
</body>
</html>
"@
# Send Email
Send-MailMessage -SmtpServer $SmtpServer `
-Port $SmtpPort `
-From $EmailFrom `
-To $EmailTo `
-Subject $EmailSubject `
-Body $HtmlBody `
-BodyAsHtml
Post a Comment