User Email Submissions by Admin review status (Mark and Notify)

Description

This query visualises user submissions where admin also performed 'mark and notify' action on the submission

Query · kql

let ReviewResults = CloudAppEvents | where ActionType == "SubmissionNotification" 
| extend SubmissionId = tostring(parse_json(RawEventData).SubmissionId)
| extend Properties = parse_json(RawEventData.ExtendedProperties)
| mv-expand element = Properties
| where element.Name == "AdminReviewResult"
| project SubmissionId, AdminReviewResult = element.Value;
CloudAppEvents
| where ActionType == "UserSubmission"
| extend SubmissionId = tostring(parse_json(RawEventData).SubmissionId), SubmissionType = parse_json(RawEventData).SubmissionType
| join kind=leftouter ReviewResults on SubmissionId
| extend UserReportedAs=iif(SubmissionType == 1, "Phish",iif(SubmissionType == 2, "Junk",iif(SubmissionType == 3, "NotJunk","")))
| extend ReviewedAccuracy=iif(UserReportedAs==AdminReviewResult, 1,0)
| extend Reviewed=iif(isempty(AdminReviewResult),"Not Reviewed","Reviewed")
| project SubmissionId,UserReportedAs,Reviewed,AdminReviewResult, ReviewedAccuracy
| summarize count() by Reviewed
| render piechart
Raw source User Email Submissions by Admin review status (Mark and Notify) · KQL
Esc
Published by Azure/Azure-Sentinel ↗, licensed under MIT ↗. Reproduced here unmodified. 
id: 6923a80a-e3df-4d81-a90d-63fc5b7f0bb5
name: User Email Submissions by Admin review status (Mark and Notify)
description: |
  This query visualises user submissions where admin also performed 'mark and notify' action on the submission
description-detailed: |
  This query visualises user submissions where admin also performed 'mark and notify' action on the submission
  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:
  - CloudAppEvents
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  let ReviewResults = CloudAppEvents | where ActionType == "SubmissionNotification" 
  | extend SubmissionId = tostring(parse_json(RawEventData).SubmissionId)
  | extend Properties = parse_json(RawEventData.ExtendedProperties)
  | mv-expand element = Properties
  | where element.Name == "AdminReviewResult"
  | project SubmissionId, AdminReviewResult = element.Value;
  CloudAppEvents
  | where ActionType == "UserSubmission"
  | extend SubmissionId = tostring(parse_json(RawEventData).SubmissionId), SubmissionType = parse_json(RawEventData).SubmissionType
  | join kind=leftouter ReviewResults on SubmissionId
  | extend UserReportedAs=iif(SubmissionType == 1, "Phish",iif(SubmissionType == 2, "Junk",iif(SubmissionType == 3, "NotJunk","")))
  | extend ReviewedAccuracy=iif(UserReportedAs==AdminReviewResult, 1,0)
  | extend Reviewed=iif(isempty(AdminReviewResult),"Not Reviewed","Reviewed")
  | project SubmissionId,UserReportedAs,Reviewed,AdminReviewResult, ReviewedAccuracy
  | summarize count() by Reviewed
  | render piechart
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.