need help editing my search to find users who have failed to log in more than 3 times in 10 minutes, then successfully logged in

[janderson19]

Hello,

I'm working on a search for blackboard that will return users who have failed to log in more than 3 times in 10 minutes. I'd like to include a field telling whether or not the student successfully logged in afterward, but I'm not sure how to go about it. I have included my current search below, as well as some sample data.

Search:

index = blackboard earliest=-34h | search evt_code=2 | geoip src_ip | stats sum(evt_code) as count values(evt_name) values(evt_code) as Message values(src_ip) as "Source IP" values(src_ip_city) as City values(src_ip_region_name) as State/Region values(src_ip_country_name) as "Country Name" by duser | bin _time span=10m | sort -count | where count > 2

data:

timestamp=Jul 26 2016 8:00:52.312CDT|app_vend=blackboard|app_name=learn|app_ver=9.1.201510.1171621|evt_code=2|evt_name=login failed|sev=2|cat=authentication|authnprovider=_104_1|dhost=xx.xxxx.edu|outcome=failure|src_ip=xx.xx.xx.xx|duid=_20745_1|duser=duser|text=login failed|authnmethod=login page|http_useragent=Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.75 Safari/537.36

timestamp=Jul 26 2016 18:01:03.453 CDT|app_vend=blackboard|app_name=learn|app_ver=9.1.201510.1171621|evt_code=2|evt_name=login failed|sev=2|cat=authentication|authnprovider=_104_1|dhost=xx.xxxx.edu|outcome=failure|src_ip=xx.xx.xx.xx|duid=_20745_1|duser=duer|text=login failed|authnmethod=login page|http_useragent=Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.75 Safari/537.36

timestamp=Jul 26 2016 23:59:29.502 CDT|app_vend=blackboard|app_name=learn|app_ver=9.1.201510.1171621|evt_code=0|evt_name=login succeeded|sev=0|cat=authentication|authnprovider=_124_1|dhost=xx.xxxx.edu|outcome=success|src_ip=xx.xx.xx.xx|duid=_22885_1|duser=duser|text=login succeeded|authnmethod=login page|http_useragent=Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36

[sundareshr · Accepted solution]

Try this

*UPDATED*

 index = blackboard earliest=-34h evt_code=2 OR evt_code=0 | geoip src_ip | bin _time span=10m | stats count values(evt_name) as EventName values(src_ip) as "Source IP" values(src_ip_city) as City values(src_ip_region_name) as State/Region values(src_ip_country_name) as "Country Name" by duser _time  | sort -count | where count>2 AND mvindex(EventName, mvcount(EventName)-1)="*Success*"

[janderson19]

Ah, yes. Got that. I missed the end when I was copying, I guess haha. What I need help with is showing that the user eventually logged in.

[sundareshr]

Try updated answer

[janderson19]

Got this to work, in a way. I used both of our queries so that they only return a value if the user failed to log in 3 or more times in 10 minutes, and failed to log in at all. Here is the query, for future reference:

set diff [ search index=blackboard AND evt_code=2 | geoip src_ip | stats sum(evt_code) as count values(evt_name) values(evt_code) as Message values(src_ip) as "Source IP" values(src_ip_city) as City values(src_ip_region_name) as State/Region values(src_ip_country_name) as "Country Name" by duser | where count > 2 ] [search index=blackboard AND evt_code=2 OR evt_code=0 | geoip src_ip | stats count values(evt_name) as EventName values(src_ip) as "Source IP" values(src_ip_city) as City values(src_ip_region_name) as State/Region values(src_ip_country_name) as "Country Name" by duser _time  | where count>2 AND mvindex(EventName, mvcount(EventName)-1)="*Success*"] | bin _time span=10m | sort -count