TI Map URL Entity to EmailUrlInfo

Requête originale, sans modification.

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let EmailUrlInfo_ = materialize(EmailUrlInfo
    | where TimeGenerated >= ago(dt_lookBack)
    | extend EmailUrlInfo_TimeGenerated = TimeGenerated);
let EmailUrls = EmailUrlInfo_ | distinct Url | summarize make_list(Url);
let EmailUrlDomains = EmailUrlInfo_ | distinct UrlDomain | summarize make_list(UrlDomain);
let EmailEvents_ = materialize(EmailEvents
    | where TimeGenerated >= ago(dt_lookBack));
let TI = materialize(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)
| extend IndicatorId = tostring(split(Id, "--")[2])
    | where TimeGenerated >= ago(ioc_lookBack)
    | where tolower(Url) in (EmailUrls) or tolower(Url) in (EmailUrlDomains)
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
    | where IsActive and (ValidUntil > now() or isempty(ValidUntil)));
(union
    (TI | join kind=innerunique (EmailUrlInfo_) on Url),
    (TI | join kind=innerunique (EmailUrlInfo_) on $left.Url == $right.UrlDomain))
| where EmailUrlInfo_TimeGenerated < ValidUntil
| summarize EmailUrlInfo_TimeGenerated = arg_max(EmailUrlInfo_TimeGenerated, *) by IndicatorId, Url
| extend Description = tostring(parse_json(Data).description)
| extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
| project EmailUrlInfo_TimeGenerated, Description, ActivityGroupNames, IndicatorId, Type, ValidUntil, Confidence, Url, UrlDomain, UrlLocation, NetworkMessageId
| extend timestamp = EmailUrlInfo_TimeGenerated
| join kind=inner (EmailEvents_) on NetworkMessageId
| where DeliveryAction !has "Blocked"
| extend Name = tostring(split(RecipientEmailAddress, '@', 0)[0]), UPNSuffix = tostring(split(RecipientEmailAddress, '@', 1)[0])