TI Map URL Entity to EmailUrlInfo

Requête originale, sans modification.

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let EmailUrlInfo_ = materialize(EmailUrlInfo
    | where isnotempty(Url)
    | where TimeGenerated >= ago(dt_lookBack)
    | extend Url = tolower(Url)
    | 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(ThreatIntelligenceIndicator
    | where TimeGenerated >= ago(ioc_lookBack)
    | where (isnotempty(Url) or isnotempty(DomainName)) 
    | where tolower(Url) in (EmailUrls) or tolower(DomainName) in (EmailUrlDomains)
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
    | where Active == true and ExpirationDateTime > now());
(union
    (TI | join kind=innerunique (EmailUrlInfo_) on Url),
    (TI | join kind=innerunique (EmailUrlInfo_) on $left.DomainName == $right.UrlDomain))
| where EmailUrlInfo_TimeGenerated < ExpirationDateTime
| summarize EmailUrlInfo_TimeGenerated = arg_max(EmailUrlInfo_TimeGenerated, *) by IndicatorId, Url
| project EmailUrlInfo_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, 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])