Before you run
Data & scope
- Required tables
- Time window
- 24 hours of observed event timestamps
- SigninLogs, AuditLogs and AzureActivity must all exist and be queryable in the same workspace; Analytics plans must support the union and join.
- Microsoft Entra ID P1 or P2 for SigninLogs ingestion, Entra audit collection, and Azure Activity export from the investigated subscriptions.
- At least 24 hours of retained data; ingestion_time() must have been recorded for delay measurements, with missing values reported separately.
Documentation review by Codex (AI). No live-tenant execution or detection-performance claim. Validate schema, data coverage and expected results in your workspace.
// Hunting starter. Documentation checked 2026-11-13; not tenant-tested.
let observationEnd = now();
let observationStart = observationEnd - 1d;
let observed =
union
(SigninLogs
| where TimeGenerated >= observationStart and TimeGenerated <= observationEnd
| project LogSource = "SigninLogs", EventTime = TimeGenerated, IngestedAt = ingestion_time()),
(AuditLogs
| where TimeGenerated >= observationStart and TimeGenerated <= observationEnd
| project LogSource = "AuditLogs", EventTime = TimeGenerated, IngestedAt = ingestion_time()),
(AzureActivity
| where TimeGenerated >= observationStart and TimeGenerated <= observationEnd
| project LogSource = "AzureActivity", EventTime = TimeGenerated, IngestedAt = ingestion_time())
| extend LagSeconds = datetime_diff("second", IngestedAt, EventTime)
| extend ValidLagSeconds = iff(LagSeconds >= 0, LagSeconds, long(null))
| summarize ObservedRows = count(),
MissingIngestionTime = countif(isnull(IngestedAt)),
NegativeLagRows = countif(LagSeconds < 0),
P50LagSeconds = percentile(ValidLagSeconds, 50),
P95LagSeconds = percentile(ValidLagSeconds, 95),
MaxLagSeconds = max(ValidLagSeconds),
LastEventTime = max(EventTime), LastIngestionTime = max(IngestedAt)
by LogSource;
datatable(LogSource:string) ["SigninLogs", "AuditLogs", "AzureActivity"]
| join kind=leftouter observed on LogSource
| extend ObservedRows = coalesce(ObservedRows, 0),
MissingIngestionTime = coalesce(MissingIngestionTime, 0),
NegativeLagRows = coalesce(NegativeLagRows, 0)
| extend ObservationState = case(ObservedRows == 0, "No rows observed in event-time window",
MissingIngestionTime > 0, "Some ingestion timestamps unavailable",
NegativeLagRows > 0, "Negative delay requires timestamp investigation",
"Observed rows have nonnegative ingestion timestamps"),
MinutesSinceLastEvent = datetime_diff("minute", observationEnd, LastEventTime)
| project LogSource, ObservationState, ObservedRows, MissingIngestionTime,
NegativeLagRows, P50LagSeconds, P95LagSeconds, MaxLagSeconds,
LastEventTime, LastIngestionTime, MinutesSinceLastEvent
| order by LogSource asc
| take 200
Read-only query. Run in your own authorized workspace after checking prerequisites.
Run this diagnostic before interpreting an empty security hunt or tuning a correlation window. It compares observed event-to-ingestion delay across interactive sign-ins, directory audit and Azure Activity data. The result includes each expected table even when it contributes no rows in the selected interval.
It is not a connector availability monitor or a service-level objective. A table can receive healthy traffic from one tenant or subscription while silently missing another. The aggregate is a starting point for checking specific coverage.
Understand the timestamps
Each branch captures ingestion_time() while reading its source table. Microsoft describes that value as approximate and notes that it can be null when ingestion-time recording was unavailable. It cannot establish an exact ordering of ingestion operations. This query uses it only to estimate delay relative to TimeGenerated. Function semantics.
The scan covers records whose TimeGenerated lies in the last 24 hours. It is an event-time sample, not all records arriving in the last 24 hours. A very late record with an older event timestamp is excluded, and an event that has not arrived cannot contribute to any percentile. Recent measurements can therefore look better than the eventually complete population.
Negative delays are counted separately and excluded from the percentile input. Missing ingestion timestamps also stay visible. Neither condition becomes an artificial zero-second delivery. Investigate timestamp definitions, transformations and source clock behavior before deciding that a negative value indicates a platform defect.
P50 and P95 are estimates over nonnegative observed delays, while the maximum shows the largest retained delay. Their usefulness depends on sample volume and the selected interval. A percentile from a handful of records is weak evidence for choosing an operational threshold. Percentile behavior.
Separate quiet data from missing data
The expected-source list is joined to the measured results. A table with no matching records appears explicitly as “No rows observed”; it is not labelled healthy or failed. The query deliberately does not use fuzzy table resolution. A missing or inaccessible table should fail visibly rather than silently produce an apparently complete report. Union resolution.
MinutesSinceLastEvent measures age within the selected event-time sample. A naturally quiet directory may have an old last event despite a functioning connector. Conversely, one recent event does not establish complete collection. Compare the result with expected source activity, connector configuration, retention and known benign events from each intended tenant or subscription.
Investigate and operationalize
Start with a source whose observed delay changed or whose expected activity is absent. Verify upstream event generation, export settings, workspace destination, permissions and transformation changes. Then inspect service health and delivery behavior. Azure Monitor documents multiple contributors to ingestion latency; an observed delay is not automatically attributable to one component. Latency considerations.
Repeat the analysis over representative busy and quiet periods before selecting alert lookbacks. Correlation windows must account for arrival skew between sources as well as event timing. A production health rule additionally needs expected-source granularity, missing-data behavior, ownership and a known-event test.
This read-only recipe was documentation-checked, not tenant-tested. It returns three source summaries within a 200-row cap. Validate populated, empty, null-ingestion-time and inaccessible-source cases before relying on it for operational decisions.
Verify the source
Microsoft references
- Microsoft Learn — Kusto ingestion_time semantics ↗
- Microsoft Learn — Azure Monitor ingestion latency ↗
- Microsoft Learn — Kusto percentile aggregation ↗
- Microsoft Learn — Kusto union operator ↗
- Microsoft Learn — SigninLogs table schema ↗
- Microsoft Learn — AuditLogs table schema ↗
- Microsoft Learn — AzureActivity table schema ↗
- Microsoft Learn — Microsoft Entra ID connector requirements ↗
Measure normal activity, validate known test cases, account for ingestion delays, choose a threshold and entity mapping, and assign an owner. Results require investigation.
Follow the detection learning path →