Splunk Search

split columns with multiple values and make into row

surekhasplunk
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

gcusello
SplunkTrust
SplunkTrust

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

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

surekhasplunk
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

gcusello
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...