topic Re: show top 5 values in column chart in Splunk Search

show top 5 values in column chart

sarit_s — Thu, 06 Jun 2019 07:43:22 GMT

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 ?

Re: show top 5 values in column chart

whrg — Thu, 06 Jun 2019 08:06:13 GMT

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

Re: show top 5 values in column chart

sarit_s — Thu, 06 Jun 2019 08:23:14 GMT

top 5 SerialNumber and top 5 pause_reason

Re: show top 5 values in column chart

whrg — Thu, 06 Jun 2019 08:28:05 GMT

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?

Re: show top 5 values in column chart

sarit_s — Thu, 06 Jun 2019 08:33:02 GMT

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

Re: show top 5 values in column chart

whrg — Thu, 06 Jun 2019 08:47:13 GMT

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

Re: show top 5 values in column chart

sarit_s — Thu, 06 Jun 2019 08:50:13 GMT

the 5 with the highest values per SerialNumber

Re: show top 5 values in column chart

Shan — Tue, 11 Jun 2019 07:31:54 GMT

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

Re: show top 5 values in column chart

sarit_s — Tue, 11 Jun 2019 08:13:00 GMT

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

Re: show top 5 values in column chart

Shan — Tue, 11 Jun 2019 09:01:17 GMT

@sarit_s ,

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

Thanks ..

Re: show top 5 values in column chart

sarit_s — Tue, 11 Jun 2019 09:24:36 GMT

this is a link to the screenshot

Re: show top 5 values in column chart

Shan — Tue, 11 Jun 2019 12:01:19 GMT

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

Thanks ..

Re: show top 5 values in column chart

sarit_s — Tue, 11 Jun 2019 12:03:46 GMT

https://imgur.com/AjrEGtc

Re: show top 5 values in column chart

sarit_s — Wed, 12 Jun 2019 12:08:42 GMT

do you have any idea?

Re: show top 5 values in column chart

sarit_s — Wed, 12 Jun 2019 12:24:09 GMT

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

Re: show top 5 values in column chart

DavidHourani — Sun, 16 Jun 2019 19:56:46 GMT

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

Re: show top 5 values in column chart

woodcock — Sun, 16 Jun 2019 22:35:16 GMT

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

Re: show top 5 values in column chart

sarit_s — Wed, 30 Sep 2020 00:59:31 GMT

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

Re: show top 5 values in column chart

DavidHourani — Mon, 17 Jun 2019 06:45:14 GMT

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.

Re: show top 5 values in column chart

sarit_s — Mon, 17 Jun 2019 06:57:55 GMT

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