How many email triggered in a day and how to schedule a report to the user?

[risingflight143]

Hi All

i am using the below query and it works fine. i.e how many emails were triggered to a Distribution list in a Month.

sourcetype="ms:o365:reporting:messagetrace"   SenderAddress=*** RecipientAddress=*dl1@contoso.com* Status IN (*) subject="***" MessageId=*** | timechart span=1mon count

I have the below requirement please guide me with query.

How many email triggered to the DL dl1@contoso.com on a day and subject of that email and sender address and i want to schedule this report to the user user1@contoso.com on daily basis.

[risingflight143]

The below syntax works fine , how do i add email received date.

sourcetype="ms:o365:reporting:messagetrace"   SenderAddress=* RecipientAddress=*dl1@contoso.com* Status IN (*) subject=* MessageId=*
| stats values(subject) as subject values(SenderAddress) as SenderAddress count by RecipientAddress

[gcusello]

HI @risingflight143,

to add a date similar to timechart, you have to use the bin command to group some timestamps otherwise you'll not be able to group events.

For this reason I used bin and in the stats command I added _time, that aren't present in your search.

Ciao.

Giuseppe

[risingflight143]

I tried the below i have added received:date_month but i am not getting output

sourcetype="ms:o365:reporting:messagetrace"   SenderAddress=* RecipientAddress=*dl1@contoso.com* Status IN (*) subject=* MessageId=*
| stats values(subject) as subject values(SenderAddress) as SenderAddress count by RecipientAddress Received:date_month 

[gcusello]

HI @risingflight143,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[gcusello]

Hi @risingflight143,

are you sure that in the same events there are RecipientAddress and Received:date_month?

this is the only reason to haven't  results.

Ciao.

Giuseppe

[gcusello]

Hi @risingflight143,

let me understand:

• you have a search that dispaly the cout per day of the emails to dl1@contoso.com 
• now to the count of the emails per day, you want to add also the list of subjects and the list of senders,

is this correct?

If this is your requirement, please try this:

sourcetype="ms:o365:reporting:messagetrace" SenderAddress=*** RecipientAddress=*dl1@contoso.com* Status IN (*) subject="***" MessageId=*** 
| bin span=1mon _time
| stats values(subject) AS subject values(sender) AS sender count BY _time

One additional hint: use always the index in your searches: they will be faster.

Ciao.

Giuseppe

[FelixLeh · Accepted solution]

sourcetype="ms:o365:reporting:messagetrace"   SenderAddress=* RecipientAddress=*dl1@contoso.com* Status IN (*) subject=* MessageId=*
| stats values(subject) as subject values(SenderAddress) as SenderAddress count by RecipientAddress

Does this achieve what you want?