KQL Search

Query Details

HomeAI ToolsDevice Query

Tifcepillar1 Data

Query

// ================================================
// TIFCE Pillar 1: Originality Score (Uniqueness)
// ================================================
// Purpose:
// - Measure how much a feed contributes indicators that are not already
//   broadly present across other feeds.
//
// Scoring logic:
// - Each IOC gets a weight of 1 / FeedCount
// - If only one feed has the IOC, it contributes 1.0 to that feed
// - If 5 feeds share the IOC, it contributes 0.2 to each feed
// - Feeds with more exclusive IOCs get higher originality scores

// Step 1: Build a deduplicated set of active IOCs per feed
let ActiveIndicators =
    ThreatIntelIndicators
    | where IsActive == true
      and IsDeleted == false
      and (isnull(ValidUntil) or ValidUntil > now())
    | where isnotempty(ObservableKey) and isnotempty(ObservableValue)
    | extend
        // Feed identifier used for scoring
        TIFeed = tostring(SourceSystem),
        // Canonical IOC format used consistently across the query
        // Normalization helps avoid mismatches caused by casing or extra spaces
        IOC = strcat(
            tolower(trim(" ", tostring(ObservableKey))),
            ":",
            tolower(trim(" ", tostring(ObservableValue)))
        )
    // Deduplicate so the same feed-IOC pair is only counted once
    | summarize by TIFeed, IOC;
// Step 2: Count how many distinct feeds report each IOC
let IOCFeedCounts =
    ActiveIndicators
    | summarize FeedCount = dcount(TIFeed) by IOC;
// Step 3: Join feed IOCs to IOC distribution and calculate originality
ActiveIndicators
| join kind=inner IOCFeedCounts on IOC
| summarize
    // Sum of fractional IOC contributions for the feed
    OriginalityScore = sum(1.0 / FeedCount),
    // Total distinct IOCs contributed by the feed
    TotalIOCs = count(),
    // Count of IOCs seen only in this single feed
    ExclusiveIOCs = countif(FeedCount == 1)
    by TIFeed
| extend
    // Average originality contribution per IOC
    AvgOriginalityPerIOC = round(OriginalityScore / TotalIOCs, 4),
    // Same idea expressed as a percentage for easier comparison
    OriginalityPct = round(100.0 * OriginalityScore / TotalIOCs, 2)
| order by OriginalityScore desc
| project
    Feed = TIFeed,
    OriginalityScore,
    TotalIOCs,
    AvgOriginalityPerIOC,
    OriginalityPct,
    ExclusiveIOCs

Explanation

This query is designed to evaluate the uniqueness of threat intelligence feeds by calculating an "Originality Score" for each feed. Here's a simplified breakdown of what the query does:

  1. Purpose: The goal is to measure how much unique information each feed contributes compared to others. A higher score indicates that a feed provides more exclusive indicators of compromise (IOCs).

  2. Scoring Logic:

    • Each IOC is given a weight based on how many feeds report it.
    • If an IOC is reported by only one feed, it contributes fully (1.0) to that feed's score.
    • If an IOC is shared by multiple feeds, its contribution is divided among them (e.g., 0.2 each if shared by 5 feeds).
    • Feeds with more exclusive IOCs receive higher scores.

  3. Steps:

    • Step 1: Create a list of active, non-deleted IOCs for each feed, ensuring each feed-IOC pair is unique. This involves normalizing the IOC format to avoid discrepancies.
    • Step 2: Count how many different feeds report each IOC.
    • Step 3: Calculate the originality score for each feed by summing the fractional contributions of its IOCs. Also, calculate the total number of IOCs and the number of exclusive IOCs for each feed.

  4. Output:

    • The query outputs a list of feeds with their originality scores, total IOCs, average originality per IOC, originality percentage, and count of exclusive IOCs.
    • The results are ordered by the originality score in descending order, highlighting the feeds with the most unique contributions.

In essence, this query helps identify which threat intelligence feeds provide the most unique and potentially valuable information.

Details

Michalis Michalos profile picture

Michalis Michalos

Released: June 3, 2026

Tables

ThreatIntelIndicators

Keywords

ThreatIntelIndicators

Operators

let|where==andorisnull>now()isnotemptyextendtostring()strcat()tolower()trim()summarizebydcount()joinkind=innersum()count()countif()==round()/*order bydescproject

Actions