topic Re: How to calculate a ratio by field value? in Splunk Search

How to calculate a ratio by field value?

splunkuserCA1 — Mon, 18 Oct 2021 22:58:38 GMT

I have some data like the following:

NAME	Code
Suzy	0
John	0
Adam	1
Suzy	1
John	0
Adam	1

I am trying to calculate the ratio of code=1 to code=0, by Name, and display these ratios by hour. The name values are dynamic and unknown at query time.

I can get halfway there, using a dynamic eval field name, like this:

index=SOME_INDEX sourcetype=SOME_SOURCETYPE code
| eval counterCode0{name} = if(code=0, 1, 0)
| eval counterCode1{name} = if(code=1, 1, 0)
| bin _time span=1m
| stats sum(counterCode0*), sum(counterCode1*) by _time

But I can't figure out how to get the ratios of counterCode1* to counterCode0*. Any ideas? Or do I need to approach this problem differently?

Re: How to calculate a ratio by field value?

splunkuserCA1 — Tue, 19 Oct 2021 00:17:03 GMT

I think I found a solution:

index=SOME_INDEX sourcetype=SOME_SOURCETYPE code
| eval counterCode0 = if(code=0, 1, 0)
| eval counterCode1 = if(code=1, 1, 0)
| bin _time span=1m
| stats sum(counterCode0) as sumCode0, sum (counterCode1) as sumCode1 by _time, name
| eval ratio=sumCode1/sumCode0
| sort _time

Perhaps using the eval counterCode0{name}=... construct was making a solution too complicated. Still, if anyone knows how solve the problem using that method, please let me know!

Re: How to calculate a ratio by field value?

richgalloway — Tue, 19 Oct 2021 00:26:14 GMT

Once you have the 0 and 1 counts, all you need is an eval to calculate their ratio. Because there are many sets of counts to evaluate, we'll use foreach to loop through them.

index=SOME_INDEX sourcetype=SOME_SOURCETYPE code | eval counterCode0{name} = if(code=0, 1, 0) | eval counterCode1{name} = if(code=1, 1, 0) | bin _time span=1m | stats sum(counterCode0*), sum(counterCode1*) by _time | foreach counterCode0* [ eval CounterRatio<<MATCHSTR>>=counterCode0<<MATCHSTR>>/counterCode1<<MATCHSTR>> ]

Feel free to modify the logic to get the desired ratio and to handle possible division by zero.

Re: How to calculate a ratio by field value?

splunkuserCA1 — Tue, 19 Oct 2021 19:00:12 GMT

That led me to the solution! Thanks. Ultimately, I had to do some more syntactical gymnastics:

1) My "name" field had some special chars that I had to remove. Otherwise, I'd get an error message like "Failed to parse templatized search for field...":

| eval newName=replace(name,"[']","")

2) For whatever reason, the "foreach" syntax provided did not work in the inner "eval" when trying to include the "counterCode1" values. Instead, I had to use this garish syntax - MATCHSEG2 worked better for me:

| foreach sum*counterCode0* [ eval CounterRatio<<MATCHSTR>>='<<FIELD>>' / 'sum(counterCode1<<MATCHSEG2>>' ]