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!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

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

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...