KQL Search

Query Details

HomeAI ToolsDevice Query

Tifcepillar4 Data

Query

// ================================================
// TIFCE Pillar 4: Feed Freshness
//
// Purpose:
// - Measure how recently each feed added or updated IOCs.
// - Blend recency and IOC addition velocity into a freshness score.
// ================================================
let FreshnessLookback = 30d;
let CanonicalIOC = (iocType:string, iocValue:string) {
    strcat(tolower(trim(" ", iocType)), ":", tolower(trim(" ", iocValue)))
};
// -----------------------------
// Build per-feed IOC first/last seen state
// -----------------------------
let FeedIOCState =
    ThreatIntelIndicators
    | where IsDeleted == false
    | where isnotempty(SourceSystem) and isnotempty(ObservableKey) and isnotempty(ObservableValue)
    | extend TIFeed = tostring(SourceSystem)
    | extend IOCTypeRaw = tostring(split(ObservableKey, ":", 0))
    | extend IOCType = replace_regex(tolower(trim(" ", IOCTypeRaw)), @"[\[\]""']", "")
    | extend IOC = CanonicalIOC(IOCType, tostring(ObservableValue))
    | extend CreatedTime = coalesce(
        todatetime(column_ifexists("Created", datetime(null))),
        TimeGenerated
      )
    | extend UpdatedTime = coalesce(
        todatetime(column_ifexists("Modified", datetime(null))),
        todatetime(column_ifexists("LastUpdatedTime", datetime(null))),
        CreatedTime,
        TimeGenerated
      )
    | summarize
        FirstSeen = min(CreatedTime),
        LastSeen  = max(UpdatedTime)
      by TIFeed, IOC;
// -----------------------------
// Aggregate freshness metrics per feed
// -----------------------------
FeedIOCState
| extend IOCAgeDays = datetime_diff("day", now(), FirstSeen)
| summarize
    TotalDistinctIOCs   = count(),
    LastNewIOC          = max(FirstSeen),
    LastFeedActivity    = max(LastSeen),
    NewIOCsLast7d       = countif(FirstSeen > ago(7d)),
    NewIOCsLast30d      = countif(FirstSeen > ago(FreshnessLookback)),
    UpdatedIOCsLast7d   = countif(LastSeen > ago(7d)),
    UpdatedIOCsLast30d  = countif(LastSeen > ago(FreshnessLookback)),
    AvgIOCAgeDays       = round(avg(todouble(IOCAgeDays)), 1),
    P50IOCAgeDays       = toint(percentile(IOCAgeDays, 50))
  by TIFeed
| extend
    DaysSinceLastNewIOC   = datetime_diff("day", now(), LastNewIOC),
    DaysSinceLastActivity = datetime_diff("day", now(), LastFeedActivity)
// Recency favors recently active feeds; velocity favors feeds adding many new IOCs.
| extend
    FreshPctRecent = round(
        iif(TotalDistinctIOCs > 0, 100.0 * todouble(NewIOCsLast30d) / todouble(TotalDistinctIOCs), 0.0),
        2
    ),
    RecencyComponent = case(
        DaysSinceLastActivity <= 1, 100.0,
        DaysSinceLastActivity <= 7, 80.0,
        DaysSinceLastActivity <= 30, 50.0,
        DaysSinceLastActivity <= 90, 20.0,
        0.0
    ),
    VelocityComponent = iif(
        TotalDistinctIOCs > 0,
        100.0 * todouble(NewIOCsLast30d) / todouble(TotalDistinctIOCs),
        0.0
    )
// Weighted score emphasizes recency (60%) over velocity (40%).
| extend
    FreshnessScore = round(0.6 * RecencyComponent + 0.4 * VelocityComponent, 2),
    FreshnessStatus = case(
        DaysSinceLastActivity <= 1, "Very Fresh (daily activity)",
        DaysSinceLastActivity <= 7, "Fresh (weekly activity)",
        DaysSinceLastActivity <= 30, "Moderately Fresh",
        "Stale / Inactive (>30 days since activity)"
    )
| order by FreshnessScore desc, DaysSinceLastActivity asc
| project
    Feed = TIFeed,
    TotalDistinctIOCs,
    FreshnessScore,
    NewIOCsLast7d,
    NewIOCsLast30d,
    UpdatedIOCsLast7d,
    UpdatedIOCsLast30d,
    DaysSinceLastNewIOC,
    DaysSinceLastActivity,
    AvgIOCAgeDays,
    P50IOCAgeDays,
    FreshPctRecent,
    LastNewIOC,
    LastFeedActivity,
    FreshnessStatus

Explanation

This query is designed to evaluate the "freshness" of threat intelligence feeds by analyzing how recently they have added or updated Indicators of Compromise (IOCs). Here's a simplified breakdown of what the query does:

  1. Setup and Definitions:

    • It defines a 30-day lookback period for assessing freshness.
    • It creates a standardized format for IOCs by combining their type and value.

  2. Building IOC State:

    • It filters out deleted IOCs and those without necessary information.
    • It extracts and cleans the IOC type and value.
    • It determines the first and last time each IOC was seen for each feed.

  3. Calculating Freshness Metrics:

    • It calculates various metrics for each feed, such as:
      • Total number of distinct IOCs.
      • The most recent time a new IOC was added.
      • The most recent time any IOC was updated.
      • Counts of new and updated IOCs in the last 7 and 30 days.
      • Average and median age of IOCs.

  4. Scoring and Status:

    • It calculates a "freshness score" for each feed, which combines:
      • Recency: How recently the feed has been active.
      • Velocity: The rate of new IOC additions.
    • Recency is given more weight (60%) than velocity (40%).
    • It assigns a freshness status based on the days since the last activity.

  5. Output:

    • The results are ordered by freshness score, with the freshest feeds at the top.
    • It projects relevant information, such as the feed name, freshness score, IOC counts, and freshness status.

Overall, the query helps identify which threat intelligence feeds are the most up-to-date and active, allowing users to prioritize them accordingly.

Details

Michalis Michalos profile picture

Michalis Michalos

Released: June 3, 2026

Tables

ThreatIntelIndicators

Keywords

ThreatIntelIndicators

Operators

letstrcattolowertrimsplitreplace_regextodatetimecolumn_ifexistscoalescesummarizeminmaxbyextendtostringdatetime_diffnowcountcountifagoroundtodoubletointpercentileiifcaseorder byproject

Actions