Counting from log based on email add for successful logins / incorrect password

[babidi]

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]

[DalJeanis · Accepted solution]

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

[babidi]

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.

[babidi]

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