Splunk Search

Getting incorrect values while using Eval and eventstats

SanthoshSreshta
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="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.

0 Karma
1 Solution

vganjare
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

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

0 Karma

SanthoshSreshta
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

vganjare
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="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
Contributor

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.

0 Karma

vganjare
Builder

try changing the stats to eventstats.

SanthoshSreshta
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

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

SanthoshSreshta
Contributor

@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

0 Karma

vganjare
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

SanthoshSreshta
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

SanthoshSreshta
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

vganjare
Builder

Glad that I could help!!

Thanks!!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...