Table data sort behaviour

[Lucas_K]

I have a search that outputs a table similar to the following.

Month starting  count
1-Sep-11    21424533
1-Oct-11    23025589
1-Nov-11    23291731
1-Dec-11    22275910
1-Jan-12    24153686
1-Feb-12    22862810
1-Mar-12    19596371
1-Apr-12    22270415
1-May-12    20289422

This table exhibits unexpected sort behaviour in the web interface (splunk v4.3.4)
You can sort in both directions via the count column but if you then click on the month starting column it only sorts it in one direction. The tool tip icon changes direction however.

I have tried changing the date format from timeformat="%d/%b/%y" to various different ones (even including the time ie. %d-%b-%y %h:%m:%s).
The only thing that allows it to sortable as a user expects is to use epoch time.
Is there any way that I can make this table sortable?

[lguinn2 · Accepted solution]

How did you get the "Month starting?"

If you did it like this: eval "Month starting"=strftime(_time,"%d-%b-%y")

then you should try fieldformat instead: fieldformat "Month starting"=strftime(_time,"%d-%b-%y")

which should make the field sort properly. Another alternative is to use two fields. I'll call them monthEpoch and monthStarting - they are both the same date, but one is formatted and one is not. Here is an example

yoursearchhere
| bucket span=1d _time
| eval monthEpoch = _time
| stats count by monthEpoch
| eval monthStarting = strftime(monthEpoch,"%d-%b-%y")
| sort monthEpoch
| table monthStarting count
| rename monthStarting as "Month starting"

HTH

[Lucas_K]

Your right. That last one is the only one that will work properly.

ie. fieldformat Month = strftime(Month,"%d/%b/%y")

Excellent thanks again! It seems that any eval-ing/formatting/copying to a new field/with a rename totally breaks how it than sorts in the table.

[lguinn2]

See if it works this way

somesearch
| eval Month=relative_time(_time, "@mon")
| some stats here
| fieldformat Month = strftime(Month,"%d/%b/%y")

I think the rename confuses fieldformat, and the field name with spaces in it may also confuse it.

[Lucas_K]

The month starting is created like this.

somesearch
| eval Month=relative_time(_time, "@mon")
| some stats here
| convert timeformat="%d/%b/%y" ctime(Month) AS "Period Month Starting"

So my search snaps each result into a monthly block so that I can then nicely combine them together using a stats by Month.

If i try and use fieldformat like this

| fieldformat "Period Month Starting"=strftime(Month,"%d-%b-%y")

I get no output what so ever.
You eval line "eval monthStarting = strftime(monthEpoch,"%d-%b-%y")" does work but gives me the same "unsortable" results table.