Splunk Search

How to edit my search to sort by subtotal?

gcusello
SplunkTrust
SplunkTrust

Hi at all

I have to show the subtotal of a stats command, but the problem is to sort the results.
My search is:

tag=GP2 | stats count by code day | appendpipe [stats sum(count) AS Totals by code ] | sort code -count

Using this search, I can sort only by code, but I have to sort by totals as shown below.

Code          day      count    totals
Code1     2015-01-16     20
Code1     2015-01-15     15
Code1                             35
Code2     2015-01-15     12
Code2     2015-01-16     10
Code2                             22

In other words, I want to group results by Code, to sort by Total and in every code to sort by count.
I found the way to sort only by Code, is it possible to do this?

thank you.

Giuseppe

0 Karma
1 Solution

somesoni2
Revered Legend

This should do it

tag=GP2 | stats count by code day | sort code -count | streamstats count as rank by code | appendpipe [stats sum(count) AS Totals sum(rank) as rank by code ] | sort code rank | fields -rank

View solution in original post

somesoni2
Revered Legend

This should do it

tag=GP2 | stats count by code day | sort code -count | streamstats count as rank by code | appendpipe [stats sum(count) AS Totals sum(rank) as rank by code ] | sort code rank | fields -rank

View solution in original post

gcusello
SplunkTrust
SplunkTrust

using the suggest of somesoni2 I found the solution:

tag=GP2 | stats count by code day | eventstats sum(count) as rank by code | appendpipe [stats values(rank) AS rank sum(count) AS totals by code ] | sort -rank code -count | fields - rank

Thank you.

gcusello
SplunkTrust
SplunkTrust

it's really close to my target but it isn't the solution: I obtained that results are grouped by code, but they aren't sorted by rank.
In addition, I semplified the search I described in my question, in my search I have more fields in stats clause:

tag=GP2 | stats count by code1 code2 code3 day | appendpipe [stats sum(count) AS Totals by code ] | sort code -count

The way (if possible) could be to assign the value of the total for the field code to the rank ?

Thank you.

Giuseppe

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!