Splunk Search

How to calculate the percentage of results within a range against the total number of events?

dovelsh12223621
Path Finder

All I want to do like this:
- The calculation of each page the bandwidth consumed.
- Calculate the total bandwidth consumption of all pages
- Calculate the compliance percentage.

I can run the following searches, but can't seem to figure out how to put them together.

index="ihs_test" | transaction clientip | stats sum(bytes) as total.

and

index="ihs_test"| transaction clientip  |  stats sum(bytes) as total_pageuse by uri_path.

All I really need to do is put them together so I can perform the calculation of round((total_pageuse/total),2) as CompliancePct.
Now, the current search I have is:

index="ihs_test"  | transaction clientip | stats sum(bytes) as total_usebytes |appendcols [search index="ihs_test" | transaction clientip | stats sum(bytes) as total_pageuse by uri_path] | eval percentage=round((total_pageuse/total),2)

But the result is not what I want.

uri_path     total_usebytes     total     percentage
xxx.html           10            100         0.1
ccc.html           50
ddd.html           40

row 2 and row 3 have no percentages returned. What can I do return the result like 0.5, 0.4?
Please tell me what to do, thanks.

Tags (1)
1 Solution

sideview
SplunkTrust
SplunkTrust

I think what you need, is to use eventstats to get the total bytes, before transaction gets involved. Your second search index="ihs_test"| transaction clientip | stats sum(bytes) as total_pageuse by uri_path. may not be doing what you think it's doing.

That search will make rows that are transactions by clientip, on which uripath and bytes are both multivalue fields. But there's no association any longer as to which uripath value has which bytes value, they're just big piles of multivalue values. Therefore when the sum(bytes) by uri_path comes along, that is going to try and untangle all the mashed-together uripath values and even if it does find something reasonable to do with them, I don't think it's going to be what you need coming out.

Anyway, I think this will be more reasonable.

index="ihs_test" | eventstats sum(bytes) as total_bytes | transaction clientip  |  stats sum(bytes) as total_pageuse first(total_bytes) as total_bytes by uri_path | eval CompliancePct=round(total_pageuse/total_bytes,2)

eventstats makes it's own pass, creating a new field called total_bytes that just is the sum of all bytes across all events. We have to make sure to pass it along in our stats clause, but then we can have at the end to do your percent calculation, etc.

View solution in original post

sideview
SplunkTrust
SplunkTrust

I think what you need, is to use eventstats to get the total bytes, before transaction gets involved. Your second search index="ihs_test"| transaction clientip | stats sum(bytes) as total_pageuse by uri_path. may not be doing what you think it's doing.

That search will make rows that are transactions by clientip, on which uripath and bytes are both multivalue fields. But there's no association any longer as to which uripath value has which bytes value, they're just big piles of multivalue values. Therefore when the sum(bytes) by uri_path comes along, that is going to try and untangle all the mashed-together uripath values and even if it does find something reasonable to do with them, I don't think it's going to be what you need coming out.

Anyway, I think this will be more reasonable.

index="ihs_test" | eventstats sum(bytes) as total_bytes | transaction clientip  |  stats sum(bytes) as total_pageuse first(total_bytes) as total_bytes by uri_path | eval CompliancePct=round(total_pageuse/total_bytes,2)

eventstats makes it's own pass, creating a new field called total_bytes that just is the sum of all bytes across all events. We have to make sure to pass it along in our stats clause, but then we can have at the end to do your percent calculation, etc.

dovelsh12223621
Path Finder

Hi friend:
Your answer solved my problem,faultlessly.I see a blog last night,which link address like "http://blogs.splunk.com/2014/04/01/search-command-stats-eventstats-and-streamstats-2/"
It explain the principle will be the same.Thanks for your reply.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...