KQL Search

Query Details

HomeAI ToolsDevice Query

Effective Retention Per Table

Query

Usage
| where TimeGenerated > ago(365d)
| summarize 
    OldestRecord = min(TimeGenerated),
    NewestRecord = max(TimeGenerated),
    TotalMB = round(sum(Quantity), 2),
    Records = count()
    by DataType
| extend 
    ObservedRetentionDays = datetime_diff('day', now(), OldestRecord),
    DataSpanDays = datetime_diff('day', NewestRecord, OldestRecord),
    LastIngestion = NewestRecord,
    TablePlan = iff(TotalMB > 0, "Active", "Empty")
| extend
    RetentionTier = case(
        ObservedRetentionDays >= 365, "1 Year+",
        ObservedRetentionDays >= 180, "6 Months+",
        ObservedRetentionDays >= 90, "90 Days (Standard)",
        ObservedRetentionDays >= 30, "30 Days",
        "< 30 Days"
    )
| project 
    DataType, ObservedRetentionDays, DataSpanDays, RetentionTier,
    OldestRecord, NewestRecord,
    TotalGB = round(TotalMB / 1024.0, 3)
| order by ObservedRetentionDays desc

Explanation

This KQL query is analyzing data usage over the past year. Here's a simple breakdown of what it does:

  1. Filter Data: It starts by selecting records from the last 365 days.

  2. Summarize Data: For each type of data (DataType), it calculates:

    • The oldest and newest record timestamps.
    • The total amount of data in megabytes, rounded to two decimal places.
    • The total number of records.

  3. Calculate Additional Metrics:

    • ObservedRetentionDays: How many days ago the oldest record was generated.
    • DataSpanDays: The number of days between the oldest and newest records.
    • LastIngestion: The timestamp of the most recent record.
    • TablePlan: Indicates if the data type is "Active" (if there's any data) or "Empty" (if there's none).

  4. Determine Retention Tier: Assigns a retention category based on how long the data has been retained:

    • "1 Year+" for data older than 365 days.
    • "6 Months+" for data older than 180 days.
    • "90 Days (Standard)" for data older than 90 days.
    • "30 Days" for data older than 30 days.
    • "< 30 Days" for data less than 30 days old.

  5. Project and Format Results: It selects and formats the final output to include:

    • Data type, observed retention days, data span days, retention tier, oldest and newest record timestamps.
    • Total data size in gigabytes, rounded to three decimal places.

  6. Order Results: Finally, it sorts the results by the number of observed retention days in descending order, showing the data types with the longest retention first.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

Usage

Keywords

Usage

Operators

|where>agosummarizeminmaxroundsumcountbyextenddatetime_diffnowiffcase>=project/order bydesc

Actions