Microsoft Sentinel medium experimental kql
GreyNoise TI map IP entity to OfficeActivity
This query maps any GreyNoise IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in OfficeActivity.
Detection Logic
let dt_lookBack = 1h; // Look back 1 hour for OfficeActivity events
let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
// Fetch GreyNoise IPv4 indicators from the ThreatIntelIndicators (STIX 2.1) table.
// GreyNoise only publishes ipv4-addr patterns, so a single ObservableKey filter is sufficient.
let GreyNoise_IPs = ThreatIntelIndicators
| where TimeGenerated >= ago(ioc_lookBack)
| where SourceSystem == 'GreyNoise'
| where ObservableKey == 'ipv4-addr:value'
// Take the latest row per indicator first, then evaluate its current state, so an
// indicator that has since been deactivated cannot be resurrected by an older row.
| summarize arg_max(TimeGenerated, *) by Id
| where IsActive == true and IsDeleted == false and ValidUntil > now()
| extend TI_ipEntity = ObservableValue
| where ipv4_is_private(TI_ipEntity) == false and TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127."
// Explicit projection keeps TimeGenerated and Type off the left side of the join, avoiding column collisions.
| project IndicatorId = Id, TI_ipEntity, ThreatType = tostring(Data.indicator_types[0]), IoCDescription = tostring(Data.description), Tags, Confidence, ValidFrom, ValidUntil;
// Perform a join between IP indicators and OfficeActivity events
GreyNoise_IPs
// Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
| join kind=innerunique (
OfficeActivity
| where TimeGenerated >= ago(dt_lookBack)
| where isnotempty(ClientIP)
| extend ClientIPValues = extract_all(@'\[?(::ffff:)?(?P<IPAddress>(\d+\.\d+\.\d+\.\d+)
| [^\]%]+)(%\d+)?\]?([-:](?P<Port>\d+))?', dynamic(["IPAddress", "Port"]), ClientIP)[0]
| extend IPAddress = iff(array_length(ClientIPValues) > 0, tostring(ClientIPValues[0]), '')
| extend OfficeActivity_TimeGenerated = TimeGenerated
)
on $left.TI_ipEntity == $right.IPAddress
// Filter out OfficeActivity events that occurred after the expiration of the corresponding indicator
| where OfficeActivity_TimeGenerated < ValidUntil
// Group the results by IndicatorId and keep the OfficeActivity event with the latest timestamp
| summarize OfficeActivity_TimeGenerated = arg_max(OfficeActivity_TimeGenerated, *) by IndicatorId
// Select the desired output fields
| project OfficeActivity_TimeGenerated, IoCDescription, IndicatorId, ThreatType, ValidFrom, ValidUntil, Confidence, Tags,
TI_ipEntity, ClientIP, UserId, Operation, ResultStatus, RecordType, OfficeObjectId, Type
| extend timestamp = OfficeActivity_TimeGenerated, Name = tostring(split(UserId, '@', 0)[0]), UPNSuffix = tostring(split(UserId, '@', 1)[0]) Field Validations
Loading…
Comments (0)
Loading comments...