Splunk Search

Getting incorrect values while using Eval and eventstats

Contributor

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="Churncsv"
| eval Churn = if(Churn="True.","1","0")
| eventstats sum(Churn) as total
churn
| eval prop= Churn100 / 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.

0 Karma
1 Solution

Builder

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

View solution in original post

0 Karma

Builder

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

View solution in original post

0 Karma

Contributor

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

0 Karma

Builder

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="Churncsv"
| eval Churn = if(Churn="True.","1","0")
| stats sum(Churn) as true
churn , count(Churn) as totalchurn by state
| eval prop= true
churn*100 / total_churn
| stats sum(prop) by state

Contributor

here is the query for getting count .
source="ChurnDATAlatlon.csv" host="LTCPU069-PC" sourcetype="Churncsv"
| 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.

0 Karma

Builder

try changing the stats to eventstats.

Contributor

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

0 Karma

Builder

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

Contributor

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

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

0 Karma

Builder

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

0 Karma

Contributor

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

thank you so much for the support.!!

0 Karma

Contributor

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

0 Karma

Builder

Glad that I could help!!

Thanks!!

0 Karma