Splunk Search

## How to calculate a ratio by field value?

Path Finder

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?

Labels (2)

• ### stats

1 Solution
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
Path Finder

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

Path Finder

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!

Get Updates on the Splunk Community!

#### Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

#### Maximize the Value from Microsoft Defender with Splunk

Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

#### This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...