topic Re: Sort year month string in timechart legend in Splunk Search

Sort year month string in timechart legend

splunkmasterfle — Fri, 18 Jul 2014 16:05:17 GMT

Hi,

I am trying to sort the legend in my timechart chronologically but can't seem to make it work.

This is my search:

index=batch | rex field=partName "regular(?<tmpPartName>.*)" | rex field=tmpPartName "(?<partNameMonth>[a-zA-Z]+)[0-9]+" | rex field=tmpPartName "[a-zA-Z]+(?<partNameYear>[0-9]+)" | eval newPartName = partNameYear + " " + partNameMonth | timechart span=1d limit=15 sum(duration) by newPartName

Sample values for the field partName:

regularjan15 regularmar15 regularapr15 regularmay15 regularfeb15 regularnov14 regularoct14 regulardec14

Anyone have an idea how I can do this ?

Re: Sort year month string in timechart legend

lguinn2 — Fri, 18 Jul 2014 17:28:39 GMT

I would do this:

index=batch
| rex field=partName "regular(?<tmpDate>.*)"
| eval newPartName = strftime(strptime(tmpDate,"%b%y"),"%Y-%m")
| timechart span=1d limit=15 sum(duration) by newPartName

Re: Sort year month string in timechart legend

somesoni2 — Fri, 18 Jul 2014 17:49:31 GMT

strptime(tmpDate,"%b%y") will return NULL as it requires a day part as well. It should be changed with strptime("01-".tmpDate,"%d-%b%y"), keeping remaining things same.

Re: Sort year month string in timechart legend

splunkmasterfle — Fri, 18 Jul 2014 18:18:50 GMT

This "works" but doesn't sort the values in the legend of my timechart, which is what I was asking

Re: Sort year month string in timechart legend

somesoni2 — Fri, 18 Jul 2014 19:57:32 GMT

This does show legend for my sample view (appears in ascending order of time and values comes as this.
2014-10
2014-11
2014-12
2015-01
2015-02
2015-03
2015-04
2015-05

Re: Sort year month string in timechart legend

splunkmasterfle — Mon, 28 Sep 2020 17:07:43 GMT

This is my search now

index=batch | eval hours = (duration/(1000*60*60)) | rex field=partName "regular(?.*)" | eval newPartTime = strftime(strptime(tmpDate + "01","%b%y%d"),"%Y-%b") | timechart span=1d limit=15 sum(duration) by newPartTime

The legend is sorted alphabetically:

2014 - Apr 2014 - Aug 2014 - Dec 2014 - Jul 2014 - Jun 2014 - May 2014 - Nov 2014 - Oct 2014 - Sep 2015 - Apr 2015 - Fed [...]

Re: Sort year month string in timechart legend

lguinn2 — Fri, 18 Jul 2014 23:08:39 GMT

Yes, Splunk automatically sorts by the "by" clause of the timechart command. Since newPartName is a string, I would expect it to sort exactly as it did.

That's why I set the format to "%Y-%m" instead of "%Y-%b" - because then it would sort by month number instead of month name.

Re: Sort year month string in timechart legend

splunkmasterfle — Mon, 21 Jul 2014 15:21:41 GMT

It's true that changing the month to a digit will fix the issue. Thank you for the suggestion but my question is how do I sort the legend with the format %Y-%b.

Re: Sort year month string in timechart legend

lguinn2 — Mon, 21 Jul 2014 16:33:14 GMT

You can't. Splunk sorts the legend based on the value of newPartName.
If it is in the format %Y-%b, then the months are going to sort alphabetically.

Re: Sort year month string in timechart legend

somesoni2 — Mon, 21 Jul 2014 17:11:15 GMT

If its acceptable to you, you can go with format like this "%Y - %m (%b)". This will get sorted properly and it does shows the string format month as well.

2014 - 04(Apr)
2014 - 08(Aug)
2014 - 12(Dec)
2014 - 07(Jul)

Re: Sort year month string in timechart legend

splunkmasterfle — Wed, 23 Jul 2014 15:16:38 GMT

This is what I ended up doing. Thank you for all the help!