Look behind the latest heartbeat and the earlier silence becomes visible. Finding missing check-ins requires both historical gap detection and a current silence check. Use a consistent UTC clock, a documented cadence, and enough tolerance to separate a missed signal from ordinary scheduling variation.
Define the Signal Before Finding Missing Check-Ins
A heartbeat records that a source checked in at a particular time. Monitoring expects another signal after the agreed cadence. A longer interval is evidence of missing check-ins, but it does not prove the exact instant the source failed or which component caused the gap.
I define cadence and tolerance before writing the alert. A source scheduled every minute still needs allowance for ordinary delay. The threshold belongs to the source's operational contract, not a universal number copied from another monitor.
Store UTC consistently and decide whether the timestamp describes the source clock or the server's receipt time. Source clocks can drift, while server receipt time can reflect delivery delays. Keeping both can explain those differences when the system requires it. The monitor needs a time contract. A timestamp wearing a UTC label is not automatically a synchronized clock.
Build the Ordered History per Source
LAG retrieves the previous timestamp within each source's ordered heartbeat history. Partition by the stable SourceID so one source cannot fill another source's gap. Duplicate timestamps should be deduplicated or given an explicit event-order rule before the gap analysis.
The sample uses synthetic UTC timestamps and a source registry containing a never-seen source. The DISTINCT step in the following query removes exact repeated check-ins before LAG. It does not hide out-of-order clock errors or merge different timestamps.
I keep the previous and next timestamps together in the output. The expected-next boundary is derived from the configured cadence, while the next observed check-in closes the detected gap. Those fields give the reader an honest absence interval. Calling the previous heartbeat the precise outage start would claim more than the signal establishes. The source was known alive then, but its next expected signal did not arrive on schedule.
CREATE TABLE #HeartbeatSources(SourceID int PRIMARY KEY);
CREATE TABLE #Heartbeats(SourceID int,ts datetime2);
INSERT #HeartbeatSources VALUES(1),(2),(3);
INSERT #Heartbeats VALUES
(1,'2026-09-24T08:00:00'),(1,'2026-09-24T08:01:00'),(1,'2026-09-24T08:05:00'),
(2,'2026-09-24T08:00:00'),(2,'2026-09-24T08:01:00');
DECLARE @CadenceSeconds int=60,@GapLimitSeconds int=135;
WITH UniqueSignals AS
(SELECT DISTINCT SourceID,ts FROM #Heartbeats),Previous AS
(
SELECT SourceID,ts,LAG(ts) OVER(PARTITION BY SourceID ORDER BY ts) AS PreviousTime
FROM UniqueSignals
)
SELECT SourceID,PreviousTime AS LastSeenBeforeGap,
DATEADD(second,@CadenceSeconds,PreviousTime) AS ExpectedNextSignal,
ts AS NextSeenAfterGap,DATEDIFF_BIG(second,PreviousTime,ts) AS GapSeconds
FROM Previous
WHERE DATEDIFF_BIG(second,PreviousTime,ts)>@GapLimitSeconds
ORDER BY SourceID,PreviousTime;Check Sources That Are Silent Right Now
A historical gap query needs a later row to close the interval. It cannot identify an ongoing silence by itself. Compare each source's latest timestamp with the report's as-of time to find that open-ended condition.
Start from the source registry, not from heartbeat rows alone. A source that never checked in has no MAX timestamp and would otherwise disappear from the report. The next query keeps those sources and labels them separately from sources with an old last observation.
What should count as silent before a newly registered source is expected to start? A production registry needs its activation and retirement rules. This sample assumes every registered source is currently expected. Keep those lifecycle dates in the real design so a future activation does not trigger a false outage. Also preserve the as-of timestamp in the result, because the current-silence classification changes as time advances.
DECLARE @AsOfUTC datetime2='2026-09-24T08:08:00',@SilenceLimitSeconds int=135;
SELECT s.SourceID,MAX(h.ts) AS LastSeenUTC,@AsOfUTC AS AsOfUTC,
CASE WHEN MAX(h.ts) IS NULL THEN 'No observed check-in' ELSE 'Silent beyond tolerance' END AS SignalStatus
FROM #HeartbeatSources AS s
LEFT JOIN #Heartbeats AS h ON h.SourceID=s.SourceID
GROUP BY s.SourceID
HAVING MAX(h.ts) IS NULL OR MAX(h.ts)<DATEADD(second,-@SilenceLimitSeconds,@AsOfUTC);
Treat Clock Errors as Data Exceptions
Clock drift can create apparent gaps or future timestamps. UTC removes timezone ambiguity, but it does not synchronize clocks by itself. Review the source's time synchronization and the receiving server's clock through the approved Windows process when timestamps disagree.
The next query finds rows beyond the chosen as-of point. The sample has none, so it returns an empty result. Those values deserve a clock or ingestion investigation instead of being allowed to suppress silence warnings indefinitely. Keep the raw timestamp and source identifier for that review.
DATEDIFF counts boundaries in the requested unit. A fine-grained threshold decision can instead compare the next timestamp directly with DATEADD of the allowed interval. Choose the comparison precision that matches the monitoring requirement. The displayed gap duration can still be useful for explanation. Do not let an apparently exact integer duration disguise an undefined tolerance or a source clock that was never trusted.
DECLARE @AsOfUTC datetime2='2026-09-24T08:08:00';
SELECT SourceID,ts FROM #Heartbeats WHERE ts>@AsOfUTC;Group Missing Check-Ins Without Losing Their Boundaries
Closed gaps and ongoing silence describe related but different states. Keep them distinguishable in a monitoring output. A source can recover after a gap and be healthy now, while another source remains silent without a closing row.
Repeated current-silence samples should update one active incident rather than create an unrelated incident every time the check runs. Use a stable source key and a defined opening and closing rule in the monitoring process. Keep the detected absence boundary, first alert time, and recovery observation separately when the operational record needs them.
Do not infer the root cause from the heartbeat alone. The source process, network delivery, and collector can each interrupt the signal. Compare related system evidence before calling it a device failure. A shared gap across several sources can direct that investigation, but correlation is still evidence to examine rather than a complete diagnosis.
Validate the Cadence and Support the Query
Test normal cadence, a delayed signal within tolerance, a closed gap, ongoing silence, duplicates, future timestamps, and a never-seen registered source. SQL Server 2012 and later support LAG for this pattern. Keep the synthetic test's as-of time fixed for reproducibility.
An index on SourceID and timestamp supports ordered history and latest-signal access. Review retention so the historical gap report covers the required period. If you filter a period before LAG, include the preceding signal needed to detect a gap crossing the period boundary.
Missing check-ins are useful operational evidence when the clock, cadence, and lifecycle rules are explicit. Combine LAG for closed gaps with a registry-based current-silence check. The result should explain which expected signal was absent and what remains unknown about the underlying failure.
Related reading on this blog: Find Missing Identity Values and Agent Jobs Running Longer Than Usual: Finding Them in Job History.
A missing heartbeat is not an exact failure time, it is evidence that an expected signal did not arrive.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
