Splunk Search

How to calculate a ratio by field value?

splunkuserCA1
Path Finder

I have some data like the following:

NAMECode
Suzy0
John0
Adam1
Suzy1
John0
Adam1

 

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)
0 Karma
1 Solution

richgalloway
SplunkTrust
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.

View solution in original post

richgalloway
SplunkTrust
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.

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

 

splunkuserCA1
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!

0 Karma
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 ...