topic Time format and table sorting issues in Splunk Search

Time format and table sorting issues

jwillaime — Tue, 29 Sep 2020 18:15:23 GMT

So, I have this search on events that cover from the 28th of February to the 6th of March, 2018:

Some basic search

    | eval customer_id = substr(host,2,5)

    | eval session_duration = stop_time - start_time

    | eval start_date = strftime(start_time,"%d/%m/%y %H:%M:%S")

    | convert rmunit(session_duration) as numSecs

    | eval stringSecs=tostring(numSecs,"duration")

    | table customer_id,  start_date,  task_id,  host_ip, from_user, stringSecs 

    | sort by start_time d

    | rename customer_id as "Customer ID", start_date as "Session Start", task_id as "Task id", host_ip as "Accessed Host", from_user as "User ID", stringSecs as "Session Duration"

Which is creating the perfect "starting" table I want, with the more recent data at the top, up until I click on the table header to modify the date.

Ascending: (expecting 28/02/18)

Descending: (expecting 06/03/18)

From what I understand, even if I specified the European time format in the search, the interactive table sorting is based on alphanumerical order.

One quick fix would be to use the ISO format (YYYY-MM-DD) (for which I am opting right now), but what if the end user want absolutely to have dd/mm/yy and be able to click on the header to change the order?
Is there an option to change this behavior?

Thanks in advance.

Re: Time format and table sorting issues

mayurr98 — Wed, 07 Mar 2018 13:45:02 GMT

okay if YYYY-MM-DD format is fixing your issue then do that and if the end user want absolutely to have dd/mm/yy then you can try converting the time format after the sort

Some basic search 
| eval customer_id = substr(host,2,5) 
| eval session_duration = stop_time - start_time 
| eval start_date = strftime(start_time,"%Y-%m-%d %H:%M:%S") 
| convert rmunit(session_duration) as numSecs 
| eval stringSecs=tostring(numSecs,"duration") 
| table customer_id, start_date, task_id, host_ip, from_user, stringSecs 
| sort by start_date 
| eval start_date=strftime(strptime(start_date,"%Y-%m-%d %H:%M:%S"),"%d/%m/%y %H:%M:%S") 
| rename customer_id as "Customer ID", start_date as "Session Start", task_id as "Task id", host_ip as "Accessed Host", from_user as "User ID", stringSecs as "Session Duration"

OR
You can use another method (bit complex but useful ) in which you need to sort date year month individually along with the entire time format and then remove unnecessary fields .
Refer this answer
https://answers.splunk.com/answers/624327/how-to-arrange-by-monthyear-chronological-order.html#comment-624330

let me know if this helps!

Re: Time format and table sorting issues

kollachandra — Wed, 07 Mar 2018 16:28:28 GMT

I would say to sort them first by and then do the eval to change the format.

Re: Time format and table sorting issues

jwillaime — Thu, 08 Mar 2018 07:48:43 GMT

Your first proposition has the same unwanted behavior. I'll take a look to the link you shared.