How to create a search to make a table of count of failed logins by user with fields in other columns?

[supreetsingh75]

A table with the count of failed login by a user for a day over the period of 7 days with the columns date, sourceip, destination ip, user and count. for eg.

date sourceip destinationip user count
Jan-18-2018 10.12.13.14 10.12.2.3 rolland 5
Jan-19-2018 10.12.13.14 10.12.2.3 rolland 12

[yannK · Accepted solution]

Make sure that the time field is extracted, then you can use a bucket _time and sum up the count per user.

  index=myindex source=mytable | bucket _time span=24h | stats sum(count) by _time user

[supreetsingh75]

Bucket_time did the trick. Thank you so much!

[493669]

From what data or table you want to create search to get count of failed logins ?

[supreetsingh75]

It's a custom index that logs failed logins as actions.

[somesoni2]

Does all events have all the fields that you want to show? If yes, you can do something like this

your base search
| eval date =strftime(_time,"%b-%d-%Y")
| stats count by date sourceip destinationip user

[supreetsingh75]

this is great but I need this query to produce a report that shows a count of user login failure for 24 hours over a period of 7 days. See my output

date sourceip destinationip user count
Jan-18-2018 10.12.13.14 10.12.2.3 rolland 5
Jan-19-2018 10.12.13.14 10.12.2.3 rolland 12

Your query does not show me the same output

[niketn]

@supreetsingh75, what is the event logged during a failed login (request/response)? Please mock/anonymize any sensitive data before posting the events.