TI Map URL Entity to DeviceNetworkEvents

Requête originale, sans modification.

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let DeviceNetworkEvents_ = DeviceNetworkEvents
  | where TimeGenerated >= ago(dt_lookBack)
  | where ActionType !has "ConnectionFailed"
  | project-rename DeviceNetworkEvents_TimeGenerated = TimeGenerated;
let DeviceNetworkEventUrls = DeviceNetworkEvents_
  | distinct Url = RemoteUrl
  | summarize make_list(Url);
ThreatIntelIndicators
//extract key part of kv pair
| extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
| where IndicatorType == "url"
| extend Url = ObservableValue
| extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
| where TimeGenerated >= ago(ioc_lookBack)
| extend Url = tolower(Url)
| where Url in (DeviceNetworkEventUrls)
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
| where IsActive and (ValidUntil > now() or isempty(ValidUntil))
| extend Description = tostring(parse_json(Data).description)
| where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;"
   | project-reorder *, Tags, TrafficLightProtocolLevel, Url, Type
| join kind=innerunique (DeviceNetworkEvents_) on $left.Url == $right.RemoteUrl
| where DeviceNetworkEvents_TimeGenerated < ValidUntil
| summarize DeviceNetworkEvents_TimeGenerated = arg_max(DeviceNetworkEvents_TimeGenerated, *) by Id, Url
| extend Description = tostring(parse_json(Data).description)
| extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
| project DeviceNetworkEvents_TimeGenerated, Id, Url, Confidence, Description, Type, Tags, TrafficLightProtocolLevel, ActionType, DeviceId, DeviceName, InitiatingProcessAccountUpn, InitiatingProcessCommandLine, RemoteIP, RemotePort
| extend Name = tostring(split(InitiatingProcessAccountUpn, '@', 0)[0]), UPNSuffix = tostring(split(InitiatingProcessAccountUpn, '@', 1)[0])
| extend timestamp = DeviceNetworkEvents_TimeGenerated, UserPrincipalName = InitiatingProcessAccountUpn