Splunk Search

how to restrict where condition.?

Contributor

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.

0 Karma
1 Solution

Builder

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!!

View solution in original post

0 Karma

Splunk Employee
Splunk Employee

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.

0 Karma

Contributor

@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.?

0 Karma

Splunk Employee
Splunk Employee

Provide a sample of your data set if you can.

0 Karma

Contributor

@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

0 Karma

Builder

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!!

View solution in original post

0 Karma

Contributor

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

0 Karma

Builder

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!!

0 Karma

Contributor

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.

0 Karma

Builder

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

0 Karma

Contributor

No @vganjare .

0 Karma

Builder

Can you please share the output?

0 Karma

Contributor

ThanQ @vganjare finally you make me smile

0 Karma

Contributor

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!