Multiple Stopped Event Reception
Query
// This is a heavy query, please, don't use it with query frequencies lower than 1h
let query_frequency = 1h;
let query_period = 3d;
let _ExpectedFrequencies =
_GetWatchlist("DataType-IngestedTables")
| project Type, Critical, ExpectedIngestionFrequency = totimespan(Frequency)
;
union withsource=_Type
//This is a comment
* // withsource= is just used to bypass the Analytics rule wizard
| where TimeGenerated > ago(query_period)
| summarize IngestionTime = max(ingestion_time()) by Type
| lookup kind=leftouter _ExpectedFrequencies on Type
| where IngestionTime between (ago(ExpectedIngestionFrequency + query_frequency) .. ago(ExpectedIngestionFrequency))
| extend
TimespanWithoutIngestion = now() - IngestionTime,
AlertSeverity = case(
Critical == "true", "High",
"Informational"
)
| project Type, Critical, ExpectedIngestionFrequency, TimespanWithoutIngestion, AlertSeverityExplanation
This KQL query is designed to monitor data ingestion for specific data types over a specified period. Here's a simplified breakdown of what it does:
-
Setup Variables: It defines two variables:
query_frequency: The frequency at which the query should be run, set to 1 hour.query_period: The time period over which data is considered, set to 3 days.
-
Retrieve Expected Frequencies: It fetches a list of data types and their expected ingestion frequencies from a watchlist named "DataType-IngestedTables". This includes whether each type is critical and the expected frequency of data ingestion.
-
Fetch Recent Data: It retrieves all data entries from the last 3 days (
query_period) and notes the source of each entry. -
Determine Latest Ingestion Time: For each data type, it calculates the most recent time data was ingested.
-
Compare with Expected Frequencies: It compares the actual latest ingestion time with the expected ingestion frequency for each data type.
-
Calculate Time Since Last Ingestion: It calculates how long it has been since the last data ingestion for each type.
-
Assign Alert Severity: It assigns an alert severity level based on whether the data type is marked as critical:
- "High" if critical.
- "Informational" otherwise.
-
Output: Finally, it outputs a table with the data type, whether it's critical, the expected ingestion frequency, the time since the last ingestion, and the alert severity.
In essence, this query helps identify data types that have not been ingested as frequently as expected and assigns a severity level to each case based on its criticality.
Details
Jose Sebastián Canós
Released: October 31, 2024
Tables
Keywords
Operators