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

[JLopez]

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?

[JLopez]

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?? 

[bowesmana]

@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_"

[kamlesh_vaghela]

@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 :

| makeresults 
| eval _raw="data
bob1@splunk.com,bob10@splunk.com|bob2@splunk.com
bob1@splunk.com|bob3@splunk.com
bob2@splunk.com|bob1@splunk.com
bob2@splunk.com|bob3@splunk.com
bob3@splunk.com|bob1@splunk.com
bob3@splunk.com|bob2@splunk.com" 
| multikv forceheader=1
| eval Recipients=mvindex(split(data,"|"),0),Sender=mvindex(split(data,"|"),1)
| table Recipients,Sender
| rename comment as "upto this is sample data" 
| 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

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.

[yuanliu]

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 =*. 

[bowesmana]

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

[bowesmana · Accepted solution]

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.