help to sort _time

[jip31]

hi

why my sort _time doesnt works please?

what is strange is that when I click directly on the field the sort doesnt works too

| eval _time = strftime(_time, "%d-%m-%y %H:%M:%S") 
| sort _time 
| stats last(host) as host, last(os) as OS by _time 
| rename host as Host, _time as Date 
| table Date, Host, OS
| sort - Date

[PickleRick · Accepted solution]

_time is internally stored as a number. It is a number of seconds since 1.01.1970.

If the field is called _time it's rendered in the UI as a string in your local timezone. (you can check it by doing

| eval mytime=_time

Since it's a number, you can normalky manipulate it (add or substract time to/from it) or sort it.

If you render it to text as you did in your example, you operate on strings and splunk doesn't understand that it's supposed to be a date. For splunk it's a normal string so if you sort by this field it sorts lexicographicallh which is definitelly not what you want.

So, to sum up - don't strftime your time fields if you want to do anything with them. If you want them to be shown in a particular way in webui, use fieldformat, not eval.

[venkatasri]

Hi @jip31 

you could possibly hitting with these,

sort command having 10K limit see if you are hitting the limit and

when you rename _time to Date it converts to epoch.. 

you are sorting | sort _time which means shows oldest on top, and again at the end | sort Date which is an epoch bring the latest events to top. check your | stats command to by _time with last() func might not give you correct context.

--

Appreciate an upvote if this helps!

[venkatasri]

| sort 0 _time

This won't hit the 10k limit.