You might be lucky enough that you have all your log reporting hosts properly resolving to fully qualified domain names (FQDN) (e.g. splunk.cal01.georgestarcher.com). If you are really lucky part of your fqdn is a location code (e.g. cal01 = San Francisco). This can be useful if the location code is in the hostname of your wifi gear logs. You can use an auto lookup against a location table to match wifi mac address activity to lat/lon based on the equipment’s site code in it’s hostname.
First, you need to create a location csv file for the lookup to use. In our example we will use the following gs-location-lookup.csv. As a disclaimer; I do not have anything located at Splunk HQ. It is just a public address to use to demonstrate this example. We will place this file in /$SPLUNK_HOME/etc/system/local/lookups. However you could place this sort of lookup in an app that you distribute to all your search heads. If you only know the address locations of your organization sites, just use Google Maps to find out the lat/lon for the address.
cal01,cal01.georgestarcher.com,Splunk HQ,250 Brannan Street,1st Floor,San Francisco,California,94107,United States,37.783031, -122.391049
Next, we define both the lookup table and the host field site code extraction in transforms.conf. We do make the assumption our site location is the component of the FQDN just before our domain name.
SOURCE_KEY = host
REGEX = (?P<hostSiteCode>[^\.]+)\.georgestarcher\.com$
filename = gs-location-lookup.csv
case_sensitive_match = false
Last, we add the automatic lookup in our props.conf to apply to any host that has a value ending in georgestarcher.com. You probably noticed that I made the lookup command output the fields all to start with host. This is because we might do other lookups against the site code. We will know specifically this location information is tied to the host name. Not a value for siteCode that might come up in our logged data that we also wish to lookup. After all, a syslog.cal01.georgestarcher.com might collect logs that have a site code in them like cal02. Now you can search for logs based on their site location.
REPORT-gs-extractSite = gs_site_code
LOOKUP-gs-siteLookup = siteLookup siteCode AS hostSiteCode OUTPUT siteCity AS hostSiteCity, siteCountry AS hostSiteCountry, siteFacility AS hostSiteFacility, siteDomainName AS hostSiteDomainName, siteAddress1 AS hostSiteAddress1, siteAddress2 AS hostSiteAddress2, siteRegion AS hostSiteRegion, siteLat AS hostSiteLat, siteLon AS hostSiteLon
Here is a bonus. If you wanted to map the events based on the host site location just add this geostats command to your searches:
| geostats latfield=hostSiteLat longfield=hostSiteLon count