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?
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.
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.
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>>' ]
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!