Query Details
Approaching Retention Boundary
Query
let _RetentionDays = 90;
let _WarningDays = 7;
Usage
| where TimeGenerated > ago(365d)
| summarize OldestRecord = min(TimeGenerated) by DataType
| extend
AgeDays = datetime_diff('day', now(), OldestRecord),
DaysUntilPurge = _RetentionDays - datetime_diff('day', now(), OldestRecord)
| where DaysUntilPurge >= 0 and DaysUntilPurge <= _WarningDays
| extend
PurgeDate = OldestRecord + totimespan(strcat(tostring(_RetentionDays), "d")),
Urgency = case(
DaysUntilPurge <= 1, "IMMINENT - Purge within 24h",
DaysUntilPurge <= 3, "URGENT - Purge within 3 days",
"WARNING - Purge within 7 days"
)
| project DataType, OldestRecord, PurgeDate, DaysUntilPurge, Urgency
| order by DaysUntilPurge asc
Explanation
This query is designed to monitor data retention and provide warnings for data that is approaching its purge date. Here's a simple breakdown of what it does:
-
Set Parameters: It defines two variables:
_RetentionDays(90 days) and_WarningDays(7 days). -
Filter Data: It looks at data generated in the last 365 days.
-
Identify Oldest Records: For each data type, it finds the oldest record's timestamp.
-
Calculate Age and Purge Timing:
- It calculates how many days old the oldest record is.
- It determines how many days are left until the data is purged, based on the 90-day retention period.
-
Filter for Warnings: It only keeps records that are within the warning period (0 to 7 days before purge).
-
Determine Purge Date and Urgency:
- It calculates the exact date when the data will be purged.
- It assigns an urgency level based on how soon the purge will happen:
- "IMMINENT" if within 24 hours,
- "URGENT" if within 3 days,
- "WARNING" if within 7 days.
-
Output: It displays the data type, the oldest record date, the purge date, days until purge, and urgency level, sorted by how soon the purge will occur.
In summary, this query helps identify and prioritize data that needs attention because it is nearing its retention limit and will soon be purged.
Details
David Alonso
Released: April 8, 2026
Tables
Keywords
Operators