Dashboards & Visualizations

Percentage - sum all values below 5% into a value called 'Other'

technie101
Explorer

Hi Splunk gurus!

Our application exposes different services as APIs. All API traffic coming into the application is logged in Splunk.

For reporting purposes, we display the total call volume in the format below.

alt text

using the query

index=something | top limit=100 service_name | sort by count desc | rename count as "# of calls", percent as "% of total calls"

The requirement now is to only show services which have call volume > 5%, but to summate all the remaining ones into a single row called 'Other'. So, the final output should be something like:

alt text

Need help on how to achieve this please.

Thanks

0 Karma
1 Solution

HiroshiSatoh
Champion

Try this!

 index=something 
| top limit=100 service_name 
| eval service_name=if(percent>5,service_name,"Other")
| stats sum(count) as count,sum(percent) as percent by service_name
| sort by count desc 
| rename count as "# of calls", percent as "% of total calls"

View solution in original post

skawasaki_splun
Splunk Employee
Splunk Employee

If you're gonna group everything under 5% as "Other", then do you really want to limit to the top 100? Your percentage won't add up to 100%.

0 Karma

niketn
Legend

@technie101, are you planning on using table or pie chart to show the results?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi technie101,
try something like this:

index=something 
| eventstats count AS Total
| stats values(Total) AS Total count BY service_name
| eval perc=count/Total*100
| table service_name count perc
| eval service_name=if(perc>5,service_name,"Other")
| stats sum(count) AS count BY service_name
| sort by count desc 
| rename count as "# of calls"

Bye.
Giuseppe

0 Karma

niketn
Legend

@cusello, I would perform stats first and then eventstats to apply to limited services (~100 based on current query), rather than enriching all events with eventstats.

 index=something 
| stats count by service_name
| eventstats sum(count) as Total
...
...
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

HiroshiSatoh
Champion

Try this!

 index=something 
| top limit=100 service_name 
| eval service_name=if(percent>5,service_name,"Other")
| stats sum(count) as count,sum(percent) as percent by service_name
| sort by count desc 
| rename count as "# of calls", percent as "% of total calls"

technie101
Explorer

Thanks Hiroshi. I've accepeted the answer because the changes on top of what I was already using were minimal.

HiroshiSatoh
Champion

Please be careful as it is truncated at the top 100.
| top limit=100 service_name

0 Karma

niketn
Legend

@technie101, you should be comparing results (as stated by @HiroshiSatoh) and performance (using Job Inspector) with the queries posted as answers. You should choose the query which gives accurate results as per your need and also performs the best while executing.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

lfedak_splunk
Splunk Employee
Splunk Employee

Hey @technie101, Can you post your final search that utilized @HiroshiSatoh's answer? That would be great to see altogether for other users. I think it would be great to feature in the Smart Answers blog series as well. 🙂

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @technie101,
Can you please try?

YOUR_FIRST_SEARCH 
| eval srno=1 
| accum srno | eval service=if(srno>5,"Other",service)
| stats sum("# of calls") as "# of calls" sum("% of total calls") as "% of total calls" by service

Change if condition as per your requirement "if(srno>5,"Other",service)".

Thanks

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...