Reporting

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

maverick27
Explorer

Hello Splunk Experts,

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

NameS_no
aaa1
ccc3
bbb2
ddd4
eee5
fff6
ggg1
iii3
hhh2
jjj4
kkk5
lll6
mmm1
ooo3
nnn2
ppp4
qqq5
rrr6


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

NameS_no
aaa1
bbb2
ccc3
ddd4
eee5
fff6
ggg1
hhh2
iii3
jjj4
kkk5
lll6
mmm1
nnn2
ooo3
ppp4
qqq5
rrr6


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

0 Karma
1 Solution

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

View solution in original post

maverick27
Explorer

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? 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

meetmshah
Builder

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

| sort GroupNum S_no 
0 Karma

maverick27
Explorer

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

0 Karma

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

meetmshah
Builder

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 - 

meetmshah_0-1711552853098.png

 

 

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

 

 

meetmshah
Builder

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

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...