how to use calculated values in geostats to generate map chart

[SanthoshSreshta]

Hi All,

How to generate Geostats chart showing some aggregated data.
columns in my CSV file named: test_csv
1. Churn : true, false
2. state : UK, SD, GD, HF.
3. latitude and longitude value for each state

I need to show a map containing, proportion of churn. formula for prop of churn=(count(churn=true)/total churn)*100 by each state.

Thanks in advance,
Santhosh.

[martin_mueller · Accepted solution]

Something along these lines maybe:

  search pipeline generating events with lat and lon fields and churn as 1 or 0 from your CSV
| eventstats sum(churn) as total_churn by state
| eval prop_of_churn = churn * 100 / total_churn
| geostats sum(prop_of_churn) by state

[martin_mueller]

You're still not telling me how total_churn should be calculated, so I'll stop guessing for now.

It'd also help if you'd post your data somewhere so people can actually test their suggestions.

[SanthoshSreshta]

@martin_mueller,
count(churn) gives total_churn. I am able to get that martin.

[martin_mueller]

Well, how should total_churn be calculated? I can't look inside your head.

[SanthoshSreshta]

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

[martin_mueller]

Try replacing sum(churn) with count in the eventstats if that's how you want to calculate total_churn.

[SanthoshSreshta]

@martin_mueller, it is showing more less values than the previous

[martin_mueller]

  search pipeline generating events with lat and lon fields and churn as TRUE or FALSE from your CSV
| eval churn = if(churn="TRUE", 1, 0)
| eventstats sum(churn) as total_churn by state
| eval prop_of_churn = churn * 100 / total_churn
| geostats sum(prop_of_churn) by state

[SanthoshSreshta]

Thank you so much your patience and giving answer to my every comment.
Thank you once again.

[SanthoshSreshta]

Hi @martin_mueller , the values I am getting are not correct .
The values are getting less than the actual values should get.
for AK state the churn (true)=3 and total churn=52 then prop =5.7692. but I am getting 0.61117
there is vast difference. any ideas please..?

[martin_mueller]

A few comments back you can see how to convert the string into numbers from within the search, so no need to do risky things with your source.

[SanthoshSreshta]

@martin_mueller how to get the count then for value TRUE and both.?

[martin_mueller]

If you're creating the CSV yourself, why not. Numbers are much easier to work with than strings.

sum(churn) is the same for 1 and 0 as count(churn=true) is for true and false.

[SanthoshSreshta]

it is very risky, but if there is no chance of doing such things using splunk, I will request our prior team, so please help me out. I am the only person doing splunk. purely depend on net. 😞

[martin_mueller]

Add this then: ... | eval churn = if(churn="TRUE", 1, 0) | ...

[SanthoshSreshta]

@martin_mueller should I need to change the source csv file's data column Churn with TRUE as 1 and FALSE as 0 ...???
and how about the count ?

[SanthoshSreshta]

Hi @martin_mueller i need count of churn whose value is TRUE. which should be divided by count of churn whose value is either TRUE or FALSE and the whole result should be multiplied with 100.
ex: (count(churn=false)/count(churn))*100.
:( pls help.