Splunk Search

Total foreach Row

cewing082
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

woodcock
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

cewing082
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

woodcock
Esteemed Legend

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

| rename TOTAL AS _TOTAL

xpac
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

xpac
SplunkTrust
SplunkTrust

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

0 Karma

xpac
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...