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!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...