topic Re: split columns with multiple values and make into row in Splunk Search

split columns with multiple values and make into row

surekhasplunk — Thu, 10 Oct 2019 15:40:16 GMT

Hi,

I have a query output which have many fields out of which only 2 fields have more than one values.
So when those fields have more than 1 value i want them to make new row entry with other field values remaining same.

index=assets [|inputlookup abc.csv | search "Infrastrucure Name"="*" AND teamInCharge="*" AND type="*"| fields + Nom | rename Nom as name]   | search result="*" AND ruleName="*" | table name  "mgmtAddress.ip" ruleName  policyName  result status | rename "mgmtAddress.ip"  as ip | eval name=upper(name)| lookup abc.csv  Nom as name output "Infrastrucure Name" "teamInCharge"

Output is as shown in the image

I want now 2 different rows as there are 2 Infrastructure names.

Please help.

Re: split columns with multiple values and make into row

gcusello — Thu, 10 Oct 2019 16:01:08 GMT

Hi surekhasplunk,
did you tried with mvexpand command?

 index=assets [|inputlookup abc.csv | search "Infrastrucure Name"="*" AND teamInCharge="*" AND type="*"| fields + Nom | rename Nom as name]   result="*" AND ruleName="*" 
| table name  "mgmtAddress.ip" ruleName  policyName  result status 
| rename "mgmtAddress.ip"  as ip 
| eval name=upper(name)
| lookup abc.csv  Nom as name OUTPUT "Infrastrucure Name" "teamInCharge"
| mvexpand "Infrastrucure Name"

Bye.
Giuseppe

Re: split columns with multiple values and make into row

surekhasplunk — Mon, 14 Oct 2019 10:31:16 GMT

Hi @gcusello,

I have few more cases where i have the field "Infrastructure Name" look like this "FRA-DMZ APP|FRA-DMZ STANDARD MARKET ACCESS|FRA-WEBCELL GBIS" . They are separated by pipe symbol . Now how to split them into different rows ?

Please help.

thanks

Re: split columns with multiple values and make into row

gcusello — Mon, 14 Oct 2019 11:00:33 GMT

Hi surekhasplunk,
To split a multivalue field in many row, you can use the mvexpand command (see https://docs.splunk.com/Documentation/Splunk/7.3.2/SearchReference/Mvexpand ).
To create a multivalue field from a list of values divided by a separator (like your example) you can use the makemv command (see https://docs.splunk.com/Documentation/Splunk/7.3.2/SearchReference/Makemv ).
You can use this command separately (as your first request) or together (like this last request).

E.g., you could use makemv to create multivalue field and then use mvexpand to split in different events, this only an example:

| makeresults 
| eval my_field="FRA-DMZ APP|FRA-DMZ STANDARD MARKET ACCESS|FRA-WEBCELL GBIS"
| makemv delim="|" my_field
| mvexpand my_field

Ciao.
Giuseppe