Splunk Search
Highlighted

two where clause?

Contributor

Hi All.

I want to calculate churned customers from two placements
(churn=0 means churned,1 as unchurned) and placement ( 0 means rural and 1 as urban).
The query i have used.
sourcetype="Customer_Churn"

| eventstats count(CHURN) by PLACEMENT
| where CHURN="0"
| eventstats count(CHURN) as c0p
| eventstats count(CHURN)
| where CHURN="0" AND PLACEMENT=0
| eventstats count(CHURN) as c0p0
| eval p=c0p0/c0p*100
| stats values(p) by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT

It is showing me only placement =0 ie. rural (rural=19.304), i want both to be displayed.
rural=19.304 and urban=80.06

Thanks a Million,
Santhosh.

0 Karma
Highlighted

Re: two where clause?

Contributor

Hi Santhosh,

Try using stats with eval together as below:

sourcetype="Customer_Churn" |stats count(eval(CHURN==0)) AS totalChurn  count(eval(CHURN==0 AND PLATECEMENT==0)) AS ruralChurn   count(eval(CHURN==0 AND PLATECEMENT==1)) AS urbanChurn by sourcetype|eval ruralChurnPercentage = (ruralChurn*100)/totalChurn  |eval urbanChurnPercentage = (urbanChurn*100)/totalChurn|table  ruralChurnPercentage urbanChurnPercentage 

Thanks,
Sanjay

View solution in original post

0 Karma
Highlighted

Re: two where clause?

Contributor

Hi @Sanjay.shrestha ThankQ so much.!!!
but one thing, as it is in table command, i am getting x-axis as ruralchurnpercentage and y-axis as urbanchurnpercentage.
but i want y-axis as percentage from 1 to 100 and two columns as ruralchurnpercentage and urbanchurnpercentage.

Thanks & Regards,
Santhosh.

0 Karma