Splunk Search

split columns with multiple values and make into row

Communicator

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 imagealt text

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

Please help.

Tags (2)
0 Karma
1 Solution

Legend

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

View solution in original post

0 Karma

Legend

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

View solution in original post

0 Karma

Communicator

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

0 Karma

Legend

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

0 Karma