topic Cannot sort dynamic column in date format in Splunk Search

Cannot sort dynamic column in date format

urapaveerapan — Tue, 29 Sep 2020 13:46:41 GMT

Hi,
I tried to summary data in each assignment_group_name by month
here is my code:
index="snow" sourcetype="snow:incident" assignment_group_name="AM*"
|dedup number
|chart count over assignment_group_name by incoming_month
|addtotals
|sort by assignment_group_name, incoming_month

But I need the column to be arranged by date format by starting from the oldest month to the latest one.
I've tried to convert the incoming_month column by trying

|eval month = strftime(incoming_month, "%b-%y")
but it's not working. Please help.

Re: Cannot sort dynamic column in date format

rjthibod — Fri, 21 Apr 2017 11:33:17 GMT

Sorting of columns is always done in some way (alphabetic or ascii or something else) when left to its own devices. So the answer to your question is there is no way to do exactly what you want without adding some extra specificity, or changing the labels.

The latter option is simple, but you have to use the number version of month, e.g., |eval month = strftime(incoming_month, "%m-%y") or |eval month = strftime(incoming_month, "%Y-%m")

The more specific option is to use the column names with wildcards, like this; however, this will only work for the same calendar year because you put the year at the end of the column name

| fields assignment_group_name Jan* Feb* Mar* Ap* May* Jun* Jul* Aug* Sep* Oct* Nov* Dec* Total*

So, if you want to make Splunk do the work for you, you need to switch to |eval month = strftime(incoming_month, "%Y-%m")

Re: Cannot sort dynamic column in date format

niketn — Fri, 21 Apr 2017 11:38:18 GMT

You can either create a lookup table with Month Abbreviations to month in digits like Jan - 01(Jan), Feb -02(Feb) etc or write a macro to perform series rename as shown below. Once you change Dec-16 to 12-Dec-16 it will show up sorted. You can also set usenull=f to hide null fields and add incoming_month="*" to your base search. (PS: All rename below should be shifted to a macro).

index="snow" sourcetype="snow:incident" assignment_group_name="AM*" incoming_month="*" 
|dedup number
|chart count over assignment_group_name by date_mdayincoming_month 
|rename "Jan*" as "01(Jan)*"
|rename "Feb*" as "02(Feb)*"
|rename "Mar*" as "03(Mar*"
|rename "Apr*" as "04(Apr)*"
|rename "May*" as "05(May)*"
|rename "Jun*" as "06(Jun)*"
|rename "Jul*" as "07(Jul)*"
|rename "Aug*" as "08(Aug)*"
|rename "Sep*" as "09(Sep)*"
|rename "Oct*" as "10(Oct)*"
|rename "Nov*" as "11(Nov)*"
|rename "Dec*" as "11(Dec)*"
|addtotals
|sort by assignment_group_name, incoming_month

Re: Cannot sort dynamic column in date format

woodcock — Fri, 21 Apr 2017 16:49:50 GMT

Like this:

index="snow" sourcetype="snow:incident" assignment_group_name="AM*" incoming_month="*" 
| dedup number
| chart count over assignment_group_name by date_mdayincoming_month 
| rename Jan* AS "           Jan*"
         Feb* AS "          Feb*"
         Mar* AS "         Mar*"
         Apr* AS "        Apr*"
         May* AS "       May*"
         Jun* AS "      Jun*"
         Jul* AS "     Jul*"
         Aug* AS "    Aug*"
         Sep* AS "   Sep*"
         Oct* AS "  Oct*"
         Nov* AS " Nov*"
|addtotals
|sort 0 BY assignment_group_name incoming_month

Note that Oct has been renamed with 2 leading spaces and Nov with just one (and Dec not at all).
The whitespace is invisible in the chart but forces the alphabetical order that you desire. This would make a fine macro.