Splunk Search

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

dbcase
Motivator

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

somesoni2
Revered Legend

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

View solution in original post

0 Karma

somesoni2
Revered Legend

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

dbcase
Motivator

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...