
Applies to any Windows server estate. The script runs in Windows PowerShell 5.1 or PowerShell 7 and stores its history in SQL Server 2019, 2022 or 2025.
An availability report answers two questions: is the server up right now, and how often has it been up over the last day, week, month and year? Mine pings every server every five minutes, records the result and the response time, and turns that history into uptime percentages.
It’s the simplest report in the set, and the one managers read most. “99.98% over 30 days” is a sentence people understand.
What the report shows
- Status. UP or DOWN, from the latest ping.
- Response time. The latest ping in milliseconds, and the average and worst over the last 24 hours.
- Uptime. The percentage of successful pings over the previous day, 7 days, 30 days and 365 days.
- Last checked. When the server was last pinged, so you can tell a down server from a broken monitoring job.
Servers that are down sort to the top, then the ones with the worst 30-day figure.
Why it matters
Most outages get noticed. The ones that don’t are the short ones: a reboot at 3am that nobody scheduled, a host that drops off the network for two minutes every night during the backup window. Those show up as a 99.6% here, and that’s your cue to go and look.
The response time is the early warning. A server that normally answers in 1 ms and now takes 40 ms is telling you about a busy host, a saturated link or a network change long before an application times out.
It also gives you evidence. When someone says “the database was down all morning”, a ping history that shows the server answering every five minutes moves the conversation on quickly.
The script
It creates dbo.ServerPingHistory the first time it runs, pings each server once, stores the result (with the error text when it fails), trims history older than 400 days and prints the uptime table.
# Invoke-ServerPing.ps1
# Pings every server in the list, stores up/down and response time, and shows
# uptime for the last day, 7, 30 and 365 days. Schedule it every 5 minutes.
# ---- Settings ---------------------------------------------------------------
$ServerListPath = 'C:\DBA\Servers.txt'
$InventoryServer = 'SQLMON01' # the instance that holds the DBA database
$InventoryDatabase = 'DBA'
$KeepDays = 400 # history kept for the 365-day figure
# -----------------------------------------------------------------------------
. "$PSScriptRoot\DbaReportHelpers.ps1"
$Servers = Get-ServerList -Path $ServerListPath
$Repo = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase
try {
Invoke-SqlNonQuery $Repo @"
IF OBJECT_ID('dbo.ServerPingHistory', 'U') IS NULL
BEGIN
CREATE TABLE dbo.ServerPingHistory
(
PingHistoryID BIGINT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_ServerPingHistory PRIMARY KEY,
ServerName NVARCHAR(128) NOT NULL,
CheckDateTime DATETIME2(0) NOT NULL,
IsOnline BIT NOT NULL,
ResponseTimeMS INT NULL,
FailureReason NVARCHAR(1000) NULL
);
CREATE INDEX IX_ServerPingHistory_ServerName_CheckDateTime
ON dbo.ServerPingHistory (ServerName, CheckDateTime)
INCLUDE (IsOnline, ResponseTimeMS);
END;
"@
foreach ($Server in $Servers) {
$Row = [ordered]@{
ServerName = $Server
CheckDateTime = Get-Date
IsOnline = $false
ResponseTimeMS = $null
FailureReason = $null
}
try {
$Ping = Test-Connection -ComputerName (Get-ComputerName $Server) -Count 1 -ErrorAction Stop
# PowerShell 7 returns a Status instead of throwing on some failures
if ($Ping.PSObject.Properties['Status'] -and "$($Ping.Status)" -ne 'Success') {
throw "Ping status: $($Ping.Status)"
}
$Row.IsOnline = $true
# Windows PowerShell 5.1 calls it ResponseTime; PowerShell 7 calls it Latency
$Row.ResponseTimeMS = if ($null -ne $Ping.ResponseTime) { [int]$Ping.ResponseTime } else { [int]$Ping.Latency }
Write-Host "$Server is UP - $($Row.ResponseTimeMS) ms"
}
catch {
$Row.FailureReason = $_.Exception.Message
Write-Warning "$Server is DOWN - $($Row.FailureReason)"
}
Add-InventoryRow -Connection $Repo -Table 'dbo.ServerPingHistory' -Row $Row
}
# Keep the table to a sensible size
Invoke-SqlNonQuery $Repo "DELETE FROM dbo.ServerPingHistory WHERE CheckDateTime < DATEADD(DAY, -$KeepDays, SYSDATETIME());"
# Uptime report
$Report = Invoke-SqlQuery $Repo @"
WITH Latest AS
(
SELECT ServerName, IsOnline, ResponseTimeMS, CheckDateTime,
ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CheckDateTime DESC) AS RN
FROM dbo.ServerPingHistory
),
Stats AS
(
SELECT
ServerName,
CAST(100.0 * SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -1, SYSDATETIME()) AND IsOnline = 1 THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -1, SYSDATETIME()) THEN 1 END), 0) AS DECIMAL(9,3)) AS Uptime1Day,
CAST(100.0 * SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -7, SYSDATETIME()) AND IsOnline = 1 THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -7, SYSDATETIME()) THEN 1 END), 0) AS DECIMAL(9,3)) AS Uptime7Days,
CAST(100.0 * SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -30, SYSDATETIME()) AND IsOnline = 1 THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -30, SYSDATETIME()) THEN 1 END), 0) AS DECIMAL(9,3)) AS Uptime30Days,
CAST(100.0 * SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -365, SYSDATETIME()) AND IsOnline = 1 THEN 1 ELSE 0 END)
/ NULLIF(SUM(CASE WHEN CheckDateTime >= DATEADD(DAY, -365, SYSDATETIME()) THEN 1 END), 0) AS DECIMAL(9,3)) AS Uptime365Days,
AVG(CASE WHEN CheckDateTime >= DATEADD(DAY, -1, SYSDATETIME()) THEN ResponseTimeMS END) AS AvgMS1Day,
MAX(CASE WHEN CheckDateTime >= DATEADD(DAY, -1, SYSDATETIME()) THEN ResponseTimeMS END) AS MaxMS1Day
FROM dbo.ServerPingHistory
GROUP BY ServerName
)
SELECT
S.ServerName,
CASE WHEN L.IsOnline = 1 THEN 'UP' ELSE 'DOWN' END AS Status,
L.ResponseTimeMS AS LastMS,
S.AvgMS1Day, S.MaxMS1Day,
S.Uptime1Day, S.Uptime7Days, S.Uptime30Days, S.Uptime365Days,
L.CheckDateTime AS LastChecked
FROM Stats AS S
JOIN Latest AS L ON L.ServerName = S.ServerName AND L.RN = 1
ORDER BY CASE WHEN L.IsOnline = 1 THEN 1 ELSE 0 END, S.Uptime30Days, S.ServerName;
"@
$Report | Format-Table -AutoSize
}
finally {
$Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script)
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
# . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.
function Get-ServerList {
# One server per line. Blank lines and anything after # are ignored,
# and duplicates (in any case) are removed.
param([Parameter(Mandatory)][string]$Path)
Get-Content -Path $Path |
ForEach-Object { ($_ -split '#')[0].Trim() } |
Where-Object { $_ } |
Sort-Object -Unique
}
function Get-ComputerName {
# SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
param([Parameter(Mandatory)][string]$Server)
($Server -split '\\')[0]
}
function Open-SqlConnection {
param(
[Parameter(Mandatory)][string]$Server,
[string]$Database = 'master'
)
$Conn = New-Object System.Data.SqlClient.SqlConnection
$Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
"TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
$Conn.Open()
$Conn
}
function Invoke-SqlQuery {
# Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
param($Connection, [string]$Sql, [hashtable]$Parameters = @{})
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
foreach ($Name in $Parameters.Keys) {
$null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
}
$Table = New-Object System.Data.DataTable
$null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
, $Table
}
function Invoke-SqlNonQuery {
param($Connection, [string]$Sql)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = $Sql
$Cmd.CommandTimeout = 300
$null = $Cmd.ExecuteNonQuery()
}
function Add-InventoryRow {
# Inserts one row from an ordered hashtable of column = value.
# Every value goes in as a parameter, never pasted into the SQL text.
param(
$Connection,
[Parameter(Mandatory)][string]$Table,
[Parameter(Mandatory)][System.Collections.IDictionary]$Row
)
$Columns = @($Row.Keys)
$Cmd = $Connection.CreateCommand()
$Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
(($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'
foreach ($Column in $Columns) {
$Value = $Row[$Column]
if ($null -eq $Value -or $Value -is [System.DBNull] -or
($Value -is [string] -and $Value.Trim() -eq '')) {
$Value = [System.DBNull]::Value
}
$null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
}
$null = $Cmd.ExecuteNonQuery()
}
Scheduling it
I run it from Task Scheduler on the DBA server every five minutes, under a service account. The action is:
powershell.exe -NoProfile -ExecutionPolicy Bypass -File "C:\DBA\Invoke-ServerPing.ps1"
Five minutes is a good balance. One sample is 0.35% of a day, so a single missed ping is visible but doesn’t wreck the monthly figure. If you need to prove a 99.99% service level, ping every minute: at five-minute intervals one missed ping a month already costs you 0.01%.
How to read it
The percentages count samples, not minutes, so they’re only as good as the schedule. If the task didn’t run for a day, that day simply has no samples, and the uptime looks fine. That’s why the report shows LastChecked: if it’s more than ten minutes old, the problem is the monitoring, not the server.
A server that’s UP with a poor 7-day figure is usually rebooting on its own. Check the System event log for event 6008 (unexpected shutdown) and 1074 (who restarted it).
Watch out for
- Ping isn’t SQL Server. A server can answer pings with the SQL Server service stopped. Pair this with a connection test, such as the inventory or database reports, which fail loudly when SQL Server doesn’t answer.
- Firewalls. ICMP echo is often blocked between network zones. A server that’s DOWN from the very first run probably isn’t down: allow ping from the monitoring server, or it’ll pull the averages down forever.
- Clusters and listeners. Ping the node names and the cluster or listener name separately. A failover shows as a node going down while the listener stays up, which is the whole point.
- Retired servers. Removing a server from the list stops new pings, but its history stays until it ages out, so it lingers on the report with an old LastChecked. Delete its rows when it’s decommissioned.
- PowerShell 7 behaves differently.
Test-ConnectionreturnsLatencyinstead ofResponseTime, and reports some failures as a status instead of an error. The script handles both.
Where it fits
This is part of my Auditing section, with the licensing, inventory, disk space, database, backup and SSIS reports. They share one server list and one DBA database. The SQL Server DBA Handbook has the rest of my day-to-day checks.
The other Auditing reports: Licensing | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors
Clive Roberts DBA
2 thoughts on “Server Availability Report: Ping and Response Time”
Comments are closed.