Splunk Search

how to use calculated values in geostats to generate map chart

Contributor

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.

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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.

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Contributor

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

0 Karma

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

Contributor

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

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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.

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

Contributor

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

0 Karma

Contributor

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!