Splunk Search
Highlighted

stats count zeroes

Communicator

I have the following search term

.... | 
| stats count(eval(action="failure")) as fails, count(eval(action="success")) as successes by user, host
| stats list(host) as "Hosts Contacted", dc(host) as "Count of Hosts", list(fails) as "Fails per Hostname", count(fails) as "Total Fails", count(successes) as "Successful Logins" by user

Im getting a table like follows:

user.................hosts contacted...count of hosts,......fails per hostname...........total fails...........successful logins
username1............somehost.................2................1...................................2...........................2
.....................somehost2.................................1................................................................ 

As we can see, the query unsuccessfully determines the result of the login attempts. For comparison, if i add list(fails) to the final stats command they will show up as 0s, but the column with Total fails will still add them up. Does my query count 0s as values and add them in the count() function, or am I missing something else here? The goal is to list amount of fails and successful logins (e.g) display the total amount of failed logins per host and the amount of successful logins, grouped by a user. Essentially, it's the same for the successful logins, if i have 4 successful logins and 0 failed, both columns will show 4.

0 Karma
Highlighted

Re: stats count zeroes

Communicator

Sorry for the bad formatting, I don't know how to tabulate data..

0 Karma
Highlighted

Re: stats count zeroes

Contributor

What's the outcome of just the first stats statement?

 .... | 
 | stats count(eval(action="failure")) as fails, count(eval(action="success")) as successes by user, host
0 Karma
Highlighted

Re: stats count zeroes

Communicator

fails will be either 0 or 1, and the same for successes

something like:

user1-------- host1-----1---0
user2---------host1-----1---1

0 Karma
Highlighted

Re: stats count zeroes

SplunkTrust
SplunkTrust

Your second stats needs to use sum() rather than count(). You can also create a synthetic field to connect the count of fails or successes to the appropriate host name.

Try this...

 | stats count(eval(action="failure")) as fails, count(eval(action="success")) as successes by user, host
 | eval hostfails = "host=".host." fails=".fails
 | eval hostsucc = "host=".host." successes=".successes
 | stats values(host) as "Hosts Contacted", dc(host) as "Count of Hosts", 
         values(hostfails) as "Fails by Host","
         sum(fails) as "Total Fails",
         values(hostsucc) as "Success by Host",
         sum(successes) as "Total Successful Logins" 
         by user

View solution in original post

Highlighted

Re: stats count zeroes

Communicator

Wow, this helped! thanks alot for the help!

Highlighted

Re: stats count zeroes

Communicator

Is there an option to not show the field "Success by Host" if successes=0 ? (e.g. show the field but no value?)
EDIT: To clarify, hosts that have no successful logins could show a blank (" ") instead of successes=0

Highlighted

Re: stats count zeroes

SplunkTrust
SplunkTrust

option 1 - leave them off completely...

 | eval hostfails =  case(fails>0,"host=".host." fails=".fails)
 | eval hostsucc = case(successes>0,"host=".host." successes=".successes)

option 2 - list the name without anything after it...

 | eval hostfails =  "host=".host.if(fails>0," fails=".fails,"")
 | eval hostsucc = "host=".host.if(successes>0," successes=".successes,"")
0 Karma