Splunk Search

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

Communicator

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

SplunkTrust
SplunkTrust

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

SplunkTrust
SplunkTrust

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

Communicator

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