topic Re: help on time sorting in Splunk Enterprise

help on time sorting

jip31 — Tue, 21 Sep 2021 17:37:15 GMT

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

Re: help on time sorting

richgalloway — Tue, 21 Sep 2021 18:05:48 GMT

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).

Re: help on time sorting

PickleRick — Tue, 21 Sep 2021 18:25:34 GMT

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.

Re: help on time sorting

jip31 — Wed, 22 Sep 2021 04:47:14 GMT

sorry but same also with _time....

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

Re: help on time sorting

danielcj — Wed, 22 Sep 2021 05:26:13 GMT

You can try the following @jip31 :

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

Re: help on time sorting

PickleRick — Wed, 22 Sep 2021 05:50:29 GMT

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)

Re: help on time sorting

bowesmana — Wed, 22 Sep 2021 06:53:41 GMT

@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

Re: help on time sorting

jip31 — Wed, 22 Sep 2021 09:46:34 GMT

sorry but it doesnt works

Re: help on time sorting

isoutamo — Wed, 22 Sep 2021 09:51:12 GMT

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

Re: help on time sorting

jip31 — Wed, 22 Sep 2021 09:51:20 GMT

it doenst works too