show top 5 values in column chart

[sarit_s]

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 ?

[sarit_s · Accepted solution]

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

[woodcock]

Please check out my answer below for something much simpler.

[woodcock]

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

[whrg]

Upvoted. Simple and elegant.

[DavidHourani]

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

[sarit_s]

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

[DavidHourani]

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.

[sarit_s]

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

[Shan]

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

[sarit_s]

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

[Shan]

@sarit_s ,

Can you display the output format. which you need to achieve ..

Thanks ..

[sarit_s]

this is a link to the screenshot

[Shan]

@sarit_s,
I don't see a link to the screenshot..

Thanks ..

[sarit_s]

https://imgur.com/AjrEGtc

[sarit_s]

do you have any idea?

[whrg]

What exactly do you want to display? The top 5 SerialNumber or the top 5 pause_reason or something else?

[sarit_s]

top 5 SerialNumber and top 5 pause_reason

[whrg]

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?

[sarit_s]

i want one for both
X axis will be SerialNumber and Y axis will be pause_reason

[whrg]

I'm still confused. How do you determine which are the top 5 values?