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.

In Microsoft Defender’s advanced hunting, the piece you’re missing is that custom indicators don’t surface directly in the AlertInfo table. AlertInfo and AlertEvidence give you the high-level alert records and entities tied to them (machines, users, processes, IPs, etc.), but they don’t explicitly say “this alert came from indicator X.” That’s why you’re not seeing a clean join between your IOC list and the alerts.

As of September 2025, the best approach is to pivot through the AlertEvidence table because that’s where you’ll see values like SHA256 hashes, IP addresses, or URLs that were recorded as part of the alert. Your custom indicator will only appear there if it was part of the triggering evidence. Once you have that, you can group by the IOC value and count how many alerts it generated. For example, if you’re interested in file hashes, you’d pull rows from AlertEvidence where EvidenceEntityType == "File" and then group by SHA256. Same logic applies to IPs and URLs by filtering on EvidenceEntityType == "IpAddress" or "Url".

A simple starting point looks like this:
AlertEvidence
| where EvidenceEntityType in ("File", "IpAddress", "Url")
| summarize AlertCount = count() by EvidenceEntityType, EvidenceEntityId
| order by AlertCount desc
Here EvidenceEntityId will hold the actual hash, IP, or URL value. This gives you a list of your IOCs and how many alerts each one generated. If you want to pull in more context (like alert title or severity), join this back to AlertInfo using AlertId.

One thing to note: not every “block” event from your custom IOC policy generates an alert. Sometimes it’s logged as a detection or audit event in other tables such as DeviceEvents (with the ActionType like IndicatorMatch or CustomIndicatorBlock). If you don’t see your IOC in AlertEvidence, check those tables too. They can give you counts of how often the IOC matched, even if no alert was raised. alerts that tie to IOCs show up in AlertEvidence, counts come from grouping on the IOC value, and for pure block/audit visibility you may need to expand into DeviceEvents. This way you’ll get a real picture of how often each indicator is firing across your environment.