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.
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
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
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
mvexpand recipient
worked exactly how I was expecting the results to be. Thanks all for your help!