Update GeoIp info in DMS database

Many of you have probably seen "IP_NOT_FOUND" entries in Engagement Analytics application.

Usually, it's a visits from internal IP address (like 192.*.*.* or 10.*.*.*) - your company network, monitoring systems, etc.
Such addresses cannot be resolved by MaxMind, so they're just displayed as "Not Found". Also, sometimes you might want to update GeoIP information manually (for example, if you know more about the particular IP than MaxMind does)


The simplest way to do it is to update the DMS database directly. This can be done in 2 easy steps.

Step 1: Update GeoIP cache
It contains cached values resolved by MaxMind(or any other, see  section in analytics config file) provider. Once you update it - all new visits will use updated values (note: there is also in-memory cache, so you might not see the effect immediately).

UPDATE [GeoIps]
SET    [CachedValue] = '
  
  ' + @AreaCode + '
  ' + @BusinessName + '
  ' + @City + '
  ' + @Country + '
  ' + @Dns + '
  ' + @IspName + '
  ' + @Latitude + '
  ' + @Longitude + '
  ' + @MetroCode + '
  ' + @PostalCode + '
  ' + @Url + '
  ',
    [Fetched] = GETDATE(),
       [Status] = 1
WHERE  [Ip] = @IpBinary

Step 2: Update existing data
Since DMS database is denormalized (for performance reasons), same values are stored in different tables and need to be updated as well.
In our case, we'll refresh tables "Locations" and "Visits".

/* Update visits */
UPDATE [Visits]
SET    [BusinessName] = @BusinessName,
       [City] = @City,
       [PostalCode] = @PostalCode,
       [MetroCode] = @MetroCode,
       [AreaCode] = @AreaCode,
       [Region] = @Region,
       [IspName] = @IspName,
       [Country] = @Country,
       [Latitude] = @Latitude,
       [Longitude] = @Longitude
WHERE  [Ip] = @IpBinary

/* Update locations*/
UPDATE [Locations]
SET    [BusinessName] = @BusinessName,
       [Country] = @Country
WHERE  [LocationId] IN (SELECT DISTINCT [LocationId]
                        FROM   Visits
                        WHERE  [Ip] = @IpBinary)

I hope you find it useful. Full version of the script can be downloaded here. Enjoy!