Splunk Search
Highlighted

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

New Member

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

0 Karma
Highlighted

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

Super Champion

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

0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

Legend

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

0 Karma
Highlighted

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

Ultra Champion

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

View solution in original post

0 Karma
Highlighted

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

New Member

Bucket_time did the trick. Thank you so much!

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.