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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...