Hi, I am new to splunk and got stuck with this requirement to split part of a row to other row..

[pankaj31]

I have a query which gives data in the below format:

ABC BCD EFG HIJ KLM NOP
123 234 456 12.33 23.45 34.6

And i need some way to get it in the below form:

ABC DEF GHI
123 345 567
12.33 23.45 34.6

I have tried using transpose and some untable queries but nothing seems to work for my need.
Please let me know if you can suggest something....

Thanks

[DalJeanis]

@pankaj31 - "stuck with this requirement"? Really? You're beginning an exciting journey with one of the hottest new languages of the decade. You get to STRETCH! REJOICE!

[DalJeanis]

You guys are trying too hard.

You only need three result fields, and each of those needs to be filled in either of two ways depending on whether it's the first or second line. So, just use some mv field with two values and mvexpand that field to differentiate between the first and second rows, and then use if to set each of your three variables. Finally, kill all the unneeded variables.

 | makeresults
 | eval ABC=123, BCD=234, EFG=456, HIJ=12.33, KLM=23.45, NOP=34.6
 | rename COMMENT as "The above creates test data."

 | rename COMMENT as "Create a variable to mark which row it is, then dup the data into two rows."
 | eval warp=mvappend("row1","row2")
 | mvexpand warp

 | rename COMMENT as "Set the three variables with their correct values for the row."
 | eval ABC=if(warp="row1",ABC,HIJ)
 | eval DEF=if(warp="row1",BCD,KLM)
 | eval GHI=if(warp="row1",EFG,NOP)

 | rename COMMENT as "Finally, kill all the unneeded variables."
 | table ABC DEF GHI

....with the following results ...

ABC    DEF    GHI 
123    234    456  
12.33  23.45  34.6  

[niketn]

@pankaj31, you can try the following. First two pipes are used to generate mock data:

| makeresults
| eval ABC=123, BCD=234, EFG=456, HIJ=12.33, KLM=23.45, NOP=34.6
| eval row1=ABC.",".BCD.",".EFG
| eval row2=HIJ.",".KLM.",".NOP
| eval row1=split(row1,",")
| eval row2=split(row2,",")
| eval row=mvzip(row1,row2)
| mvexpand row
| eval row=split(row,",")
| eval row1=mvindex(row,0)
| eval row2=mvindex(row,1)
| table row1 row2
| transpose
| fields row*

[niketn]

@pankaj31, is there a correlation between every 3rd column like ABC and HIJ? Are there going to be only 6 columns? Are their names going to be fixed?

[pankaj31]

Hi @niketnilay,
Yes, there are going to be 6 columns only with fixed column names.

[niketn]

Just curious, can you also have more than one row? Can you give actual Field Names? Can you share what is the current query (anonymize any sensitive information if required)?

[pankaj31]

@niketnilay: the query is always generating only single row result with 6 columns and as mentioned above i need to bring 3 columns data on the second row.

To keep my query simple you can assume that the columns and rows format and count are not going to increase or decrease.