Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

KQL to get user reported emails?

Copper Contributor

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 

3 Replies
bumping this, if that's permitted...

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.

Hi,
I've found this and was exactly what I was looking for. But I need to create a routine to extract this data monthly.
Running the query only returns the last 7 days, but on the report section I can see that has a report from the last 30 days.
Is it possible to get 30 days worth of data from the query?
Thanks