topic Re: Where clause with eval and stats in Splunk Search

Where clause with eval and stats

SanthoshSreshta — Tue, 19 May 2015 12:49:54 GMT

Hi All.
I want to calculate percent of Total revenue in Rural and Urban areas.
The columns i have are Total_Revenue and PLACEMENT with values 0 and 1 where 0 represents Rural and 1 represents Urban.

 sourcetype="Customer_Churn" 
  | eventstats sum(Total_Revenue) as fin_rev
  | eventstats sum(Total_Revenue) as rural_rev where PLACEMENT=0
  | eventstats sum(Total_Revenue) aa urban_rev where PLACEMENT=1
  | eval REVENUE=rur_rev*100/urban_rev by PLACEMENT
  | replace 0 with Rural in PLACEMENT 
  | replace 1 with Urban in PLACEMENT

the above query doesnt get executed. please improvise that.

Thanks a lot in advance.

Re: Where clause with eval and stats

wpreston — Tue, 19 May 2015 13:06:49 GMT

I don't think you can put a where statement inside of eventstats like that. Instead, try using eval to create two new fields based on the value of PLACEMENT which you can then use eventstats on. Also, your eval statement for REVENUE uses a rur_rev field; should that be rural_rev instead? Something like this:

sourcetype="Customer_Churn" 
| eval Placement0Revenue=if(PLACEMENT="0", Total_Revenue, null())
| eval Placement1Revenue=if(PLACEMENT="1", Total_Revenue, null())
| eventstats sum(Total_Revenue) as fin_rev
| eventstats sum(Placement0Revenue) as rural_rev
| eventstats sum(Placement1Revenue) as urban_rev 
| eval REVENUE=rural_rev*100/urban_rev by PLACEMENT
| replace 0 with Rural in PLACEMENT 
| replace 1 with Urban in PLACEMENT

There may be a more elegant way of accomplishing what you need, but I think this will work.

Re: Where clause with eval and stats

aweitzman — Tue, 19 May 2015 13:14:52 GMT

I think you're making this harder than it needs to be. Try this:

 sourcetype="Customer_Churn" 
   | stats sum(Total_Revenue) as rev by PLACEMENT
   | eventstats sum(rev) as fin_rev
   | eval REVENUE = rev*100/fin_rev
   | table PLACEMENT REVENUE
   | replace 0 with Rural in PLACEMENT
   | replace 1 with Urban in PLACEMENT

stats will separate the sums for you; there's no need to compute them separately. Once that's done, you can use eventstats to get the overall total, since you're just summing everything.

Re: Where clause with eval and stats

SanthoshSreshta — Mon, 28 Sep 2020 19:59:06 GMT

Hi @wpreston Yes, I have missed that. I have corrected and checked it now.
the code i have written is wrong anyways. can you please guide me in displaying the total_revenue percentage of each placement. the formula is REVENUE=rural_rev*100/fin_rev. this i have missed in above code. by this it is showing same values for both placements.
Can you please correct it.

Re: Where clause with eval and stats

aweitzman — Tue, 19 May 2015 13:23:51 GMT

The answer I gave above will give you what you want.

Re: Where clause with eval and stats

SanthoshSreshta — Tue, 19 May 2015 13:25:07 GMT

wow.!!
you made it so simple in the first attempt itself.
I have same type of requirement with churn also. can you guide me in solving that. both should be in same query.
i will post a fresh one.

Re: Where clause with eval and stats

SanthoshSreshta — Tue, 19 May 2015 13:30:23 GMT

yes, i got it 🙂

Re: Where clause with eval and stats

woodcock — Tue, 19 May 2015 14:30:31 GMT

Try this:

sourcetype="Customer_Churn" | stats sum(Total_Revenue) as revenue by PLACEMENT | replace "0" with "rural_rev" in PLACEMENT | replace "1" with "urban_rev" in PLACEMENT | untable PLACEMENT revenue value | xyseries revenue PLACEMENT value | eval REVENUE=rur_rev*100/urban_rev

Re: Where clause with eval and stats

woodcock — Tue, 19 May 2015 14:32:33 GMT

Mine should be far more efficient than the other answer.