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
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.
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.
You can rename the field like this at the end and it will disappear from the chart:
| rename TOTAL AS _TOTAL
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.
Then do a sort on Total, afterwards use | fields
to remove it 😉
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. 🙂
Probably missing the sort command of the field Total
at the end.