How to compare two user lists and find number of distinct users who have successful/failed login events

[adam_ali_syd]

I need some help in formulating a complex search command. The requirement is to take one list (list2) of users and see which ones on the list don't match the ones on the other list (list1).

I have a set of events for users logins as per the below:

_time username loginStatus

---

2019-10-30 19:00:39.819 theCrazy1@gmail.com success
2019-10-30 19:00:28.266 theCrazy1@gmail.com fail
2019-10-30 19:00:13.158 theCrazy2@gmail.com success
2019-10-30 19:00:12.383 theCrazy2@gmail.com fail
2019-10-30 19:00:12.381 theCrazy3@gmail.com fail
2019-10-30 19:00:12.382 theCrazy4@gmail.com fail
2019-10-30 19:00:12.384 theCrazy5@gmail.com fail
2019-10-30 19:00:12.385 theCrazy6@gmail.com fail
2019-10-30 19:00:12.386 theCrazy7@gmail.com fail

As you can see from the above, some users have an event for successful logins as well as an event for failed logins. Some other users have only failed login events. I want to a search command that can return the below:

1. number of distinct users who have successful login events
   • |tstats dc(username) where loginStatus::success by loginStatus
2. number of distinct users who has failed login events but no successful login events
   • no idea how to do that based on 1 above

Based on the above, the result should be:

Success failed

---

2 5

Could you please confirm the below:
- Can Splunk do that?
- If yes, what would be the most efficient way to do it?
- Can you give the exact search command based on the above?

The only thing I could come up with was the below which didn't work:

index=myindex  loginStatus::success | table username |rename username AS username2 |dedup username2 sortBy username2 | append [search index= myindex  loginStatus::fail | table username | dedup username sortBy username] | diff diff maxlen=0 attribute= username

[woodcock]

Like this:

|makeresults | eval _raw="
                   time            username loginStatus
2019-10-30T19:00:39.819 theCrazy1@gmail.com success
2019-10-30T19:00:28.266 theCrazy1@gmail.com fail
2019-10-30T19:00:13.158 theCrazy2@gmail.com success
2019-10-30T19:00:12.383 theCrazy2@gmail.com fail
2019-10-30T19:00:12.381 theCrazy3@gmail.com fail
2019-10-30T19:00:12.382 theCrazy4@gmail.com fail
2019-10-30T19:00:12.384 theCrazy5@gmail.com fail
2019-10-30T19:00:12.385 theCrazy6@gmail.com fail
2019-10-30T19:00:12.386 theCrazy7@gmail.com fail"
| multikv forceheader=1 copyattrs=f
| eval _time = strptime(time, "%Y-%m-%dT%H:%M:%S.%3N")
| table _time username loginStatus
| sort 0 - _time

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| dedup username
| stats count BY loginStatus

[adam_ali_syd]

Hi @arjunpkishore5

I will test the above and get back to you.

Cheers,
Adam

[arjunpkishore5]

Try this

your base search
| stats count(eval(loginStatus=="success")) as success,count(eval(loginStatus=="fail")) as fail by username
| fillnull success value=0
| stats dc(eval(if(success>0, username,null()))) as success, dc(eval(if(success==0, username,null()))) as fail

Hope this helps