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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...