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 each feed has added or updated Indicators of Compromise (IOCs). Here's a simplified breakdown of what the query does:

Setup and Definitions:
- It defines a 30-day lookback period to assess the freshness of the feeds.
- A function CanonicalIOC is used to standardize the format of IOCs by combining their type and value.
Building IOC State:
- The query filters out deleted or incomplete threat intelligence indicators.
- It extracts relevant information such as the feed source, IOC type, and value.
- It determines the first and last time each IOC was seen in the feed.
Aggregating Freshness Metrics:
- For each feed, it calculates various metrics:
  - Total number of distinct IOCs.
  - The most recent time a new IOC was added or an existing one was updated.
  - Counts of new and updated IOCs over the last 7 and 30 days.
  - Average and median age of IOCs.
Calculating Freshness Score:
- It calculates how recent and active each feed is, with a focus on:
  - Recency: How recently the feed has been active.
  - Velocity: The rate at which new IOCs are added.
- A weighted score is computed, giving more importance to recency (60%) over velocity (40%).
Determining Freshness Status:
- Based on the activity, feeds are categorized into statuses like "Very Fresh," "Fresh," "Moderately Fresh," or "Stale/Inactive."
Output:
- The results are ordered by freshness score, with the freshest feeds at the top.
- It projects relevant information for each feed, including the freshness score, number of new and updated IOCs, and freshness status.

In summary, this query helps to assess and rank threat intelligence feeds based on how actively they are adding or updating IOCs, providing insights into their current relevance and activity level.

Details

Michalis Michalos

Released: June 3, 2026

Tables

ThreatIntelIndicators

Keywords

ThreatIntelIndicators

Operators

letstrcattolowertrimtostringsplitreplace_regexcoalescetodatetimecolumn_ifexistssummarizeminmaxbydatetime_diffnowcountcountifagoroundavgtodoubletointpercentileextendiifcaseorder byproject

Actions

GitHub