topic Re: String split to the same field in Splunk Search

String split to the same field

ttchorz — Mon, 27 Mar 2017 14:17:33 GMT

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.

Re: String split to the same field

somesoni2 — Mon, 27 Mar 2017 14:31:02 GMT

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

Re: String split to the same field

DalJeanis — Mon, 27 Mar 2017 15:46:58 GMT

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

Re: String split to the same field

ttchorz — Mon, 27 Mar 2017 17:56:24 GMT

mvexpand recipient

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