MaxMinds GeoIP for local MySQL

Use this forum if you have problems with a hMailServer script, such as hMailServer WebAdmin or code in an event handler.
Post Reply
palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-23 03:36

Downloads, unzips and loads MaxMinds GeoLite2 csv data into MySQL for local use.

Powershell:

Code: Select all

<#

SYNOPSIS
	Install MaxMindas geoip database on MySQL

DESCRIPTION
	Download and unzip MaxMinds cvs geoip data, then populate MySQL with csv data

INSTRUCTIONS
	REQUIRED: PSipcalc.ps1 : https://www.powershelladmin.com/wiki/Calculate_and_enumerate_subnets_with_PSipcalc : copy into same folder as this powershell script.
	Create or obtain credentials for database on which to install table `geo_ip`.
	Use SQL below to create columns on `geo_ip`.
	Update user variables.
	Run once per month via task scheduler.

SPECIAL INSTRUCTIONS
	Loading the database takes over one hour. Set your scheduled task for after midnight.
	
SQL
	CREATE TABLE IF NOT EXISTS `geo_ip` (
	  `minip` varchar(15) NOT NULL,
	  `maxip` varchar(15) NOT NULL,
	  `geoname_id` int(7) NOT NULL,
	  `countrycode` varchar(2) NOT NULL,
	  `countryname` varchar(48) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	COMMIT;

SAMPLE QUERY
	SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('182.253.228.22') >= INET_ATON(minip) AND INET_ATON('182.253.228.22') <= INET_ATON(maxip)

#>

### User Variables ###
$GeoIPDir = 'C:\scripts\geolite2' 	# Location of files. No trailing "\" please
$MySQLAdminUserName = 'geoip'
$MySQLAdminPassword = 'supersecretpassword'
$MySQLDatabase = 'geoip'
$MySQLHost = 'localhost'
### End User Variables ###

Function MySQLQuery($Query) {
	$DBErrorLog = '$GeoIPDir\DBError.log'
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database=" + $MySQLDatabase
	Try {
	  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
	  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
	  $Connection.ConnectionString = $ConnectionString
	  $Connection.Open()
	  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
	  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	  $DataSet = New-Object System.Data.DataSet
	  $RecordCount = $dataAdapter.Fill($dataSet, "data")
	  $DataSet.Tables[0]
	  }
	Catch {
	  Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to run query : $query `n$Error[0]" | out-file $DBErrorLog -append
	}
	Finally {
	  $Connection.Close()
	}
}

#	Delete old files, download GeoLite2-Country-CSV zip file, uncompress, rename folder
Remove-Item -Recurse -Force $GeoIPDir\"GeoLite2-Country-CSV"
Remove-Item -Force -Path $GeoIPDir\"GeoLite2-Country-CSV.zip"
$url = "https://geolite.maxmind.com/download/geoip/database/GeoLite2-Country-CSV.zip"
$output = "$GeoIPDir\GeoLite2-Country-CSV.zip"
$MMErrorLog = "$GeoIPDir\error.log"
Start-BitsTransfer -Source $url -Destination $output
Expand-Archive $output -DestinationPath $GeoIPDir
$MMFolder = Get-ChildItem $GeoIPDir | Where-Object {$_.PSIsContainer -eq $true} | Sort-Object
If ($MMFolder -match 'GeoLite2-Country-CSV_[0-9]{8}'){
	Rename-Item -Path $GeoIPDir\$MMFolder $GeoIPDir\"GeoLite2-Country-CSV"
} Else {
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : `n$Error[0]" | Out-File $MMErrorLog
}

$PSIPCalc = '$GeoIPDir\PSipcalc.ps1'
$CountryBlocksIPV4 = '$GeoIPDir\GeoLite2-Country-CSV\GeoLite2-Country-Blocks-IPv4.csv'
$GeoIPObjects = import-csv -Path $CountryBlocksIPV4 -Delimiter "," -Header network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider
$GeoIPObjects | foreach-object {
	$Network = $_.network
	$GeoNameID = $_.geoname_id
	If ($GeoNameID -match '[0-9]{1,12}'){
		& $CIDRParse -NetworkAddress $Network | where-object {
			$MinIP = $_.NetworkAddress
			IF([string]::IsNullOrWhiteSpace($_.Broadcast)){
				$MaxIP = $_.NetworkAddress
			} Else {
				$MaxIP = $_.Broadcast
			}
		}
		$Query = "INSERT INTO geo_ip (minip,maxip,geoname_id) VALUES ('$MinIP','$MaxIP','$GeoNameID')"
		MySQLQuery($Query)
	}
}

$CountryLocations = "$GeoIPDir\GeoLite2-Country-CSV\GeoLite2-Country-Locations-en.csv"
$GeoIPNameObjects = import-csv -Path $CountryLocations -Delimiter "," -Header geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name,is_in_european_union
$GeoIPNameObjects | foreach-object {
	$GeoNameID = $_.geoname_id
	$CountryCode = $_.country_iso_code
	$CountryName = $_.country_name
	If ($GeoNameID -match '[0-9]{1,12}'){
		$Query = "UPDATE geo_ip SET countrycode='$CountryCode', countryname='$CountryName' WHERE geoname_id='$GeoNameID'"
		MySQLQuery($Query)
	}
}

EventHandlers.vbs:

Code: Select all

'	GeoIP LOCAL Country CODE Lookup
Function GeoIPCC(sIPAddress)
	dim cn, rs, connectionString, m_CountryName, m_CountryCode
	set cn = CreateObject("ADODB.Connection")
	set rs = CreateObject("ADODB.Recordset")
	connectionString = "Driver={MariaDB ODBC 3.0 Driver}; Server=localhost; Database=geoip; User=geoip; Password=supersecretpassword;"
	cn.Open connectionString
	rs.open "SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('" & sIPAddress & "') >= INET_ATON(minip) AND INET_ATON('" & sIPAddress & "') <= INET_ATON(maxip)", cn, 3
	rs.MoveFirst
	while not rs.eof
		m_CountryCode = rs.Fields(0)
		m_CountryName = rs.Fields(1)
		rs.MoveNext
	wend
	cn.close
	GeoIPCC = m_CountryCode
End Function

'	GeoIP LOCAL Country NAME Lookup
Function GeoIPCN(sIPAddress)
	dim cn, rs, connectionString, m_CountryName, m_CountryCode
	set cn = CreateObject("ADODB.Connection")
	set rs = CreateObject("ADODB.Recordset")
	connectionString = "Driver={MariaDB ODBC 3.0 Driver}; Server=localhost; Database=geoip; User=geoip; Password=supersecretpassword;"
	cn.Open connectionString
	rs.open "SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('" & sIPAddress & "') >= INET_ATON(minip) AND INET_ATON('" & sIPAddress & "') <= INET_ATON(maxip)", cn, 3
	rs.MoveFirst
	while not rs.eof
		m_CountryCode = rs.Fields(0)
		m_CountryName = rs.Fields(1)
		rs.MoveNext
	wend
	cn.close
	GeoIPCN = m_CountryName
End Function

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-23 04:03

Some pointers with error handling the vbs would be great.

User avatar
SorenR
Senior user
Senior user
Posts: 3572
Joined: 2006-08-21 15:38
Location: Denmark

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2019-10-23 10:18

Brainstorming ... Not fully testet ... The trick is how to use "ByVal" and "ByRef". :mrgreen:

ByVal = Parse a copy of the variable, does not change content of original variable. (Default)
ByRef = Parse reference to variable, will change content of the original variable.

Save code to "geoiptest.vbs" and try it from command prompt.

Code: Select all

'
'   GeoIP LOCAL Country CODE Lookup
'
Sub GeoIPLookup(ByVal sIPAddress, ByRef m_CountryCode, ByRef m_CountryName)
    Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
    oConn.Open "Driver={MariaDB ODBC 3.0 Driver}; Server=localhost; Database=geoip; User=geoip; Password=supersecretpassword;"

    If oConn.State <> 1 Then
'       EventLog.Write( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        WScript.Echo( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        m_CountryCode = "XX"
        m_CountryName = "ERROR"
        Exit Sub
    End If

    m_CountryCode = "NF"
    m_CountryName = "NOT FOUND"

    Set oRecord = oConn.Execute("SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('" & sIPAddress & "') >= INET_ATON(minip) AND INET_ATON('" & sIPAddress & "') <= INET_ATON(maxip)")
    Do Until oRecord.EOF
        m_CountryCode = oRecord("countrycode")
        m_CountryName = oRecord("countryname")
        oRecord.MoveNext
    Loop
    oConn.Close
    Set oRecord = Nothing
End Sub

'
'   Using GeoIPLookup
'
Dim sIPAddress, m_CountryCode, m_CountryName
Call GeoIPLookup("127.0.0.1", m_CountryCode, m_CountryName)

WScript.Echo( "m_CountryCode = " & m_CountryCode )
WScript.Echo( "m_CountryName = " & m_CountryName )
WScript.Quit 0
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-23 12:39

SorenR wrote:
2019-10-23 10:18
Brainstorming ... Not fully testet ... The trick is how to use "ByVal" and "ByRef". :mrgreen:

ByVal = Parse a copy of the variable, does not change content of original variable. (Default)
ByRef = Parse reference to variable, will change content of the original variable.

Save code to "geoiptest.vbs" and try it from command prompt.
Awesome, man! Thanks! The test works like a charm.

Only one issue: NF = Norfolk Island :mrgreen:

User avatar
SorenR
Senior user
Senior user
Posts: 3572
Joined: 2006-08-21 15:38
Location: Denmark

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2019-10-23 17:35

palinka wrote:
2019-10-23 12:39
Only one issue: NF = Norfolk Island :mrgreen:
Like I wrote ... "Not fully tested" :mrgreen:
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-24 02:10

Update - no longer need 2 files and some other changes. Next task is to figure out how to update the database incrementally because it takes forever to load it in full.

GeoLite2MySQL.ps1

Code: Select all

<#
.SYNOPSIS
	Install MaxMindas geoip database on MySQL

.DESCRIPTION
	Download and unzip MaxMinds cvs geoip data, then populate MySQL with csv data

.FUNCTIONALITY
	1) If geoip table does not exist, it gets created
	2) Deletes all data from table if exists (required when updating database)
	3) Downloads MaxMinds geolite2 cvs data as zip file, uncompresses it, then renames the folder
	4) Reads IPv4 cvs data, then calculates the lowest and highest IP from each network in the database
	5) Inserts lowest and highest IP calculated above and geoname_id from IPv4 cvs file
	6) Reads geo-name cvs file and updates each record with country code and country name based on the geoname_id

.NOTES
	Run once per month or once per 3 months via task scheduler
	Loading the database takes over one hour. Set your scheduled task for after midnight
	
.EXAMPLE
	Example query to return countrycode and countryname from database:
	
	SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('182.253.228.22') >= INET_ATON(minip) AND INET_ATON('182.253.228.22') <= INET_ATON(maxip)

#>

### User Variables 
$GeoIPDir = 'C:\scripts\geolite2' 	# Location of files. No trailing "\" please
$MySQLAdminUserName = 'geoip'
$MySQLAdminPassword = 'supersecretpassword'
$MySQLDatabase = 'geoip'
$MySQLHost = 'localhost'
### End User Variables 

# https://www.quadrotech-it.com/blog/querying-mysql-from-powershell/
Function MySQLQuery($Query) {
	$DBErrorLog = '$GeoIPDir\DBError.log'
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database=" + $MySQLDatabase
	Try {
	  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
	  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
	  $Connection.ConnectionString = $ConnectionString
	  $Connection.Open()
	  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
	  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	  $DataSet = New-Object System.Data.DataSet
	  $RecordCount = $dataAdapter.Fill($dataSet, "data")
	  $DataSet.Tables[0]
	  }
	Catch {
	  Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to run query : $query `n$Error[0]" | out-file $DBErrorLog -append
	}
	Finally {
	  $Connection.Close()
	}
}

# Function from: https://www.ryandrane.com/2016/05/getting-ip-network-information-powershell/
Function Get-IPv4NetworkInfo
{
    Param
    (
        [Parameter(ParameterSetName="IPandMask",Mandatory=$true)] 
        [ValidateScript({$_ -match [ipaddress]$_})] 
        [System.String]$IPAddress,

        [Parameter(ParameterSetName="IPandMask",Mandatory=$true)] 
        [ValidateScript({$_ -match [ipaddress]$_})] 
        [System.String]$SubnetMask,

        [Parameter(ParameterSetName="CIDR",Mandatory=$true)] 
        [ValidateScript({$_ -match '^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)/([0-9]|[0-2][0-9]|3[0-2])$'})]
        [System.String]$CIDRAddress,

        [Switch]$IncludeIPRange
    )

    # If @CIDRAddress is set
    if($CIDRAddress)
    {
         # Separate our IP address, from subnet bit count
        $IPAddress, [int32]$MaskBits =  $CIDRAddress.Split('/')

        # Create array to hold our output mask
        $CIDRMask = @()

        # For loop to run through each octet,
        for($j = 0; $j -lt 4; $j++)
        {
            # If there are 8 or more bits left
            if($MaskBits -gt 7)
            {
                # Add 255 to mask array, and subtract 8 bits 
                $CIDRMask += [byte]255
                $MaskBits -= 8
            }
            else
            {
                # bits are less than 8, calculate octet bits and
                # zero out our $MaskBits variable.
                $CIDRMask += [byte]255 -shl (8 - $MaskBits)
                $MaskBits = 0
            }
        }

        # Assign our newly created mask to the SubnetMask variable
        $SubnetMask = $CIDRMask -join '.'
    }

    # Get Arrays of [Byte] objects, one for each octet in our IP and Mask
    $IPAddressBytes = ([ipaddress]::Parse($IPAddress)).GetAddressBytes()
    $SubnetMaskBytes = ([ipaddress]::Parse($SubnetMask)).GetAddressBytes()

    # Declare empty arrays to hold output
    $NetworkAddressBytes   = @()
    $BroadcastAddressBytes = @()
    $WildcardMaskBytes     = @()

    # Determine Broadcast / Network Addresses, as well as Wildcard Mask
    for($i = 0; $i -lt 4; $i++)
    {
        # Compare each Octet in the host IP to the Mask using bitwise
        # to obtain our Network Address
        $NetworkAddressBytes +=  $IPAddressBytes[$i] -band $SubnetMaskBytes[$i]

        # Compare each Octet in the subnet mask to 255 to get our wildcard mask
        $WildcardMaskBytes +=  $SubnetMaskBytes[$i] -bxor 255

        # Compare each octet in network address to wildcard mask to get broadcast.
        $BroadcastAddressBytes += $NetworkAddressBytes[$i] -bxor $WildcardMaskBytes[$i] 
    }

    # Create variables to hold our NetworkAddress, WildcardMask, BroadcastAddress
    $NetworkAddress   = $NetworkAddressBytes -join '.'
    $BroadcastAddress = $BroadcastAddressBytes -join '.'
    $WildcardMask     = $WildcardMaskBytes -join '.'

    # Now that we have our Network, Widcard, and broadcast information, 
    # We need to reverse the byte order in our Network and Broadcast addresses
    [array]::Reverse($NetworkAddressBytes)
    [array]::Reverse($BroadcastAddressBytes)

    # We also need to reverse the array of our IP address in order to get its
    # integer representation
    [array]::Reverse($IPAddressBytes)

    # Next we convert them both to 32-bit integers
    $NetworkAddressInt   = [System.BitConverter]::ToUInt32($NetworkAddressBytes,0)
    $BroadcastAddressInt = [System.BitConverter]::ToUInt32($BroadcastAddressBytes,0)
    $IPAddressInt        = [System.BitConverter]::ToUInt32($IPAddressBytes,0)

    #Calculate the number of hosts in our subnet, subtracting one to account for network address.
    $NumberOfHosts = ($BroadcastAddressInt - $NetworkAddressInt) - 1

    # Declare an empty array to hold our range of usable IPs.
    $IPRange = @()

    # If -IncludeIPRange specified, calculate it
    if ($IncludeIPRange)
    {
        # Now run through our IP range and figure out the IP address for each.
        For ($j = 1; $j -le $NumberOfHosts; $j++)
        {
            # Increment Network Address by our counter variable, then convert back
            # lto an IP address and extract as string, add to IPRange output array.
            $IPRange +=[ipaddress]([convert]::ToDouble($NetworkAddressInt + $j)) | Select-Object -ExpandProperty IPAddressToString
        }
    }

    # Create our output object
    $obj = New-Object -TypeName psobject

    # Add our properties to it
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "IPAddress"           -Value $IPAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "SubnetMask"          -Value $SubnetMask
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "NetworkAddress"      -Value $NetworkAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "BroadcastAddress"    -Value $BroadcastAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "WildcardMask"        -Value $WildcardMask
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "NumberOfHostIPs"     -Value $NumberOfHosts
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "IPRange"             -Value $IPRange

    # Return the object
    return $obj
}

#	Delete old files if exist
Remove-Item -Recurse -Force $GeoIPDir\"GeoLite2-Country-CSV"
Remove-Item -Force -Path $GeoIPDir\"GeoLite2-Country-CSV.zip"

#	Download latest GeoLite2 data
$url = "https://geolite.maxmind.com/download/geoip/database/GeoLite2-Country-CSV.zip"
$output = "$GeoIPDir\GeoLite2-Country-CSV.zip"
Start-BitsTransfer -Source $url -Destination $output
Expand-Archive $output -DestinationPath $GeoIPDir

#	Rename folder so script can find it
$MMErrorLog = "$GeoIPDir\error.log"
$MMFolder = Get-ChildItem $GeoIPDir | Where-Object {$_.PSIsContainer -eq $true} | Sort-Object
If ($MMFolder -match 'GeoLite2-Country-CSV_[0-9]{8}'){
	Rename-Item -Path $GeoIPDir\$MMFolder $GeoIPDir\"GeoLite2-Country-CSV"
} Else {
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : `n$Error[0]" | Out-File $MMErrorLog
}

#	Create table if it doesn't exist
$GeoIPTable = "geo_ip"
$Query = "
	CREATE TABLE IF NOT EXISTS $GeoIPTable (
	  minip varchar(15) NOT NULL,
	  maxip varchar(15) NOT NULL,
	  geoname_id int(7) NOT NULL,
	  countrycode varchar(2) NOT NULL,
	  countryname varchar(48) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	COMMIT;
	"
MySQLQuery($Query)

#	Delete all data on table (required when updating data)
$Query = "DELETE FROM $GeoIPTable"
MySQLQuery($Query)

#	Read IPv4 cvs file, convert CIDR network address to lowest and highest IPs in range, then insert into database
$CountryBlocksIPV4 = "$GeoIPDir\GeoLite2-Country-CSV\GeoLite2-Country-Blocks-IPv4-test.csv"
$GeoIPObjects = import-csv -Path $CountryBlocksIPV4 -Delimiter "," -Header network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider
$GeoIPObjects | foreach-object {
	$Network = $_.network
	$GeoNameID = $_.geoname_id
	If ($GeoNameID -match '[0-9]{1,12}'){
		Get-IPv4NetworkInfo -CIDRAddress $Network | ForEach-Object {
			$MinIP = $_.NetworkAddress
			$MaxIP = $_.BroadcastAddress
		}
		$Query = "INSERT INTO $GeoIPTable (minip,maxip,geoname_id) VALUES ('$MinIP','$MaxIP','$GeoNameID')"
		MySQLQuery($Query)
	}
}

#	Read country info cvs and insert into database
$CountryLocations = "$GeoIPDir\GeoLite2-Country-CSV\GeoLite2-Country-Locations-en.csv"
$GeoIPNameObjects = import-csv -Path $CountryLocations -Delimiter "," -Header geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name,is_in_european_union
$GeoIPNameObjects | foreach-object {
	$GeoNameID = $_.geoname_id
	$CountryCode = $_.country_iso_code
	$CountryName = $_.country_name
	If ($GeoNameID -match '[0-9]{1,12}'){
		$Query = "UPDATE $GeoIPTable SET countrycode='$CountryCode', countryname='$CountryName' WHERE geoname_id='$GeoNameID'"
		MySQLQuery($Query)
	}
}

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-24 20:17

Revamped for incremental updates. That's pretty necessary since it takes my not-so-super hardware over 1 hour to fill the database. Incremental updates take a couple of minutes.

Future updates/bug fixes will be on GitHub: https://github.com/palinkas-jo-reggelt/GeoLite2MySQL

Features:
* You only fill in a few user variables - completely automated
* Script will create table
* Script will download files
* Automatically deletes old files and renames new ones as necessary
* Compares old to new for incremental table fill
* Run via scheduled task every Wednesday (MaxMinds releases data updates every Tuesday)

Sample query:

Code: Select all

SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('182.253.228.22') >= INET_ATON(minip) AND INET_ATON('182.253.228.22') <= INET_ATON(maxip)
EventHandlers.vbs subroutine (Thanks, Soren!)

Code: Select all

Sub GeoIPLookup(ByVal sIPAddress, ByRef m_CountryCode, ByRef m_CountryName)
    Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
    oConn.Open "Driver={MariaDB ODBC 3.0 Driver}; Server=localhost; Database=geoip; User=geoip; Password=supersecretpassword;"

    If oConn.State <> 1 Then
'       EventLog.Write( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        WScript.Echo( "Sub GeoIPLookup - ERROR: Could not connect to database" )
        m_CountryCode = "XX"
        m_CountryName = "ERROR"
        Exit Sub
    End If

    m_CountryCode = "NX"
    m_CountryName = "NOT FOUND"

    Set oRecord = oConn.Execute("SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('" & sIPAddress & "') >= INET_ATON(minip) AND INET_ATON('" & sIPAddress & "') <= INET_ATON(maxip)")
    Do Until oRecord.EOF
        m_CountryCode = oRecord("countrycode")
        m_CountryName = oRecord("countryname")
        oRecord.MoveNext
    Loop
    oConn.Close
    Set oRecord = Nothing
End Sub
Call subroutine:

Code: Select all

	Dim m_CountryCode, m_CountryName
	Call GeoIPLookup(oClient.IPAddress, m_CountryCode, m_CountryName)

GeoLite2MySQL.ps1

Code: Select all

<#

.SYNOPSIS
	Install MaxMindas geoip database on MySQL

.DESCRIPTION
	Download and unzip MaxMinds cvs geoip data, then populate MySQL with csv data

.FUNCTIONALITY
	1) If geoip table does not exist, it gets created
	2) Deletes old files, renames previously "new" "old" in order to compare
	3) Downloads MaxMinds geolite2 cvs data as zip file, uncompresses it, then renames the folder
	4) Compares new and old data for incremental changes
	5) Reads IPv4 cvs data, then calculates the lowest and highest IP from each network in the database
	6) Deletes obsolete records
	7) Inserts lowest and highest IP in range and geoname_id from IPv4 cvs file
	8) Reads geo-name cvs file and updates each record with country code and country name based on the geoname_id
	9) Includes various error checking to keep from blowing up a working database on error

.NOTES
	Run every Wednesday via task scheduler (MaxMinds releases updates on Tuesdays)
	Initial loading of the database takes over one hour - subsequent updates are incremental, so they only take a few minutes
	
.EXAMPLE
	Example query to return countrycode and countryname from database:
	
	SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('182.253.228.22') >= INET_ATON(minip) AND INET_ATON('182.253.228.22') <= INET_ATON(maxip)

#>

### User Variables ###
$GeoIPDir = 'C:\scripts\geolite2' 	# Location of files. No trailing "\" please. Please make sure folder exists.
$MySQLAdminUserName = 'geoip'
$MySQLAdminPassword = 'supersecretpassword'
$MySQLDatabase = 'geoip'
$MySQLHost = 'localhost'
### End User Variables ###

# https://www.quadrotech-it.com/blog/querying-mysql-from-powershell/
Function MySQLQuery($Query) {
	$DBErrorLog = '$GeoIPDir\DBError.log'
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database=" + $MySQLDatabase
	Try {
	  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
	  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
	  $Connection.ConnectionString = $ConnectionString
	  $Connection.Open()
	  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
	  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	  $DataSet = New-Object System.Data.DataSet
	  $RecordCount = $dataAdapter.Fill($dataSet, "data")
	  $DataSet.Tables[0]
	  }
	Catch {
	  Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to run query : $query `n$Error[0]" | out-file $DBErrorLog -append
	}
	Finally {
	  $Connection.Close()
	}
}

# https://www.ryandrane.com/2016/05/getting-ip-network-information-powershell/
Function Get-IPv4NetworkInfo
{
    Param
    (
        [Parameter(ParameterSetName="IPandMask",Mandatory=$true)] 
        [ValidateScript({$_ -match [ipaddress]$_})] 
        [System.String]$IPAddress,

        [Parameter(ParameterSetName="IPandMask",Mandatory=$true)] 
        [ValidateScript({$_ -match [ipaddress]$_})] 
        [System.String]$SubnetMask,

        [Parameter(ParameterSetName="CIDR",Mandatory=$true)] 
        [ValidateScript({$_ -match '^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)/([0-9]|[0-2][0-9]|3[0-2])$'})]
        [System.String]$CIDRAddress,

        [Switch]$IncludeIPRange
    )

    # If @CIDRAddress is set
    if($CIDRAddress)
    {
         # Separate our IP address, from subnet bit count
        $IPAddress, [int32]$MaskBits =  $CIDRAddress.Split('/')

        # Create array to hold our output mask
        $CIDRMask = @()

        # For loop to run through each octet,
        for($j = 0; $j -lt 4; $j++)
        {
            # If there are 8 or more bits left
            if($MaskBits -gt 7)
            {
                # Add 255 to mask array, and subtract 8 bits 
                $CIDRMask += [byte]255
                $MaskBits -= 8
            }
            else
            {
                # bits are less than 8, calculate octet bits and
                # zero out our $MaskBits variable.
                $CIDRMask += [byte]255 -shl (8 - $MaskBits)
                $MaskBits = 0
            }
        }

        # Assign our newly created mask to the SubnetMask variable
        $SubnetMask = $CIDRMask -join '.'
    }

    # Get Arrays of [Byte] objects, one for each octet in our IP and Mask
    $IPAddressBytes = ([ipaddress]::Parse($IPAddress)).GetAddressBytes()
    $SubnetMaskBytes = ([ipaddress]::Parse($SubnetMask)).GetAddressBytes()

    # Declare empty arrays to hold output
    $NetworkAddressBytes   = @()
    $BroadcastAddressBytes = @()
    $WildcardMaskBytes     = @()

    # Determine Broadcast / Network Addresses, as well as Wildcard Mask
    for($i = 0; $i -lt 4; $i++)
    {
        # Compare each Octet in the host IP to the Mask using bitwise
        # to obtain our Network Address
        $NetworkAddressBytes +=  $IPAddressBytes[$i] -band $SubnetMaskBytes[$i]

        # Compare each Octet in the subnet mask to 255 to get our wildcard mask
        $WildcardMaskBytes +=  $SubnetMaskBytes[$i] -bxor 255

        # Compare each octet in network address to wildcard mask to get broadcast.
        $BroadcastAddressBytes += $NetworkAddressBytes[$i] -bxor $WildcardMaskBytes[$i] 
    }

    # Create variables to hold our NetworkAddress, WildcardMask, BroadcastAddress
    $NetworkAddress   = $NetworkAddressBytes -join '.'
    $BroadcastAddress = $BroadcastAddressBytes -join '.'
    $WildcardMask     = $WildcardMaskBytes -join '.'

    # Now that we have our Network, Widcard, and broadcast information, 
    # We need to reverse the byte order in our Network and Broadcast addresses
    [array]::Reverse($NetworkAddressBytes)
    [array]::Reverse($BroadcastAddressBytes)

    # We also need to reverse the array of our IP address in order to get its
    # integer representation
    [array]::Reverse($IPAddressBytes)

    # Next we convert them both to 32-bit integers
    $NetworkAddressInt   = [System.BitConverter]::ToUInt32($NetworkAddressBytes,0)
    $BroadcastAddressInt = [System.BitConverter]::ToUInt32($BroadcastAddressBytes,0)
    $IPAddressInt        = [System.BitConverter]::ToUInt32($IPAddressBytes,0)

    #Calculate the number of hosts in our subnet, subtracting one to account for network address.
    $NumberOfHosts = ($BroadcastAddressInt - $NetworkAddressInt) - 1

    # Declare an empty array to hold our range of usable IPs.
    $IPRange = @()

    # If -IncludeIPRange specified, calculate it
    if ($IncludeIPRange)
    {
        # Now run through our IP range and figure out the IP address for each.
        For ($j = 1; $j -le $NumberOfHosts; $j++)
        {
            # Increment Network Address by our counter variable, then convert back
            # lto an IP address and extract as string, add to IPRange output array.
            $IPRange +=[ipaddress]([convert]::ToDouble($NetworkAddressInt + $j)) | Select-Object -ExpandProperty IPAddressToString
        }
    }

    # Create our output object
    $obj = New-Object -TypeName psobject

    # Add our properties to it
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "IPAddress"           -Value $IPAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "SubnetMask"          -Value $SubnetMask
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "NetworkAddress"      -Value $NetworkAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "BroadcastAddress"    -Value $BroadcastAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "WildcardMask"        -Value $WildcardMask
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "NumberOfHostIPs"     -Value $NumberOfHosts
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "IPRange"             -Value $IPRange

    # Return the object
    return $obj
}

$ErrorLog = "$GeoIPDir\ErrorLog.log"
$ToAddIPv4 = "$GeoIPDir\ToAddIPv4.csv"
$ToDelIPv4 = "$GeoIPDir\ToDelIPv4.csv"

#	Delete old files if exist
If (Test-Path $GeoIPDir\"GeoLite2-Country-CSV-Old") {Remove-Item -Recurse -Force $GeoIPDir\"GeoLite2-Country-CSV-Old"}
If (Test-Path $GeoIPDir\"GeoLite2-Country-CSV.zip") {Remove-Item -Force -Path $GeoIPDir\"GeoLite2-Country-CSV.zip"}
If (Test-Path $ToAddIPv4) {Remove-Item -Force -Path $ToAddIPv4}
If (Test-Path $ToDelIPv4) {Remove-Item -Force -Path $ToDelIPv4}
If (Test-Path "$GeoIPDir\GeoLite2-Country-CSV-New") {Rename-Item -Path "$GeoIPDir\GeoLite2-Country-CSV-New" "$GeoIPDir\GeoLite2-Country-CSV-Old"}

#	Check to make sure files deleted
If ((Test-Path $ToAddIPv4) -or (Test-Path $ToDelIPv4)){
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Failed to delete old ToDelIPv4.csv and/or ToAddIPv4.csv" | out-file $ErrorLog -append
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Quitting Script" | out-file $ErrorLog -append
	Exit
}

#	Create new comparison CSVs
New-Item $ToAddIPv4 -value "network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider`n"
New-Item $ToDelIPv4 -value "network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider`n"

#	Check to make sure new comparison CSVs created
If ((-not (Test-Path $ToAddIPv4)) -or (-not (Test-Path $ToDelIPv4))){
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : $ToAddIPv4 and/or $ToDelIPv4 do not exist" | out-file $ErrorLog -append
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Quitting Script" | out-file $ErrorLog -append
	Exit
}

#	Download latest GeoLite2 data and unzip
Try {
	$url = "https://geolite.maxmind.com/download/geoip/database/GeoLite2-Country-CSV.zip"
	$output = "$GeoIPDir\GeoLite2-Country-CSV.zip"
	Start-BitsTransfer -Source $url -Destination $output -ErrorAction Stop
	Expand-Archive $output -DestinationPath $GeoIPDir -ErrorAction Stop
}
Catch {
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to download and/or unzip : `n$Error[0]" | out-file $ErrorLog -append
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Quitting Script" | out-file $ErrorLog -append
	Exit
}

#	Rename folder so script can find it
Get-ChildItem $GeoIPDir | Where-Object {$_.PSIsContainer -eq $true} | ForEach {
	If ($_.Name -match 'GeoLite2-Country-CSV_[0-9]{8}') {
		$FolderName = $_.Name
	}
}
Rename-Item "$GeoIPDir\$FolderName" "$GeoIPDir\GeoLite2-Country-CSV-New"

# If new downloaded folder does not exist or could not be renamed, then throw error
If (-not (Test-Path "$GeoIPDir\GeoLite2-Country-CSV-New")){
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : $GeoIPDir\GeoLite2-Country-CSV-New does not exist" | out-file $ErrorLog -append
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Quitting Script" | out-file $ErrorLog -append
	Exit
}

#	Create table if it doesn't exist
$GeoIPTable = "geo_ip"
$Query = "
	CREATE TABLE IF NOT EXISTS $GeoIPTable (
	  minip varchar(15) NOT NULL,
	  maxip varchar(15) NOT NULL,
	  geoname_id int(7) NOT NULL,
	  countrycode varchar(2) NOT NULL,
	  countryname varchar(48) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	COMMIT;
	"
MySQLQuery($Query)

#	Compare Old and New data for changes
If ((Test-Path "$GeoIPDir\GeoLite2-Country-CSV-Old") -and (Test-Path "$GeoIPDir\GeoLite2-Country-CSV-New")){
$CompareCSVIPV4Old = Get-Content "$GeoIPDir\GeoLite2-Country-CSV-Old\GeoLite2-Country-Blocks-IPv4.csv"
$CompareCSVIPV4New = Get-Content "$GeoIPDir\GeoLite2-Country-CSV-New\GeoLite2-Country-Blocks-IPv4.csv"
	Compare-Object $CompareCSVIPV4Old $CompareCSVIPV4New | ForEach-Object {
		If ($_.SideIndicator -eq '=>') {
			Write-Output $_.InputObject | Out-File $ToAddIPv4 -Encoding ASCII -Append
		} Else {
			Write-Output $_.InputObject | Out-File $ToDelIPv4 -Encoding ASCII -Append
		}
	}
}

#	If database previously loaded then use ToAdd/ToDel to make incremental changes - otherwise, load entire CSV into database
#	First, check to see database has previously loaded entries
$Query = "SELECT COUNT(minip) AS numrows FROM $GeoIPTable"
MySQLQuery($Query) | ForEach {
	$EntryCount = $_.numrows
}

#	If pass 3 tests: exists old folder, exists new folder, database previously populated - THEN proceed to load table from incremental
If ((Test-Path "$GeoIPDir\GeoLite2-Country-CSV-Old") -and (Test-Path "$GeoIPDir\GeoLite2-Country-CSV-New") -and ($EntryCount -gt 0)){

	# 	Load table from incremental: 
	# 	Read ToDelIPv4 cvs file, convert CIDR network address to lowest and highest IPs in range, then delete from database
	$GeoIPObjects = import-csv -Path $ToDelIPv4 -Delimiter "," -Header network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider
	$GeoIPObjects | foreach-object {
		$Network = $_.network
		$GeoNameID = $_.geoname_id
		If ($GeoNameID -match '[0-9]{1,12}'){
			Get-IPv4NetworkInfo -CIDRAddress $Network | ForEach-Object {
				$MinIP = $_.NetworkAddress
				$MaxIP = $_.BroadcastAddress
			}
			$Query = "DELETE FROM $GeoIPTable WHERE minip='$MinIP' AND maxip='$MaxIP'"
			MySQLQuery($Query)
		}
	}

	# 	Read ToAddIPv4 cvs file, convert CIDR network address to lowest and highest IPs in range, then insert into database
	$GeoIPObjects = import-csv -Path $ToAddIPv4 -Delimiter "," -Header network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider
	$GeoIPObjects | foreach-object {
		$Network = $_.network
		$GeoNameID = $_.geoname_id
		If ($GeoNameID -match '[0-9]{1,12}'){
			Get-IPv4NetworkInfo -CIDRAddress $Network | ForEach-Object {
				$MinIP = $_.NetworkAddress
				$MaxIP = $_.BroadcastAddress
			}
			$Query = "INSERT INTO $GeoIPTable (minip,maxip,geoname_id) VALUES ('$MinIP','$MaxIP','$GeoNameID')"
			MySQLQuery($Query)
		}
	}

	# 	Read country info cvs and insert into database
	$CountryLocations = "$GeoIPDir\GeoLite2-Country-CSV-New\GeoLite2-Country-Locations-en.csv"
	$GeoIPNameObjects = import-csv -Path $CountryLocations -Delimiter "," -Header geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name,is_in_european_union
	$GeoIPNameObjects | foreach-object {
		$GeoNameID = $_.geoname_id
		$CountryCode = $_.country_iso_code
		$CountryName = $_.country_name
		If ($GeoNameID -match '[0-9]{1,12}'){
			$Query = "UPDATE $GeoIPTable SET countrycode='$CountryCode', countryname='$CountryName' WHERE geoname_id='$GeoNameID' AND countrycode='' AND countryname=''"
			MySQLQuery($Query)
		}
	}
}

#	If pass 2 tests: exists new folder, database UNpopulated - then proceed to load table as new
ElseIf ((Test-Path "$GeoIPDir\GeoLite2-Country-CSV-New") -and ($EntryCount -eq 0)){

	# 	Load table from NEW: 
	# 	Read cvs file, convert CIDR network address to lowest and highest IPs in range, then insert into database
	$CountryBlocksIPV4 = "$GeoIPDir\GeoLite2-Country-CSV-New\GeoLite2-Country-Blocks-IPv4.csv"
	$GeoIPObjects = import-csv -Path $CountryBlocksIPV4 -Delimiter "," -Header network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider
	$GeoIPObjects | foreach-object {
		$Network = $_.network
		$GeoNameID = $_.geoname_id
		If ($GeoNameID -match '[0-9]{1,12}'){
			Get-IPv4NetworkInfo -CIDRAddress $Network | ForEach-Object {
				$MinIP = $_.NetworkAddress
				$MaxIP = $_.BroadcastAddress
			}
			$Query = "INSERT INTO $GeoIPTable (minip,maxip,geoname_id) VALUES ('$MinIP','$MaxIP','$GeoNameID')"
			MySQLQuery($Query)
		}
	}

	# 	Read country info cvs and insert into database
	$CountryLocations = "$GeoIPDir\GeoLite2-Country-CSV-New\GeoLite2-Country-Locations-en.csv"
	$GeoIPNameObjects = import-csv -Path $CountryLocations -Delimiter "," -Header geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name,is_in_european_union
	$GeoIPNameObjects | foreach-object {
		$GeoNameID = $_.geoname_id
		$CountryCode = $_.country_iso_code
		$CountryName = $_.country_name
		If ($GeoNameID -match '[0-9]{1,12}'){
			$Query = "UPDATE $GeoIPTable SET countrycode='$CountryCode', countryname='$CountryName' WHERE geoname_id='$GeoNameID' AND countrycode='' AND countryname=''"
			MySQLQuery($Query)
		}
	}
}

#	Else Exit since neither incremental nor new load can be accomplished
Else {
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to complete database load : Either Old or New data doesn't exist." | out-file $ErrorLog -append
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Quitting Script" | out-file $ErrorLog -append
	Exit
}<#
.SYNOPSIS
	Install MaxMindas geoip database on MySQL

.DESCRIPTION
	Download and unzip MaxMinds cvs geoip data, then populate MySQL with csv data

.FUNCTIONALITY
	1) If geoip table does not exist, it gets created
	2) Deletes all data from table if exists (required when updating database)
	3) Downloads MaxMinds geolite2 cvs data as zip file, uncompresses it, then renames the folder
	4) Reads IPv4 cvs data, then calculates the lowest and highest IP from each network in the database
	5) Inserts lowest and highest IP calculated above and geoname_id from IPv4 cvs file
	6) Reads geo-name cvs file and updates each record with country code and country name based on the geoname_id

.NOTES
	Run once per month or once per 3 months via task scheduler
	Loading the database takes over one hour. Set your scheduled task for after midnight
	
.EXAMPLE
	Example query to return countrycode and countryname from database:
	
	SELECT countrycode, countryname FROM geo_ip WHERE INET_ATON('182.253.228.22') >= INET_ATON(minip) AND INET_ATON('182.253.228.22') <= INET_ATON(maxip)

#>

### User Variables 
$GeoIPDir = 'C:\scripts\geolite2' 	# Location of files. No trailing "\" please
$MySQLAdminUserName = 'geoip'
$MySQLAdminPassword = 'supersecretpassword'
$MySQLDatabase = 'geoip'
$MySQLHost = 'localhost'
### End User Variables 

#	Function from: https://www.quadrotech-it.com/blog/querying-mysql-from-powershell/
Function MySQLQuery($Query) {
	$DBErrorLog = '$GeoIPDir\DBError.log'
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database=" + $MySQLDatabase
	Try {
	  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
	  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
	  $Connection.ConnectionString = $ConnectionString
	  $Connection.Open()
	  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
	  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
	  $DataSet = New-Object System.Data.DataSet
	  $RecordCount = $dataAdapter.Fill($dataSet, "data")
	  $DataSet.Tables[0]
	  }
	Catch {
	  Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to run query : $query `n$Error[0]" | out-file $DBErrorLog -append
	}
	Finally {
	  $Connection.Close()
	}
}

#	Function from: https://www.ryandrane.com/2016/05/getting-ip-network-information-powershell/
Function Get-IPv4NetworkInfo
{
    Param
    (
        [Parameter(ParameterSetName="IPandMask",Mandatory=$true)] 
        [ValidateScript({$_ -match [ipaddress]$_})] 
        [System.String]$IPAddress,

        [Parameter(ParameterSetName="IPandMask",Mandatory=$true)] 
        [ValidateScript({$_ -match [ipaddress]$_})] 
        [System.String]$SubnetMask,

        [Parameter(ParameterSetName="CIDR",Mandatory=$true)] 
        [ValidateScript({$_ -match '^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)/([0-9]|[0-2][0-9]|3[0-2])$'})]
        [System.String]$CIDRAddress,

        [Switch]$IncludeIPRange
    )

    # If @CIDRAddress is set
    if($CIDRAddress)
    {
         # Separate our IP address, from subnet bit count
        $IPAddress, [int32]$MaskBits =  $CIDRAddress.Split('/')

        # Create array to hold our output mask
        $CIDRMask = @()

        # For loop to run through each octet,
        for($j = 0; $j -lt 4; $j++)
        {
            # If there are 8 or more bits left
            if($MaskBits -gt 7)
            {
                # Add 255 to mask array, and subtract 8 bits 
                $CIDRMask += [byte]255
                $MaskBits -= 8
            }
            else
            {
                # bits are less than 8, calculate octet bits and
                # zero out our $MaskBits variable.
                $CIDRMask += [byte]255 -shl (8 - $MaskBits)
                $MaskBits = 0
            }
        }

        # Assign our newly created mask to the SubnetMask variable
        $SubnetMask = $CIDRMask -join '.'
    }

    # Get Arrays of [Byte] objects, one for each octet in our IP and Mask
    $IPAddressBytes = ([ipaddress]::Parse($IPAddress)).GetAddressBytes()
    $SubnetMaskBytes = ([ipaddress]::Parse($SubnetMask)).GetAddressBytes()

    # Declare empty arrays to hold output
    $NetworkAddressBytes   = @()
    $BroadcastAddressBytes = @()
    $WildcardMaskBytes     = @()

    # Determine Broadcast / Network Addresses, as well as Wildcard Mask
    for($i = 0; $i -lt 4; $i++)
    {
        # Compare each Octet in the host IP to the Mask using bitwise
        # to obtain our Network Address
        $NetworkAddressBytes +=  $IPAddressBytes[$i] -band $SubnetMaskBytes[$i]

        # Compare each Octet in the subnet mask to 255 to get our wildcard mask
        $WildcardMaskBytes +=  $SubnetMaskBytes[$i] -bxor 255

        # Compare each octet in network address to wildcard mask to get broadcast.
        $BroadcastAddressBytes += $NetworkAddressBytes[$i] -bxor $WildcardMaskBytes[$i] 
    }

    # Create variables to hold our NetworkAddress, WildcardMask, BroadcastAddress
    $NetworkAddress   = $NetworkAddressBytes -join '.'
    $BroadcastAddress = $BroadcastAddressBytes -join '.'
    $WildcardMask     = $WildcardMaskBytes -join '.'

    # Now that we have our Network, Widcard, and broadcast information, 
    # We need to reverse the byte order in our Network and Broadcast addresses
    [array]::Reverse($NetworkAddressBytes)
    [array]::Reverse($BroadcastAddressBytes)

    # We also need to reverse the array of our IP address in order to get its
    # integer representation
    [array]::Reverse($IPAddressBytes)

    # Next we convert them both to 32-bit integers
    $NetworkAddressInt   = [System.BitConverter]::ToUInt32($NetworkAddressBytes,0)
    $BroadcastAddressInt = [System.BitConverter]::ToUInt32($BroadcastAddressBytes,0)
    $IPAddressInt        = [System.BitConverter]::ToUInt32($IPAddressBytes,0)

    #Calculate the number of hosts in our subnet, subtracting one to account for network address.
    $NumberOfHosts = ($BroadcastAddressInt - $NetworkAddressInt) - 1

    # Declare an empty array to hold our range of usable IPs.
    $IPRange = @()

    # If -IncludeIPRange specified, calculate it
    if ($IncludeIPRange)
    {
        # Now run through our IP range and figure out the IP address for each.
        For ($j = 1; $j -le $NumberOfHosts; $j++)
        {
            # Increment Network Address by our counter variable, then convert back
            # lto an IP address and extract as string, add to IPRange output array.
            $IPRange +=[ipaddress]([convert]::ToDouble($NetworkAddressInt + $j)) | Select-Object -ExpandProperty IPAddressToString
        }
    }

    # Create our output object
    $obj = New-Object -TypeName psobject

    # Add our properties to it
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "IPAddress"           -Value $IPAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "SubnetMask"          -Value $SubnetMask
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "NetworkAddress"      -Value $NetworkAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "BroadcastAddress"    -Value $BroadcastAddress
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "WildcardMask"        -Value $WildcardMask
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "NumberOfHostIPs"     -Value $NumberOfHosts
    Add-Member -InputObject $obj -MemberType NoteProperty -Name "IPRange"             -Value $IPRange

    # Return the object
    return $obj
}

#	Delete old files if exist
Remove-Item -Recurse -Force $GeoIPDir\"GeoLite2-Country-CSV"
Remove-Item -Force -Path $GeoIPDir\"GeoLite2-Country-CSV.zip"

#	Download latest GeoLite2 data
$url = "https://geolite.maxmind.com/download/geoip/database/GeoLite2-Country-CSV.zip"
$output = "$GeoIPDir\GeoLite2-Country-CSV.zip"
Start-BitsTransfer -Source $url -Destination $output
Expand-Archive $output -DestinationPath $GeoIPDir

#	Rename folder so script can find it
$MMErrorLog = "$GeoIPDir\error.log"
$MMFolder = Get-ChildItem $GeoIPDir | Where-Object {$_.PSIsContainer -eq $true} | Sort-Object
If ($MMFolder -match 'GeoLite2-Country-CSV_[0-9]{8}'){
	Rename-Item -Path $GeoIPDir\$MMFolder $GeoIPDir\"GeoLite2-Country-CSV"
} Else {
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : `n$Error[0]" | Out-File $MMErrorLog
}

#	Create table if it doesn't exist
$GeoIPTable = "geo_ip"
$Query = "
	CREATE TABLE IF NOT EXISTS $GeoIPTable (
	  minip varchar(15) NOT NULL,
	  maxip varchar(15) NOT NULL,
	  geoname_id int(7) NOT NULL,
	  countrycode varchar(2) NOT NULL,
	  countryname varchar(48) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	COMMIT;
	"
MySQLQuery($Query)

#	Delete all data on table (required when updating data)
$Query = "DELETE FROM $GeoIPTable"
MySQLQuery($Query)

#	Read IPv4 cvs file, convert CIDR network address to lowest and highest IPs in range, then insert into database
$CountryBlocksIPV4 = "$GeoIPDir\GeoLite2-Country-CSV\GeoLite2-Country-Blocks-IPv4-test.csv"
$GeoIPObjects = import-csv -Path $CountryBlocksIPV4 -Delimiter "," -Header network,geoname_id,registered_country_geoname_id,represented_country_geoname_id,is_anonymous_proxy,is_satellite_provider
$GeoIPObjects | foreach-object {
	$Network = $_.network
	$GeoNameID = $_.geoname_id
	If ($GeoNameID -match '[0-9]{1,12}'){
		Get-IPv4NetworkInfo -CIDRAddress $Network | ForEach-Object {
			$MinIP = $_.NetworkAddress
			$MaxIP = $_.BroadcastAddress
		}
		$Query = "INSERT INTO $GeoIPTable (minip,maxip,geoname_id) VALUES ('$MinIP','$MaxIP','$GeoNameID')"
		MySQLQuery($Query)
	}
}

#	Read country info cvs and insert into database
$CountryLocations = "$GeoIPDir\GeoLite2-Country-CSV\GeoLite2-Country-Locations-en.csv"
$GeoIPNameObjects = import-csv -Path $CountryLocations -Delimiter "," -Header geoname_id,locale_code,continent_code,continent_name,country_iso_code,country_name,is_in_european_union
$GeoIPNameObjects | foreach-object {
	$GeoNameID = $_.geoname_id
	$CountryCode = $_.country_iso_code
	$CountryName = $_.country_name
	If ($GeoNameID -match '[0-9]{1,12}'){
		$Query = "UPDATE $GeoIPTable SET countrycode='$CountryCode', countryname='$CountryName' WHERE geoname_id='$GeoNameID'"
		MySQLQuery($Query)
	}
}

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-25 16:51

Added primary key to db table. Queries now 5 times faster.

Before: 1.5 seconds
After: 0.3 seconds

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-25 17:35

Holy crap! I've been messing with the query and with a little googling got the time down to 0.05 seconds!

Sample query:

Code: Select all

SELECT countrycode, countryname FROM `geoip` WHERE INET_ATON('14.1.224.229') BETWEEN INET_ATON(minip) AND INET_ATON(maxip) LIMIT 1
The "LIMIT 1" does the trick. It apparently cuts off the table scan when the first result is found. There should be only one result anyway, so this is perfect.

Went from 1.5 seconds to 0.3 seconds to 0.05 seconds. Not bad...

EDIT - testing an IP near the end of the table results in up to 0.9 seconds scan time. Still not bad. The lower the IP, the faster the query.

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-25 22:18

I'm no MySQL expert. I looked around some more and found an even more efficient query.

Code: Select all

SELECT * FROM (SELECT * FROM geoip WHERE INET_ATON('223.114.216.150') <= INET_ATON(maxip) LIMIT 1) AS A WHERE INET_ATON(minip) <= INET_ATON('223.114.216.150')
The IP is high, so its the longest possible query time. I ran it several times. The highest query time was 0.38 seconds. When the IP is low (like 15.15.15.15), the query time is consistently below 0.01 seconds. :mrgreen:

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-26 14:20

palinka wrote:
2019-10-25 22:18

Code: Select all

SELECT * FROM (SELECT * FROM geoip WHERE INET_ATON('223.114.216.150') <= INET_ATON(maxip) LIMIT 1) AS A WHERE INET_ATON(minip) <= INET_ATON('223.114.216.150')
This ^^ does not work :(

It works sometimes but not all the time. I don't know why. Too bad.

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-26 20:04

Bug fix update. I think this project is finished.

Files: https://github.com/palinkas-jo-reggelt/GeoLite2MySQL

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-27 14:15

palinka wrote:
2019-10-26 14:20
palinka wrote:
2019-10-25 22:18

Code: Select all

SELECT * FROM (SELECT * FROM geoip WHERE INET_ATON('223.114.216.150') <= INET_ATON(maxip) LIMIT 1) AS A WHERE INET_ATON(minip) <= INET_ATON('223.114.216.150')
This ^^ does not work :(

It works sometimes but not all the time. I don't know why. Too bad.
It turns out that I had a syntax error and ^this^ is indeed the fastest query (that I can come up with).

Code: Select all

SELECT countrycode, countryname FROM (SELECT * FROM geoip WHERE INET_ATON('223.114.216.150') <= maxipaton LIMIT 1) AS A WHERE minipaton <= INET_ATON('223.114.216.150')
Plus, I added columns "minipaton" and "maxipaton" which are INET_ATON(minip) and INET_ATON(maxip) already tabulated, so it should save a millisecond or two on the query. :D

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-27 17:13

Holy cow - I just boosted query speed with a nitrous oxide injected supercharger strapped to a rocket engine by changing the primary key to maxipaton. Now ALL queries are UNDER 0.01 seconds. :mrgreen:

So much to learn about database design.

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-30 05:12

Just ran the first update. Maxminds releases updates weekly. I figured there wouldn't be many changes - I mean, how many IPs get swapped around between countries weekly? But there were about 2,000 changes. I looked at a few randomly. One went from USA to Russia, one went from Taiwan to Singapore, Korea to Singapore. Who knew these things bounced around so much?

User avatar
mattg
Moderator
Moderator
Posts: 20837
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: MaxMinds GeoIP for local MySQL

Post by mattg » 2019-10-30 06:56

I don't think that they do...
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-30 10:57

mattg wrote:
2019-10-30 06:56
I don't think that they do...
Then maxminds had 2000 corrections?

I think many of these networks are probably multinational corporate owned and get shuffled around as needed. Some of the blocks are as small as a single ip.

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-10-31 00:12

I added email notification with counts of the various things getting added and deleted during update.

https://github.com/palinkas-jo-reggelt/GeoLite2MySQL

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-11-13 14:05

This week's update was a success. Everything worked and I got the email notification:

Code: Select all

GeoIP update start: 11/13/2019 1:30:01 AM

339,851 : (A) Records in database prior to update
  5,809 : (B) Records removed from database
  7,637 : (C) Records inserted into database
======= :
341,679 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
======= :
341,679 : Actual number of records in NEW IPV4 csv
======= :
341,679 : Queried number of records in database (should match NEW IPV4 csv)

GeoIP database update SUCCESS. All records accounted for.


GeoIP update successful.

GeoIP update finish: 11/13/2019 3:11:15 AM

Completed update in -01:41:14
I just have to fix the way the time to complete is reported.

Also, it takes a LOOOOOOONG time. Part of that is my wimpy hardware, I assume. I don't know how to make it more efficient. The tables and csv are pretty big. It takes a while just to read the full csv - at 340+k lines - to look for changes. I guess I don't really care how long it takes (within reason) as long as it gets the job done accurately.

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-12-06 14:35

Looks like practically every week there are a couple thousand changes to the database.

Code: Select all

GeoIP update start: 12/4/2019 1:30:02 AM

342,554 : (A) Records in database prior to update
  1,405 : (B) Records removed from database
  2,748 : (C) Records inserted into database
======= :
343,897 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
======= :
343,897 : Actual number of records in NEW IPV4 csv
======= :
343,897 : Queried number of records in database (should match NEW IPV4 csv)

GeoIP database update SUCCESS. All records accounted for.


GeoIP update successful.

GeoIP update finish: 12/4/2019 2:11:31 AM

Completed update in 0 hours 41 minutes 29 seconds

User avatar
SorenR
Senior user
Senior user
Posts: 3572
Joined: 2006-08-21 15:38
Location: Denmark

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2019-12-06 14:54

palinka wrote:
2019-12-06 14:35
Looks like practically every week there are a couple thousand changes to the database.

Code: Select all

GeoIP update start: 12/4/2019 1:30:02 AM

342,554 : (A) Records in database prior to update
  1,405 : (B) Records removed from database
  2,748 : (C) Records inserted into database
======= :
343,897 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
======= :
343,897 : Actual number of records in NEW IPV4 csv
======= :
343,897 : Queried number of records in database (should match NEW IPV4 csv)

GeoIP database update SUCCESS. All records accounted for.


GeoIP update successful.

GeoIP update finish: 12/4/2019 2:11:31 AM

Completed update in 0 hours 41 minutes 29 seconds
According to RIPE: "we made our final /22 IPv4 allocation from the last remaining addresses in our available pool."

So... Europe do not have any IPv4 addresses left. Time to trade!

These are just some of them...

https://iptrading.com/
https://www.ipv4connect.com/
https://ipv4brokers.net

Expect IPv4 allocations to shift continents with a snap of your fingers :mrgreen:
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-12-06 15:16

SorenR wrote:
2019-12-06 14:54
palinka wrote:
2019-12-06 14:35
Looks like practically every week there are a couple thousand changes to the database.

Code: Select all

GeoIP update start: 12/4/2019 1:30:02 AM

342,554 : (A) Records in database prior to update
  1,405 : (B) Records removed from database
  2,748 : (C) Records inserted into database
======= :
343,897 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
======= :
343,897 : Actual number of records in NEW IPV4 csv
======= :
343,897 : Queried number of records in database (should match NEW IPV4 csv)

GeoIP database update SUCCESS. All records accounted for.


GeoIP update successful.

GeoIP update finish: 12/4/2019 2:11:31 AM

Completed update in 0 hours 41 minutes 29 seconds
According to RIPE: "we made our final /22 IPv4 allocation from the last remaining addresses in our available pool."

So... Europe do not have any IPv4 addresses left. Time to trade!

These are just some of them...

https://iptrading.com/
https://www.ipv4connect.com/
https://ipv4brokers.net

Expect IPv4 allocations to shift continents with a snap of your fingers :mrgreen:
That definitely explains a lot. And also shows why it's good to stay updated weekly. :mrgreen:

User avatar
SorenR
Senior user
Senior user
Posts: 3572
Joined: 2006-08-21 15:38
Location: Denmark

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2019-12-06 15:24

palinka wrote:
2019-12-06 15:16
SorenR wrote:
2019-12-06 14:54
palinka wrote:
2019-12-06 14:35
Looks like practically every week there are a couple thousand changes to the database.

Code: Select all

GeoIP update start: 12/4/2019 1:30:02 AM

342,554 : (A) Records in database prior to update
  1,405 : (B) Records removed from database
  2,748 : (C) Records inserted into database
======= :
343,897 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
======= :
343,897 : Actual number of records in NEW IPV4 csv
======= :
343,897 : Queried number of records in database (should match NEW IPV4 csv)

GeoIP database update SUCCESS. All records accounted for.


GeoIP update successful.

GeoIP update finish: 12/4/2019 2:11:31 AM

Completed update in 0 hours 41 minutes 29 seconds
According to RIPE: "we made our final /22 IPv4 allocation from the last remaining addresses in our available pool."

So... Europe do not have any IPv4 addresses left. Time to trade!

These are just some of them...

https://iptrading.com/
https://www.ipv4connect.com/
https://ipv4brokers.net

Expect IPv4 allocations to shift continents with a snap of your fingers :mrgreen:
That definitely explains a lot. And also shows why it's good to stay updated weekly. :mrgreen:
Perhaps daily or hourly ??
SørenR.

“Those who don't know history are doomed to repeat it.”
― Edmund Burke

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2019-12-06 16:39

SorenR wrote:
2019-12-06 15:24
palinka wrote:
2019-12-06 15:16
SorenR wrote:
2019-12-06 14:54

According to RIPE: "we made our final /22 IPv4 allocation from the last remaining addresses in our available pool."

So... Europe do not have any IPv4 addresses left. Time to trade!

These are just some of them...

https://iptrading.com/
https://www.ipv4connect.com/
https://ipv4brokers.net

Expect IPv4 allocations to shift continents with a snap of your fingers :mrgreen:
That definitely explains a lot. And also shows why it's good to stay updated weekly. :mrgreen:
Perhaps daily or hourly ??
I would, but maxmind only releases updates weekly. On Tuesdays, normally, so my scheduled task runs on Wednesday mornings.

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2020-01-03 23:07

On Dec 31, MaxMind started requiring a license key to download the GeoLite2. Therefore, this week's update failed. I also discovered an issue in the script with renaming the extracted data folder.

More info on the change to license key API: https://blog.maxmind.com/2019/12/18/sig ... databases/

Updated scripts are here: https://github.com/palinkas-jo-reggelt/GeoLite2MySQL

Instructions for obtaining license key:

1) Register for a GeoLite2 account here: https://www.maxmind.com/en/geolite2/signup
2) After successful login to your MaxMind account, generate a new license key (Services > License Key > Generate New Key)
3) Update the license key variable at the top of GeoLite2MySQL.ps1

The license key is free.

User avatar
mattg
Moderator
Moderator
Posts: 20837
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: MaxMinds GeoIP for local MySQL

Post by mattg » 2020-01-04 00:47

palinka wrote:
2020-01-03 23:07
The license key is free.
For the moment, I'd suggest
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2020-01-04 00:58

mattg wrote:
2020-01-04 00:47
palinka wrote:
2020-01-03 23:07
The license key is free.
For the moment, I'd suggest
From what I read, it has to do with complying with regulation. It only applies to the free ones because a license/api key - and therefore also consent to their terms of agreement - has always been required for the pay versions.

I don't see this as a step toward putting the geolite2 databases behind a paywall. They've had a two tiered system for a while. The pay-for databases with a lot more info and the free ones with a lot less info.

Time will tell, I guess.

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2020-03-24 22:32

I just committed a big update with bug fixes and a lot of cleanup.

* Fixed a regex bug that caused certain networks to be skipped when inserting into database
* Added choice of debug verbosity: to console, file, both or none (you still get email report no matter what you choose)
* Cleaned up debugging greatly
* Separated database and scheduled task creation into new SetupGeoLite2SQL.ps1
* Got rid of "Initial Load" altogether (a remnant from earlier code logic) - script is the same for first time database loading so "initial loading" was just redundant code

https://github.com/palinkas-jo-reggelt/GeoLite2SQL

Corona-chan is proving to be valuable for some things. :mrgreen:

palinka
Senior user
Senior user
Posts: 1941
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2020-03-26 03:38

For some reason, MaxMind leaves a couple of geoname_id entries blank. Added a routine to retrieve this missing information from ip-api.com in order to make the database complete. The routine looks up json data from ip-api, then cross references the countrycode against the MaxMind country name csv and inserts the correct geoname_id into the database. Then, when the country name info gets loaded later in the script, the correct country name and country code get inserted into the database (instead of getting wiped via update with no information).

Files: https://github.com/palinkas-jo-reggelt/GeoLite2SQL

Post Reply