Splunk Search

Arrange non-null values in a field

rupesh26
Path Finder

Hi,

I have the below events

100, ABC, , , 110, DEF, , , , , , , 
, ,120 ,GHI, 130, JKL, , , , , , ,
, ,140 ,MNO , , , , , , , 150,PQR ,

Ex: for the below event,
100, ABC, , , 110, DEF, , , , , , ,

these are the fields

Key_ID1="100"
Key_Value1="ABC"
Key_ID2=""
Key_Value2=""
Key_ID3="110"
Key_Value3="DEF"
Key_ID4=""
Key_Value4=""
Key_ID5=""
Key_Value5=""
Key_ID6=""
Key_Value6=""

I need to assign all these fields to a single field and move all null values to one side and not null values to the other side. something like below

100,ABC,110,DEF , , , , , , , , , 
120,GHI,130,JKL, , , , , , , , ,
140,MNO,150,PQR, , , , , , , , ,

then it has to be exported to a report.

I tried if conditions coalesce, nothing helped. Did someone tried this before ?

Tags (1)
0 Karma
1 Solution

jawaharas
Motivator

Not efficient, but works! Play around.

| makeresults 
| eval myVar=",,140,MNO,,,,,,,150,PQR," 
| rex field=myVar mode=sed "s/,{2,}/,/g" 
| rex field=myVar mode=sed "s/^,|,$//g" 
| eval myVarCopy=myVar 
| makemv delim="," myVarCopy 
| eval myCount=mvcount(myVarCopy) 
`comment("by counting number of existing fields, remaining null fields are added as commas")`
| eval output=case(myCount=4,myVar . ",,,,,,,,", myCount=5,myVar . ",,,,,,,") 
| fields output

View solution in original post

jawaharas
Motivator

Not efficient, but works! Play around.

| makeresults 
| eval myVar=",,140,MNO,,,,,,,150,PQR," 
| rex field=myVar mode=sed "s/,{2,}/,/g" 
| rex field=myVar mode=sed "s/^,|,$//g" 
| eval myVarCopy=myVar 
| makemv delim="," myVarCopy 
| eval myCount=mvcount(myVarCopy) 
`comment("by counting number of existing fields, remaining null fields are added as commas")`
| eval output=case(myCount=4,myVar . ",,,,,,,,", myCount=5,myVar . ",,,,,,,") 
| fields output

rupesh26
Path Finder

That Helped 🙂
Thanks

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...