Splunk Search

stats and eventstats

Contributor

Hi All.

I want to calculate the percentage of churned_customer in rural and urban areas.
The columns i have are CHURN with values 0 and 1 where "0" represents unchurn and "1" represents churned and another column PLACEMENT with values 0 and 1 where 0 represents Rural and 1 represents Urban.

the query i had used is

sourcetype="Customer_Churn"
| stats count(eval(CHURN="1")) as churned_count by PLACEMENT
| stats count(eval(CHURN="1")) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| stats values(churned_percentage) by PLACEMENT

it is not showing any values. please resolve it.

I have calculated for total_revenue in the same manner by using the code mention below.
here i have calculated tot_revenue percentage in rural and urban areas of all churns. where in above i want only churned. please some one help me out.

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

thanks a lot in advance,
Santhosh.

0 Karma
1 Solution

Motivator

After your first stats statement, your CHURN values are gone, so the second stats statement doesn't work. You want something similar to the second search, like so:

sourcetype="Customer_Churn" 
     | stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
     | eventstats sum(churned_count) as tot_churn_count
     | eval churned_percentage=churned_count*100/tot_churn_count
     | stats values(churned_percentage) as Churn_Percent by PLACEMENT
     | replace 0 with Rural in PLACEMENT
     | replace 1 with Urban in PLACEMENT

View solution in original post

New Member

sourcetype="Customer_Churn"
| eventstats count(eval(CHURN="1")) as churned_count by PLACEMENT
| eventstats count(eval(CHURN="1")) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats values(churned_percentage) by PLACEMENT

hope this will work

0 Karma

Motivator

After your first stats statement, your CHURN values are gone, so the second stats statement doesn't work. You want something similar to the second search, like so:

sourcetype="Customer_Churn" 
     | stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
     | eventstats sum(churned_count) as tot_churn_count
     | eval churned_percentage=churned_count*100/tot_churn_count
     | stats values(churned_percentage) as Churn_Percent by PLACEMENT
     | replace 0 with Rural in PLACEMENT
     | replace 1 with Urban in PLACEMENT

View solution in original post

Contributor

Hi @aweitzm yeah.!!! I got it individually.
but, when I am combining both of the queries it is showing empty results, can you please club them.
I have tried like this.

 sourcetype="Customer_Churn" 
      | stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
      | eventstats sum(churned_count) as tot_churn_count
      | eval churned_percentage=churned_count*100/tot_churn_count
      | stats sum(Total_Revenue) as rev by PLACEMENT
      | eventstats sum(rev) as fin_rev
      | eval REVENUE = rev*100/fin_rev
      | stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent  by PLACEMENT
      | replace 0 with Rural in PLACEMENT
      | replace 1 with Urban in PLACEMENT

Can you find any clue for that. I want them to give values when combined both queries.

0 Karma

Communicator

Hi Santhosh,

When you execute transformation commands you will end up loosing other columns its better you use event level stats comamnds till the final transformation to retain all the values.

Once you execute the first stats command you wille end up with only PLACEMENT, churned_count. Use below query,
sourcetype="Customer_Churn"
| eventstats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
| eventstats sum(churned_count) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(rev) as fin_rev
| eval REVENUE = rev*100/fin_rev
| stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT

Contributor

Here is the Query for my actual result. Thanks for helping me @Arun_N_007 and @aweitzman

sourcetype="Customer_Churn" 
| eventstats sum(eval(if(CHURN=="0",1,0))) as churned_count by PLACEMENT
| eventstats sum(eval(if(CHURN=="0",1,0))) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(Total_Revenue) as fin_rev
| eval REVENUE = rev*100/fin_rev
| stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT

Contributor

@Arun_N_007 sorry .I tried to delete the comment of mailID. it has deleted all the thread. I am so sorry.
and I have accepted the answer.

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!