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

[srinathd]

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"

[MuS]

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

[srinathd]

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.

[MuS]

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

[srinathd]

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.

[MuS]

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