Splunk Search

String split to the same field

ttchorz
Path Finder

Hi,

I am doing some email count analysis and run into the following problem.
For inbound email the recipients field contains a string of email addresses(it could be few or hundreds of addresses) each separated by ";".

Using eval recipient=split(recipients,";") I am able to split them but they are all split into a single field recipient which contains multiple values.

I want to do ...|search recipient="abc@abc.com" | timechart span=1d by recipient however the search returns timechart for ALL users in the receipent field.

I was thinking about using regex but I do not know how many fields to extract based on various number of recipients for each email message.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

You can use mvexpand command to convert each recipient into single row. Use it after your eval -split command. So it'll be like this

your base search | eval recipient=split(recipients,";") | mvexpand recipient |search recipient="abc@abc.com" | timechart span=1d count by recipient

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

What to do is going to depend on what you want with all those timecharts.

If you don't want to look at timechart for all the recipients, then put into words what you DO want to look at.

I suspect that you could use mvexpand to create a single record for each person for each email, then use stats (not timechart) against a binned _time field to roll them all together, then from that, select which persons you want to analyse. For example, something like the following would give you timecharts for the top 5 recipients who received the most emails. (air code)

 your base search 
| eval recipient=split(recipients,";") 
| mvexpand recipient 
| bin _time span=1d
| stats count as rdcount by recipient _time
| appendpipe [| stats sum(rdcount) as ecount by recipient | top 5 ecount ]
| eventstats max(ecount) as ecount by recipient
| where isnotnull(ecount) 
| timechart span=1d sum(rdcount) as count by recipient 
0 Karma

somesoni2
SplunkTrust
SplunkTrust

You can use mvexpand command to convert each recipient into single row. Use it after your eval -split command. So it'll be like this

your base search | eval recipient=split(recipients,";") | mvexpand recipient |search recipient="abc@abc.com" | timechart span=1d count by recipient

ttchorz
Path Finder
mvexpand recipient

worked exactly how I was expecting the results to be. Thanks all for your help!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...