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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...