Hi Splunk Answers!
I'm new to Splunk. I am trying to create a statistics table that shows our VPN users, their failed logins, and a timestamp.
My current search string is this
host=* sourcetype=UTM:system sub=auth name="Authentication failed" AND "Authentication Failed" | convert timeformat="%m-%d-%y %I:%M %p" ctime(_time) as thetime | stats list(thetime) as "Timestamp" by user
Any sorting I append to the end of that search string doesn't sort by the newest date/time. Preferably, I'd like to see the top ten latest failed authentications by user.
Thanks in advance.
Give this a try
host=* sourcetype=UTM:system sub=auth name="Authentication failed" AND "Authentication Failed" | dedup 10 user | convert timeformat="%m-%d-%y %I:%M %p" ctime(_time) as thetime | stats list(thetime) as "Timestamp" by user
Try this
host=* sourcetype=UTM:system sub=auth name="Authentication failed" AND "Authentication Failed" | convert timeformat="%m-%d-%y %I:%M %p" ctime(_time) as thetime | sort thetime | streamstats count by user | where count<=10 | stats list(thetime) as "Timestamp" by user
Hi, and thanks. This string gives the same result as the above result. It lists users alphabetically, then their associated failed logins by time. I'm interested in the 10 most recent failed login attempts and their associated users.
Give this a try
host=* sourcetype=UTM:system sub=auth name="Authentication failed" AND "Authentication Failed" | dedup 10 user | convert timeformat="%m-%d-%y %I:%M %p" ctime(_time) as thetime | stats list(thetime) as "Timestamp" by user
Hi, thanks for the answer. It's still grouping the events by user and not by time.
The query should be showing top 10 latest failed (generated by dedup) authentication entries for every user, as per your requirement. If the sorting of the Timestamp is off, then try this
host=* sourcetype=UTM:system sub=auth name="Authentication failed" AND "Authentication Failed" | dedup 10 user | stats list(_time) as "Timestamp" by user | eval Timestamp=mvsort(Timestamp)| convert timeformat="%m-%d-%y %I:%M %p" ctime(Timestamp) as Timestamp
Or provide some sample expected output and current output?
Hi, and thanks again for your help. I've attached a screenshot of the output. What it appears to be doing is listing the users alphabetically, and then each user's latest failed logins. I was hoping to get the latest failed logins, and their associated user.
It may be simple as this.
host=* sourcetype=UTM:system sub=auth name="Authentication failed" AND "Authentication Failed" | head 10 | table user _time | eval Timestamp=strftime(_time,"%m-%d-%y %I:%M %p") | table user Timestamp
Okay, I think that worked. Thank you for your help!