Query Details
Data Ingestion Status Monitoring
Query
//This query monitors data ingestion freshness across Sentinel tables
//Identifies delays and assigns status based on warning/critical thresholds
// Define warning and critical thresholds
let warningThresholdDays = 3;
let criticalThresholdDays = 7;
union withsource = SentinelTableName *
| where TimeGenerated > ago(30d)
| summarize LastIngestionTime = max(TimeGenerated) by SentinelTableName
| extend DaysSinceLastIngestion = datetime_diff('day', now(), LastIngestionTime)
| extend Status = case(
DaysSinceLastIngestion > criticalThresholdDays, "🔴 Critical: Ingestion Delayed",
DaysSinceLastIngestion > warningThresholdDays, "🟡 Warning: Needs Attention",
DaysSinceLastIngestion == 0, "🟢 Up to Date",
"🟡 Acceptable: Recently Ingested"
)
| sort by DaysSinceLastIngestion desc Explanation
This query is designed to monitor how up-to-date the data ingestion is for various tables in Microsoft Sentinel. It checks when data was last ingested into each table and categorizes the freshness of this data based on specific time thresholds. Here's a breakdown of what it does:
-
Thresholds Definition: It sets two thresholds for data freshness:
- A warning threshold of 3 days.
- A critical threshold of 7 days.
-
Data Collection: It gathers data from all Sentinel tables, focusing on entries from the last 30 days.
-
Last Ingestion Time: For each table, it determines the most recent time data was ingested.
-
Days Calculation: It calculates how many days have passed since the last data ingestion for each table.
-
Status Assignment: Based on the number of days since the last ingestion, it assigns a status to each table:
- "🔴 Critical: Ingestion Delayed" if the delay exceeds 7 days.
- "🟡 Warning: Needs Attention" if the delay is more than 3 days but less than or equal to 7 days.
- "🟢 Up to Date" if data was ingested today.
- "🟡 Acceptable: Recently Ingested" for other cases where data is relatively fresh but not from today.
-
Sorting: Finally, it sorts the tables by the number of days since the last ingestion, with the most delayed tables appearing first.
Details
Antone Andrade
Released: November 10, 2024
Tables
Keywords
Operators