How to separate Multivalue row into their own multiple rows?

[jpfrancetic]

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

[jpfrancetic · Accepted solution]

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

[kamlesh_vaghela]

@jpfrancetic 

Did you tried mvexpand command?

YOUR_SEARCH
| mvexpand Instance_name

Sample Search:

| makeresults | eval _raw="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" | multikv forceheader=1 | eval Instance_name = split(Instance_name,"|")
| table Domain_Name,Instance_name,Last_Phone_home,Search_execution_time
|rename comment as "Upto now is for sample data only" 
| mvexpand Instance_name

 

KV

[jpfrancetic]

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

[kamlesh_vaghela]

😕

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

[gcusello]

Hi @jpfrancetic,

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

Ciao.

Giuseppe

[jpfrancetic]

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

[gcusello]

Hi @jpfrancetic,

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

Ciao.

Giuseppe

[jpfrancetic]

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)