topic Re: Create a table with emails sent and emails received from a given emails addresses in Splunk Search

How to create a table with emails sent and emails received from a given emails addresses?

JLopez — Tue, 08 Nov 2022 16:03:25 GMT

Hi Guys,

I'm trying to create a table with the count emails sent and emails received from a given emails addresses

Column 1 Column 2 Column 3
Email addresses Emails received email sent
bob1@splunk.com <Number> <Number>
bob2@splunk.com <Number> <Number>

I tried this with append command but the result are shown under one another

my search is

index=email_index Recipients IN(bob1@splunk.com, bob2@splunk.com, bob3@splunk.com )
|stats count as "Emails received" by Recipients
| append
[search index=email_index Sender IN(bob1@splunk.com, bob2@splunk.com, bob3@splunk.com )
|stats count as "Emails sent" by Sender]
|table "Emails received" "Emails sent" Recipients Sender

Anyone can help me please?

Re: Create a table with emails sent and emails received from a given emails addresses

bowesmana — Tue, 08 Nov 2022 01:03:43 GMT

Simple version

index=email_index Recipients IN(bob1@splunk.com, bob2@splunk.com, bob3@splunk.com ) | stats count as "Emails received" by Recipients | rename Recipients as EmailAddress | append [ search index=email_index Sender IN(bob1@splunk.com, bob2@splunk.com, bob3@splunk.com ) | stats count as "Emails sent" by Sender | rename Sender as EmailAddress ] | stats values(*) as * by EmailAddress

may be possible to do it with a single search, i.e. without append, but would need more thought.

Re: Create a table with emails sent and emails received from a given emails addresses

yuanliu — Tue, 08 Nov 2022 04:32:12 GMT

Any particular reason to use append? Simple stats seems better suited.

index=email_index (Recipients=* OR Sender=*) | eval EmailAddress = coalesce(Recipients, Sender) | stats count(Recipients) as "Emails received" count(Sender) as "Emails sent" by EmailAddress

If you only want data on three E-mails, you can use IN instead of =*.

Re: Create a table with emails sent and emails received from a given emails addresses

kamlesh_vaghela — Tue, 08 Nov 2022 05:13:23 GMT

@JLopez

You can also try this search. Just go through each statement to get more on logic.

Here have assumed that Recipients may have multiple emails by a comma separated. So you can change the search as per your values in the Recipients fields.

YOUR_SEARCH | eval Recipients = split(Recipients,",") | eval EmailAddress = mvappend(Recipients,Sender) | mvexpand EmailAddress | eval Recipients_flg = if(Recipients=EmailAddress,1,0) | eval Sender_flg = if(Sender=EmailAddress,1,0) | stats sum(Recipients_flg) as "Emails received" sum(Sender_flg) as "Emails sent" by EmailAddress

My Sample Search :

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

Re: Create a table with emails sent and emails received from a given emails addresses

bowesmana — Tue, 08 Nov 2022 06:35:04 GMT

It looks like the reason for the append is that the data is in a single event per email and recipients is MV, so coalesce will always yield Recipients

Re: How to create a table with emails sent and emails received from a given emails addresses?

JLopez — Fri, 11 Nov 2022 10:23:17 GMT

thanks @bowesmana

this worked for me and the visualization is within the same rows! ( that was my pain )

Appreciate your time and expertise!

why are you using the * within the values??

Re: How to create a table with emails sent and emails received from a given emails addresses?

bowesmana — Sun, 13 Nov 2022 22:17:28 GMT

@JLopez Using wildcards is a simple way of shortcutting typing out all the field names.

After the first query and second queries, you have 3 fields, "Emails Sent", "Emails Received" and EmailAddress.

You could do it long hand and do

| stats values("Emails Sent") as "Emails Sent" values("Emails Received") as "Emails Received" by EmailAddress

but you don't need to.

Also, using wildcards for field selections promotes good use of field naming conventions, in that you can use techniques such as partial wildcards to select certain groups of field names, e.g. count_* to reference all fields with the starting text "count_"