Splunk Search

How to group results by count with high number of values

external_alien_
Explorer

Hi everybody, I'm new to Splunk and this will be my first question!
I'm tinkering with some server response time data, and I would like to group the results by showing the percentage of response times within certain parameters. I was trying to group the data with one second intervals to see how many response times were within 0-1 seconds, 1-2,[...], 14-15 etc.
I tried filtering at the end of my query with:

| bin response_time span=1 | stats sum(count) as count by response_time

However, I got the warning :

These results may be truncated. This visualization is configured to display a maximum of 1000 results per series, and that limit has been reached. 

Am I filtering the values correctly but simply getting way too many results or is there something else I'm missing? Any way to narrow down the results further?
Purpose: My end goal is to display the results in for example a pie chart where probably 99% of the values would be between 0-1s.

All help is greatly appreciated, thank you in advance! 😃

1 Solution

external_alien_
Explorer

Got it! used the "top" command! Now have a beautiful pie chart with drill-down functionality that I'll expand on! thank you @sideview for your guidance and tips!
What works for me:

| eval secret_response_time = response_time  | bin response_time span=1s  
| eval response_time=case(secret_response_time>=0 AND secret_response_time<1,"0-1", secret_response_time>=1 AND secret_response_time<2,"1-2",secret_response_time>=2 AND secret_response_time<3,"2-3", secret_response_time>=3, "3+")  
| top response_time

View solution in original post

0 Karma

external_alien_
Explorer

Got it! used the "top" command! Now have a beautiful pie chart with drill-down functionality that I'll expand on! thank you @sideview for your guidance and tips!
What works for me:

| eval secret_response_time = response_time  | bin response_time span=1s  
| eval response_time=case(secret_response_time>=0 AND secret_response_time<1,"0-1", secret_response_time>=1 AND secret_response_time<2,"1-2",secret_response_time>=2 AND secret_response_time<3,"2-3", secret_response_time>=3, "3+")  
| top response_time

View solution in original post

0 Karma

sideview
SplunkTrust
SplunkTrust

One more helpful comment - If you flip it around so that the case statement works its way down, then you can eliminate every one of your "AND secret_response_time

sideview
SplunkTrust
SplunkTrust

This is doable, reality is just making it a bit tricky.

Say in the end you want to create categories that are like:
0-1
1-2
2-3
3-4
... but then at 10 you want different handling!
10-100
100+

The bin command is great but of course it can only create buckets of a single size. So we can cheat.

We save off a "secret_response_time" field for ourselves. Then after the bin command does it's work we do a case() function inside an eval command, that can selectively overwrite the bin command's work for us.

| eval secret_response_time = response_time
| bin response_time span=1 
| eval response_time=case(secret_response_time>=100,"100+",secret_response_time>=10,"10-100")
| stats sum(count) as count by response_time

End results -- the bin command handles the simple intervals, and we just override it for the custom ones.

0 Karma

external_alien_
Explorer

Hi again guys! After some soul searching and tweaking I ended up with:

eval secret_response_time = response_time | bin response_time span=1s

| eval response_time=case(secret_response_time>=0 AND secret_response_time<1,"0-1", secret_response_time>=1 AND secret_response_time<2,"1-2",secret_response_time>=2 AND secret_response_time<3,"2-3", secret_response_time>=3, "3+")
| stats sum(count) as count by response_time

This gives me a beautiful column diagram with Count on the Y-axis and the tags "0-1, 1-2" etc on the X-axis. Unfortunately count seems to be Zero, or at least it's not visible since the graph is blank. If I go into the statistics tab all response times are labeled correctly, they're just no being displayed in the graph! So close! Any tips? 😃

0 Karma

sideview
SplunkTrust
SplunkTrust

You have sum(count) as count, which.... if you were aggregating rows that had themselves come out of a stats command, would make perfect sense. Here unfortunately it makes no sense because there is no "count" field on the incoming rows to sum up. replace it with just stats count by response_time

0 Karma

external_alien_
Explorer

This sounds like a really clever trick, thank you! 😃
Unfortunately I got the error "The specified span would result in too many (>50000) rows." And if I select a pie chart I'm greeted with "numeric data required". I'll play around with trying to limit the span and range and will keep an eye on this thread! Thank you for your fast response!

0 Karma

woodcock
Esteemed Legend

I suggest you rethink your intentions and decide on what you really need. A pie chart like that would be useless, right?

0 Karma

external_alien_
Explorer

Well I was hoping to get a decent overview over the systems health and maybe also trigger an alert when more than 1% of response times are above "X". If I get more proficient at Splunk I would like to have a function to click and display only the 1% of values that are above "X" for example!
And it's not useless since it's for my own personal benefit, to get better at the Splunk syntax and for me to learn what is possible! 😃

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!