topic Re: How many email triggered in a day and how to schedule a report to the user? in Splunk Search

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

risingflight143 — Fri, 23 Sep 2022 15:41:08 GMT

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.

Re: Message Trace

FelixLeh — Fri, 23 Sep 2022 13:53:18 GMT

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?

Re: Message Trace

gcusello — Fri, 23 Sep 2022 13:56:06 GMT

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

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

risingflight143 — Fri, 23 Sep 2022 16:41:23 GMT

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

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

risingflight143 — Fri, 23 Sep 2022 17:11:03 GMT

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

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

gcusello — Fri, 23 Sep 2022 21:12:50 GMT

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

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

gcusello — Fri, 23 Sep 2022 21:14:16 GMT

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

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

gcusello — Sat, 24 Sep 2022 05:14:23 GMT

HI @risingflight143,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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