How to edit my search to produce a map visualization by zip code?

dbcase — Mon, 23 Jan 2017 20:16:57 GMT

Hi,

I have this search

index=betadb|eval length=len(PREMISE_FK)|where length=5|stats count by PREMISE_FK|rename PREMISE_FK as zipcode|lookup zipcode_geo.csv Zipcode as zipcode OUTPUT Lat,Long|eval_geo=Lat+","+Long

Which generates a stats table that looks like this

zipcode count   Lat Long
99362   1   46.11   -118.3
96734   1   21.4    -157.73
95223   1   38.35   -120.2
95062   1   36.97   -121.98
95050   1   37.34   -121.95
94583   1   37.78   -121.99
94538   2   37.5    -121.96
94301   1   37.44   -122.15
94117   1   37.76   -122.44
94065   3   37.53   -122.24
94062   2   37.4    -122.29
94025   1   37.45   -122.17

The lookup table looks like this

Zipcode Lat Long
705 18.14   -66.26
610 18.28   -67.14
611 18.28   -66.79
612 18.45   -66.73
601 18.16   -66.72
631 18.19   -66.82
602 18.38   -67.18
603 18.43   -67.15
604 18.43   -67.15
605 18.43   -67.15
703 18.25   -66.1
704 17.96   -66.22
7675    40.98   -74.03
7677    41.02   -74.05
7885    40.89   -74.58
7981    40.82   -74.41
7999    40.82   -74.41
8888    40.62   -74.76
8889    40.6    -74.76
7095    40.55   -74.28
7481    40.99   -74.16
10451   40.84   -73.87
10452   40.84   -73.87
10453   40.84   -73.87
<snip>

But none of the map visualizations will work. What am I missing?

FYI, the data looks like this

1/23/17
1:26:14.000 PM  
99362,WA,TCA203,1
ACCOUNT_FK =    1 HARDWARE_MODEL =  TCA203 PREMISE_FK = 99362 eventtype =   external-referer    eventtype = visitor-type-referred field2 =  WA field3 = TCA203 host =   beta index =    betadb linecount =  1 source =  /var/nfs/SAT_SplunkLogs/db/beta/countTsByZipCodeResults.csv sourcetype =    csv splunk_server = idx5.icontrol.splunkcloud.com timestamp =   none unix_category =    all_hosts unix_group =  default
1/23/17
1:26:14.000 PM  
96734,HI,NGHUBA,1
ACCOUNT_FK =    1 HARDWARE_MODEL =  NGHUBA PREMISE_FK = 96734 eventtype =   external-referer    eventtype = visitor-type-referred field2 =  HI field3 = NGHUBA host =   beta index =    betadb linecount =  1 source =  /var/nfs/SAT_SplunkLogs/db/beta/countTsByZipCodeResults.csv sourcetype =    csv splunk_server = idx5.icontrol.splunkcloud.com timestamp =   none unix_category =    all_hosts unix_group =  default
1/23/17
1:26:14.000 PM  
95223,CA,TCA203,1
ACCOUNT_FK =    1 HARDWARE_MODEL =  TCA203 PREMISE_FK = 95223 eventtype =   external-referer    eventtype = visitor-type-referred field2 =  CA field3 = TCA203 host =   beta index =    betadb linecount =  1 source =  /var/nfs/SAT_SplunkLogs/db/beta/countTsByZipCodeResults.csv sourcetype =    csv splunk_server = idx5.icontrol.splunkcloud.com timestamp =   none unix_category =    all_hosts unix_group =  default

Re: How to edit my search to produce a map visualization by zip code?

somesoni2 — Mon, 23 Jan 2017 20:23:30 GMT

Give this a try

index=betadb|eval length=len(PREMISE_FK)|where length=5|stats count by PREMISE_FK|rename PREMISE_FK as zipcode|lookup zipcode_geo.csv Zipcode as zipcode OUTPUT Lat,Long| geostats latfield=Lat longfield=Long sum(count) as count by zipcode

Re: How to edit my search to produce a map visualization by zip code?

dbcase — Mon, 23 Jan 2017 21:30:31 GMT

Thanks Somesoni2, That worked with one minor tweak.... I added globallimit=0 to the geostats command

topic Re: How to edit my search to produce a map visualization by zip code? in Splunk Search

How to edit my search to produce a map visualization by zip code?

Re: How to edit my search to produce a map visualization by zip code?

Re: How to edit my search to produce a map visualization by zip code?