MVZIP With the same field on the same Line

[raby1996]

Hello All,

I have a set of data that looks like the excerpt below:

[44] 2017-12-22 to 2017-12-29:
       2017-12-22 (Fri) to  2017-12-29 (Fri) : rotation1 : p1@email.com
       2017-12-22 (Fri) and 2017-12-23 (Sat) : rotation2     : p2@email.com
       2017-12-23 (Sat) and 2017-12-24 (Sun) : rotation3   : p3@email.com
       2017-12-23 (Sat) and 2017-12-24 (Sun) : rotation4    : p4@email.com
       2017-12-25 (Mon) to  2017-12-29 (Fri) : rotation5 : p5@email.com
       2017-12-25 (Mon) to  2017-12-29 (Fri) : rotation6 : p6@email.com
       2017-12-25 (Mon) to  2017-12-29 (Fri) : rotation7    : p7@email.com p8@email.com

And, I am able to extract each person on each rotation and create a list by table using the stats command which looks something like this:

Person                     Rotation                 Dates                      
p1@email.com              rotation1              2017-12-22 (Fri) to 2017-12-29 (Fri)

This continues, and I get a table listing all the person's rotations for the year, however I run into an issue for rotation7 since there are usually 2 people on it, when I use mvzip for the rotation, dates and person It only picks up the first name, so it would look like rotation=(2017-12-25 (Mon) to 2017-12-29 (Fri) , rotation7, p7@email.com), however what I need is for it to pick up the second name as well and zip it with the correct rotation as a separate entry.
In other words, it would be the previous rotation I.E
rotation=(2017-12-25 (Mon) to 2017-12-29 (Fri) , rotation7, p7@email.com),
and another event as
rotation=(2017-12-25 (Mon) to 2017-12-29 (Fri) , rotation7, p8@email.com).

I have max_match=0, and I am expanding the events however I have not been able to figure this out, are the any suggestions?

Thanks you!

[woodcock · Accepted solution]

This fakes the data:

|makeresults| eval _raw=" [44] 2017-12-22 to 2017-12-29:
    2017-12-22 (Fri) to  2017-12-29 (Fri) : rotation1 : p1@email.com
    2017-12-22 (Fri) and 2017-12-23 (Sat) : rotation2     : p2@email.com
    2017-12-23 (Sat) and 2017-12-24 (Sun) : rotation3   : p3@email.com
    2017-12-23 (Sat) and 2017-12-24 (Sun) : rotation4    : p4@email.com
    2017-12-25 (Mon) to  2017-12-29 (Fri) : rotation5 : p5@email.com
    2017-12-25 (Mon) to  2017-12-29 (Fri) : rotation6 : p6@email.com
    2017-12-25 (Mon) to  2017-12-29 (Fri) : rotation7    : p7@email.com p8@email.com"

This is the solution:

| rex max_match=0 "(?ms)(?:^|[\r\n])\s*(?<row>\d+[^\r\n]+)"
| mvexpand row
| rename row AS _raw | fields - _time
| rex "(?<dates>\d+\-\d+\-\d+\s+\(\w+\)\s+\w+\s+\d+\-\d+\-\d+\s+\(\w+\))\s+:\s+(?<rotation>rotation\d+)\s+:\s+(?<person>[^\r\n]+)(?<!\s)"
| makemv person
| mvexpand person

[sduff_splunk]

Can you provide the full search you are using to convert the data into the table?
I expect we will be able to extend the regex you are using to include all the email addresses, do an makemv and then mvzip to get the output you require