how to restrict where condition.?

[SanthoshSreshta]

Hi All.

I have a scenario where, the where clause is used to filter and other side the same where clause should not effect the final stats command.
query is
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

i need for rural and urban. for now am only getting rural values.
please help me out. can anyone give me same logic using sub search. i am unaware of such things..

Thanks,
Santhosh.

[esix_splunk]

Eventstats is expensive, as it iterates through each event and writes the field back to the event. You might be able to consolidate this down to...

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

Not sure if that will work without seeing your data set.

[SanthoshSreshta]

@esix_splunk for the code you have sent it is showing Rural=100 and Urban=0.
so i tried of separating like as
sourcetype="Customer_Churn"

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

then it started showing Rural=19.304 and Urban=0. but i need Urban=80.906 😞

Any ideas.?

[esix_splunk]

Provide a sample of your data set if you can.

[SanthoshSreshta]

@esix_splunk , here it is

PLACEMENT       CHURN     customer#
------------------------------------------------------------
     0                        1                 1
     1                        1                 2
     1                        0                 3
     1                        1                 4

I want the proportion ratio.
ie:for
Rural: prop_ratio=(count(Customer#) where CHURN=0 and PLACEMENT=0) / count(customer#) where CHURN=0 and PLACEMENT=0 and 1
Urban: prop_ratio=(count(Customer#) where CHURN=0 and PLACEMENT=1) / count(customer#) where CHURN=0 and PLACEMENT=0 and 1

[vganjare · Accepted solution]

You can try using conditional stats. Try following query:
sourcetype="Customer_Churn"
| eventstats count(CHURN) by PLACEMENT
| eventstats count(eval(CHURN=="0")) as c0p
| eventstats count(CHURN)
| eventstats count(eval(CHURN=="0" AND PLACEMENT==0)) as c0p0
| eval p=c0p0/c0p*100
| stats values(p) by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT

Basically, try to provide the condition within the event stats command like ** | eventstats count(eval(CHURN=="0")) as c0p**

Thanks!!

[SanthoshSreshta]

@vganjare , it is showing rural and urban but of same values.
Rural=19.304
Urban=19.304.

[vganjare]

Do you have additional values in PLACEMENT apart from 0 and 1? If only two values are present, you can use

    | eval PLACEMENT = if(PLACEMENT =="0", "Rural", "Urban")

Also, your problem statement is not clear.

Thanks!!

[SanthoshSreshta]

Hi, vganjare by using this query I am able to get both values of rural and urban

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

but they are in table form. when converted to column they are not plotting. on y-axis percentage should come and two column values as urban and rural must come.

[vganjare]

can you try using the visualization options provided in splunk to check if any other visualization is coming or not?

[SanthoshSreshta]

No @vganjare .

[vganjare]

Can you please share the output?

[SanthoshSreshta]

ThanQ @vganjare finally you make me smile

[SanthoshSreshta]

NO @vganjare I don't have other values except 0 and 1. I have used then it is showing Rural=19.304 and Urban=0.

My problem is, I am not able to get the value for Urban.
I want Rural=19.304 and Urban=80.06.