Hello
im trying to show top 5 values in column chart
this is my query:
index="ssys_*_fdm" pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| `Region`
| `pauseReason`
|`SerialNumber`
| top 5 pause_reason SerialNumber
| table pause_reason SerialNumber
but the chart is empty
removing the table returns me SerialNumber and count in the chart which i don't want
what am i doing wrong ?
thanks
i finally did it that way :
(index=ssys_*_fdm OR index=other_fdm) pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| pauseReason
| stats count by SerialNumber,pause_reason
| eventstats sum(count) as total by SerialNumber
| sort - total
| streamstats dc(SerialNumber) as i
| where i<=5
| chart values(count) over SerialNumber by pause_reason
thanks
i finally did it that way :
(index=ssys_*_fdm OR index=other_fdm) pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| pauseReason
| stats count by SerialNumber,pause_reason
| eventstats sum(count) as total by SerialNumber
| sort - total
| streamstats dc(SerialNumber) as i
| where i<=5
| chart values(count) over SerialNumber by pause_reason
Please check out my answer below for something much simpler.
You are making this WAY too hard. It is simply this:
index="ssys_*_fdm" pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| `Region`
| `pauseReason`
| `SerialNumber`
| top 5 pause_reason BY SerialNumber
| table pause_reason SerialNumber
Upvoted. Simple and elegant.
Hi @sarit_s,
Have a look here for sorting based on two terms :
https://answers.splunk.com/answers/246476/how-to-sort-a-table-based-on-two-columns-in-order.html
So you're really close to the answer, it should be like this :
index=ssys_*_fdm pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| `SerialNumber`
| `pauseReason`
|stats count by SerialNumber,pause_reason
|sort 5 -count
|stats list(pause_reason) as pause_reason, count by SerialNumber
|sort 5 -count
| fields SerialNumber,pause_reason, count
Hope this works out for you.
Cheers,
David
thanks
i finally did it that way :
(index=ssys_*_fdm OR index=other_fdm) pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| pauseReason
| stats count by SerialNumber,pause_reason
| eventstats sum(count) as total by SerialNumber
| sort - total
| streamstats dc(SerialNumber) as i
| where i<=5
| chart values(count) over SerialNumber by pause_reason
thanks for your help
Awesome glad to hear it's working ! You can convert your comment to an answer and accept it so other can use if ever they have the same issue ^^ Also give @woodcock 's answer a try, it should do the trick and looks way easier.
i want to update my question
i want to display graph that showing top 5 SerialNumbers for top 5 pause_reasons..
i got this query
index=ssys_*_fdm pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| `SerialNumber`
| `pauseReason`
|stats count by SerialNumber,pause_reason
|sort -count
|stats list(pause_reason) as pause_reason, count by SerialNumber
|sort -count | head 5
| fields SerialNumber,pause_reason
this query gives me the desired result in table but the chart is empty
Dear @sarit_s,
Use below query and change index and by clause filed as per your need.
index=* pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| stats count(pause_reason) as pause_reason , count(SerialNumber) as SerialNumber by sourcetype
| table sourcetype pause_reason SerialNumber
| sort -pause_reason -SerialNumber limit=5
While displaying in chart, choose Column chart so that both pause_reason SerialNumber top 5 values will be displayed..
you can try another approach also ..
index=* pauseReason: NOT "pauseReason: NotPaused" pauseReason: NOT "pauseReason: UserPaused"
| stats count(pause_reason) as pause_reason , count(SerialNumber) as SerialNumber by sourcetype
| table sourcetype pause_reason SerialNumber
| top limit=5 sourcetype pause_reason SerialNumber
Thanks..
Hi
Thanks for your comment
i need clause field to be by SerialNumber and since we are counting the SerialNumber in stats it is not possible to have it in clause field
@sarit_s ,
Can you display the output format. which you need to achieve ..
Thanks ..
this is a link to the screenshot
@sarit_s,
I don't see a link to the screenshot..
Thanks ..
do you have any idea?
What exactly do you want to display? The top 5 SerialNumber or the top 5 pause_reason or something else?
top 5 SerialNumber and top 5 pause_reason
You could create two seperate columns charts; one for SerialNumber and one for pause_reason.
Or do you want only one column chart for both? If so, what are the X axis and the Y axis supposed to be?
i want one for both
X axis will be SerialNumber and Y axis will be pause_reason
I'm still confused. How do you determine which are the top 5 values?