How to get the percentage using values in event over two columns?

[splunker1981]

Hello Splunk masters

I am trying to figure out how to get a rate (percent) by looking at two strings within a column, then dividing by values in another column

Sample data below.  What I'm trying to do is calculate the rate of "incomplete" by batch week.  Rate is calculated by taking the batch week, getting the total = (complete + incomplete) / incomplete.  As shown below, I included a sample of what I'd like to get as a final output.  This is way beyond my Splunk-fu and hoping someone can help me out here.   Thanks for the help in advanced

Sample Data
site	batch_status	batch_week	status_count
2506	complete	      16	      7
2506	incomplete	      16	      4
2506	complete	      17	      5
2506	incomplete	      17	      3
2506	complete	      18	      2
2506	incomplete	      18	      4

			
What I'd like to get back			
2506	incomplete	16	36%
2506	incomplete	17	38%
2506	incomplete	18	-66%

[gcusello · Accepted solution]

Hi @splunker1981,

please see my approach and adapt it to your needs:

<your_search
| stats 
   sum(eval(if(batch_status="complete",status_count,0))) AS complete
   sum(eval(if(batch_status="incomplete",status_count,0))) AS incomplete
   BY site batch_week
| eval perc=((complete+incomplete)/incomplete)."%"
| table site batch_week incomplete perc

Ciao.

Giuseppe

[splunker1981]

Thanks a bunch! That did the trick

[gcusello]

Hi @splunker1981,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉