Possible Phishing with CSL and Network Sessions

//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])

status: Available
queryFrequency: 1d
id: 6c3a1258-bcdd-4fcd-b753-1a9bc826ce12
tactics:
- InitialAccess
- CommandAndControl
entityMappings:
- fieldMappings:
  - columnName: InitiatingProcessAccountUpn
    identifier: FullName
  - columnName: InitiatingProcessAccountName
    identifier: Name
  - columnName: InitiatingProcessAccountDomain
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: RecipientEmailAddress
    identifier: FullName
  - columnName: RecipientEmailName
    identifier: Name
  - columnName: RecipientEmailUPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: DeviceName
    identifier: FullName
  - columnName: HostName
    identifier: HostName
  - columnName: HostNameDomain
    identifier: DnsDomain
  entityType: Host
- fieldMappings:
  - columnName: SourceIP
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: DestinationIP
    identifier: Address
  entityType: IP
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - AlertEvidence
  - EmailEvents
  - IdentityInfo
  - DeviceEvents
  - DeviceNetworkEvents
- connectorId: Zscaler
  dataTypes:
  - CommonSecurityLog
- connectorId: Fortinet
  dataTypes:
  - CommonSecurityLog
- connectorId: CheckPoint
  dataTypes:
  - CommonSecurityLog
- connectorId: PaloAltoNetworks
  dataTypes:
  - CommonSecurityLog
- connectorId: AWSS3
  datatypes:
  - AWSVPCFlow
- connectorId: WindowsForwardedEvents
  dataTypes:
  - WindowsEvent
- connectorId: SecurityEvents
  dataTypes:
  - SecurityEvent
- connectorId: WindowsSecurityEvents
  dataTypes:
  - SecurityEvent
- connectorId: MicrosoftSysmonForLinux
  dataTypes:
  - Syslog
- connectorId: AzureNSG
  dataTypes:
  - AzureDiagnostics
- connectorId: AzureMonitor(VMInsights)
  dataTypes:
  - VMConnection
- connectorId: AIVectraStream
  dataTypes:
  - VectraStream_CL
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender XDR/Analytic Rules/PossiblePhishingwithCSL&NetworkSession.yaml
version: 1.1.2
tags:
- SchemaVersion: 0.2.5
  Schema: ASimNetworkSessions
description: |
  'This query looks for malicious URL clicks in phishing email recognized by MDO in correlation with CommonSecurityLogs(CSL) & NetworkSession events. 
  If your workspace doesnt have one of the many data sources required for ASIM it may give informational error which can be safely ignored.'  
relevantTechniques:
- T1566
- T1102
triggerThreshold: 0
queryPeriod: 1d
triggerOperator: gt
name: Possible Phishing with CSL and Network Sessions
severity: Medium
kind: Scheduled
query: |
  //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])