topic Re: How to |addtotals on a |stats list result? in Splunk Search

How to |addtotals on a |stats list result?

bruno_eduardo — Mon, 26 Oct 2015 19:45:14 GMT

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.

Re: How to |addtotals on a |stats list result?

Runals — Mon, 26 Oct 2015 20:01:07 GMT

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

Re: How to |addtotals on a |stats list result?

somesoni2 — Mon, 26 Oct 2015 20:04:12 GMT

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" ]

Re: How to |addtotals on a |stats list result?

bruno_eduardo — Mon, 26 Oct 2015 20:14:16 GMT

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

Re: How to |addtotals on a |stats list result?

bruno_eduardo — Mon, 26 Oct 2015 20:15:14 GMT

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

Re: How to |addtotals on a |stats list result?

stmyers7941 — Mon, 26 Oct 2015 20:51:55 GMT

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"