Splunk Search

Searching for percentage of total count grouped into buckets

tmtcollins
Explorer

I have a list of article IDs and their corresponding article view counts for a given day.

I want to see what percentage of articles are viewed between 1-50 times per day, 51-100 times per day, 101-150 times per day, 151-200 times per day and then >201 times per day. ( I may want to further configure these buckets later)

My basic search to get the article ID and count is attached.

sourcetype=200000747_ivu_access|where id !="" and UserName!="-"|lookup ECCOUser.csv ADSID as UserName OUTPUT Country Department Organisation 
| lookup ArticleDetails.csv ArticleID as id OUTPUT ArticleTitle|transaction UserName maxpause=30s| stats count(id) as ArticleCount  by id |sort ArticleCount desc 

Many thanks for any help, I am struggling with this one.

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

index="YoushouldAlwaysSpecifyAnIndex" AND sourcetype="200000747_ivu_access"
| where id !="" AND UserName!="-"
| lookup ECCOUser.csv ADSID AS UserName OUTPUT Country Department Organisation 
| lookup ArticleDetails.csv ArticleID AS id OUTPUT ArticleTitle
| transaction UserName maxpause=30s
| stats count(id) AS ArticleCount  BY id
| sort 0 - ArticleCount
| bin ArticleCount span=50 bins=5
| top ArticleCount

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

index="YoushouldAlwaysSpecifyAnIndex" AND sourcetype="200000747_ivu_access"
| where id !="" AND UserName!="-"
| lookup ECCOUser.csv ADSID AS UserName OUTPUT Country Department Organisation 
| lookup ArticleDetails.csv ArticleID AS id OUTPUT ArticleTitle
| transaction UserName maxpause=30s
| stats count(id) AS ArticleCount  BY id
| sort 0 - ArticleCount
| bin ArticleCount span=50 bins=5
| top ArticleCount
0 Karma

tmtcollins
Explorer

You sir, are a splunk answering machine..

This did the trick!

I had a slight issue because by bin/bucket range was so large that I changed

span=50 bins=5

to

span=1log5

Many thanks,

Tim

lpmarra
Engager

Try something like this on your line 3 to group into buckets:

| eval "Total Clicks"=case(ArticleCount>=1 AND ArticleCount<=50,"1-50", ArticleCount>50 AND <=100,"51-100", ArticleCount>100 AND ArticleCount<=150,"101-150", ArticleCount>150 AND ArticleCount<=200,"151-200", ArticleCount>201,">201")

That should give you the buckets your looking for. Then, to get the percentage for each bucket, try this:

| eventstats sum(ArticleCount) as Percent | eval "Percent Views"=round(ArticleCount*100/Percent,1)
0 Karma
Get Updates on the Splunk Community!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...