Splunk Search

How to have a sort command ignore one result/leave it at the bottom of the sort?

kearaspoor
SplunkTrust
SplunkTrust

I have a search that looks at number of enabled vs disabled users in our AD structure by organizational unit, calculates the sum of each unit and the percentage of accounts enabled. It is successfully charted with the following column names:
OrgUnit, Disabled, Enabled, Total, %Enabled

The problem is that I want it sorted by the %Enabled field AND also retain the column totals at the bottom with the properly calculated percentage. I can get it to do one or the other but can't figure out how to do both.

search | chart count OVER OrgUnit BY AccountDisabled | addtotals row=t col=t label=OrgUnit labelfield=total | eval "%Enabled"=round(100*Enabled/Total,0)

Gets me the properly calculated totals at the bottom of each column but when I add a sort to the %Enabled field the "total" results get mixed in the middle.

search | chart count OVER OrgUnit BY AccountDisabled | addtotals row=t | eval "%Enabled"=round(100*Enabled/Total,0) | sort "%Enabled" | addcoltotals label=OrgUnit labelfield=total

Gets me the correct sort order but adds together all the percentage values rather than calculates the percent of the totals. (so it ends up being over 100%)

I've also played around with getting the addcoltotals value to skip the percentage column, and adding a new eval to get the correctly calculated percentage for the totals, but then I'm not having any luck getting it to combine with the existing %Enabled results.

Thank you!

0 Karma
1 Solution

somesoni2
Revered Legend

Try this

search | chart count OVER OrgUnit BY AccountDisabled | eval Total=Enabled+Disabled  | eval "%Enabled"=round(100*Enabled/Total,0)  | sort "%Enabled" | appendpipe [|stats sum(Enabled) as Enabled sum(Disabled) as Disabled sum(Total) as Total |eval OrgUnit="ALL" | eval "%Enabled"=round(100*Enabled/Total,0  ]

View solution in original post

0 Karma

somesoni2
Revered Legend

Try this

search | chart count OVER OrgUnit BY AccountDisabled | eval Total=Enabled+Disabled  | eval "%Enabled"=round(100*Enabled/Total,0)  | sort "%Enabled" | appendpipe [|stats sum(Enabled) as Enabled sum(Disabled) as Disabled sum(Total) as Total |eval OrgUnit="ALL" | eval "%Enabled"=round(100*Enabled/Total,0  ]
0 Karma

kearaspoor
SplunkTrust
SplunkTrust

Other than needing to add a closing parenthesis to the last eval it works like a charm! Thank you!

Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...