Custom detection-Emails with QR from non-prevalent senders

id: 516046e8-a460-4f7b-86eb-421d3a9cdff1
name: Custom detection-Emails with QR from non-prevalent senders
description: |
  In this detection, we check the sender prevalence over the last 14 days and use the same to detect malicious activity via email containing QR code
description-detailed: |
  In this detection, we check the sender prevalence over the last 14 days and use the same to detect malicious activity via email containing QR code using Defender for Office 365 data.
  Reference - https://techcommunity.microsoft.com/t5/microsoft-defender-for-office/hunting-and-responding-to-qr-code-based-phishing-attacks-with/ba-p/4074730 
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - EmailEvents
  - EmailUrlInfo
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  let QRCode_emails = EmailUrlInfo
  | where Timestamp > ago (2d)
  | where UrlLocation == "QRCode"
  | distinct Url,NetworkMessageId;
  let nMIDs = QRCode_emails | distinct NetworkMessageId;
  // Extracting sender of the email with QRCode:
  let senders_NMIDs = EmailEvents
  | where Timestamp > ago (2d)
  | where DeliveryAction != "Blocked" // Only delivered or Junked emails are interesting
  | where isnotempty(NetworkMessageId)
  | where NetworkMessageId in (nMIDs)
  | distinct  Timestamp, NetworkMessageId, RecipientEmailAddress, SenderFromAddress, InternetMessageId, RecipientObjectId, ReportId;
  let senders = senders_NMIDs
  | distinct SenderFromAddress;
  // Checking sender prevalence in the organization
  let senderprevalence = EmailEvents
  | where Timestamp between (ago(14d)..(now()-24h))
  | where isnotempty(SenderFromAddress)
  | where SenderFromAddress in (senders)
  | summarize TotalEmailCount = count()  by SenderFromAddress
  | where TotalEmailCount > 1;
  let prevalent_Sender = senderprevalence
  | where isnotempty (SenderFromAddress)
  | distinct SenderFromAddress;
  // Checking where email sender was not prevalent.
  let nMIDs_from_non_prevalent_Senders = senders_NMIDs
  | where SenderFromAddress !in (prevalent_Sender)
  | distinct NetworkMessageId;
  let QRCode_emails_from_non_prevalent_senders = QRCode_emails
  | where NetworkMessageId in (nMIDs_from_non_prevalent_Senders)
  | join kind=inner senders_NMIDs on NetworkMessageId
  | project Timestamp,Url,NetworkMessageId, InternetMessageId, RecipientObjectId,RecipientEmailAddress, ReportId;
  QRCode_emails_from_non_prevalent_senders
version: 1.0.0