topic Re: Total foreach Row in Splunk Search

Total foreach Row

cewing082 — Wed, 02 May 2018 15:16:35 GMT

I have a query that shows me the type of operations someone has performed but I would also like to sort by the total number of operations (show i can see most active users at the top) - a total column would be awesome as well.

Data:

Name Operation1 Operation2 Operation3

User 5 0 3
User 1 0 0
User 4 1 2

Query so far:
index=*
| eval UserId=mvindex(split(UserId,"@"),0)
| eval tcount=eventcount summarize=false
| lookup peopledata network_uid AS UserId OUTPUT name as Name, location
| chart count by Name, Operation

Re: Total foreach Row

xpac — Wed, 02 May 2018 16:17:48 GMT

Try this:

index=*
| eval UserId=mvindex(split(UserId,"@"),0) 
| eval tcount=eventcount summarize=false
| lookup peopledata network_uid AS UserId OUTPUT name as Name, location 
| chart count by Name, Operation
| addtotals

Hope that helps - if it does I'd be happy if you would upvote/accept this answer, so others could profit from it. 🙂

Re: Total foreach Row

somesoni2 — Wed, 02 May 2018 16:24:30 GMT

Probably missing the sort command of the field Total at the end.

Re: Total foreach Row

woodcock — Wed, 02 May 2018 16:32:17 GMT

You need the addtotals command. Experiment with all 4 patterns of row=t/f and col=t/f. You may need to use fillnull to add a header value on the last row.

Re: Total foreach Row

cewing082 — Wed, 02 May 2018 16:40:08 GMT

addtotals is great but adds a column at the end with the total ( great!) but when i go to chart the values, it includes the total as recorable item.

Re: Total foreach Row

xpac — Wed, 02 May 2018 17:06:13 GMT

Then do a sort on Total, afterwards use | fields to remove it 😉

Re: Total foreach Row

woodcock — Wed, 02 May 2018 18:38:34 GMT

You can rename the field like this at the end and it will disappear from the chart:

| rename TOTAL AS _TOTAL

Re: Total foreach Row

xpac — Wed, 02 May 2018 21:16:42 GMT

Using | addtotals label=_TOTAL should also be possible, right?

Also - if you use sort, remember to use | sort 0 _TOTAL, because by default sort is limited to 10000 results.