KQL Search

// detection rule
let QRCode_emails = EmailUrlInfo
    | where TimeGenerated > 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 TimeGenerated > ago (2d)
    | where DeliveryLocation != "Blocked" // Only delivered or Junked emails are interesting
    | where isnotempty(NetworkMessageId)
    | where NetworkMessageId in (nMIDs)
    | distinct  TimeGenerated, NetworkMessageId, RecipientEmailAddress, SenderFromAddress, InternetMessageId, RecipientObjectId, ReportId;
let senders = senders_NMIDs
    | distinct SenderFromAddress;
// Checking sender prevalence in the organization
let senderprevalence = EmailEvents
    | where TimeGenerated  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 if in clicked emails 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 TimeGenerated,Url, NetworkMessageId, InternetMessageId, RecipientObjectId, ReportId;
QRCode_emails_from_non_prevalent_senders