topic Re: Separate Multivalue row into multiple rows in Splunk Search

How to separate Multivalue row into their own multiple rows?

jpfrancetic — Thu, 03 Mar 2022 17:45:50 GMT

I am trying to separate multi value rows into their own rows. I have been trying to separate by adding a comma after the end of each row and then splitting them based on the comma, but I am only able to split the first repetition of the pattern. Can anyone help?

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	2022-02-28	2022-03-3

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-02
Domain1.com	instance2.com	2022-02-28	2022-03-02
Domain1.com	instance3.com	2022-02-28	2022-03-02
Domain1.com	instance4.com	2022-02-28	2022-03-02

Re: Separate Multivalue row into multiple rows

gcusello — Thu, 03 Mar 2022 16:28:11 GMT

Hi @jpfrancetic,

did you tried with mvexpand (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Mvexpand)?

Ciao.

Giuseppe

Re: Separate Multivalue row into multiple rows

jpfrancetic — Thu, 03 Mar 2022 16:31:58 GMT

I have tried that but it isn't separating the values. This led me to try adding a comma to the end of each line in order to separate the commas. But when I do that, it only adds a comma to the first .com and misses the rest in a given row

Re: Separate Multivalue row into multiple rows

gcusello — Thu, 03 Mar 2022 16:36:55 GMT

Hi @jpfrancetic,

could you share your search and a sample of your events?

Ciao.

Giuseppe

Re: Separate Multivalue row into multiple rows

kamlesh_vaghela — Thu, 03 Mar 2022 16:39:08 GMT

@jpfrancetic

Did you tried mvexpand command?

YOUR_SEARCH | mvexpand Instance_name

Sample Search:

Re: Separate Multivalue row into multiple rows

jpfrancetic — Thu, 03 Mar 2022 17:18:53 GMT

This is the current search I am running. Replacing the .com/.net with a comma at the end then separating it on the comma

| inputlookup last_activity.csv
| eval executionTime=strftime(now(), "%Y-%m-%d %H.%M.%S")
| fields fqdn, ci_name_instance, first_onboarded, last_phone_home_time, executionTime
| where isnotnull(fqdn)
| replace "*.com*" WITH "*.com,*" IN ci_name_instance
| replace "*.net*" WITH "*.net,*" IN ci_name_instance
| eval ci_name_instance=split(ci_name_instance,",")
| mvexpand ci_name_instance
| replace "" WITH "NULL" IN ci_name_instance
| where ci_name_instance != "NULL"
| eval ci_name_instance = replace (ci_name_instance, "\n","")

Below is a search result without the row separation (blurred out for confidentiality reasons)

Re: Separate Multivalue row into multiple rows

jpfrancetic — Thu, 03 Mar 2022 17:40:09 GMT

mvexpand does not separate the rows when I use it. it only separates when I include something like a comma. My biggest problem with this is that it only adds a comma to the first instance. So it would end up looking like this:

Domain Name	Instance name	Last Phone home	Search execution time
Domain1.com	instance1.com, instance2.com instance3.com instance4.com	2022-02-28	2022-03-3

and it separates into this:

Domain Name	Instance name	Last Phone home	Search execution time
Domain1.com	instance1.com	2022-02-28	2022-03-3
Domain1.com	instance2.cominstance3.cominstance4.com	2022-02-28	2022-03-3

Re: Separate Multivalue row into multiple rows

kamlesh_vaghela — Fri, 04 Mar 2022 08:06:31 GMT

😕

just remove comma and try below search as it is.

YOUR_SEARCH | eval Instance_name = split(Instance_name," ") | mvexpand Instance_name

Sample.

| makeresults | eval _raw="Domain_Name,Instance_name,Last_Phone_home,Search_execution_time Domain1.com,,2022-02-28,2022-03-3" | multikv forceheader=1 | eval Instance_name = "instance1.com instance2.com instance3.com instance4.com" | table Domain_Name,Instance_name,Last_Phone_home,Search_execution_time | rename comment as "Upto now is for sample data only" | eval Instance_name = split(Instance_name," ") | mvexpand Instance_name

Re: How to separate Multivalue row into their own multiple rows?

jpfrancetic — Fri, 04 Mar 2022 15:09:28 GMT

UPDATE:

I have solved the problem I am facing. I was experiencing an issue with mvexpand not splitting the rows without prior manipulation. in order to work around this, I replaced all new lines in instance_name with a comma, then split on that comma, and finally expand the values.

| eval instance_name = replace (instance_name , "\n",",")
| eval instance_name =split(instance_name ,",")
| mvexpand instance_name