Quarantine Phish Reason trend

Description

This query visualises the amount of phish emails that are quarantined, summarized daily by the detection method

Query · kql

let TimeStart = startofday(ago(30d));
let TimeEnd = startofday(now());
let baseQuery = EmailEvents
| where Timestamp >= TimeStart
| where DetectionMethods has "Phish" and DeliveryLocation == "Quarantine";
let ml=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Advanced filter'
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Advanced filter";
let camp=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Campaign'
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Campaign";
let fd=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'File detonation' and Phish !has 'File detonation reputation'
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "File detonation";
let fdr=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'File detonation reputation'
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "File detonation reputation";
let frp=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Fingerprint matching' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Fingerprint matching";
let gf=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'General filter' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "General filter";
let bimp=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Impersonation brand' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Impersonation brand";
let dimp=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Impersonation domain' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Impersonation domain";
let uimp=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Impersonation user' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Impersonation user";
let mimp=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Mailbox intelligence impersonation' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Mailbox intelligence impersonation";
let sdmarc=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Spoof DMARC' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Spoof DMARC";
let spoofe=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Spoof external domain' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Spoof external domain";
let spoofi=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'Spoof intra-org' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "Spoof intra-org";
let ud=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'URL detonation' and Phish !has 'URL detonation reputation'
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "URL detonation";
let udr=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'URL detonation reputation' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "URL detonation reputation";
let umr=baseQuery
| project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
| where Phish has 'URL malicious reputation' 
| make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
| extend Details = "URL malicious reputation";
union ml,camp,fd,fdr,frp,gf,bimp,dimp,uimp,mimp,sdmarc,spoofe,spoofi,ud,udr,umr
| project Count, Details, Timestamp
| render timechart
Raw source Quarantine Phish Reason trend · KQL
Esc
Published by Azure/Azure-Sentinel ↗, licensed under MIT ↗. Reproduced here unmodified. 
id: 6f7df1b7-5613-45e5-9b82-3ec95d86e0e8
name: Quarantine Phish Reason trend
description: |
  This query visualises the amount of phish emails that are quarantined, summarized daily by the detection method
description-detailed: |
  This query visualises the amount of phish emails that are quarantined, summarized daily by the detection method
  Query is also included as part of the Defender for Office 365 solution in Sentinel: https://techcommunity.microsoft.com/blog/microsoftdefenderforoffice365blog/part-2-build-custom-email-security-reports-and-dashboards-with-workbooks-in-micr/4411303
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - EmailEvents
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  let TimeStart = startofday(ago(30d));
  let TimeEnd = startofday(now());
  let baseQuery = EmailEvents
  | where Timestamp >= TimeStart
  | where DetectionMethods has "Phish" and DeliveryLocation == "Quarantine";
  let ml=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Advanced filter'
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Advanced filter";
  let camp=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Campaign'
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Campaign";
  let fd=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'File detonation' and Phish !has 'File detonation reputation'
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "File detonation";
  let fdr=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'File detonation reputation'
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "File detonation reputation";
  let frp=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Fingerprint matching' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Fingerprint matching";
  let gf=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'General filter' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "General filter";
  let bimp=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Impersonation brand' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Impersonation brand";
  let dimp=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Impersonation domain' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Impersonation domain";
  let uimp=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Impersonation user' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Impersonation user";
  let mimp=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Mailbox intelligence impersonation' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Mailbox intelligence impersonation";
  let sdmarc=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Spoof DMARC' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Spoof DMARC";
  let spoofe=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Spoof external domain' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Spoof external domain";
  let spoofi=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'Spoof intra-org' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "Spoof intra-org";
  let ud=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'URL detonation' and Phish !has 'URL detonation reputation'
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "URL detonation";
  let udr=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'URL detonation reputation' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "URL detonation reputation";
  let umr=baseQuery
  | project Timestamp,RecipientEmailAddress,NetworkMessageId, DT=parse_json(DetectionMethods) | evaluate bag_unpack(DT) 
  | where Phish has 'URL malicious reputation' 
  | make-series Count= count() default = 0 on Timestamp from TimeStart to TimeEnd step 1d 
  | extend Details = "URL malicious reputation";
  union ml,camp,fd,fdr,frp,gf,bimp,dimp,uimp,mimp,sdmarc,spoofe,spoofi,ud,udr,umr
  | project Count, Details, Timestamp
  | render timechart
version: 1.0.0

Detection rules belong to the projects that publish them and remain under their own licenses. This site indexes and links to them; it claims no rights in them.