Splunk Search

Sort order of subsearch results

bfnpmsz
New Member

Ok, treat me nice, please...

I am working on a dashboard which totals and reports data from two different date ranges based on a date picker date range.

Start: Aug. 31st to Sept. 13th. This is my overall date range.

Using the overall date range, I have calculated the first week range as follows.

    <panel>
      <table>
        <title>Top 5 ATM Details</title>
        <search>
          <query>* ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01  
[search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 earliest=$earliest$ latest=$latest$ | reverse | head 1 | addinfo | eval earliest = relative_time(info_min_time, "@w1") | fields earliest] 
[search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 earliest=$earliest$ latest=$latest$ | head 1 | addinfo | eval latest = relative_time(info_max_time, "-6d@w1") | fields latest] | top limit=5 InstanceName showperc=FALSE showcount=FALSE | sort count |
 join max=0 [search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 [search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 earliest=$earliest$ latest=$latest$ | reverse | head 1 | addinfo | eval earliest = relative_time(info_min_time, "@w1") | fields earliest] 
[search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 earliest=$earliest$ latest=$latest$ | head 1 | addinfo | eval latest = relative_time(info_max_time, "-6d@w1") | fields latest] | fields InstanceName EventName] | top  limit=5 EventName by InstanceName showperc=FALSE | reverse</query>
          <earliest>1440997200</earliest>
          <latest>1441602000</latest>
        </search>
      </table>
    </panel>

The data is coming back correct, but.... the order is out of sequence.

My dashboard it trying to show the EventNames and count from the top 10 InstanceNames.

I am trying to replicate this:

alt text

But what I am getting is this:

alt text

I would love to have the report come back ordered by InstanceName EventName count and then by the EventName count all desending.

A1310-2350 total items 21
A1310-2350 ATM - A1310-2350 - Issue #1 15
A1310-2350 ATM - A1310-2350 - Issue #2 3
A1310-2350 ATM - A1310-2350 - Issue #3 2
A1310-2350 ATM - A1310-2350 - Issue #4 1

A1240-2350 total items 15
A1240-2350 ATM - A1240-2350 - Issue #1 10
A1240-2350 ATM - A1240-2350 - Issue #2 5

A1235-2350 total items 10
A1235-2350 ATM - A1235-2350 - Issue #1 8
A1235-2350 ATM - A1235-2350 - Issue #2 2

So as you can see, I want the report ordered by the count of InstanceName Events then by the events within InstanceName by the event count.

Help me out. I think I am CLOSE, but need a little assistance.

Thanks for the help in advance

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try (optimized the earliest/latest calculation as well)

Updated to correct sorting

 * ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01 
 [ | gentimes start=-1 | addinfo | eval earliest = relative_time(info_min_time, "@w1") | eval latest = relative_time(info_max_time, "-6d@w1") | fields earliest latest ]
 | top limit=5 InstanceName showperc=FALSE showcount=FALSE
 |  join max=0 
 [search ClassName=Proview  eventtype="Notification Ticketed"  host=ALVIONIX01
 [ | gentimes start=-1 | addinfo | eval earliest = relative_time(info_min_time, "@w1") | eval latest = relative_time(info_max_time, "-6d@w1") | fields earliest latest ] | fields InstanceName EventName] 
 | top  limit=5 EventName by InstanceName showperc=FALSE | eventstats sum(count) as rank by InstanceName | eval InstanceName=tostring(rank)."##".InstanceName | fields - rank
 | appendpipe [| stats sum(count) as count by InstanceName | eval EventName="0#total items"  ]
 | sort -InstanceName EventName -count 
 | eval InstanceName=mvindex(split(InstanceName,"##"),1) | replace "0#*" with * in EventName
0 Karma

bfnpmsz
New Member

Thanks for the quick response. Your solution is close but I am still not getting the sequence I want. I need ordered by InstanceName -count then EventName by -count.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

I got confused by you samples, they were properly sorted by both EventName and Count. Try the updated answer.

0 Karma

bfnpmsz
New Member

I apologize for the confusion. But you are the MAN!!!! It looks great now and thanks for the help.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Glad to be help of 🙂

If there are followup question on this, please close the question by accepting the answer. Happy Splunking...

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 ...