topic change date/time format for field in csv lookup table in Getting Data In

change date/time format for field in csv lookup table

DeanDeleon0 — Thu, 21 Mar 2019 18:03:27 GMT

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.

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

Re: change date/time format for field in csv lookup table

DMohn — Thu, 21 Mar 2019 18:25:18 GMT

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.

Re: change date/time format for field in csv lookup table

DeanDeleon0 — Mon, 25 Mar 2019 16:10:25 GMT

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!

Re: change date/time format for field in csv lookup table

DMohn — Mon, 25 Mar 2019 16:54:29 GMT

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!