Getting Data In

change date/time format for field in csv lookup table

DeanDeleon0
Path Finder

Having a bit of an issue understanding how to apply this to change the date/time format of a field from a lookup table. The issue I am having is that when sorting by "Completion Date" the dates do not sort correct because of the format. For example Jan/7/2019 will show higher than Jan/17/2019. So the idea was to convert the timestamp to include a 0 (Jan/07/2019) so it sorts properly. Any help is appreciated.

alt text

It works in testing as per below, I can't get it to apply to my query:

alt text

0 Karma
1 Solution

DMohn
Motivator

You can gat the table to sort correctly by using a (hidden) sort field...

<your query here> | eval sortTime=strptime(YourTimeField,"YourConversion") | sort + sortTime | table your,fields,without,sortTime

This way Splunk first sorts the events by the sortTime field, which is Unix TImestamp, so in correct order, and then just not displays it.

View solution in original post

0 Karma

DMohn
Motivator

You can gat the table to sort correctly by using a (hidden) sort field...

<your query here> | eval sortTime=strptime(YourTimeField,"YourConversion") | sort + sortTime | table your,fields,without,sortTime

This way Splunk first sorts the events by the sortTime field, which is Unix TImestamp, so in correct order, and then just not displays it.

0 Karma

DeanDeleon0
Path Finder

Hi DMohn,

It turns out, "YourTimeField" doesn't like to work with things with spaces between.. such as "Completion Date". I renamed it to "Completion_Date" and finally got it to work. This helped a lot. Thanks!

0 Karma

DMohn
Motivator

If you have field names with spaces in them, you need to quote the fields! Or replace the name with underscores, just as you did. And yes, in this special case, Splunk will have difficulties recognizing the former as a variable and the latter as a string.

Thats why it is always a good practice to go with non-space field names, and do the conversion (remaming) at the very end!

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...