topic Re: Mapping by Zip code in Splunk Search

Mapping by Zip code

ryankrieger — Fri, 24 Jan 2020 16:40:08 GMT

When I am trying to map by Zipcode I get the stats table to genereate but when switching to geostats it takes 4 results from the stats table and makes it 39. Seems to be grouping by geobin instead of zip

Any ideas why this is happening?

index="indexA" servco_name="store*" servtype_id="CFAIL"
| rename zip_code as Zipcode
| lookup zip_code  Zipcode OUTPUT Lat Long
| geostats latfield=Lat longfield=Long  Sum(Count)

index="IndexA" servco_name="Store*" servtype_id="CFAIL"
| rename zip_code as Zipcode
| lookup zip_code  Zipcode OUTPUT Lat Long
| Stats Sum(Count) by Zipcode

Re: Mapping by Zip code

cdhippen — Fri, 24 Jan 2020 20:32:13 GMT

Is this two searches or one? I also see that you're doing a sum of a count? Could you give some sample data and your desired output?

Re: Mapping by Zip code

to4kawa — Fri, 24 Jan 2020 20:35:29 GMT

UPDATE:

index="indexA" servco_name="store*" servtype_id="CFAIL"
 | rename zip_code as Zipcode
 | stats Sum(Count)  as Count by Zipcode
 | lookup zip_code  Zipcode OUTPUT Lat Long
 | geostats latfield=Lat longfield=Long  values(Count) as Count  values(Zipcode) as Zipcode

how about this?

Re: Mapping by Zip code

mydog8it — Fri, 24 Jan 2020 20:38:45 GMT

I think you might just be missing a "BY" clause...

 index="indexA" servco_name="store*" servtype_id="CFAIL"
 | rename zip_code as Zipcode
 | lookup zip_code  Zipcode OUTPUT Lat Long
 | geostats latfield=Lat longfield=Long  Sum(Count) BY Zipcode

Re: Mapping by Zip code

ryankrieger — Fri, 24 Jan 2020 20:49:22 GMT

When I add that by Zipcode clause I still get more stats than events.

796 events, 344 stats when count by Zipcode but it create 1,427 map points using geostats

Re: Mapping by Zip code

ryankrieger — Fri, 24 Jan 2020 20:56:36 GMT

this is 2 searches The first one gives way extra results when mapping, the 2nd one gives the correct rollup.

I can't give the raw data due to privacy but in the data there is a field called zip_code, I use a lookup to get the lat and long associated with that zip and then want to map the events by zip.

Re: Mapping by Zip code

to4kawa — Fri, 24 Jan 2020 21:07:22 GMT

is that wrong?
Do you want to count by each Zipcode?

Re: Mapping by Zip code

ryankrieger — Wed, 30 Sep 2020 03:48:56 GMT

I would like to see number of events per Zipcode
This is how it show up with the geostats most of the geobins contains multiple zips.

geobin latitude longitude Zipcode
bin_id_zl_0_y_4_x_1 20.50500 -156.95500 96701
96740

bin_id_zl_0_y_5_x_1 35.54629 -108.64629 38637
50322

50613

Re: Mapping by Zip code

to4kawa — Fri, 24 Jan 2020 21:45:26 GMT

Do you check Zipcode lat and lon are right?