Splunk Search

Calculating percentages over multiple counts

hduncan7
Engager

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.

0 Karma
1 Solution

aromanauskas
Path Finder

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.

View solution in original post

0 Karma

evania
Splunk Employee
Splunk Employee

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!

0 Karma

hduncan7
Engager

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

0 Karma

aromanauskas
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Observe and Secure All Apps with Splunk

  Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

Splunk Decoded: Business Transactions vs Business IQ

It’s the morning of Black Friday, and your e-commerce site is handling 10x normal traffic. Orders are flowing, ...

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...