How to search the distinct counts of failed and successful usernames used in login transactions, group by IP, and total for each IP?

[hokieb]

I am trying to pull distinct counts of failed and successful usernames used in login transactions grouped by IP address along with overall totals for each IP.

Example:

.... | stats count(eval(status=="FAILED")) as FAILED, dc(if(status=="FAILED", username)) as FAILED_UNIQUE, count(eval(status=="SUCCESS")) as SUCCESS, dc(if(status=="SUCCESS", username)) as SUCCESS_UNIQUE, count(username) as TOTAL, dc(username) as TOTAL_UNIQUE by ipv4 | sort - TOTAL

My results give me correct values in everything except the dc() columns...I suppose since the filtering is not possible within the dc() function. Are there any other ways to efficiently accomplish this?

[bmacias84 · Accepted solution]

You could try something like this. using eval and if to create new fields and copy existing

....| eval failed=if(status=="FAILED",1,0) 
| eval success=if(status=="SUCCESS",1,0) 
| eval failed_name=if(status=="FAILED",username,isnull())
| eval success_name=if(status=="SUCCESS",username,isnul())
| stats sum(failed) as FAILED, sum(sucess) as SUCCESS, dc(failed_name) as FAILED_UNIQUE, dc(success_name) as SUCCESS_UNIQUE, count(username) as TOTAL_UNIQUE by ipv4
|sore - TOTAL

[hokieb]

That got it! Thanks!

Note for others I changed these 2 lines though...
| eval failed_name=if(status=="FAILED",username,NULL)
| eval success_name=if(status=="SUCCESS",username,NULL)