Splunk Search

Sort the output of chart command

dpatiladobe
Explorer

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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]

View solution in original post

0 Karma

woodcock
Esteemed Legend

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]
0 Karma

dpatiladobe
Explorer

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 ?

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

dpatiladobe
Explorer

Thanks I got it

0 Karma

dpatiladobe
Explorer

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]
0 Karma

woodcock
Esteemed Legend

Never use | sort without a number. EVER.

0 Karma
Get Updates on the Splunk Community!

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...