topic Re: Why am I not able to sort the date column in a table by clicking on the header? in Splunk Search

Why am I not able to sort the date column in a table by clicking on the header?

srinathd — Mon, 28 Sep 2020 18:04:03 GMT

Hi,

In the logs the START_DATE_PROFILE is in the format "20090914" i.e, (%Y%m%d) . I want to show the date as 14-Sep-2014 and the column name must be "Start Date". I am using this below query but the date is not sorting properly if we click on the column header. Please let me know.

index=test24 | eval START_DATE_PROFILE=strftime(strptime(START_DATE_PROFILE, "%Y%m%d"), "%d-%b-%Y") | convert timeformat="%d-%b-%Y" mktime(START_DATE_PROFILE) AS START_DATE_PROFILE | fieldformat START_DATE_PROFILE=strftime(START_DATE_PROFILE,"%d-%b-%Y") | table START_DATE_PROFILE |rename START_DATE_PROFILE as "Start Date"

Re: Why am I not able to sort the date column in a table by clicking on the header?

MuS — Mon, 03 Nov 2014 10:14:55 GMT

Hi srinathd,

from the docs http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Sort about sorting:

Alphabetic strings are sorted lexicographically.
Punctuation strings are sorted lexicographically.
Numeric data is sorted as you would expect for numbers and the sort order is specified (ascending or descending).
Alphanumeric strings are sorted based on the data type of the first character. If it starts with a number, it's sorted numerically based on that number alone; otherwise, it's sorted lexicographically.
Strings that are a combination of alphanumeric and punctuation characters are sorted the same way as alphanumeric strings.

hope that helps to understand sorting in Splunk.

cheers, MuS

Re: Why am I not able to sort the date column in a table by clicking on the header?

srinathd — Mon, 28 Sep 2020 18:04:05 GMT

Thanks MuS for your reply. But the sorting is working fine if the column name is "START_DATE_PROFILE". If we rename it to "Start Date" then it is not working.

Re: Why am I not able to sort the date column in a table by clicking on the header?

MuS — Mon, 03 Nov 2014 10:47:54 GMT

okay, then why don't you simply eval the date field with the correct name in the first place?

index=test24 | eval Start_Date=strftime ....

Re: Why am I not able to sort the date column in a table by clicking on the header?

srinathd — Mon, 03 Nov 2014 11:59:58 GMT

column should contain space and it should be sortable. If it has any space in the column header, it is not sorting properly. But i need the column name with space.

Re: Why am I not able to sort the date column in a table by clicking on the header?

MuS — Mon, 03 Nov 2014 12:32:36 GMT

this run everywhere command works as it should:

index=_internal | bucket _time span=1d | stats count by _time | eval foo=strftime(_time, "%Y%m%d")  | eval "b a r"=foo | eval b_a_z=foo| table foo, "b a r", b_a_z

sorting is possible and working either on foo,b a r or b_a_z