Advanced Hunting Query Help

The Key: The ThreatIndicators Field in AlertInfo

When an alert is generated specifically because of a custom indicator you submitted (what Defender calls "TI-based detection"), the AlertInfo table is populated with details about that indicator.

However, this information is not in a top-level column. It's stored in a JSON array within the ThreatIndicators column.

Each object in this JSON array contains details about the indicator that contributed to the alert, including:

• IndicatorValue: The actual IOC value (the file hash, the IP address, etc.).
• IndicatorType: The type of IOC (e.g., FileSha256, IpAddress).
• Action: The action taken (e.g., Blocked, Audited).

Therefore, the process to get the count you want is:

1. Filter the AlertInfo table for alerts that have TI information.
2. Use the mv-expand operator to "un-pack" the JSON array in the ThreatIndicators column, creating a separate row for each indicator.
3. Parse the JSON to extract the IndicatorValue.
4. Summarize the results by counting the occurrences of each IndicatorValue.

The KQL Query You Need

Here is the query that accomplishes this. You can run this directly in Advanced Hunting.

kusto

AlertInfo
// Filter for alerts that are specifically generated by custom Threat Intelligence.
// The 'DetectionSource' for these alerts is 'CustomTi'.
| where DetectionSource == "CustomTi"
// The ThreatIndicators column contains the IOC details in a JSON array.
// We need to expand this array so that each indicator gets its own row.
| mv-expand todynamic(ThreatIndicators)
// Now that we have individual rows, parse the JSON to get the IOC value and type.
| extend IndicatorValue = tostring(ThreatIndicators.IndicatorValue),
           IndicatorType = tostring(ThreatIndicators.IndicatorType),
           IndicatorAction = tostring(ThreatIndicators.Action)
// We are only interested in the IOC value itself.
| project AlertId, Timestamp, Title, IndicatorValue, IndicatorType, IndicatorAction
// Now, group by the IOC value and count how many times each one appears.
| summarize
    AlertCount = count(),
    FirstSeen = min(Timestamp),
    LastSeen = max(Timestamp),
    // Take any value for the type and action, as they should be the same for each IOC.
    IndicatorType = take_any(IndicatorType),
    IndicatorAction = take_any(IndicatorAction)
    by IndicatorValue
// Sort to see the most frequent IOCs at the top.
| order by AlertCount desc

How to Use and Interpret the Results

When you run this query, you will get a table that looks like this:

This output gives you exactly what you were looking for:

• A distinct list of each IOC (IndicatorValue) that has generated an alert.
• The total number of times it has generated an alert (AlertCount).
• The first and last time the alert was seen.
• The type of indicator and the action you configured for it.

Why AlertEvidence Is a Dead End for This Specific Task

You were smart to look at AlertEvidence, as that's where you find details about the files, processes, and IPs involved in an alert. However, for a TI-based alert, the AlertEvidence table will show you the evidence of the blocked file on a specific machine, but it doesn't contain a direct link back to the custom indicator object that you created.

The AlertInfo table is the "master record" for the alert itself, and its ThreatIndicators field is the specific place where Defender records why the alert was generated in the case of a TI match.

You were not missing anything obvious at all. This is a common hurdle, and solving it requires knowing about the DetectionSource == "CustomTi" filter and how to use mv-expand to unpack the JSON data. You were on the right path, and this query should give you the exact results you need.