Splunk Search

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

New Member

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
0 Karma

Esteemed Legend

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
0 Karma

New Member

Hi @arjunpkishore5

I will test the above and get back to you.

Cheers,
Adam

0 Karma

Motivator

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!