Splunk Search

How to edit my search to sort by subtotal?

gcusello
Legend

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

gcusello
Legend

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
Legend

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
Get Updates on the Splunk Community!

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...