Sort the output of chart command

[dpatiladobe]

I am trying to plot chart by ObjectName , Date by Duration. And wanted to sort them by Date desc and Duration desc. I am not able to get same.

some search .....| eval ObjectName=if(isnull(ObjectName), "Full_Processing_time",ObjectName) |eval Date=strftime(_time,"%d/%m/%y") | eval Duration=round(tonumber(Duration)/1000/60,2) | chart max(Duration) as D by ObjectName,Date | sort 0 D Date | fields - D Date

[woodcock · Accepted solution]

First of all, you cannot sort by D because this is involved in a 2-dimensional matrix; you can only sort by the X-axis ( Date ) or Y-axis ( ObjectName ) field names (or both). In this case, you would like the the date sorting reversed so that the most recent is on the left instead of the right.
I have done this many times several different ways but I have decided to try something new this time.
Try adding this to the bottom of your existing search:

| fields ObjectName * [| makeresults 
| addinfo
| foreach info_*_time [ bin <<FIELD>> span=1d | eval <<FIELD>>=strftime(<<FIELD>>, "%m/%d/%Y") ]
| map search="|gentimes start=$info_min_time$ end=$info_max_time$"
| sort 0 - starttime
| fields starttime
| eval starttime = strftime(starttime, "%m/%d/%Y")
| stats list(starttime) AS search
| nomv search]

Here is a run-anywhere example (try running for Last 7 Days😞

| tstats range(_time) AS Duration WHERE index="_*" AND sourcetype="*splunk*" BY sourcetype _time span=1d
| rename sourcetype AS ObjectName

| rename COMMENT AS "Everything above generates sample data; everything below is your solution."

| eval ObjectName=if(isnull(ObjectName), "Full_Processing_time",ObjectName) 
| eval Duration=round(tonumber(Duration)/1000/60,2)
| eval Date=strftime(_time, "%m/%d/%Y")
| chart limit=0 max(Duration) AS D BY ObjectName Date
| fields ObjectName * [| makeresults 
| addinfo
| foreach info_*_time [ bin <<FIELD>> span=1d | eval <<FIELD>>=strftime(<<FIELD>>, "%m/%d/%Y") ]
| map search="|gentimes start=$info_min_time$ end=$info_max_time$"
| sort 0 - starttime
| fields starttime
| eval starttime = strftime(starttime, "%m/%d/%Y")
| stats list(starttime) AS search
| nomv search]

[dpatiladobe]

Thanks @woodcock , These is awesome. These sorts the Chart by Date desc, I want to add the Duration also to sort chart as desc order of duration. Is that possible ?

[woodcock]

No, sorting by D is not possible and I mentioned this in the very first part of my answer, That field's data is now 2-dimensional and sorting in 2 dimensions has no meaning. Take a look at the output and think about it and you will see what I mean.

[dpatiladobe]

Thanks I got it

[dpatiladobe]

If i add | sort -D after the fields ObjectName * , it sorts according to Duration desc but then it goes and sort Date as asc

| tstats range(_time) AS Duration WHERE index="_*" AND sourcetype="*splunk*" BY sourcetype _time span=1d
 | rename sourcetype AS ObjectName

 | rename COMMENT AS "Everything above generates sample data; everything below is your solution."

 | eval ObjectName=if(isnull(ObjectName), "Full_Processing_time",ObjectName) 
 | eval Duration=round(tonumber(Duration)/1000/60,2)
 | eval Date=strftime(_time, "%m/%d/%Y")
 | chart limit=0 max(Duration) AS D BY ObjectName Date
 | fields ObjectName * [| makeresults 
 | addinfo
 | foreach info_*_time [ bin <<FIELD>> span=1d | eval <<FIELD>>=strftime(<<FIELD>>, "%m/%d/%Y") ]
 | map search="|gentimes start=$info_min_time$ end=$info_max_time$"
 | sort 0 - starttime
 | fields starttime
 | eval starttime = strftime(starttime, "%m/%d/%Y")
 | stats list(starttime) AS search
 | nomv search]

[woodcock]

Never use | sort without a number. EVER.