topic Re: How to order chronologically when _time has been evaluated with strftime? in Splunk Search

How to order chronologically when _time has been evaluated with strftime?

3DGjos — Thu, 12 Dec 2019 16:01:56 GMT

Hello, I always have problems ordering my events after evaluating _time to something else. See this query for example:

| mybasesearch
| eval "Last seen"=strftime(_time, "%d/%m/%Y %H:%M") 
| morequerytablestuff
| sort - _time
| fields - _time

Here I had to keep _time in my table, sort the events, and then remove the _time field from it.

Is there a better way of achieving this?

Re: How to order chronologically when _time has been evaluated with strftime?

gcusello — Thu, 12 Dec 2019 16:42:19 GMT

Hi @3DGjos,
your search seems to be correct, only one thing: don't use space between - and _time (that instead you have to use in fields command), so use something like this

mybasesearch
| eval "Last seen"=strftime(_time, "%d/%m/%Y %H:%M") 
| morequerytablestuff
| sort -_time
| fields - _time

Then I have two questions:

at the end of your basesearch (I think that you're speaking of Post Process Search), did you used the command fields with all the fields you need in panels' searches including _time?
in morequerytablestuff have you some stats or chart or timechart commands? if yes, remember that after you can use ony the fields that are in the command.

Ciao.
Giuseppe

Re: How to order chronologically when _time has been evaluated with strftime?

Anantha123 — Thu, 12 Dec 2019 16:44:40 GMT

Hi,

you have to use "Last seen" for rest of your query and you are evaluating and assigning _time value to this variable

give |sort - "Last seen" | fields - "Last seen".

Thanks
Anantha.

Re: How to order chronologically when _time has been evaluated with strftime?

3DGjos — Thu, 12 Dec 2019 17:13:37 GMT

Hello,
I know that. The thing is, when I sort by "Last seen", splunk does not recognize the field as a date field. So it sort it numerically as text string, then it does not respect the date order.

Re: How to order chronologically when _time has been evaluated with strftime?

3DGjos — Thu, 12 Dec 2019 17:16:21 GMT

Hello,
yes i'm passing the _time values in my stats command, and passed all the fields from the base search.

the problem is, that splunk does not recognize the "last seen" field as a date field.

Re: How to order chronologically when _time has been evaluated with strftime?

gcusello — Thu, 12 Dec 2019 17:31:48 GMT

Hi @3DGjos,
infact "last seen" field is a string that is sorted as a string in alphabetical order, for this reason it's correct to sort for _time.

Yoy eventually could change the order of the statements:

 mybasesearch
 | morequerytablestuff
 | sort -_time
 | reame _time AS "Last seen"
 | eval "Last seen"=strftime("Last seen", "%d/%m/%Y %H:%M")

but this solution isn't so different from your one.

Ciao.
Giuseppe

Re: How to order chronologically when _time has been evaluated with strftime?

woodcock — Fri, 13 Dec 2019 00:18:19 GMT

Don't do it that way, use fieldformat like this:

mybasesearch
| rename _time AS "Last seen"
| fieldformat  "Last seen"=strftime('Last seen', "%d/%m/%Y %H:%M") 
| morequerytablestuff
| sort 0 - "Last seen"