topic Re: Calculating percentages over multiple counts in Splunk Search

Calculating percentages over multiple counts

hduncan7 — Tue, 04 Jun 2019 12:38:02 GMT

I'm trying to get percentages based on the number of logs per table. I want the results to look like this:

**Table                   Count                    Percentage**
Total                     14392                    100
TBL1                      8302                     57.68
TBL2                      4293                     29.93
TBL3                      838                      5.82
TBL4                      639                      4.44
TBL5                      320                      2.22

Here's my search so far:

text = "\*" (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5) | eventstats count AS Total
| append [search PAJYE text = "\*TBL1\*" | stats count | eval "Count Type" = "TBL1 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*TBL2\*" | stats count | eval "Count Type" = "TBL2 Count" | eval "Percentage" = ((count/Total)\*100)] 
| append [search PAJYE text = "\*TBL3\*" | stats count | eval "Count Type" = "TBL3 Count" | eval "Percentage" = ((count/Total)\*100)] 
| append [search PAJYE text = "\*TBL4\*" | stats count | eval "Count Type" = "TBL4 Count" | eval "Percentage" = ((count/Total)\*100)] 
| append [search PAJYE text = "\*TBL5\*" | stats count | eval "Count Type" = "TBL5 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*" (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5) | stats count | eval "Count Type" = "Total Count" | eval "Percentage" = ((count/Total)\*100)]
| rename count as "Count"
| sort - "Count"
| table "Count Type", "Count", "Percentage"

I've tried so many different methods of trying to get this to work. My results are either a percentage column with no data, the counts get messed up, or pages of empty rows following my data.

Any help would be appreciated.

Re: Calculating percentages over multiple counts

aromanauskas — Tue, 04 Jun 2019 14:41:25 GMT

This should be easily simplified.

Try:

<search> (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5)|rex "TBL(?P<table_number>\d+)" |stats count by table_number | eventstats sum(count) AS total | eval percent=(count/total)*100

Once these results come in it can be refined to something useable.

Re: Calculating percentages over multiple counts

evania — Mon, 17 Jun 2019 21:59:38 GMT

Hi @hduncan7 ,

Did you have a chance to check out any answers? If it worked, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help you.

Thanks for posting!

Re: Calculating percentages over multiple counts

hduncan7 — Tue, 18 Jun 2019 11:29:32 GMT

How do I approve? I don't see a button to do that?