How to Get Percentage with Case and Stats

[jepoyyyy]

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!

[kristian_kolb · Accepted solution]

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

[jepoyyyy]

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!