Splunk Search

Where clause with eval and stats

Contributor

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.

0 Karma
1 Solution

Motivator

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.

View solution in original post

Esteemed Legend

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

Esteemed Legend

Mine should be far more efficient than the other answer.

0 Karma

Motivator

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.

View solution in original post

Contributor

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.

0 Karma

Motivator

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.

0 Karma

Contributor

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.

0 Karma

Motivator

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

0 Karma

Contributor

yes, i got it 🙂

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!