How can I use the chart command so that the field names appear in reverse alphabetical order?

[andrewtrobec]

Hello all,

I have created a chart that plots instance counts over time. For visualisation purposes I am grouping by month in the MM-YYYY format. Here is the code:

| eval start_time = strptime(start_time ,"%d/%m/%y %H:%M:%S")    
| bucket span=1mon start_time
| eval start_time =strftime(start_time , "%m-%Y")    
| chart count over instance by start_time 

This will order the months in chronological order:

instance, 03-2017,04-2017-05-2017
instance01, 65,43,12
instance02, 11,452,231
instance03, 89,42,235

What I would like to do is set the fields to reverse chronological order:

instance, 05-2017,04-2017-03-2017
instance01, 12,43,65
instance02, 231,452,11
instance03, 235,42,89

Is this possible?

Thank you and best regards,

Andrew

[niketn · Accepted solution]

@andrewtrobec, can you please try the following?

<YourBaseSearch>
| eval start_time=strftime(strptime(start_time,"%d/%m/%y %H:%M:%S"),"%Y-%m")
| bin start_time span=1mon start_time
| chart count over start_time by instance
| reverse
| transpose header_field=start_time column_name=instance

PS: I have change string time format from mm-YYYY to YYYY-mm so that string time is always sorted. In string time 01-2018 will be smaller than 02-2017 where 2018-01 will be greater than 02-2017

[andrewtrobec]

@niketnilay Thanks for taking the time.
The reverse command affects the chart rows rather than the field names, so it does not return the desired output. Following the example above, it produces the following output:

 instance, 03-2017,04-2017-05-2017
 instance03, 89,42,235
 instance02, 11,452,231
 instance01, 65,43,12

[niketn]

Hi @andrewtrobec, If you would notice I have done three things

1) Inversed the rows with columns in the chart command. You had over instance by start_time, while I have over start_time by instance. This gives me start_time in Rows not columns.

2) I have applied reverse to sort Start Time in reverse chronological order.

3) I have used transpose to inverse the results again i.e start_time becomes column and instance becomes rows. PS: Transpose command by default puts 5 rows as columns. You can change with a number based on number of columns you need or else you can put 0 for no limit.

Please refer to following run anywhere search based on Splunk's _internal index on similar lines as your question:

index=_internal group=thruput name=index_thruput earliest=-7d@d latest=now
| bin _time span=1d 
| eval _time=strftime(_time,"%Y-%m-%d")
| chart sum(kb) AS daily_KB over _time by name
| reverse
| transpose 0 header_field=_time column_name=name

[andrewtrobec]

@niketnilay You're right, I missed that point. Thank you very much!

[niketn]

@andrewtrobec, I am glad I could help 🙂