TI Map URL Entity to UrlClickEvents

Requête originale, sans modification.

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let UrlClickEvents_ = materialize(UrlClickEvents
    | where TimeGenerated >= ago(dt_lookBack)
    | extend UrlClickEvents_TimeGenerated = TimeGenerated);
let ChainReportID = UrlClickEvents_
    | mv-expand todynamic(UrlChain)
    | extend UrlChain = tolower(UrlChain)
    | project ReportId, Url, UrlChain;
// Url is not always in UrlChain, so we need to check both
let ClickedUrls = 
  (union isfuzzy=false (ChainReportID), (ChainReportID | project Url = UrlChain))
  | distinct Url
  | summarize make_list(Url);
let TI = materialize(ThreatIntelligenceIndicator
  | where TimeGenerated >= ago(ioc_lookBack)
  | where isnotempty(Url) and tolower(Url) in (ClickedUrls)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true and ExpirationDateTime > now()
  | project-rename TI_Url = Url, TI_Type = Type
  );
(union isfuzzy=false (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.UrlChain),
  (TI | join kind=innerunique (ChainReportID) on $left.TI_Url == $right.Url))
| project-away UrlChain
| join kind=innerunique (UrlClickEvents_) on ReportId
| where UrlClickEvents_TimeGenerated < ExpirationDateTime
| summarize UrlClickEvents_TimeGenerated = arg_max(UrlClickEvents_TimeGenerated, *) by IndicatorId
| project UrlClickEvents_TimeGenerated, AccountUpn, Description, ActivityGroupNames, IndicatorId, ThreatType, ExpirationDateTime, ConfidenceScore, Url, NetworkMessageId
| extend timestamp = UrlClickEvents_TimeGenerated
| extend timestamp = UrlClickEvents_TimeGenerated, Name = tostring(split(AccountUpn, '@', 0)[0]), UPNSuffix = tostring(split(AccountUpn, '@', 1)[0])