separate rows from columns with multivalue

[jrodriguezap]

I'm trying to split a pair of rows with a pair of multivalued columns. The value in both columns is related to each position of the multivalued column. To make myself clear, I'm displaying the initial result table, and below that is the table for the desired result.

I tried mvexpand, but that doesn't give me the expected result.

Example:

I have rows like this:

Domain Name	Instance name	Last Phone home	Search execution time
Domain1.com	instance1.com                      instance2.com instance3.com            instance4.com             instance5.com	2022-02-28	2022-03-1 2022-03-2 2022-03-4 2022-03-5

And I would like to transform them into this:

Domain Name	Instance name	Last Phone home	Search execution time
Domain1.com	instance1.com	2022-02-28	2022-03-01
Domain1.com	instance2.com	2022-02-28	2022-03-02
Domain1.com	instance3.com	2022-02-28
Domain1.com	instance4.com	2022-02-28	2022-03-04
Domain1.com	instance5.com	2022-02-28	2022-03-05

[jrodriguezap]

Thank you very much @PrewinThomas , with what you commented along with @bowesmana  I was able to specify what I needed

[ITWhisperer]

Perhaps the reason you are struggling is because you have painted yourself into a corner; try taking a step back. How did you get to the position of have 2 multi-value fields in the first place. Perhaps there is another way to create the table so that you don't lose the correlation between instance name and the execution time. Please share some anonymised sample events and the search that you are using to create the table in the first place.

[isoutamo]

Have you created that table with stats ... values? You should try list instead of values and then those lines will keep their orders and amounts also match. After that you could use above mvzip trick to split those to correct rows.

[livehybrid]

Hi @jrodriguezap 

As others have said, it isnt clear how "Search execution time" for instance3.com is known to be blank. How are you getting the data to this point, there may be something you can do further back in your search to create a known empty placeholder for empty results. 

Based on the logic for "Last phone home" where you have a single date that is associated with all your rows, it could be interpreted that any one of the search execution times could be associated with instance3.com.

Are you able to share more info on how you got to your initial table?

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[yuanliu]

You need to clarify a fundamental gap in your problem: What determines that it is instance3.com that is missing Search execution time, and not instance1.com, instance2.com, or any other Instance name?  Without a definitive condition, your task is an impossible one.

[bowesmana · Accepted solution]

You can only expand that if you can correlate the instance name with the search execution time MV fields.

In your desired output you show that instance3 does not have a search execution time. How can that be "known" by the search? Is the presence of Last Phone home for instance3 relevant.

But generally if you have a 1:1 relationship with the MV elements in more than one field, the solution is as @PrewinThomas suggests, in that you mvzip the two MV fields together, remove those fields, mvexpand and split again, i.e.

...
| eval combined=mvzip('Instance name', 'Search execution time', "##")
| fields - "Instance name" "Search execution time"
| mvexpand combined
| rex field=combined "(?<Instance name>.*##)##(?<Search execution time>.*)"
| fields - combined

but in your case that won't work because you have 5 elements in one field and 4 in the other, so you have to understand how to deal with the missing instance3 data.

[jrodriguezap]

Applying this suggestion worked for me...
I've tested it with more data, and so far there have been no inconsistencies.

I really appreciate the input! 😄

[PrewinThomas]

@jrodriguezap 

Can you try this,

| eval pair=mvzip('Instance name', 'Search execution time', "||")
| mvexpand pair
| eval "Instance name"=mvindex(split(pair,"||"),0), "Search execution time"=mvindex(split(pair,"||"),1)
| fields "Domain Name" "Instance name" "Last Phone home" "Search execution time"

Regards,
Prewin
Splunk Enthusiast | Always happy to help! If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!