Server Availability Report: Ping and Response Time

SQL Server Auditing: Server Availability Report: Ping and Response Time

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-Connection returns Latency instead of ResponseTime, 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