Ratio using src_bytes instead of count for two fields

[jaywilwk]

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).

[srioux · Accepted solution]

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.

[srioux]

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

[jaywilwk]

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

[jaywilwk]

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

[srioux]

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

[jaywilwk]

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

[jaywilwk]

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

[srioux]

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.

[jaywilwk]

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.

[srioux]

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.

[jaywilwk]

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