The following are sample logs for successful login and incorrect password attempts based on email address:
May 2 00:19:58 MyAccount INFO: com.ebpsource.Consumer.Customer0: - I3 00100 Customer0 - *****COMPLETED Login xyz@gmail.com
May 2 00:21:31 MyAccount ERROR: com.ebpsource.Consumer.Customer: - E0 35010 Customer - Password not matched when logging on user xyz@GMAIL.COM (external user key of 20102:1570022) from IP address 10.211.140.27
The following is the required output:
Email Address Successgful Login Attempts Incorrect Password Attempts Date
xyz@gmail.com 4 2 02-May-2017
abc@hotmail.com 1 6 01-May-2017
wert@abc.com 3 4 38-Apr-2017
Appreciate feedback as to what should be the search criteria to extract both this information for successful logins/incorrect passwords attempt by users:
Right now I am able to gather one portion of information using the following search criteria:
index =os host = myaccount "COMPLETED Login" | stats count as "Successfull Login Attempts" by EmailAdd
However I like to also have the counts for incorrect password attempts along with date added in this result, I tried using the following APPEND command to add it but doesnt work:
index =os host = myaccount "COMPLETED Login" | stats count as "Successfull Login Attempts" by EmailAdd | append [search index =os host = myaccount "Password not matched" | stats count as "Incorrect Password Attempts" by EmailAdd]
That output format is probably not ideal, but we'll look at that after we've collected the data and given you a look at it...
search index =os host = myaccount ("COMPLETED Login" OR "Password not matched")
| rex field=_raw "(?<result>COMPLETED)"
| eval result=if(isnull(result),"Failure","Success")
| table EmailAdd _time result
Now we have a record for each success or failure, with the date and time. Let's keep the entire date and time for now...
| eval failTime=if(result="Failure",strftime(_time,"%Y-%m-%d %H:%M:%S"),null())
| eval failCount=if(result="Failure",1,0)
| eval succTime=if(result="Success",strftime(_time,"%Y-%m-%d %H:%M:%S"),null())
| eval succCount=if(result="Success",1,0)
| bin _time span=1d
| stats sum(failCount) as FailureCount, values(failTime) as FailureTimes, sum(succCount) as SuccessCount, values(succTime) as SuccessTimes by EmailAdd _time
You can change the span to weekly or monthly if you want to aggregate at a higher level.
For example, let's suppose we don't care what time of day the access attempts are, and we want to see a week at a time.
| eval failDate=if(result="Failure",strftime(_time,"%Y-%m-%d"),null())
| eval failCount=if(result="Failure",1,0)
| eval succDate=if(result="Success",strftime(_time,"%Y-%m-%d"),null())
| eval succCount=if(result="Success",1,0)
| bin _time span=1d
| stats sum(failCount) as FailureCount, values(failDate) as FailureDates, sum(succCount) as SuccessCount, values(succDate) as SuccessDates by EmailAdd _time
| eval FailureDates=FailureDates."...Count=".FailureCount
| eval SuccessDates=SuccessDates."...Count=".SuccessCount
| bin _time span=1w
| stats sum(FailureCount) as FailureCount, values(FailureTimes) as FailureTimes, sum(SuccessCount) as SuccessCount, values(SuccessTimes) as SuccessTimes by EmailAdd _time
That output format is probably not ideal, but we'll look at that after we've collected the data and given you a look at it...
search index =os host = myaccount ("COMPLETED Login" OR "Password not matched")
| rex field=_raw "(?<result>COMPLETED)"
| eval result=if(isnull(result),"Failure","Success")
| table EmailAdd _time result
Now we have a record for each success or failure, with the date and time. Let's keep the entire date and time for now...
| eval failTime=if(result="Failure",strftime(_time,"%Y-%m-%d %H:%M:%S"),null())
| eval failCount=if(result="Failure",1,0)
| eval succTime=if(result="Success",strftime(_time,"%Y-%m-%d %H:%M:%S"),null())
| eval succCount=if(result="Success",1,0)
| bin _time span=1d
| stats sum(failCount) as FailureCount, values(failTime) as FailureTimes, sum(succCount) as SuccessCount, values(succTime) as SuccessTimes by EmailAdd _time
You can change the span to weekly or monthly if you want to aggregate at a higher level.
For example, let's suppose we don't care what time of day the access attempts are, and we want to see a week at a time.
| eval failDate=if(result="Failure",strftime(_time,"%Y-%m-%d"),null())
| eval failCount=if(result="Failure",1,0)
| eval succDate=if(result="Success",strftime(_time,"%Y-%m-%d"),null())
| eval succCount=if(result="Success",1,0)
| bin _time span=1d
| stats sum(failCount) as FailureCount, values(failDate) as FailureDates, sum(succCount) as SuccessCount, values(succDate) as SuccessDates by EmailAdd _time
| eval FailureDates=FailureDates."...Count=".FailureCount
| eval SuccessDates=SuccessDates."...Count=".SuccessCount
| bin _time span=1w
| stats sum(FailureCount) as FailureCount, values(FailureTimes) as FailureTimes, sum(SuccessCount) as SuccessCount, values(SuccessTimes) as SuccessTimes by EmailAdd _time
Thanks DalJeanis.
Only question why the extracted field: EmailAdd field is not visible when the search happens for "Password not matched", using field extractor I tried sample log for "Password not matched" , however it says EmailAdd field already exists. The only thing different between the log for "Complete Login" and "Password not matched" is the position of Email Address.
I tried the following recommended search option:
index =os host = myaccount ("COMPLETED Login" OR "Password not matched")|eval failTime=if(result="Failure",strftime(_time,"%Y-%m-%d %H:%M:%S"),null()) | eval failCount=if(result="Failure",1,0) | eval succTime=if(result="Success",strftime(_time,"%Y-%m-%d %H:%M:%S"),null()) | eval succCount=if(result="Success",1,0) | bin _time span=1d | stats sum(failCount) as FailureCount, values(failTime) as FailureTimes, sum(succCount) as SuccessCount, values(succTime) as SuccessTimes by EmailAdd _time
For some reason the output for count columns (FailureCount, FailureTimes,SuccessCount & SuccessTimes are all zero except the initial first few columns i.e. EmailAdd and _time. It worked once but now its not.
Also how can I create a BAR chart graph from this result.
Please advise