User email submissions (FN) from Junk Folder

id: a1eb19d6-9d86-425c-bd1d-86495c2ca714
name: User email submissions (FN) from Junk Folder
description: |
  This query visualises the total ammount of user false negative submissions from the junk folder
description-detailed: |
  This query visualises the total ammount of user false negative submissions that are already detected by MDO and already delivered in the junk folder
  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
  - EmailEvents
tactics:
  - InitialAccess
relevantTechniques:
  - T1566
query: |
  CloudAppEvents 
  | where ActionType == "UserSubmission"
  | extend SubmissionType = tostring((parse_json(RawEventData)).SubmissionType),SubmissionContentType=tostring((parse_json(RawEventData)).SubmissionContentType), RecipientObjectId=AccountObjectId
  | extend User_SubmissionType=
  iff(SubmissionType == "1" and ActionType == "UserSubmission" and SubmissionContentType=="Mail","User_Phish_FN",
  iff(SubmissionType == "0" and ActionType == "UserSubmission" and SubmissionContentType=="Mail","User_Spam_FN","Other")),
  NetworkMessageId=tostring((parse_json(RawEventData).ObjectId))
  | where SubmissionContentType == "Mail" and SubmissionType in ("1","0")
  | join EmailEvents on NetworkMessageId, RecipientObjectId
  | where DeliveryLocation == "Junk folder"
  | summarize count() by ThreatTypes,User_SubmissionType
  | project ThreatTypes,User_SubmissionType, Emails = count_
version: 1.0.0