Forum Discussion
Advanced Hunting Query Help
Hey y'all,
I'm trying to write a query that can be used to determine the number of times an each IOC generated an alert (file hash, URL, IP, etc).
I'm using the query builder tool within Defender, and I'm looking into the AlertInfo and AlertEvidence tables, but I'm not seeing where the link exists between each of these alert records and the corresponding IOC.
For instance. If I submit a custom indicator, to Block a file identified by a sha256 hash, and that file gets correctly blocked, I want to see a count for the number of times that IOC value (the hash in this instance) triggered an alert.
I'm hoping the community can help me determine whether I'm missing something glaringly obvious or if there's some documentation I haven't read yet. Thanks for reading!
1 Reply
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.
