split columns with multiple values and make into row

[surekhasplunk]

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.

[gcusello · Accepted solution]

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

[surekhasplunk]

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

[gcusello]

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