How to |addtotals on a |stats list result?

[bruno_eduardo]

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.

[somesoni2]

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]

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

[Runals · Accepted solution]

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]

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"

[bruno_eduardo]

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