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:
Based on the above, the result should be:
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
|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
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