Forum Discussion
Advanced Hunting Query Help
vimal_raj1984hotmail Thank you so much for the well thought out response, especially after so much time since the original post.
Only one issue: The query is throwing an error due to ThreatIndicators being a missing column. Is it possible this is located in another table, or has been renamed?
// Find alerts generated by Custom Threat Intelligence
AlertInfo
| where DetectionSource == "CustomTi"
// Join with the AlertEvidence table to get entity information
| join kind=inner AlertEvidence on AlertId
// The 'Entities' column is a JSON array, so we expand it to get a row for each entity
| mv-expand todynamic(Entities)
// We only care about the specific entity type for Threat Intelligence
| where Entities.EntityType == "ThreatIntelligence"
// Extract the relevant details from the ThreatIntelligence entity
| extend
IndicatorValue = tostring(Entities.IndicatorValue),
IndicatorType = tostring(Entities.IndicatorType),
IndicatorSource = tostring(Entities.IndicatorSource), // e.g., "Azure" for custom TI
IndicatorAction = tostring(Entities.Action)
// Group by the IOC value to get the count
| summarize
AlertCount = count(),
FirstSeen = min(Timestamp),
LastSeen = max(Timestamp),
// Take any value for 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