topic Re: How to sort date field year wise (which is in this format - ex : Jan-2015) in Splunk Search

How to sort date field year wise (which is in this format - ex : Jan-2015)

rijinc — Tue, 29 Sep 2020 12:28:12 GMT

this is my query:

|index = *
count(search) AS "total_count" SPLITROW Test_ID SPLITROW R_S_Me SPLITROW Set SPLITROW Created_Date
| eval targ_met = if(R_S_Me="Y", 1, if (R_S_Me="N", 0,-1))

| eval resp_per = ((targ_met/total_count)*100)
| eval _time = strftime(strptime("01-".'Created_Date', "%d-%b-%Y"),"%b%Y")
| chart avg(resp_per) as "Response Metric" by Set, _time

Results is in this format:

Set dec-2015 Feb-2016 Jan-2015
Set1 1 2 3
Set2 1 2 3
set3 1 2 3

i am not able to sort the date as first Jan-2015 and then as Dec-2015 , here it is sorting alphabetical order .
Any way to get in date format ?

Re: How to sort date field year wise (which is in this format - ex : Jan-2015)

rjthibod — Wed, 18 Jan 2017 14:08:05 GMT

In general, Splunk does not really make it easy to let you sort columns/fields in arbitrary ways. Splunk pretty much always want to sort columns/fields in ASCII order.

So, the easiest solution is to use the numeric versions of the year/month, e.g., 2016-01, 2016-02, etc. That way you can do something like | fields Set * in your query and the column names will be sorted in chronological order from left to right.

Re: How to sort date field year wise (which is in this format - ex : Jan-2015)

rjthibod — Wed, 18 Jan 2017 14:10:09 GMT

So you would change your eval to this

| eval _time = strftime(strptime("01-".'Created_Date', "%d-%b-%Y"),"%Y-%m")

Re: How to sort date field year wise (which is in this format - ex : Jan-2015)

lakshmisri — Mon, 30 Jan 2017 06:17:51 GMT

While displaying I would still want to use the expanded month name rather than 01, 02. Any idea on that?

Re: How to sort date field year wise (which is in this format - ex : Jan-2015)

rjthibod — Mon, 30 Jan 2017 11:24:14 GMT

You cannot do that without specifying the fieldnames in the explicit order you want using fields or table; however, I am pretty sure that will display empty values for any columns you include that don't have data.

Like I said in my original answer, Splunk does not really make this kind of thing easy. You will find many other questions on Splunk Answers with the same kind of problem, with no consistent answers.

Re: How to sort date field year wise (which is in this format - ex : Jan-2015)

woodcock — Mon, 06 Mar 2017 06:11:03 GMT

Like this:

| index = *
| stats count(search) AS "total_count" SPLITROW Test_ID SPLITROW R_S_Me SPLITROW Set SPLITROW Created_Date 
| eval targ_met = if(R_S_Me="Y", 1, if (R_S_Me="N", 0,-1)) 
| eval resp_per = ((targ_met/total_count)*100) 
| eval Month = strftime(strptime("01-".'Created_Date', "%d-%b-%Y"),"%b%Y")
| streamstats dc(Month) AS _serial
| eval Month = case((_serial==13),                 Month,
                    (_serial==12),           " " . Month,
                    (_serial==11),          "  " . Month,
                    (_serial==10),         "   " . Month,
                    (_serial==9),         "    " . Month,
                    (_serial==8),        "     " . Month,
                    (_serial==7),       "      " . Month,
                    (_serial==6),      "       " . Month,
                    (_serial==5),     "        " . Month,
                    (_serial==4),    "         " . Month,
                    (_serial==3),   "          " . Month,
                    (_serial==2),  "           " . Month,
                    (_serial==1), "            " . Month)
| chart avg(resp_per) as "Response Metric" OVER Set BY Month