Splunk Search

stats and eventstats

SanthoshSreshta
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

aweitzman
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

umeshsnwl
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

aweitzman
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

SanthoshSreshta
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

Arun_N_007
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

SanthoshSreshta
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

SanthoshSreshta
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...