Splunk Search

How to |addtotals on a |stats list result?

bruno_eduardo
Path Finder

Hi Everyone,

I would like to add a row, about a total (sum), for each segment list (see the picture), and if a list has only one value than there is no need to show a total. (only if possible)

here is what I got:

index="Test" |stats count by "Event Category", "Threat Type" |stats list("Threat Type") as "Threat Type" list(count) as Count by "Event Category"

Using |addtotal doesn't work at all.

alt text

Tags (3)
0 Karma
1 Solution

Runals
Motivator

You could probably add an addcoltotals at the end of this query but haven't tested. Note the change is in the second stats command where I'm doing a sum of the count field

index="Test" |stats count by "Event Category", "Threat Type" |stats sum(count) as Total list("Threat Type") as "Threat Type" list(count) as Count by "Event Category" | where Total > 1 | sort -Total

If you want to sort the results within each section you would need to do that between the stats commands. For example

index="Test" |stats count by "Event Category", "Threat Type" | sort -count |stats sum(count) as Total list("Threat Type") as "Threat Type" list(count) as Count by "Event Category" | where Total > 1 | sort -Total

View solution in original post

somesoni2
Revered Legend

Try something like this

index="Test" |stats count by "Event Category", "Threat Type" |stats list("Threat Type") as "Threat Type" list(count) as Count by "Event Category" | appendpipe [| stats sum(Count) as Count by "Event Category" | eventstats count as noofrows | where noofrows>1| eval "Event Category"='Event Category'."-Total"  ] | appendpipe [| stats sum(Count) as Count count as noofrows | where noofrows>1 | eval "Event Category"="Grand Total" ]

bruno_eduardo
Path Finder

Thanks, Why I can't vote for more than one answer?

0 Karma

Runals
Motivator

You could probably add an addcoltotals at the end of this query but haven't tested. Note the change is in the second stats command where I'm doing a sum of the count field

index="Test" |stats count by "Event Category", "Threat Type" |stats sum(count) as Total list("Threat Type") as "Threat Type" list(count) as Count by "Event Category" | where Total > 1 | sort -Total

If you want to sort the results within each section you would need to do that between the stats commands. For example

index="Test" |stats count by "Event Category", "Threat Type" | sort -count |stats sum(count) as Total list("Threat Type") as "Threat Type" list(count) as Count by "Event Category" | where Total > 1 | sort -Total

View solution in original post

stmyers7941
Path Finder

I tested this method and added the addtotals command...it works well enough:

index="Test" 
| stats count by "Event Category", "Threat Type"
| stats sum(count) as Total list("Threat Type") as "Threat Type" list(count) as Count by "Event Category"
| addtotals col=t fieldname=Total label=TOTAL labelfield="Event Category"
| table "Event Category" "Threat Type" "Count" "Total"

0 Karma

bruno_eduardo
Path Finder

Thanks, Why I can't vote for more than one answer?

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!