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 ?
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
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.
So you would change your eval to this
| eval _time = strftime(strptime("01-".'Created_Date', "%d-%b-%Y"),"%Y-%m")
While displaying I would still want to use the expanded month name rather than 01, 02. Any idea on that?
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.