Getting incorrect values while using Eval and eventstats

[SanthoshSreshta]

Hi,

I had count of some condition and aggregated data. using both I stored them in some variable using eval. then that variable is used for stats or geostats. but I am getting less value than the actual value.
below is the query I have used. any thing i have missed.?
** sourcetype="Churn_csv"
| eval Churn = if(Churn="True.","1","0")
| eventstats sum(Churn) as total_churn
| eval prop= Churn*100 / total_churn
| stats sum(prop) by state**

actually my aim is to get " count(churn=true)*100/count(churn) for each state " this data to be displayed in geostats chart.

Thanks in advance,
Santhosh.

[vganjare · Accepted solution]

Try chaning following query:

eventstats sum(Churn) as total_churn

As the Churn is classified as 1 and 0, the sum will give you the total for Churun==true. Try using count(Churn) for getting the total.

Thanks!!

[SanthoshSreshta]

@vganjare , when I am trying it individually in another search, it is showing count of all correctly , but when using it with above query it is not showing correct values. What would be the problem. I am very confused. 😞
example:
state name:AK
churn=true:3
total churn:52.
when we calculate it should get 5.7692 but here it is showing 0.090009
any ideas

Thanks.
Santhosh.

[vganjare]

Can you please provide the query used in another search? Here, I think, you are calculating the % churn by state.

Also, the above query can be written in below format:
sourcetype="Churn_csv"
| eval Churn = if(Churn="True.","1","0")
| stats sum(Churn) as true_churn , count(Churn) as total_churn by state
| eval prop= true_churn*100 / total_churn
| stats sum(prop) by state

[SanthoshSreshta]

here is the query for getting count .
source="Churn_DATA_lat_lon.csv" host="LTCPU069-PC" sourcetype="Churn_csv"
| eval Churn = if(Churn="True.",1,0)
| stats sum(Churn) as total_churn, count(Churn) by state

based on your proposed query, it is now showing perfect values, but not able to add geostats.
what would be the problem.

[vganjare]

try changing the stats to eventstats.

[SanthoshSreshta]

@vganjare I have changed stats to eventstats, but didnt kept geostats, i have used stats instead, it shown me different values. then after keeping geostats, it shown the perfect values, can you please explain me the actual reason behind it.?

[vganjare]

I suspect, the geostats command tries to add/extract the information for the location (for mapping purpose). I dont expect any differences in the results computed by stats vs eventstats. Most likely, it should not happen. You can read the details of geostats at http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchReference/Geostats

Thanks!!

[SanthoshSreshta]

@vganjare , one more inconsistency again 😞
it is showing some more values with correct values.
source="Churn_DATA_lat_lon.csv" host="LTCPU069-PC" sourcetype="Churn_csv"
| eval Churn = if(Churn="True.","1","0")
| eventstats sum(Churn) as true_churn , count(Churn) as total_churn by state
| eval prop= true_churn*100 / total_churn
| geostats sum(prop) by state globallimit=0

getting different values in statistics, in geostats chart only incorrect value.
please help

[vganjare]

What are the different values of Churn? Is it True or True. ? Can you try stats values(Churn) before | eval Churn = if(Churn="True.","1","0")?

[SanthoshSreshta]

thank god!!
finally got it on my own 😉
i just changed sum(prop) to values(prop) 🙂

thank you so much for the support.!!

[SanthoshSreshta]

yes, perfect.!!!
splunk is very complicated !!
sorry for making you so pinpointed and make you to answer silly Q?'s
I am very very new and purely self learning guy from my firm.( my domain is SAP BO ) actuallly 🙂
anyway thanks a lot.!!!

[vganjare]

Glad that I could help!!

Thanks!!