How to calculate a ratio by field value?

[splunkuserCA1]

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?

[richgalloway · Accepted solution]

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.

[splunkuserCA1]

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>>' ]

[splunkuserCA1]

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!