topic Re: Sort order of subsearch results in Splunk Search

Sort order of subsearch results

bfnpmsz — Fri, 25 Sep 2015 16:07:20 GMT

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:

But what I am getting is this:

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

Re: Sort order of subsearch results

somesoni2 — Fri, 25 Sep 2015 16:49:44 GMT

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

Re: Sort order of subsearch results

bfnpmsz — Fri, 25 Sep 2015 18:23:23 GMT

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.

Re: Sort order of subsearch results

somesoni2 — Fri, 25 Sep 2015 19:10:06 GMT

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

Re: Sort order of subsearch results

bfnpmsz — Fri, 25 Sep 2015 19:38:47 GMT

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

Re: Sort order of subsearch results

somesoni2 — Fri, 25 Sep 2015 19:51:42 GMT

Glad to be help of 🙂

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