How to sort every 6 rows of a column in splunk?

[maverick27]

Hello Splunk Experts,

Lets say i have a table that contains 2 columns as shown below:

Name	S_no
aaa	1
ccc	3
bbb	2
ddd	4
eee	5
fff	6
ggg	1
iii	3
hhh	2
jjj	4
kkk	5
lll	6
mmm	1
ooo	3
nnn	2
ppp	4
qqq	5
rrr	6

Now, I need to sort every 6 rows of 's_no' column and populate the table. Something like this:

Name	S_no
aaa	1
bbb	2
ccc	3
ddd	4
eee	5
fff	6
ggg	1
hhh	2
iii	3
jjj	4
kkk	5
lll	6
mmm	1
nnn	2
ooo	3
ppp	4
qqq	5
rrr	6

Could you please help me with the query? Much appreciated!

[maverick27]

Hello,

Thankyou @ITWhisperer @meetmshah for the quick revert and apologies for the delay in response. The solution indeed works. However, when I try to create a trellis layout (split by S_no), the graphs are displayed in the original order (1,3,2,4,5,6) and not how I want it to be i.e. 1,2,3,4,5,6. 

Is this a bug by any chance? 

[ITWhisperer]

Sounds like a feature - trellis is probably sorting the display based on the first field

[meetmshah]

Hello @maverick27 sort should work in that case right? ie. - 

| sort GroupNum S_no 

[maverick27]

NO. It doesn't work in trellis layout even though the result is sorted. I am already using the following in the query:

sort 0 group S_no

[ITWhisperer · Accepted solution]

| streamstats count as row
| eval group=floor((row - 1) / 6)
| sort 0 group S_no
| fields - group row

[meetmshah]

Done, Can you please below search in Splunk and confirm if this is something you want - 

| makeresults 
| eval data="aaa,1 ccc,3 bbb,2 ddd,4 eee,5 fff,6 ggg,1 iii,3 hhh,2 jjj,4 kkk,5 lll,6 mmm,1 ooo,3 nnn,2 ppp,4 qqq,5 rrr,6" 
| makemv data delim=" " 
| mvexpand data 
| rex field=data "(?<Name>\w+),(?<S_no>\d+)" 
| streamstats count as row_num 
| eval GroupNum = floor((row_num - 1) / 6) 
| sort GroupNum S_no 
| fields - _time data row_num GroupNum

Output - 

Please accept the solution and hit Karma, if this helps!

[meetmshah]

Hello, Just checking through if the issue was resolved or you have any further questions?