Splunk Search

Time format and table sorting issues

jwillaime
Explorer

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)
alt text

Descending: (expecting 06/03/18)
alt text

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.

0 Karma

kollachandra
Path Finder

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

0 Karma

mayurr98
Super Champion

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#comme...

let me know if this helps!

0 Karma

jwillaime
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...

4 Ways the Splunk Community Helps You Prepare for .conf25

.conf25 is right around the corner, and whether you’re a first-time attendee or a seasoned Splunker, the ...