Archive
Highlighted

Total foreach Row

New Member

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

Tags (1)
0 Karma
Highlighted

Re: Total foreach Row

SplunkTrust
SplunkTrust

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. 🙂

0 Karma
Highlighted

Re: Total foreach Row

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Total foreach Row

Esteemed Legend

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.

0 Karma
Highlighted

Re: Total foreach Row

New Member

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.

0 Karma
Highlighted

Re: Total foreach Row

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Total foreach Row

Esteemed Legend

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

| rename TOTAL AS _TOTAL
Highlighted

Re: Total foreach Row

SplunkTrust
SplunkTrust

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.

0 Karma