topic Re: Getting incorrect values while using Eval and eventstats in Splunk Search

Getting incorrect values while using Eval and eventstats

SanthoshSreshta — Mon, 28 Sep 2020 19:58:16 GMT

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.

Re: Getting incorrect values while using Eval and eventstats

vganjare — Fri, 15 May 2015 09:20:58 GMT

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

Re: Getting incorrect values while using Eval and eventstats

SanthoshSreshta — Fri, 15 May 2015 09:37:13 GMT

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

Re: Getting incorrect values while using Eval and eventstats

vganjare — Mon, 28 Sep 2020 19:54:33 GMT

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

Re: Getting incorrect values while using Eval and eventstats

SanthoshSreshta — Mon, 28 Sep 2020 19:58:22 GMT

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.

Re: Getting incorrect values while using Eval and eventstats

vganjare — Fri, 15 May 2015 10:03:06 GMT

try changing the stats to eventstats.

Re: Getting incorrect values while using Eval and eventstats

SanthoshSreshta — Fri, 15 May 2015 10:06:06 GMT

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

Re: Getting incorrect values while using Eval and eventstats

SanthoshSreshta — Fri, 15 May 2015 10:08:32 GMT

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

Re: Getting incorrect values while using Eval and eventstats

vganjare — Fri, 15 May 2015 10:08:38 GMT

Glad that I could help!!

Thanks!!

Re: Getting incorrect values while using Eval and eventstats

vganjare — Fri, 15 May 2015 10:14:24 GMT

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

Re: Getting incorrect values while using Eval and eventstats

SanthoshSreshta — Mon, 28 Sep 2020 19:58:24 GMT

@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

Re: Getting incorrect values while using Eval and eventstats

vganjare — Fri, 15 May 2015 10:35:20 GMT

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")?

Re: Getting incorrect values while using Eval and eventstats

SanthoshSreshta — Fri, 15 May 2015 10:57:38 GMT

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

thank you so much for the support.!!