Splunk Search

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

JLopez
Explorer

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?

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

View solution in original post

JLopez
Explorer

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

 

Screenshot 2022-11-08 at 10.43.01 AM.png

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.

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

Tags (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...