How to edit my search to sort by date, time, and user?

[HCadmins]

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.

[sundareshr]

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

[HCadmins]

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.

[somesoni2 · Accepted solution]

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

[HCadmins]

Hi, thanks for the answer. It's still grouping the events by user and not by time.

[somesoni2]

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 

[somesoni2]

Or provide some sample expected output and current output?

[HCadmins]

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.

[somesoni2]

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

[HCadmins]

Okay, I think that worked. Thank you for your help!