topic Re: Ratio using src_bytes instead of count for two fields in Splunk Search

Ratio using src_bytes instead of count for two fields

jaywilwk — Mon, 28 Sep 2020 14:42:37 GMT

how can I do a ratio search not based on count, but based on src_bytes (inbound traffic) to get a ratio for two fields. For example, I want to do a ratio of two categories Shopping sites to Search Engine sites, but not by count but by bandwidth (src_bytes).

Re: Ratio using src_bytes instead of count for two fields

srioux — Thu, 05 Sep 2013 12:54:57 GMT

You didn't give us a lot to go on.

Assuming that the two record types would have differing sourcetype (which they might not), the following should work:

...your base search search here... | stats sum(src_bytes) AS Size by sourcetype

That would create a sum of the values in src_bytes, using sourcetype as a grouping, over the timeframe of your search. If you have another field differentiating the two categories, you should be able to use that instead of sourcetype.

Re: Ratio using src_bytes instead of count for two fields

jaywilwk — Mon, 28 Sep 2020 14:42:54 GMT

Here's what i'm trying to do. I'm trying to get a ratio of events within a category, but I'm only concern with two events. One event is in the category IBC. The other events I want to consolidate those into one event in the category, to get a ratio of IBC to Non IBC traffic by src_bytes.
index=proxysg sourcetype=proxysg | stats sum(src_bytes) as MB by category | eval MB=round(MB/1024/1024,2) | category!=IBC Allow* as Non-Ibc, category=IBC Allow as IbC Allow

Re: Ratio using src_bytes instead of count for two fields

srioux — Thu, 05 Sep 2013 14:28:47 GMT

Is 'category' a field in your raw data, do you have it extracted, or is that piece of the search still pending? Can you provide a few sample records (anonymize the data set as required).

There are likely a few ways to get what you're looking for.

Re: Ratio using src_bytes instead of count for two fields

jaywilwk — Thu, 05 Sep 2013 14:37:10 GMT

Category is an extracted field. This search didn't work for me at all. This is the basic search I started out with manipulating to try to yield some results.

Re: Ratio using src_bytes instead of count for two fields

srioux — Thu, 05 Sep 2013 14:44:21 GMT

Try:

index=proxysg sourcetype=proxysg | eval Category=case(like(category,"IBC Allow%"),"IBC",1=1,"Non-IBC") | stats sum(src_bytes) AS Bytes by Category | eval MB=round(Bytes/1024/1024,2)

The ratios may need to be calculated once we've appropriately categorized the data.

Re: Ratio using src_bytes instead of count for two fields

jaywilwk — Thu, 05 Sep 2013 14:51:00 GMT

The results shown was the two Category's IBC, Non-IBC along with Bytes field and MB field.

Re: Ratio using src_bytes instead of count for two fields

jaywilwk — Thu, 05 Sep 2013 15:31:13 GMT

Is it possible to have it setup like this? I'm mainly concern with the layout of the results. The results of this shows a list of bases with the post, gets and the ratio of get/post:
index=proxysg sourcetype=proxysg | stats count(eval(method="POST")) as POST, count(eval(method="GET")) AS GET by base | eval RATIO OF GET/POST=(GET/POST

Re: Ratio using src_bytes instead of count for two fields

srioux — Mon, 28 Sep 2020 14:42:59 GMT

I don't know what your results look like, so not sure. That said, here's another search which should give you a ratio:

index=proxysg sourcetype=proxysg | eval Category=case(like(category,"IBC Allow%"),"IBC",1=1,"Non-IBC") | stats sum(eval(round(if(Category="IBC",src_bytes,0)/1024/1024,2))) AS IBC_MB, sum(eval(round(if(Category="Non-IBC",src_bytes,0)/1024/1024,2))) AS Non-IBC_MB | eval Ratio=IBC_MB/Non-IBC_MB

Re: Ratio using src_bytes instead of count for two fields

jaywilwk — Thu, 05 Sep 2013 17:12:16 GMT

that GET/POST one I sent you showed results like this:
base POST GET RATIO OF GET/POST
1. base a 9 9 1
2. base b 6 2 0.33
3. base c 2 3 1.50

Re: Ratio using src_bytes instead of count for two fields

jaywilwk — Mon, 09 Sep 2013 16:12:05 GMT

I have a question. On the first ratio search you sent, is it possible to have the search show like this:
base IbC Non-IbC Ratio of IbC/Non-IbC
base a 22 23 0.96
base b 6 7 0.86
base c 25 26 0.96

Re: Ratio using src_bytes instead of count for two fields

srioux — Mon, 28 Sep 2020 14:44:31 GMT

Try:
index=proxysg sourcetype=proxysg | eval Category=case(like(category,"IBC Allow%"),"IBC",1=1,"Non-IBC") | stats sum(eval(round(if(Category="IBC",src_bytes,0)/1024/1024,2))) AS IBC_MB, sum(eval(round(if(Category="Non-IBC",src_bytes,0)/1024/1024,2))) AS Non_IBC_MB by base | eval Ratio=IBC_MB/Non_IBC_MB