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
SplunkTrust
SplunkTrust

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

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...