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.
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!!
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.
@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.?
Provide a sample of your data set if you can.
@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
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!!
@vganjare , it is showing rural and urban but of same values.
Rural=19.304
Urban=19.304.
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!!
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.
can you try using the visualization options provided in splunk to check if any other visualization is coming or not?
No @vganjare .
Can you please share the output?
ThanQ @vganjare finally you make me smile
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.