Splunk Search

show top 5 values in column chart

sarit_s
Communicator

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 ?

Tags (1)
0 Karma
1 Solution

sarit_s
Communicator

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

View solution in original post

sarit_s
Communicator

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
Esteemed Legend

Please check out my answer below for something much simpler.

0 Karma

woodcock
Esteemed Legend

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
Motivator

Upvoted. Simple and elegant.

0 Karma

DavidHourani
Super Champion

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

0 Karma

sarit_s
Communicator

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

0 Karma

DavidHourani
Super Champion

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.

0 Karma

sarit_s
Communicator

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

0 Karma

Shan
Builder

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

0 Karma

sarit_s
Communicator

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

0 Karma

Shan
Builder

@sarit_s ,

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

Thanks ..

0 Karma

sarit_s
Communicator

alt text

this is a link to the screenshot

0 Karma

Shan
Builder

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

Thanks ..

0 Karma

sarit_s
Communicator
0 Karma

sarit_s
Communicator

do you have any idea?

0 Karma

whrg
Motivator

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

0 Karma

sarit_s
Communicator

top 5 SerialNumber and top 5 pause_reason

0 Karma

whrg
Motivator

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?

0 Karma

sarit_s
Communicator

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

0 Karma

whrg
Motivator

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...