Splunk Search

How do I get the total and percentage on each row on the following table?

skribble5
Explorer

Hello there,

My current code is giving me the following (if the screenshot is not clear, I provide the numbers later on):

alt text

By each row or 'campaign', it shows the counts of events that fell in each of the 3 buckets (i.e. 'glanced', 'read','skimmed'). The value of field "read_category" has the 3 buckets.

In the above picture, here are the numbers if the picture is blurry:
-First Campaign: glanced = 217, read=200, skimmed=196
-Second Campaign: glanced=1488, read = 1307, skimmed = 953

I would now like to add % on each row by bucket for the row. The sum of percentages on each row should add to 100%. Something like this:

alt text

How can I achieve this? I would appreciate any help. Thanks in advance!
My current code is:

sourcetype="xxx" source="yyy.log" 

| eval read_category = case (
     duration < 3,"glanced",
     duration > 8, "read",
     duration >= 3 AND duration <= 8, "skimmed"
     )
| lookup cml_campaign_lookup_2018_12_11.csv deliveryid OUTPUT Campaign, sent_date
| table  deliveryid, user, duration, clientIP, read_category, Campaign, sent_date
| chart count as total over Campaign by read_category
0 Karma
1 Solution

kmaron
Motivator

try this

 sourcetype="xxx" source="yyy.log"  
 | eval read_category = case (
      duration < 3,"glanced",
      duration > 8, "read",
      duration >= 3 AND duration <= 8, "skimmed"
      )
 | lookup cml_campaign_lookup_2018_12_11.csv deliveryid OUTPUT Campaign, sent_date
 | table  deliveryid, user, duration, clientIP, read_category, Campaign, sent_date
 | chart count as total over Campaign by read_category
 | eval totcount=glanced + read + skimmed
 | eval glancedperc = glanced/totcount*100
 | eval readperc = read/totcount*100
 | eval skimmedperc = skimmed/totcount*100
 | fields - totcount

View solution in original post

adonio
Ultra Champion

try and run this anywhere:

| makeresults count=300
| eval campaign = "campaign1;;;campaign2"
| makemv  delim=";;;" campaign
| mvexpand campaign
| eval duration = random()%20
| eval read_category = case (
      duration < 3,"glanced",
      duration > 8, "read",
      duration >= 3 AND duration <= 8, "skimmed"
      )
| rename COMMENT as "the above generates data below is the solution" 
| eventstats count as total_count by campaign
| stats count(eval(read_category=="read")) as "read" count(eval(read_category=="skimmed")) as "skimmed" count(eval(read_category=="glanced")) as "glanced"  max(total_count) as total by campaign
| eval read_perc = round(read/total*100)."%"
| eval skimmed_perc = round(skimmed/total*100)."%"
| eval glanced_perc = round(glanced/total*100)."%"
| table *

screenshot:

alt text

hope it helps

kmaron
Motivator

try this

 sourcetype="xxx" source="yyy.log"  
 | eval read_category = case (
      duration < 3,"glanced",
      duration > 8, "read",
      duration >= 3 AND duration <= 8, "skimmed"
      )
 | lookup cml_campaign_lookup_2018_12_11.csv deliveryid OUTPUT Campaign, sent_date
 | table  deliveryid, user, duration, clientIP, read_category, Campaign, sent_date
 | chart count as total over Campaign by read_category
 | eval totcount=glanced + read + skimmed
 | eval glancedperc = glanced/totcount*100
 | eval readperc = read/totcount*100
 | eval skimmedperc = skimmed/totcount*100
 | fields - totcount

skribble5
Explorer

Hi kmaron - thanks for looking into this and providing feedback. That has totally worked. Thank you so much!!

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