topic Re: stats count zeroes in Splunk Search

stats count zeroes

christoffertoft — Thu, 28 Sep 2017 14:57:58 GMT

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.

Re: stats count zeroes

christoffertoft — Thu, 28 Sep 2017 15:07:26 GMT

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

Re: stats count zeroes

grittonc — Thu, 28 Sep 2017 15:58:53 GMT

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

Re: stats count zeroes

DalJeanis — Thu, 28 Sep 2017 16:49:03 GMT

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

Re: stats count zeroes

christoffertoft — Fri, 29 Sep 2017 07:07:17 GMT

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

something like:

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

Re: stats count zeroes

christoffertoft — Fri, 29 Sep 2017 07:09:22 GMT

Wow, this helped! thanks alot for the help!

Re: stats count zeroes

christoffertoft — Fri, 29 Sep 2017 07:22:28 GMT

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

Re: stats count zeroes

DalJeanis — Fri, 29 Sep 2017 14:19:17 GMT

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,"")