stats count zeroes

[christoffertoft]

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.

[DalJeanis · Accepted solution]

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

[christoffertoft]

Wow, this helped! thanks alot for the help!

[christoffertoft]

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

[DalJeanis]

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

[christoffertoft]

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

[grittonc]

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

[christoffertoft]

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

something like:

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