https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256136#M76688 <P>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.<IMG src="http://i68.tinypic.com/2hygwt1.png" alt="alt text" /></P> Wed, 31 Aug 2016 21:41:16 GMT HCadmins 2016-08-31T21:41:16Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256131#M76683 <P>Hi Splunk Answers!</P> <P>I'm new to Splunk. I am trying to create a statistics table that shows our VPN users, their failed logins, and a timestamp.</P> <P>My current search string is this</P> <PRE><CODE>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 </CODE></PRE> <P>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. </P> <P>Thanks in advance.</P> Wed, 31 Aug 2016 19:54:18 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256131#M76683 HCadmins 2016-08-31T19:54:18Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256132#M76684 <P>Give this a try</P> <PRE><CODE>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 </CODE></PRE> Wed, 31 Aug 2016 21:07:10 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256132#M76684 somesoni2 2016-08-31T21:07:10Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256133#M76685 <P>Hi, thanks for the answer. It's still grouping the events by user and not by time. </P> Wed, 31 Aug 2016 21:22:50 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256133#M76685 HCadmins 2016-08-31T21:22:50Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256134#M76686 <P>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</P> <PRE><CODE>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 </CODE></PRE> Wed, 31 Aug 2016 21:33:49 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256134#M76686 somesoni2 2016-08-31T21:33:49Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256135#M76687 <P>Or provide some sample expected output and current output?</P> Wed, 31 Aug 2016 21:34:11 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256135#M76687 somesoni2 2016-08-31T21:34:11Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256136#M76688 <P>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.<IMG src="http://i68.tinypic.com/2hygwt1.png" alt="alt text" /></P> Wed, 31 Aug 2016 21:41:16 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256136#M76688 HCadmins 2016-08-31T21:41:16Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256137#M76689 <P>Try this</P> <PRE><CODE>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&lt;=10 | stats list(thetime) as "Timestamp" by user </CODE></PRE> Wed, 31 Aug 2016 22:06:37 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256137#M76689 sundareshr 2016-08-31T22:06:37Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256138#M76690 <P>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.</P> Wed, 31 Aug 2016 22:12:41 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256138#M76690 HCadmins 2016-08-31T22:12:41Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256139#M76691 <P>It may be simple as this. </P> <PRE><CODE> 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 </CODE></PRE> Wed, 31 Aug 2016 22:14:38 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256139#M76691 somesoni2 2016-08-31T22:14:38Z https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256140#M76692 <P>Okay, I think that worked. Thank you for your help!</P> Thu, 01 Sep 2016 17:26:44 GMT https://community.splunk.com/t5/Splunk-Search/How-to-edit-my-search-to-sort-by-date-time-and-user/m-p/256140#M76692 HCadmins 2016-09-01T17:26:44Z