Splunk Search

How to Get Percentage with Case and Stats

jepoyyyy
Explorer

Good day!

I am having trouble getting the percentages after grouping the data via case. Any help would greatly be appreciated.

Here is the sample data:

User ID, Upload, Download
User1    1024    4098  
User2    512     2231  
User3    998     1054 

Now, I have this search to group the users by usage

   index=some_index 
   | eval total=Upload+Download    
   | eval category = case(total>-1 AND total<1048577, "1. Below 1MB",  
                     total>1048578 AND total<10485761, "2. 1MB to 10MB",  
                     total>10485760 AND total<20971521, "3. 10MB+ to 20MB",  
                     total>9663676416, "4. Above 9GB")    
   | stats count, sum(total) AS Payload by category  
   | sort category
   | rename category as "Total Usage", count as "Subscriber Count"

from the search above, I get the following result:

Total Usage         Subscriber Count          Payload  
1. Below 1MB                  51,275          1.21 GB  
2. 1MB to 10MB                 5,186         22.94 GB  
3. 10MB+ to 20MB               3,032         43.55 GB  
4. Above 9GB                   4,924         77.16 TB

My question is, how do I add the percentage of the count and payload for each Category to have a result like the one below:

Total Usage         Subscriber Count   Percentage     Payload     Payload Percentage  
1. Below 1MB                  51,275          20%     1.21 GB                     2%  
2. 1MB to 10MB                 5,186           5%    22.94 GB                    15%  
3. 10MB+ to 20MB               3,032           3%    43.55 GB                    17%    
4. Above 9GB                   4,924           2%    77.16 TB                    30%

Thank you in advance!

0 Karma
1 Solution

kristian_kolb
Ultra Champion

Something along these lines would probably work - edit the end of your search to look like this (the first stats is the same as in your search, with a slight renaming of field values).

... 
| stats count AS SubscriberCount, sum(total) AS Payload by category  
| eventstats sum(SubscriberCount) AS TOTAL_USERS 
| eventstats sum(Payload) AS TOTAL_PAYLOAD 
| eval SubscriberPercentage = round(100*SubscriberCount / TOTAL_USERS,0) . "%"
| eval PayloadPercentage = round(100 * Payload / TOTAL_PAYLOAD,0) . "%"
| sort category
| rename category as "Total Usage" 
| table "Total Usage" SubscriberCount SubscriberPercentage Payload PayloadPercentage

/K

View solution in original post

0 Karma

kristian_kolb
Ultra Champion

Something along these lines would probably work - edit the end of your search to look like this (the first stats is the same as in your search, with a slight renaming of field values).

... 
| stats count AS SubscriberCount, sum(total) AS Payload by category  
| eventstats sum(SubscriberCount) AS TOTAL_USERS 
| eventstats sum(Payload) AS TOTAL_PAYLOAD 
| eval SubscriberPercentage = round(100*SubscriberCount / TOTAL_USERS,0) . "%"
| eval PayloadPercentage = round(100 * Payload / TOTAL_PAYLOAD,0) . "%"
| sort category
| rename category as "Total Usage" 
| table "Total Usage" SubscriberCount SubscriberPercentage Payload PayloadPercentage

/K

0 Karma

jepoyyyy
Explorer

Thank you very much Kristian. It worked like a charm! Also, thank you for fixing the formatting of my post. I'm still not used to the proper formatting style 😄

Thanks again!

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...