Splunk Search

MVZIP With the same field on the same Line

Path Finder

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!

0 Karma
1 Solution

Esteemed Legend

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

View solution in original post

Esteemed Legend

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

View solution in original post

Splunk Employee
Splunk Employee

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

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!