Possible Phishing with CSL and Network Sessions

Requête originale, sans modification.

//SuspiciousUrlClicked
AlertEvidence
| where ServiceSource =~ "Microsoft Defender for Office 365"
| where EntityType =~ "Url"
| project AlertId, RemoteUrl
| join kind=inner (
AlertEvidence
| where ServiceSource =~ "Microsoft Defender for Office 365"
| where EntityType =~ "MailMessage"
| project AlertId, NetworkMessageId
)
on AlertId
| distinct RemoteUrl, NetworkMessageId
| join EmailEvents on NetworkMessageId
| distinct RemoteUrl, NetworkMessageId, RecipientEmailAddress, RecipientObjectId
| join kind = inner IdentityInfo on $left.RecipientObjectId  == $right.AccountObjectId
| distinct RemoteUrl, NetworkMessageId, RecipientEmailAddress , RecipientObjectId, AccountSID
| join kind = inner  
(DeviceEvents
| where ActionType =~ "BrowserLaunchedToOpenUrl"| where isnotempty(RemoteUrl)
| project  UrlClickedByUserSid = RemoteUrl,
InitiatingProcessAccountSid, DeviceName, DeviceId, InitiatingProcessFileName,
InitiatingProcessAccountUpn, InitiatingProcessAccountName, InitiatingProcessAccountDomain
)
on $left.AccountSID == $right.InitiatingProcessAccountSid and $left.RemoteUrl == $right.UrlClickedByUserSid
| distinct  RemoteUrl, NetworkMessageId, RecipientEmailAddress, RecipientObjectId,
 AccountSID, UrlClickedByUserSid, DeviceName, DeviceId, InitiatingProcessFileName,
 InitiatingProcessAccountUpn, InitiatingProcessAccountName, InitiatingProcessAccountDomain
|  join kind=inner
(
//Suspicious url clicked found in common security logs
CommonSecurityLog
| project TimeGenerated, DeviceVendor, DeviceProduct, DeviceAction, DestinationDnsDomain, DestinationIP, RequestURL, SourceIP, SourceHostName, RequestClientApplication
) on $left.RemoteUrl== $right.RequestURL
|  join kind=inner
(
//Find the relevant network sessions
_Im_NetworkSession
| where isnotempty(DstIpAddr)
| where not(ipv4_is_private(DstIpAddr))
| project TimeGenerated, SrcIpAddr, SrcPortNumber, DstIpAddr, DstPortNumber, DstBytes, SrcBytes
) on  $left.DestinationIP == $right.DstIpAddr //The relevant network session being projected 
| summarize count() by TimeGenerated, RecipientEmailAddress, UrlClickedByUserSid, InitiatingProcessAccountUpn, InitiatingProcessAccountName, InitiatingProcessAccountDomain,
DeviceName, InitiatingProcessFileName, DeviceProduct, DeviceAction, SourceIP, DestinationIP, RequestClientApplication
| extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(DeviceName, DomainIndex + 1), DeviceName)
| extend RecipientEmailName = tostring(split(RecipientEmailAddress,'@',0)[0]), RecipientEmailUPNSuffix = tostring(split(RecipientEmailAddress,'@',1)[0])