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.
palinka
Senior user
Senior user
Posts: 4455
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: 4455
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: 6308
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.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4455
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: 6308
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.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4455
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: 4455
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: 4455
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: 4455
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: 4455
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: 4455
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: 4455
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: 4455
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: 4455
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: 4455
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: 22435
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: 4455
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: 4455
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: 4455
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: 4455
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: 6308
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.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4455
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: 6308
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.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4455
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: 4455
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: 22435
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: 4455
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: 4455
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: 4455
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

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2020-10-04 04:29

Updated tonight.

* fixed a divide by 0 bug that only presented if option console verbose was enabled
* updated email function to attach debug log
* housekeeping

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

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2020-10-07 16:02

I optimized a query and finally got a chance to test it last night (with the new weekly release of MaxMind data). 10 times faster now. YUUUUGE improvement.

I'm going to experiment with just deleting all the data and starting from scratch. That would remove several steps involving manipulating the CSVs and could further speed things up.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2020-10-08 03:25

palinka wrote:
2020-10-07 16:02
I'm going to experiment with just deleting all the data and starting from scratch. That would remove several steps involving manipulating the CSVs and could further speed things up.
Well that was a bust. Good news is the most recent one works better than ever.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-26 10:03

RAN INTO A PROBLEM

geo_ip table is created but it is blank with no values

Code: Select all



Mode                LastWriteTime         Length Name                                                                                                                                                 
----                -------------         ------ ----                                                                                                                                                 
-a----       26/03/2021  01:05 PM              0 DebugLog.log                                                                                                                                         
13:05:08 : 00:00 : GeoIP Update Start
13:05:08 : 00:00 : Deleting old files
13:05:08 : 00:00 : Downloading MaxMind data
13:05:15 : 00:06 : Renaming MaxMind data folder
13:05:15 : 00:00 : Querying number of records in database before starting : 0 records
13:05:15 : 00:00 : Loading MaxMind CountryBlocks csv
13:05:33 : 00:17 : MaxMind CSV Loaded
13:05:33 : 00:00 : Exporting MaxMind data to reduced csv for comparison
13:06:21 : 00:47 : Reduced csv exported
13:06:21 : 00:00 : 322175 Records in MaxMind CountryBlocks csv
13:06:21 : 00:00 : Loading entries from database for comparison to MaxMind data
13:06:21 : 00:00 : Database entries Loaded
13:06:21 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:197 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand
 
13:06:30 : 00:09 : Comparison completed
13:06:30 : 00:00 : Preparing to delete MaxMind removed records from database
13:06:30 : 00:00 : ERROR : Update failed : 
Could not find file 'C:\Scripts\GeoIP\Script-Created-Files\ToDelIPv4.csv'. Cannot bind argument to parameter 'ReferenceObject' because it is null.[0]
13:06:30 : 00:00 : ERROR : Quitting Script


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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-26 12:29

gotspatel wrote:
2021-03-26 10:03
RAN INTO A PROBLEM

geo_ip table is created but it is blank with no values

Code: Select all



Mode                LastWriteTime         Length Name                                                                                                                                                 
----                -------------         ------ ----                                                                                                                                                 
-a----       26/03/2021  01:05 PM              0 DebugLog.log                                                                                                                                         
13:05:08 : 00:00 : GeoIP Update Start
13:05:08 : 00:00 : Deleting old files
13:05:08 : 00:00 : Downloading MaxMind data
13:05:15 : 00:06 : Renaming MaxMind data folder
13:05:15 : 00:00 : Querying number of records in database before starting : 0 records
13:05:15 : 00:00 : Loading MaxMind CountryBlocks csv
13:05:33 : 00:17 : MaxMind CSV Loaded
13:05:33 : 00:00 : Exporting MaxMind data to reduced csv for comparison
13:06:21 : 00:47 : Reduced csv exported
13:06:21 : 00:00 : 322175 Records in MaxMind CountryBlocks csv
13:06:21 : 00:00 : Loading entries from database for comparison to MaxMind data
13:06:21 : 00:00 : Database entries Loaded
13:06:21 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:197 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand
 
13:06:30 : 00:09 : Comparison completed
13:06:30 : 00:00 : Preparing to delete MaxMind removed records from database
13:06:30 : 00:00 : ERROR : Update failed : 
Could not find file 'C:\Scripts\GeoIP\Script-Created-Files\ToDelIPv4.csv'. Cannot bind argument to parameter 'ReferenceObject' because it is null.[0]
13:06:30 : 00:00 : ERROR : Quitting Script

Its because the reference files $DBcsv and $ToDelIPv4.csv were not created - because there were no entries in the database from which to create those files. They *should* be created when you start the script. This is a logic problem that affects the first time run only.

Changing this block at line 85 should fix the issue:

Code: Select all

<#	Delete old files if exist  #>
VerboseOutput "Deleting old files"
$Timer = Get-Date
If (Test-Path "$PSScriptRoot\GeoLite2-Country-CSV") {Remove-Item -Recurse -Force "$PSScriptRoot\GeoLite2-Country-CSV"}
If (Test-Path "$PSScriptRoot\Script-Created-Files\GeoLite2-Country-CSV.zip") {Remove-Item -Force -Path "$PSScriptRoot\Script-Created-Files\GeoLite2-Country-CSV.zip"}
If (Test-Path $EmailBody) {Remove-Item -Force -Path $EmailBody}
If (Test-Path $MMcsv) {Remove-Item -Force -Path $MMcsv}
If (Test-Path $DBcsv) {Remove-Item -Force -Path $DBcsv}
If (Test-Path $ToAddIPv4) {Remove-Item -Force -Path $ToAddIPv4}
If (Test-Path $ToDelIPv4) {Remove-Item -Force -Path $ToDelIPv4}
New-Item -Path $MMcsv
New-Item -Path $DBcsv
New-Item -Path $ToAddIPv4
New-Item -Path $ToDelIPv4
Run it again like this and if it works for you, I'll update github.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-26 12:41

No so other error after replacing the block

Code: Select all

Directory: C:\Scripts\GeoIP


Mode                LastWriteTime         Length Name                                                                                                                                                 
----                -------------         ------ ----                                                                                                                                                 
d-----       26/03/2021  04:09 PM                Script-Created-Files                                                                                                                                 
-a----       26/03/2021  04:09 PM              0 DebugLog.log                                                                                                                                         
16:09:19 : 00:00 : GeoIP Update Start
16:09:19 : 00:00 : Deleting old files


    Directory: C:\Scripts\GeoIP\Script-Created-Files


Mode                LastWriteTime         Length Name                                                                                                                                                 
----                -------------         ------ ----                                                                                                                                                 
-a----       26/03/2021  04:09 PM              0 CSV-MM.csv                                                                                                                                           
-a----       26/03/2021  04:09 PM              0 CSV-DB.csv                                                                                                                                           
-a----       26/03/2021  04:09 PM              0 ToAddIPv4.csv                                                                                                                                        
-a----       26/03/2021  04:09 PM              0 ToDelIPv4.csv                                                                                                                                        
16:09:19 : 00:00 : ERROR : Failed to delete old ToDelIPv4.csv and/or ToAddIPv4.csv
16:09:19 : 00:00 : ERROR : Quitting Script



I suspect the problem is that after first download of maxmind files the db table is not being populated with the downloaded data.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-26 13:30

No. The issue is later the script checks to make sure the files were actually deleted. They were deleted, but then we re- created them so of course the test failed. :oops:

Look down a few lines to the next block of code. All the new-item lines should go AFTER that next block ("Check to make sure files deleted").

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-26 13:37

Code: Select all

<#	Delete old files if exist  #>
VerboseOutput "Deleting old files"
$Timer = Get-Date
If (Test-Path "$PSScriptRoot\GeoLite2-Country-CSV") {Remove-Item -Recurse -Force "$PSScriptRoot\GeoLite2-Country-CSV"}
If (Test-Path "$PSScriptRoot\Script-Created-Files\GeoLite2-Country-CSV.zip") {Remove-Item -Force -Path "$PSScriptRoot\Script-Created-Files\GeoLite2-Country-CSV.zip"}
If (Test-Path $EmailBody) {Remove-Item -Force -Path $EmailBody}
If (Test-Path $MMcsv) {Remove-Item -Force -Path $MMcsv}
If (Test-Path $DBcsv) {Remove-Item -Force -Path $DBcsv}
If (Test-Path $ToAddIPv4) {Remove-Item -Force -Path $ToAddIPv4}
If (Test-Path $ToDelIPv4) {Remove-Item -Force -Path $ToDelIPv4}


<#	Check to make sure files deleted  #>
If ((Test-Path $ToAddIPv4) -or (Test-Path $ToDelIPv4)){
	VerboseOutput "ERROR : Failed to delete old ToDelIPv4.csv and/or ToAddIPv4.csv"
	VerboseOutput "ERROR : Quitting Script"
	EmailOutput "GeoIP update failed to delete old files. See error log."
	If (($AttachDebugLog) -and (((Get-Item $DebugLog).length/1MB) -gt $MaxAttachmentSize)){
		EmailOutput "Debug log too large to email. Please see file in GeoLite2SQL script folder."
	}
	EmailResults
	Exit
}

New-Item -Path $MMcsv
New-Item -Path $DBcsv
New-Item -Path $ToAddIPv4
New-Item -Path $ToDelIPv4

STILL SOME ISSUE in COMPARING AND ADDING TO DB

Code: Select all

PS C:\Users\NSP.Admin> C:\Scripts\GeoIP\GeoLite2SQL.ps1


    Directory: C:\Scripts\GeoIP


Mode                LastWriteTime         Length Name                                                        
----                -------------         ------ ----                                                        
-a----       26/03/2021  05:02 PM              0 DebugLog.log                                                
17:02:45 : 00:00 : GeoIP Update Start
17:02:45 : 00:00 : Deleting old files


    Directory: C:\Scripts\GeoIP\Script-Created-Files


Mode                LastWriteTime         Length Name                                                        
----                -------------         ------ ----                                                        
-a----       26/03/2021  05:02 PM              0 CSV-MM.csv                                                  
-a----       26/03/2021  05:02 PM              0 CSV-DB.csv                                                  
-a----       26/03/2021  05:02 PM              0 ToAddIPv4.csv                                               
-a----       26/03/2021  05:02 PM              0 ToDelIPv4.csv                                               
17:02:45 : 00:00 : Downloading MaxMind data
17:02:51 : 00:06 : Renaming MaxMind data folder
17:02:52 : 00:00 : Querying number of records in database before starting : 0 records
17:02:52 : 00:00 : Loading MaxMind CountryBlocks csv
17:03:03 : 00:11 : MaxMind CSV Loaded
17:03:03 : 00:00 : Exporting MaxMind data to reduced csv for comparison
17:03:39 : 00:35 : Reduced csv exported
17:03:39 : 00:00 : 322175 Records in MaxMind CountryBlocks csv
17:03:39 : 00:00 : Loading entries from database for comparison to MaxMind data
17:03:39 : 00:00 : Database entries Loaded
17:03:39 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:216 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.C 
   ompareObjectCommand
 
17:03:47 : 00:08 : Comparison completed
17:03:47 : 00:00 : Preparing to delete MaxMind removed records from database
17:03:47 : 00:00 : Csv loaded, ready to delete records from database
17:03:47 : 00:00 : Finished deleting records from database
17:03:47 : 00:00 : Preparing to add new records to database from comparison csv
17:03:47 : 00:00 : Csv loaded, ready to add updated records to database
17:03:47 : 00:00 : Finished adding updated records to database
17:03:47 : 00:00 : Loading updated MaxMind country name CSV
17:03:47 : 00:00 : Country name csv loaded, ready to update records in database
17:03:49 : 00:02 : Finished updating country name records in database
17:03:49 : 00:00 : Database update complete - preparing email report.
17:03:49 : 00:00 :  
17:03:49 : 00:00 :         : (A) Records in database prior to update
17:03:49 : 00:00 :         : (B) Records tabulated to be removed from database
17:03:49 : 00:00 :      -1 : (C) Records tabulated to be inserted into database
17:03:49 : 00:00 : ======= :
17:03:49 : 00:00 :      -1 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
17:03:49 : 00:00 : ======= :
17:03:49 : 00:00 : 3,22,175 : Actual number of records in NEW IPV4 csv
17:03:49 : 00:00 : ======= :
17:03:49 : 00:00 :         : Queried number of records in database (should match NEW IPV4 csv)
17:03:49 : 00:00 :  
17:03:49 : 00:00 : GeoIP database update ***FAILED**. Record Count Mismatch
17:03:49 : 00:00 : Preparing email report.
17:03:49 : 00:00 : GeoIP update finish: 26/03/2021 05:03 PM
17:03:49 : 00:00 : Completed update in 1 minute 4 seconds 

LINE 216 is "Compare-Object -ReferenceObject $(Get-Content $DBcsv) -DifferenceObject $(Get-Content $MMcsv) | ForEach-Object {"

Database table is still empty

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-27 00:54

gotspatel wrote:
2021-03-26 13:37
STILL SOME ISSUE in COMPARING AND ADDING TO DB

Code: Select all

17:03:39 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:216 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.C 
   ompareObjectCommand
 
Try this in same location after "Check to make sure files deleted":

Code: Select all

New-Item -Path $MMcsv -Value '"network","geoname_id"'
New-Item -Path $DBcsv -Value '"network","geoname_id"'
New-Item -Path $ToAddIPv4 -Value '"network","geoname_id"'
New-Item -Path $ToDelIPv4 -Value '"network","geoname_id"'
This all revolves around the file being null. We're taking it step by step because I don't know how PS will react to each iteration. Before I thought just creating the file would work, which obviously didn't. I *think* if you add content to the files - as above - it should resolve the issue. If not, then add dummy data like this:

Code: Select all

New-Item -Path $DBcsv -Value '"network","geoname_id"'`n'"255.255.255.255/32","0"'

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-27 06:12

Code: Select all

New-Item -Path $MMcsv -Value '"network","geoname_id"'
New-Item -Path $DBcsv -Value '"network","geoname_id"'
New-Item -Path $ToAddIPv4 -Value '"network","geoname_id"'
New-Item -Path $ToDelIPv4 -Value '"network","geoname_id"'
RESULT:

Code: Select all

PS C:\Users\NSP.Admin> C:\Scripts\GeoIP\GeoLite2SQL.ps1


    Directory: C:\Scripts\GeoIP


Mode                LastWriteTime         Length Name                                                        
----                -------------         ------ ----                                                        
-a----       27/03/2021  09:31 AM              0 DebugLog.log                                                
09:31:04 : 00:00 : GeoIP Update Start
09:31:04 : 00:00 : Deleting old files


    Directory: C:\Scripts\GeoIP\Script-Created-Files


Mode                LastWriteTime         Length Name                                                        
----                -------------         ------ ----                                                        
-a----       27/03/2021  09:31 AM             22 CSV-MM.csv                                                  
-a----       27/03/2021  09:31 AM             22 CSV-DB.csv                                                  
-a----       27/03/2021  09:31 AM             22 ToAddIPv4.csv                                               
-a----       27/03/2021  09:31 AM             22 ToDelIPv4.csv                                               
09:31:05 : 00:00 : Downloading MaxMind data
09:31:11 : 00:06 : Renaming MaxMind data folder
09:31:11 : 00:00 : Querying number of records in database before starting : 0 records
09:31:11 : 00:00 : Loading MaxMind CountryBlocks csv
09:31:23 : 00:11 : MaxMind CSV Loaded
09:31:23 : 00:00 : Exporting MaxMind data to reduced csv for comparison
09:31:58 : 00:35 : Reduced csv exported
09:31:58 : 00:00 : 322175 Records in MaxMind CountryBlocks csv
09:31:58 : 00:00 : Loading entries from database for comparison to MaxMind data
09:31:58 : 00:00 : Database entries Loaded
09:31:58 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:202 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.C 
   ompareObjectCommand
 
09:32:06 : 00:08 : Comparison completed
09:32:06 : 00:00 : Preparing to delete MaxMind removed records from database
09:32:06 : 00:00 : Csv loaded, ready to delete records from database
09:32:06 : 00:00 : Finished deleting records from database
09:32:06 : 00:00 : Preparing to add new records to database from comparison csv
09:32:06 : 00:00 : Csv loaded, ready to add updated records to database
09:32:06 : 00:00 : Finished adding updated records to database
09:32:06 : 00:00 : Loading updated MaxMind country name CSV
09:32:06 : 00:00 : Country name csv loaded, ready to update records in database
09:32:09 : 00:02 : Finished updating country name records in database
09:32:09 : 00:00 : Database update complete - preparing email report.
09:32:09 : 00:00 :  
09:32:09 : 00:00 :         : (A) Records in database prior to update
09:32:09 : 00:00 :       1 : (B) Records tabulated to be removed from database
09:32:09 : 00:00 :         : (C) Records tabulated to be inserted into database
09:32:09 : 00:00 : ======= :
09:32:09 : 00:00 :      -1 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
09:32:09 : 00:00 : ======= :
09:32:09 : 00:00 : 3,22,175 : Actual number of records in NEW IPV4 csv
09:32:09 : 00:00 : ======= :
09:32:09 : 00:00 :         : Queried number of records in database (should match NEW IPV4 csv)
09:32:09 : 00:00 :  
09:32:09 : 00:00 : GeoIP database update ***FAILED**. Record Count Mismatch
09:32:09 : 00:00 : Preparing email report.
09:32:09 : 00:00 : GeoIP update finish: 27/03/2021 09:32 AM
09:32:09 : 00:00 : Completed update in 1 minute 4 seconds 



This all revolves around the file being null. We're taking it step by step because I don't know how PS will react to each iteration. Before I thought just creating the file would work, which obviously didn't. I *think* if you add content to the files - as above - it should resolve the issue. If not, then add dummy data like this:

Code: Select all

New-Item -Path $DBcsv -Value '"network","geoname_id"'`n'"255.255.255.255/32","0"'
[/quote]

RESULT:

Code: Select all

PS C:\Users\NSP.Admin> C:\Scripts\GeoIP\GeoLite2SQL.ps1


    Directory: C:\Scripts\GeoIP


Mode                LastWriteTime         Length Name                                                        
----                -------------         ------ ----                                                        
-a----       27/03/2021  09:38 AM              0 DebugLog.log                                                
09:38:29 : 00:00 : GeoIP Update Start
09:38:29 : 00:00 : Deleting old files


    Directory: C:\Scripts\GeoIP\Script-Created-Files


Mode                LastWriteTime         Length Name                                                        
----                -------------         ------ ----                                                        
-a----       27/03/2021  09:38 AM             22 CSV-MM.csv                                                  
New-Item : A positional parameter cannot be found that accepts argument 'n"255.255.255.255/32","0"'.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:109 char:1
+ New-Item -Path $DBcsv -Value '"network","geoname_id"'''n'"255.255.255 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [New-Item], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.NewItemCommand
 
-a----       27/03/2021  09:38 AM             22 ToAddIPv4.csv                                               
-a----       27/03/2021  09:38 AM             22 ToDelIPv4.csv                                               
09:38:30 : 00:00 : Downloading MaxMind data
09:38:35 : 00:05 : Renaming MaxMind data folder
09:38:35 : 00:00 : Querying number of records in database before starting : 0 records
09:38:35 : 00:00 : Loading MaxMind CountryBlocks csv
09:38:55 : 00:19 : MaxMind CSV Loaded
09:38:55 : 00:00 : Exporting MaxMind data to reduced csv for comparison
09:39:45 : 00:50 : Reduced csv exported
09:39:45 : 00:00 : 322175 Records in MaxMind CountryBlocks csv
09:39:45 : 00:00 : Loading entries from database for comparison to MaxMind data
09:39:45 : 00:00 : Database entries Loaded
09:39:45 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:202 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.C 
   ompareObjectCommand
 
09:39:55 : 00:10 : Comparison completed
09:39:55 : 00:00 : Preparing to delete MaxMind removed records from database
09:39:55 : 00:00 : Csv loaded, ready to delete records from database
09:39:55 : 00:00 : Finished deleting records from database
09:39:55 : 00:00 : Preparing to add new records to database from comparison csv
09:39:55 : 00:00 : Csv loaded, ready to add updated records to database
09:39:55 : 00:00 : Finished adding updated records to database
09:39:55 : 00:00 : Loading updated MaxMind country name CSV
09:39:55 : 00:00 : Country name csv loaded, ready to update records in database
09:39:57 : 00:01 : Finished updating country name records in database
09:39:57 : 00:00 : Database update complete - preparing email report.
09:39:57 : 00:00 :  
09:39:57 : 00:00 :         : (A) Records in database prior to update
09:39:57 : 00:00 :       1 : (B) Records tabulated to be removed from database
09:39:57 : 00:00 :         : (C) Records tabulated to be inserted into database
09:39:57 : 00:00 : ======= :
09:39:57 : 00:00 :      -1 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
09:39:57 : 00:00 : ======= :
09:39:57 : 00:00 : 3,22,175 : Actual number of records in NEW IPV4 csv
09:39:57 : 00:00 : ======= :
09:39:57 : 00:00 :         : Queried number of records in database (should match NEW IPV4 csv)
09:39:57 : 00:00 :  
09:39:57 : 00:00 : GeoIP database update ***FAILED**. Record Count Mismatch
09:39:57 : 00:00 : Preparing email report.
09:39:57 : 00:00 : GeoIP update finish: 27/03/2021 09:39 AM
09:39:57 : 00:00 : Completed update in 1 minute 27 seconds 


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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-27 18:56

Try Add-Content. I just tried it (simple add content test) and it worked fine. Creates the file and adds the content.

Code: Select all

Add-Content -Path $MMcsv -Value '"network","geoname_id"'
Add-Content -Path $DBcsv -Value '"network","geoname_id"'
Add-Content -Path $ToAddIPv4 -Value '"network","geoname_id"'
Add-Content -Path $ToDelIPv4 -Value '"network","geoname_id"'
With content - ANY content - the comparison function should work.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-28 07:10

Sorry to bug you but still problem is here

Code: Select all


Compare-Object -ReferenceObject $(Get-Content $DBcsv) -DifferenceObject $(Get-Content $MMcsv) | ForEach-Object {
THE LOG

Code: Select all

PS C:\Users\NSP.Admin> C:\Scripts\GeoIP\GeoLite2SQL.ps1


    Directory: C:\Scripts\GeoIP


Mode                LastWriteTime         Length Name                                                                                                                                                                                          
----                -------------         ------ ----                                                                                                                                                                                          
d-----       28/03/2021  10:34 AM                Script-Created-Files                                                                                                                                                                          
-a----       28/03/2021  10:34 AM              0 DebugLog.log                                                                                                                                                                                  
10:34:19 : 00:00 : GeoIP Update Start
10:34:19 : 00:00 : Deleting old files
10:34:19 : 00:00 : Downloading MaxMind data
10:34:25 : 00:06 : Renaming MaxMind data folder
10:34:26 : 00:00 : Querying number of records in database before starting : 0 records
10:34:26 : 00:00 : Loading MaxMind CountryBlocks csv
10:34:37 : 00:11 : MaxMind CSV Loaded
10:34:37 : 00:00 : Exporting MaxMind data to reduced csv for comparison
10:35:12 : 00:35 : Reduced csv exported
10:35:12 : 00:00 : 322175 Records in MaxMind CountryBlocks csv
10:35:12 : 00:00 : Loading entries from database for comparison to MaxMind data
10:35:12 : 00:00 : Database entries Loaded
10:35:12 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:202 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand
 
10:35:20 : 00:07 : Comparison completed
10:35:20 : 00:00 : Preparing to delete MaxMind removed records from database
10:35:20 : 00:00 : Csv loaded, ready to delete records from database
10:35:20 : 00:00 : Finished deleting records from database
10:35:20 : 00:00 : Preparing to add new records to database from comparison csv
10:35:20 : 00:00 : Csv loaded, ready to add updated records to database
10:35:20 : 00:00 : Finished adding updated records to database
10:35:20 : 00:00 : Loading updated MaxMind country name CSV
10:35:20 : 00:00 : Country name csv loaded, ready to update records in database
10:35:22 : 00:02 : Finished updating country name records in database
10:35:22 : 00:00 : Database update complete - preparing email report.
10:35:22 : 00:00 :  
10:35:22 : 00:00 :         : (A) Records in database prior to update
10:35:22 : 00:00 :       1 : (B) Records tabulated to be removed from database
10:35:22 : 00:00 :         : (C) Records tabulated to be inserted into database
10:35:22 : 00:00 : ======= :
10:35:22 : 00:00 :      -1 : Tabulated (A - B + C) number of records (should match NEW IPV4 csv)
10:35:22 : 00:00 : ======= :
10:35:22 : 00:00 : 3,22,175 : Actual number of records in NEW IPV4 csv
10:35:22 : 00:00 : ======= :
10:35:22 : 00:00 :         : Queried number of records in database (should match NEW IPV4 csv)
10:35:22 : 00:00 :  
10:35:22 : 00:00 : GeoIP database update ***FAILED**. Record Count Mismatch
10:35:23 : 00:00 : Preparing email report.
10:35:23 : 00:00 : GeoIP update finish: 28/03/2021 10:35 AM
10:35:23 : 00:00 : Completed update in 1 minute 3 seconds 



PS C:\Users\NSP.Admin> 


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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-28 13:49

gotspatel wrote:
2021-03-28 07:10
Sorry to bug you but still problem is here
Can you confirm these files exist with the headers intact?

Code: Select all

Add-Content -Path $MMcsv -Value '"network","geoname_id"'
Add-Content -Path $DBcsv -Value '"network","geoname_id"'
Add-Content -Path $ToAddIPv4 -Value '"network","geoname_id"'
Add-Content -Path $ToDelIPv4 -Value '"network","geoname_id"'
They should be in a folder called "Script-Created-Files" within the script root folder. Do the folder and files exist? Does each of those files contain

Code: Select all

"network","geoname_id"
C:\path\to\geolite2sql\script\Script-Created-Files\CSV-DB.csv

If not then it could be a permissions issue. Try running as admin or moving the script to a non-system location.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-28 14:06

palinka wrote:
2021-03-28 13:49
gotspatel wrote:
2021-03-28 07:10
Sorry to bug you but still problem is here
Can you confirm these files exist with the headers intact?

Code: Select all

Add-Content -Path $MMcsv -Value '"network","geoname_id"'
Add-Content -Path $DBcsv -Value '"network","geoname_id"'
Add-Content -Path $ToAddIPv4 -Value '"network","geoname_id"'
Add-Content -Path $ToDelIPv4 -Value '"network","geoname_id"'
They should be in a folder called "Script-Created-Files" within the script root folder. Do the folder and files exist? Does each of those files contain

Code: Select all

"network","geoname_id"
C:\path\to\geolite2sql\script\Script-Created-Files\CSV-DB.csv

If not then it could be a permissions issue. Try running as admin or moving the script to a non-system location.
All Files are being created with the "network","geoname_id" EXCEPT "CSV-DB.csv" is created without the headers.

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-03-28 16:15

@Palinka ...

How different is this from what your code does?

I gave up on the PowerShell scripting... :wink: :oops:

Needs the MaxMind converter: https://github.com/maxmind/geoip2-csv-c ... r/releases

File: import.cmd

Code: Select all

echo %DATE% %TIME% >> import.log
echo. >> import.log
curl -G https://download.maxmind.com/app/geoip_download -d "edition_id=GeoLite2-Country-CSV" -d "license_key=KEYKEYKEY" -d "suffix=zip" -o GeoLite2-Country-CSV.zip >> import.log
7z e -y GeoLite2-Country-CSV.zip >> import.log

:country
fc GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.csv >NUL && goto location || goto newcountry >> import.log

:newcountry
geoip2-csv-converter.exe -block-file=GeoLite2-Country-Blocks-IPv4.csv -output-file=GeoCountry.csv -include-integer-range
copy /y GeoLite2-Country-Locations-en.csv GeoLocation2.csv >> import.log
C:\MariaDB\bin\mysqlimport -h 192.168.0.5 -P 3306 -u script -p#PASSWORD --local -v -i -d --ignore-lines=1 --fields-terminated-by="," --lines-terminated-by="\n" hmailserver_extra GeoCountry.csv >> import.log

:location
fc GeoLite2-Country-Locations-en.csv GeoLocation.csv >NUL && goto same || goto newlocation >> import.log

:newlocation
copy /y GeoLite2-Country-Locations-en.csv GeoLocation.csv >> import.log
C:\MariaDB\bin\mysqlimport -h 192.168.0.5 -P 3306 -uscript -p#PASSWORD --local -v -i -d --ignore-lines=1 --fields-terminated-by="," --lines-terminated-by="\n" hmailserver_extra GeoLocation.csv >> import.log

:same
echo Exiting ... >> import.log
echo. >> import.log
exit 0

Code: Select all

Table       Create Table                                              
----------  ----------------------------------------------------------
geocountry  CREATE TABLE geocountry (                               
              network_start_integer          int(11)      DEFAULT NULL,           
              network_last_integer           int(11)      DEFAULT NULL,            
              geoname_id                     int(11)      DEFAULT NULL,                      
              registered_country_geoname_id  int(11)      DEFAULT NULL,   
              represented_country_geoname_id int(11)      DEFAULT NULL,  
              is_anonymous_proxy             tinyint(1)   DEFAULT NULL,           
              is_satellite_provider          tinyint(1)   DEFAULT NULL,        
              KEY geoname_id (geoname_id),                        
              KEY network_start_integer (network_start_integer),  
              KEY network_last_integer (network_last_integer)     
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8                      

Code: Select all

Table        Create Table                                       
-----------  ---------------------------------------------------
geolocation  CREATE TABLE geolocation (                       
               geoname_id                    int(11)      DEFAULT NULL,               
               locale_code                   varchar(192) DEFAULT NULL,         
               continent_code                varchar(2)   DEFAULT NULL,        
               continent_name                varchar(192) DEFAULT NULL,      
               country_iso_code              varchar(2)   DEFAULT NULL,      
               country_name                  varchar(192) DEFAULT NULL,        
               is_in_european_union          tinyint(1)   DEFAULT NULL,  
               KEY geoname_id (geoname_id)                  
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8               
Querying the tables you need to join them by "geoname_id"

Code: Select all

SELECT * FROM geolocation 
INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id 
WHERE network_start_integer <= INET_ATON('1.2.3.4') AND INET_ATON('1.2.3.4') <= network_last_integer;
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-28 23:07

gotspatel wrote:
2021-03-28 14:06
All Files are being created with the "network","geoname_id" EXCEPT "CSV-DB.csv" is created without the headers.
This is clearly something on your end. Try this powershell. Put the script in some place that's NOT where you're running geolite2sql:

Code: Select all

$DBcsv = "$PSScriptRoot\Script-Created-Files\CSV-DB.csv"

<#	Create folder for temporary script files if it doesn't exist  #>
If (-not(Test-Path "$PSScriptRoot\Script-Created-Files")) {
	md "$PSScriptRoot\Script-Created-Files"
}

<#  Create new file and add stuff to it  #>
New-Item -Path $DBcsv
'"network","geoname_id"' | Out-File $DBcsv -Encoding ASCII -Append
'"255.255.255.255/32","0"' | Out-File $DBcsv -Encoding ASCII -Append

<#  2nd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# New-Item -Path $DBcsv -Value '"network","geoname_id"'

<#  3rd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# Add-Content -Path $DBcsv -Value '"network","geoname_id"'
For me it works on all 3 tries.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-28 23:26

SorenR wrote:
2021-03-28 16:15
@Palinka ...

How different is this from what your code does?
Mine only does country
I gave up on the PowerShell scripting... :wink: :oops:
powershell gets you smiles from all the ladies, and it makes you smell nice.
Needs the MaxMind converter: https://github.com/maxmind/geoip2-csv-c ... r/releases

File: import.cmd

Code: Select all

echo %DATE% %TIME% >> import.log
echo. >> import.log
curl -G https://download.maxmind.com/app/geoip_download -d "edition_id=GeoLite2-Country-CSV" -d "license_key=KEYKEYKEY" -d "suffix=zip" -o GeoLite2-Country-CSV.zip >> import.log
7z e -y GeoLite2-Country-CSV.zip >> import.log

:country
fc GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.csv >NUL && goto location || goto newcountry >> import.log

:newcountry
geoip2-csv-converter.exe -block-file=GeoLite2-Country-Blocks-IPv4.csv -output-file=GeoCountry.csv -include-integer-range
copy /y GeoLite2-Country-Locations-en.csv GeoLocation2.csv >> import.log
C:\MariaDB\bin\mysqlimport -h 192.168.0.5 -P 3306 -u script -p#PASSWORD --local -v -i -d --ignore-lines=1 --fields-terminated-by="," --lines-terminated-by="\n" hmailserver_extra GeoCountry.csv >> import.log

:location
fc GeoLite2-Country-Locations-en.csv GeoLocation.csv >NUL && goto same || goto newlocation >> import.log

:newlocation
copy /y GeoLite2-Country-Locations-en.csv GeoLocation.csv >> import.log
C:\MariaDB\bin\mysqlimport -h 192.168.0.5 -P 3306 -uscript -p#PASSWORD --local -v -i -d --ignore-lines=1 --fields-terminated-by="," --lines-terminated-by="\n" hmailserver_extra GeoLocation.csv >> import.log

:same
echo Exiting ... >> import.log
echo. >> import.log
exit 0
What is that old fashioned stuff anyway? :lol:

But seriously, my script updates the database weekly - which is the frequency of maxmind updates. Instead of deleting and re-inserting 330k rows, which takes hours, my script does it in a few minutes because it looks only for changes between the db and the new maxmind release.

But its been ages since I started from scratch, so its not unsurprising that a fresh install might run into issues. You know, software development is a bit solipsistic - if it doesn't happen to me, it simply doesn't happen. :mrgreen:

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-03-29 00:59

palinka wrote:
2021-03-28 23:26
What is that old fashioned stuff anyway? :lol:

But seriously, my script updates the database weekly - which is the frequency of maxmind updates. Instead of deleting and re-inserting 330k rows, which takes hours, my script does it in a few minutes because it looks only for changes between the db and the new maxmind release.

But its been ages since I started from scratch, so its not unsurprising that a fresh install might run into issues. You know, software development is a bit solipsistic - if it doesn't happen to me, it simply doesn't happen. :mrgreen:
Well... MySQL is running on Windows Server 2003 R2 32-bit E6550 Core2 Duo 2.33 MHz. The "script" is run in a DOS box on my Windows 10 Home 64-bit i7-6500U CPU @ 2.50GHz. I'm using Gigabit networking and there are two switches between machines.

Execution as described in the CMD file (I added an Echo date time in the end).

Code: Select all

29-03-2021  0:39:32,09 
 

7-Zip 19.00 (x64) : Copyright (c) 1999-2018 Igor Pavlov : 2019-02-21

Scanning the drive for archives:
1 file, 1873566 bytes (1830 KiB)

Extracting archive: GeoLite2-Country-CSV.zip
--
Path = GeoLite2-Country-CSV.zip
Type = zip
Physical Size = 1873566

Everything is Ok

Files: 13
Size:       15502070
Compressed: 1873566
        1 file(s) copied.
Connecting to 192.168.0.5
Selecting database hmailserver_extra
Deleting the old data from table GeoCountry
Loading data from LOCAL file: C:/Udvikling/MaxMind/GeoCountry.csv into GeoCountry
hmailserver_extra.GeoCountry: Records: 322175  Deleted: 0  Skipped: 0  Warnings: 681649
Disconnecting from 192.168.0.5
        1 file(s) copied.
Connecting to 192.168.0.5
Selecting database hmailserver_extra
Deleting the old data from table GeoLocation
Loading data from LOCAL file: C:/Udvikling/MaxMind/GeoLocation.csv into GeoLocation
hmailserver_extra.GeoLocation: Records: 252  Deleted: 0  Skipped: 0  Warnings: 2
Disconnecting from 192.168.0.5
Exiting ... 
 
29-03-2021  0:39:47,13 
 
15 seconds Old school at its best :mrgreen:

Yes I noticed the warnings too - no idea what they say ;-)

I did shoot myself in the foot as my primary mailserver is 32-bit and the MaxMind "network" converter is 64-bit ... Oh well, gotta run it from my secondary server then.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-03-29 01:29

Oh WOW... Altered table from int(11) to bigint(20) on the network_start and network_end columns...

Reduced warnings to 324153 ... I kid you not - there is NO WAY to list the warnings using mysqlimport :roll:

This time a bit longer

1:21:55,55 to 1:25:06,59
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-29 02:29

SorenR wrote:
2021-03-29 00:59
15 seconds Old school at its best :mrgreen:
There's no school like the old school... :mrgreen:

I don't know why I didn't think of that before: mysqlimport. I'm still a noob. New School. :oops:

I'm going to have to rethink the whole thing now.
SorenR wrote:
2021-03-29 01:29
1:21:55,55 to 1:25:06,59
Don't you sleep?

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-29 02:43

SorenR wrote:
2021-03-28 16:15

Code: Select all

SELECT * FROM geolocation 
INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id 
WHERE network_start_integer <= INET_ATON('1.2.3.4') AND INET_ATON('1.2.3.4') <= network_last_integer;
One thing I did find that could be useful to you was that dividing the query into a subquery with a limit of 1 on the subquery can speed up query times.

Untested on your tables:

Code: Select all

SELECT * 
FROM 
(
	SELECT *
	FROM geocountry
	WHERE INET_ATON('1.2.3.4') <= network_last_integer
	LIMIT 1
)
INNER JOIN geolocation ON geocountry.geoname_id = geolocation.geoname_id 
WHERE geocountry.network_start_integer <= INET_ATON('1.2.3.4');

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-29 06:08

palinka wrote:
2021-03-28 23:07
gotspatel wrote:
2021-03-28 14:06
All Files are being created with the "network","geoname_id" EXCEPT "CSV-DB.csv" is created without the headers.
This is clearly something on your end. Try this powershell. Put the script in some place that's NOT where you're running geolite2sql:

Code: Select all

$DBcsv = "$PSScriptRoot\Script-Created-Files\CSV-DB.csv"

<#	Create folder for temporary script files if it doesn't exist  #>
If (-not(Test-Path "$PSScriptRoot\Script-Created-Files")) {
	md "$PSScriptRoot\Script-Created-Files"
}

<#  Create new file and add stuff to it  #>
New-Item -Path $DBcsv
'"network","geoname_id"' | Out-File $DBcsv -Encoding ASCII -Append
'"255.255.255.255/32","0"' | Out-File $DBcsv -Encoding ASCII -Append

<#  2nd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# New-Item -Path $DBcsv -Value '"network","geoname_id"'

<#  3rd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# Add-Content -Path $DBcsv -Value '"network","geoname_id"'
For me it works on all 3 tries.
This creates the file with data, no problem in that.

I downloaded fresh files from your git and set up again with the code to create new files, new files are created checked and found that when the comparison starts somehow the file CSV-DB.csv is wiped clean again and hence there is no data to compare which is throwing the error.

Code: Select all

09:31:30 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:197 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand
 
09:31:40 : 00:09 : Comparison completed

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-29 07:31

gotspatel wrote:
2021-03-29 06:08
palinka wrote:
2021-03-28 23:07
gotspatel wrote:
2021-03-28 14:06
All Files are being created with the "network","geoname_id" EXCEPT "CSV-DB.csv" is created without the headers.
This is clearly something on your end. Try this powershell. Put the script in some place that's NOT where you're running geolite2sql:

Code: Select all

$DBcsv = "$PSScriptRoot\Script-Created-Files\CSV-DB.csv"

<#	Create folder for temporary script files if it doesn't exist  #>
If (-not(Test-Path "$PSScriptRoot\Script-Created-Files")) {
	md "$PSScriptRoot\Script-Created-Files"
}

<#  Create new file and add stuff to it  #>
New-Item -Path $DBcsv
'"network","geoname_id"' | Out-File $DBcsv -Encoding ASCII -Append
'"255.255.255.255/32","0"' | Out-File $DBcsv -Encoding ASCII -Append

<#  2nd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# New-Item -Path $DBcsv -Value '"network","geoname_id"'

<#  3rd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# Add-Content -Path $DBcsv -Value '"network","geoname_id"'
For me it works on all 3 tries.
This creates the file with data, no problem in that.

I downloaded fresh files from your git and set up again with the code to create new files, new files are created checked and found that when the comparison starts somehow the file CSV-DB.csv is wiped clean again and hence there is no data to compare which is throwing the error.

Code: Select all

09:31:30 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:197 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand
 
09:31:40 : 00:09 : Comparison completed
At this point, you're probably better off using Soren's. Its clearly orders of magnitude faster than mine.

When I have time, I'm going make the switch, but use powershell instead of that clunky old school stuff.

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-03-29 10:02

palinka wrote:
2021-03-29 07:31
gotspatel wrote:
2021-03-29 06:08
palinka wrote:
2021-03-28 23:07


This is clearly something on your end. Try this powershell. Put the script in some place that's NOT where you're running geolite2sql:

Code: Select all

$DBcsv = "$PSScriptRoot\Script-Created-Files\CSV-DB.csv"

<#	Create folder for temporary script files if it doesn't exist  #>
If (-not(Test-Path "$PSScriptRoot\Script-Created-Files")) {
	md "$PSScriptRoot\Script-Created-Files"
}

<#  Create new file and add stuff to it  #>
New-Item -Path $DBcsv
'"network","geoname_id"' | Out-File $DBcsv -Encoding ASCII -Append
'"255.255.255.255/32","0"' | Out-File $DBcsv -Encoding ASCII -Append

<#  2nd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# New-Item -Path $DBcsv -Value '"network","geoname_id"'

<#  3rd version: Now delete the files and folder, then comment out the section directly above and uncomment this and try again  #>
# Add-Content -Path $DBcsv -Value '"network","geoname_id"'
For me it works on all 3 tries.
This creates the file with data, no problem in that.

I downloaded fresh files from your git and set up again with the code to create new files, new files are created checked and found that when the comparison starts somehow the file CSV-DB.csv is wiped clean again and hence there is no data to compare which is throwing the error.

Code: Select all

09:31:30 : 00:00 : Comparing updated MaxMind data to database
Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\Scripts\GeoIP\GeoLite2SQL.ps1:197 char:35
+         Compare-Object -ReferenceObject $(Get-Content $DBcsv) -Differ ...
+                                         ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand
 
09:31:40 : 00:09 : Comparison completed
At this point, you're probably better off using Soren's. Its clearly orders of magnitude faster than mine.

When I have time, I'm going make the switch, but use powershell instead of that clunky old school stuff.
I found a few bugs last night, still working on getting rid of the warnings but they do not seem to interfere...

Will post update later today.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-03-29 12:14

palinka wrote:
2021-03-29 02:43
Untested on your tables:

Code: Select all

SELECT * 
FROM 
(
	SELECT *
	FROM geocountry
	WHERE INET_ATON('1.2.3.4') <= network_last_integer
	LIMIT 1
)
INNER JOIN geolocation ON geocountry.geoname_id = geolocation.geoname_id 
WHERE geocountry.network_start_integer <= INET_ATON('1.2.3.4');
Also, I forgot. Making column network_last_integer the primary key was the trick to making queries lightning fast.

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-03-29 14:57

If you are like me where PowerShell is a bit like "Yeah, I'll get into it when I get some spare time" then perhaps try this old school approach. :mrgreen:

Ok, I've been working on indexes to optimize stuff and field types to get rid of all the warning during import.

This is the "local" version where import is done on the local database server. I have a "remote" version if database server is a 32 bit server since the GeoLite2 convertes is 64-bit only.

https://github.com/maxmind/geoip2-csv-c ... r/releases

File: import.cmd

Code: Select all

@echo off

echo START %DATE% %TIME% >> import.log
echo. >> import.log

set PATH=%PATH%;"C:\MariaDB\Bin";"C:\Program Files\7-zip"
set IKEY=Enter key from MaxMind here
set IUSR=DB user with rights to DATA
set IPAS=Password for above user
set DATA=Database to use
REM Remove any spaces on both sides of the "=" that you just entered! It's a DOS/Windows thing.

curl -G https://download.maxmind.com/app/geoip_download -d "edition_id=GeoLite2-Country-CSV" -d "license_key=%IKEY%" -d "suffix=zip" -o GeoLite2-Country-CSV.zip 1>>import.log 2>&1
7z e -y GeoLite2-Country-CSV.zip 1>>import.log 2>&1

:country
fc GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.csv >NUL && goto location || goto newcountry

:newcountry
geoip2-csv-converter.exe -block-file=GeoLite2-Country-Blocks-IPv4.csv -output-file=GeoCountry.csv -include-integer-range
copy /y GeoLite2-Country-Locations-en.csv GeoLocation2.csv 1>>import.log 2>&1
mysqlimport -u %IUSR% -p%IPAS% --local -v -r --ignore-lines=1 --fields-terminated-by="," --fields-optionally-enclosed-by="""" --lines-terminated-by="\n" %DATA% GeoCountry.csv 1>>import.log 2>&1

:location
fc GeoLite2-Country-Locations-en.csv GeoLocation.csv >NUL && goto same || goto newlocation

:newlocation
copy /y GeoLite2-Country-Locations-en.csv GeoLocation.csv 1>>import.log 2>&1
mysqlimport -u %IUSR% -p%IPAS% --local -v -r --ignore-lines=1 --fields-terminated-by="," --fields-optionally-enclosed-by="""" --lines-terminated-by="\n" %DATA% GeoLocation.csv 1>>import.log 2>&1

:same
echo FINISH %DATE% %TIME% >> import.log
echo. >> import.log
goto exit

    SELECT * FROM geolocation INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id
    WHERE network_start_integer <= INET_ATON('8.8.8.8') AND INET_ATON('8.8.8.8') <= network_last_integer;

Table       Create Table
----------  -----------------------------------------------------------------
geocountry  CREATE TABLE geocountry (
              network_start_integer bigint(20) NOT NULL DEFAULT '0',
              network_last_integer bigint(20) NOT NULL DEFAULT '0',
              geoname_id varchar(20) NOT NULL DEFAULT '',
              registered_country_geoname_id varchar(20) DEFAULT NULL,
              represented_country_geoname_id varchar(20) DEFAULT NULL,
              is_anonymous_proxy varchar(1) DEFAULT NULL,
              is_satellite_provider varchar(1) DEFAULT NULL,
              PRIMARY KEY (network_start_integer,network_last_integer),
              KEY network_start_integer (network_start_integer),
              KEY network_last_integer (network_last_integer)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8


Table        Create Table
-----------  ---------------------------------------------------
geolocation  CREATE TABLE geolocation (
               geoname_id varchar(20) NOT NULL DEFAULT '',
               locale_code varchar(192) DEFAULT NULL,
               continent_code varchar(2) DEFAULT NULL,
               continent_name varchar(192) DEFAULT NULL,
               country_iso_code varchar(2) DEFAULT NULL,
               country_name varchar(192) DEFAULT NULL,
               is_in_european_union varchar(1) DEFAULT NULL,
               PRIMARY KEY (geoname_id)
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


:exit
exit 0
Script...

Code: Select all

How to use:

If (InStr("|DK|GL|FO|", GeoLite2(oClient.IPAddress)) = 0) Then EventLog.Write( oClient.IPAddress & " is outside my comfort zone ;-)" )


'******************************************************************************************************************************
'********** GeoLite2 lookup (MySQL)                                                                                  **********
'******************************************************************************************************************************

' geocountry  CREATE TABLE geocountry (
'               network_start_integer bigint(20) NOT NULL DEFAULT '0',
'               network_last_integer bigint(20) NOT NULL DEFAULT '0',
'               geoname_id varchar(20) NOT NULL DEFAULT '',
'               registered_country_geoname_id varchar(20) DEFAULT NULL,
'               represented_country_geoname_id varchar(20) DEFAULT NULL,
'               is_anonymous_proxy varchar(1) DEFAULT NULL,
'               is_satellite_provider varchar(1) DEFAULT NULL,
'               PRIMARY KEY (network_start_integer,network_last_integer),
'               KEY network_start_integer (network_start_integer),
'               KEY network_last_integer (network_last_integer)
'             ) ENGINE=InnoDB DEFAULT CHARSET=utf8


' geolocation  CREATE TABLE geolocation (
'                geoname_id varchar(20) NOT NULL DEFAULT '',
'                locale_code varchar(192) DEFAULT NULL,
'                continent_code varchar(2) DEFAULT NULL,
'                continent_name varchar(192) DEFAULT NULL,
'                country_iso_code varchar(2) DEFAULT NULL,
'                country_name varchar(192) DEFAULT NULL,
'                is_in_european_union varchar(1) DEFAULT NULL,
'                PRIMARY KEY (geoname_id)
'              ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Function GeoLite2(strIP)

    Private Const DBDRVR  = "MySQL ODBC 5.3 Unicode Driver"
    Private Const DBUID   = "DB user with rights to DATA"
    Private Const DBPW    = "Password for above user"
    Private Const DBEXTRA = "Database to use"

    Dim i, a, strData
    Dim oRecord, oDB : Set oDB = CreateObject("ADODB.Connection")
    oDB.Open "DRIVER={" & DBDRVR & "};Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
    If oDB.State <> 1 Then
        EventLog.Write( "GeoLite2 - ERROR: Could not connect to database" )
        GeoLite2 = "VOID"
        Exit Function
    End If
    Set oRecord = oDB.Execute("SELECT * FROM geolocation INNER JOIN geocountry ON geocountry.geoname_id = geolocation.geoname_id WHERE network_start_integer <= INET_ATON('" & strIP & "') AND INET_ATON('" & strIP & "') <= network_last_integer;")
    Do Until oRecord.EOF
        If (oRecord("country_iso_code") <> "") Then
            If strData = "" Then
                strData = oRecord("country_iso_code")
            Else
                strData = strData & "," & oRecord("country_iso_code")
            End If
        End If
        oRecord.MoveNext
    Loop
    If (Trim(strData) <> "") Then
        GeoLite2 = strData
    Else
        GeoLite2 = "VOID"
    End If
    Set oRecord = Nothing
    oDB.Close
    Set oDB = Nothing
End Function
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-29 15:35

SorenR wrote:
2021-03-29 14:57
If you are like me where PowerShell is a bit like "Yeah, I'll get into it when I get some spare time" then perhaps try this old school approach. :mrgreen:

Ok, I've been working on indexes to optimize stuff and field types to get rid of all the warning during import.

This is the "local" version where import is done on the local database server. I have a "remote" version if database server is a 32 bit server since the GeoLite2 convertes is 64-bit only.

SNIP

Thanks for the old school approach. It works. :mrgreen:

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-03-29 16:03

Glad to be of service :mrgreen:

I actually found a bug, nothing major and if you only run the script once a week it does not matter... It's the "fc" file compare on one of the datasets that trigger mysqlimport also when downloaded data is not changed by comparing files that will never match. :roll:
Again, if you only run the script on wednesdays (or tuesdays) no change is required.

Database tables ... KEYS and also the PRIMARY KEY on the tables are there for two reasons. First to make lookups faster but Secondly ... because I use "-r" or "--replace" parameter on the mysqlimport commandline to maintain uniqueness of imported data.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-29 17:02

palinka wrote:
2021-03-28 23:07
gotspatel wrote:
2021-03-28 14:06
All Files are being created with the "network","geoname_id" EXCEPT "CSV-DB.csv" is created without the headers.
This is clearly something on your end. Try this powershell. Put the script in some place that's NOT where you're running geolite2sql:

FOUND THE ISSUE

The database table being EMPTY the comparison was not working.

WHAT I DID TO SOLVE.

1. Inserted a record manually with dummy data in the table geo_ip

NETWORK = 0
minip = 0.0.0.0
maxip = 0.0.0.0
geoname_id= 0
Countrycode =IN
Countryname= INDIA
minipaton=0
maxipaton=0


Hope it helps you in updating the codes.
Last edited by gotspatel on 2021-03-29 17:31, edited 1 time in total.

gotspatel
Senior user
Senior user
Posts: 347
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-03-29 17:03

SorenR wrote:
2021-03-29 16:03
Glad to be of service :mrgreen:

I actually found a bug, nothing major and if you only run the script once a week it does not matter... It's the "fc" file compare on one of the datasets that trigger mysqlimport also when downloaded data is not changed by comparing files that will never match. :roll:
Again, if you only run the script on wednesdays (or tuesdays) no change is required.

Database tables ... KEYS and also the PRIMARY KEY on the tables are there for two reasons. First to make lookups faster but Secondly ... because I use "-r" or "--replace" parameter on the mysqlimport commandline to maintain uniqueness of imported data.
YES I will be updating every wednesdays :D

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-06 18:17

palinka wrote:
2021-03-29 12:14
palinka wrote:
2021-03-29 02:43
Untested on your tables:

Code: Select all

SELECT * 
FROM 
(
	SELECT *
	FROM geocountry
	WHERE INET_ATON('1.2.3.4') <= network_last_integer
	LIMIT 1
)
INNER JOIN geolocation ON geocountry.geoname_id = geolocation.geoname_id 
WHERE geocountry.network_start_integer <= INET_ATON('1.2.3.4');
Also, I forgot. Making column network_last_integer the primary key was the trick to making queries lightning fast.

Finally got around to trying this out. Yes, its super ultra mega fast! :mrgreen:

Using this query is ~2 times faster:

Showing rows 0 - 0 (1 total, Query took 0.0041 seconds.)

Code: Select all

SELECT * 
FROM (
	SELECT * 
	FROM geocountry 
	WHERE INET_ATON('212.186.81.105') <= network_last_integer
	LIMIT 1
	) AS a 
INNER JOIN geolocations AS b on a.geoname_id = b.geoname_id
WHERE network_start_integer <= INET_ATON('212.186.81.105')
LIMIT 1;

Showing rows 0 - 0 (1 total, Query took 0.0098 seconds.)

Code: Select all

SELECT * FROM geolocations 
INNER JOIN geocountry ON geocountry.geoname_id = geolocations.geoname_id 
WHERE network_start_integer <= INET_ATON('212.186.81.105') AND INET_ATON('212.186.81.105') <= network_last_integer;
Setting network_last_integer to primary key didn't make a difference. Same results in the 0.004 seconds range. Couldn't get it down below 0.0040!

I'll have a modern, new-car-smell powershell in a while. :mrgreen:

Post Reply