May 04 2023 02:06 PM
Hi all,
I'm looking for a KQL query to pull back email report submissions / user reported emails - is this possible?
MS pull this data in a 365 security report: https://learn.microsoft.com/en-us/microsoft-365/security/office-365-security/reports-email-security?...
I'm looking to retrieve the same data.
Thanks
May 25 2023 07:32 AM - edited May 25 2023 07:36 AM
If you have taken the option for the Outlook Report Message add-in to copy submissions to a secops account then the following should work:
// cause 1 = junk
// cause 2 = not junk
// cause 3 = phish
//
EmailEvents
| where RecipientEmailAddress == "secops@yourdomain"
| extend cause = substring(Subject,0,1)
| where cause == "1" or cause == "2" or cause == "3"
| project Timestamp, EmailDirection, SenderFromAddress, RecipientEmailAddress, DeliveryLocation, Subject, cause
| extend week = week_of_year(Timestamp)
| sort by week asc
| summarize count () by week, cause
| sort by week asc, cause asc
| render columnchart
If you don't want to summarize and instead want the list of submissions then drop the last 4 or 5 lines. If you only want certain types of user submissions then amend the relevant "where" filter.
If your secops account also picks up direct reports then they won't necessarily have the right leading digit in the subject line.
This method does not show admin submissions.
Nov 14 2023 02:22 PM