Splunk Search
Highlighted

Searching for percentage of total count grouped into buckets

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
Highlighted

Re: Searching for percentage of total count grouped into buckets

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
Highlighted

Re: Searching for percentage of total count grouped into buckets

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
Highlighted

Re: Searching for percentage of total count grouped into buckets

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