Splunk Search

Sort the average duration based on time rather than alphabetically

chitreshakumar
Communicator

I have an average duration field which has months ,days ,hours and minutes.I want it to be sorted descending order
-Months,days,hours and minutes
Ascending -minutes,hours,days and months

0 Karma
1 Solution

elliotproebstel
Champion

If you are displaying this data in a dashboard and want to display the duration field as you've currently formatted it but sort based on the numeric duration values, I would recommend an approach that uses fieldformat to display the values while maintaining the actual values in numeric format. Here's a link to another answer that uses this approach to display data with one set of formatting while sorting based on another:
https://answers.splunk.com/answers/585952/when-clicking-on-table-header-want-to-sort-by-case-1.html#...

So the general approach will be to have a field called, say, average_duration that contains a numeric value representing the total number of seconds in the duration. (Or, if your data is only granular to the minute, then the total number of minutes is also fine.) Separately, you'll have a field called display_duration that will be formatted to match your desired display format. At the end of your search string, you'll add:

| sort - average_duration 
| fieldformat average_duration=display_duration

Or if you wanted to sort the values in an ascending order, that would be:

| sort average_duration 
| fieldformat average_duration=display_duration

And then in the XML formatting for the table you are displaying, you'll use the <fields> tag to specifically indicate which fields you want to display. Presumably, this would be a list of all the fields you are currently displaying - and specifically, not the field display_duration.

Here's some more info about fieldformat:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Fieldformat
And here is some useful info about the spec of a simpleXML dashboard, specifically about the options you can put within a table:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/PanelreferenceforSimplifiedXML#table

View solution in original post

0 Karma

elliotproebstel
Champion

If you are displaying this data in a dashboard and want to display the duration field as you've currently formatted it but sort based on the numeric duration values, I would recommend an approach that uses fieldformat to display the values while maintaining the actual values in numeric format. Here's a link to another answer that uses this approach to display data with one set of formatting while sorting based on another:
https://answers.splunk.com/answers/585952/when-clicking-on-table-header-want-to-sort-by-case-1.html#...

So the general approach will be to have a field called, say, average_duration that contains a numeric value representing the total number of seconds in the duration. (Or, if your data is only granular to the minute, then the total number of minutes is also fine.) Separately, you'll have a field called display_duration that will be formatted to match your desired display format. At the end of your search string, you'll add:

| sort - average_duration 
| fieldformat average_duration=display_duration

Or if you wanted to sort the values in an ascending order, that would be:

| sort average_duration 
| fieldformat average_duration=display_duration

And then in the XML formatting for the table you are displaying, you'll use the <fields> tag to specifically indicate which fields you want to display. Presumably, this would be a list of all the fields you are currently displaying - and specifically, not the field display_duration.

Here's some more info about fieldformat:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Fieldformat
And here is some useful info about the spec of a simpleXML dashboard, specifically about the options you can put within a table:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/PanelreferenceforSimplifiedXML#table

0 Karma

mayurr98
Super Champion

can you provide some sample data values ?
are you converting it to Months,days,hours and minutes eventually in the query?
if you are converting then do a sort before conversion.
refer this documentation for sort command:
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Sort

Let me know if this helps!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...