help on time sorting

[jip31]

hello

I dont succeed to sort the events by time

the format time field is for example :   1632218561

what is wrong please?

index="tutu" sourcetype="toto"  
| search statustext=TimedOut 
| sort - time 
| eval time = strftime(_time, "%d-%m-%y %H:%M") 
| stats last(time) as Heure, last(statustext) as statustext by desktop 

[isoutamo]

Hi

probably you have too many events for sort without limits?

If the first argument to the sort command is a number, then at most that many results are returned, in order. If no number is specified, the default limit of 10000 is used. If the number 0 is specified, all of the results are returned. See the count argument for more information.

Can you try sort 0 - time instead of sort - time ?

r. Ismo

[bowesmana]

@jip31 

OK, lots of posts here, so hopefully you're not too confused...

You are trying to

index="tutu" sourcetype="toto"  
| search statustext=TimedOut 
| sort - time 
| eval time = strftime(_time, "%d-%m-%y %H:%M") 
| stats last(time) as Heure, last(statustext) as statustext by desktop 

• Sort time, so that the LATEST time is at the top
• Find the LAST time from the list, i.e. the EARLIEST time

Sorting is something to avoid, as it's expensive - and not necessary here, sorting should always be done as LATE as possible

All you therefore need is

index="tutu" sourcetype="toto" statustext=TimedOut 
| stats earliest(_time) as Heure, earliest(statustext) as statustext by desktop 
| eval time = strftime(_time, "%d-%m-%y %H:%M") 

Note that you do not need a separate search command for statustext, it can be combined with the first line.

At the end, you will have 4 fields

_time - the epoch time
time - your formatted time
statustext + desktop

If you then want to sort, you can sort by any of these fields

Hope this helps

[jip31]

hi 

it doenst works too

[richgalloway]

The field to be sorted must exist before sorting.  IOW, put the eval command before the sort command or sort the _time field (which always exists).

[PickleRick]

Also remember that if you do strftime and convert your _time to some string representation, it will be sorted alphabeticaly, not date-wise!

So you'd better use _time.

[jip31]

sorry but same also with _time....

| eval _time = strftime(_time, "%d-%m-%y %H:%M") 
| sort - _time 
| stats last(_time) as Heure

[PickleRick]

No, no, no. Leave _time alone (don't overwrite it, especially, not with string values). You want to have your original _time to sort by it.

So you want to

| sort - _time

As first step of your pipeline.

Besides, instead of sorting and chosing last value, you can use

| stats earliest(_raw)

Or even, in your case, since you're just interested in time

| stats earliest_time(_raw)

[danielcj · Accepted solution]

You can try the following @jip31 :

<YOUR_SEARCH>
| sort - _time 
| eval Heure=strftime(_time, "%d-%m-%y %H:%M") 
| stats last(Heure) as Heure

[jip31]

sorry but it doesnt works