Splunk Search

Mapping by Zip code

ryankrieger
Loves-to-Learn

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
0 Karma

mydog8it
Builder

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
0 Karma

to4kawa
Ultra Champion

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?

0 Karma

ryankrieger
Loves-to-Learn

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

ryankrieger
Loves-to-Learn

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

0 Karma

to4kawa
Ultra Champion

Do you check Zipcode lat and lon are right?

0 Karma

cdhippen
Path Finder

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?

0 Karma

ryankrieger
Loves-to-Learn

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.

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...