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
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
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.
I got confused by you samples, they were properly sorted by both EventName and Count. Try the updated answer.
I apologize for the confusion. But you are the MAN!!!! It looks great now and thanks for the help.
Glad to be help of 🙂
If there are followup question on this, please close the question by accepting the answer. Happy Splunking...