User Email Submissions accuracy vs Admin review verdict

Description

This query visualises user submissions type compared to admin review verdict

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(AdminReviewResult==UserReportedAs, "Correct", iif(AdminReviewResult=="Phish" and UserReportedAs == "Junk", "Phish reported as junk",iif(AdminReviewResult=="Junk" and UserReportedAs == "Phish","Junk reported as Phish",iif(AdminReviewResult=="NotJunk","Reported but not malicious or spam",iif(isempty(AdminReviewResult),"Not Reviewed","Not correct")))))
| extend Reviewed=iif(isempty(AdminReviewResult),"Not Reviewed","Reviewed")
| project SubmissionId,UserReportedAs,Reviewed,AdminReviewResult, ReviewedAccuracy
| where Reviewed=="Reviewed"
| summarize count() by ReviewedAccuracy
| render piechart
Raw source User Email Submissions accuracy vs Admin review verdict · KQL
Esc
Published by Azure/Azure-Sentinel ↗, licensed under MIT ↗. Reproduced here unmodified. 
id: 0d03314a-ecbe-47f0-b411-2a9471f26b46
name: User Email Submissions accuracy vs Admin review verdict
description: |
  This query visualises user submissions type compared to admin review verdict
description-detailed: |
  This query visualises user submissions type compared to admin review verdict, such as a User reporting a message as Phish but admin review is 'No threat found'
  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(AdminReviewResult==UserReportedAs, "Correct", iif(AdminReviewResult=="Phish" and UserReportedAs == "Junk", "Phish reported as junk",iif(AdminReviewResult=="Junk" and UserReportedAs == "Phish","Junk reported as Phish",iif(AdminReviewResult=="NotJunk","Reported but not malicious or spam",iif(isempty(AdminReviewResult),"Not Reviewed","Not correct")))))
  | extend Reviewed=iif(isempty(AdminReviewResult),"Not Reviewed","Reviewed")
  | project SubmissionId,UserReportedAs,Reviewed,AdminReviewResult, ReviewedAccuracy
  | where Reviewed=="Reviewed"
  | summarize count() by ReviewedAccuracy
  | 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.