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: 2695
Joined: 2017-09-12 17:57

Re: MaxMinds GeoIP for local MySQL

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

SorenR wrote:
2021-03-29 10:02
I found a few bugs last night, still working on getting rid of the warnings but they do not seem to interfere...
Did you find the source of the warnings?

How to make mysqlimport more verbose? I'm guessing it has something to do with empty fields?

16777216,16777471,2077456,2077456,,0,0 = 16777216,16777471,2077456,2077456,!!!EMPTY_FIELD!!!,0,0

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-04-06 19:01

palinka wrote:
2021-04-06 18:24
SorenR wrote:
2021-03-29 10:02
I found a few bugs last night, still working on getting rid of the warnings but they do not seem to interfere...
Did you find the source of the warnings?

How to make mysqlimport more verbose? I'm guessing it has something to do with empty fields?

16777216,16777471,2077456,2077456,,0,0 = 16777216,16777471,2077456,2077456,!!!EMPTY_FIELD!!!,0,0
Mysqlimport is NOT very informative. Do your SQL IMPORT in a SQL command windows to debug.. :roll:

Everywyere I have searched on the Interweb I find DBA's preferring JOIN over complicated queries... Comparing 0.004 seconds to 0.009 seconds will only matter if do millions of queries per minute.

Notice how I defined the primary keys. It needs to be this way if you want to import and "replace" and not "delete & import". Much faster.

New table definitions at the end.

Code: Select all

@echo off

echo START %DATE% %TIME%  1>>import.log 2>&1
echo.  1>>import.log 2>&1

set PATH=%PATH%;"C:\MariaDB\Bin";"C:\Program Files\7-zip"
set IKEY=KEYKEYKEY
set IUSR=DBUSER
set IPAS=DBPASSWORD
set DATA=hmailserver_extra

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 1>>import.log 2>&1

:newcountry
geoip2-csv-converter.exe -block-file=GeoLite2-Country-Blocks-IPv4.csv -output-file=GeoCountry.csv -include-integer-range 1>>import.log 2>&1
copy /y GeoLite2-Country-Blocks-IPv4.csv GeoCountry2.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 1>>import.log 2>&1

: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%  1>>import.log 2>&1
echo.  1>>import.log 2>&1
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
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-06 19:15

SorenR wrote:
2021-04-06 19:01
Everywyere I have searched on the Interweb I find DBA's preferring JOIN over complicated queries... Comparing 0.004 seconds to 0.009 seconds will only matter if do millions of queries per minute.

Notice how I defined the primary keys. It needs to be this way if you want to import and "replace" and not "delete & import". Much faster.
I tried primary key and it didn't make a difference. The query (and having a key/keys) seem more important.

The reason my query is twice as fast as yours is because the LIMIT 1 in the subquery ends the table scan immediately, and then the outside query just looks at what's left over.

The LIMIT 1 in the outside query doesn't seem to make a difference on the little bit of testing I did. However, *I think* when the IP is in a high range (2xx.a.b.c), it could make a difference. Further testing is required.

Anyway, Mr. Turbo Audi Guy, isn't faster ALWAYS better???? :mrgreen:

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-06 19:34

SorenR wrote:
2021-04-06 19:01
Mysqlimport is NOT very informative. Do your SQL IMPORT in a SQL command windows to debug.. :roll:
From phpmyadmin after deleting the first row and inserting same row from csv:

Code: Select all

INSERT INTO geocountry (
	network_start_integer,
	network_last_integer,
	geoname_id,
	registered_country_geoname_id,
	represented_country_geoname_id,
	is_anonymous_proxy,
	is_satellite_provider
) 
VALUES (
	'16777216',
	'16777471',
	'2077456',
	'2077456',
	'',
	'0',
	'0'
)

Code: Select all

Warning: #1366 Incorrect integer value: '' for column `geoip`.`geocountry`.`represented_country_geoname_id` at row 1
So, I was right. It was the blank field.

Interestingly, phpmyadmin would not take the query unless I wrapped all the values in single quotes - including the empty value. This is obviously not a problem for mysqlimport.

Nothing you can do about the empty values unless maxmind changes their converter or you can always use my slow as molasses method: line by line import.

As part of my soon-to-be-abandoned :D maxmind project, I use powershell import-csv, which is capable of spitting out properly wrapped and/or transformed values. It takes almost 10 minutes to load the 320k+ line maxmind csv, but if you do that, you can turn blanks into zeros and you won't get the warnings anymore.

I think I can live with the warnings, since they don't do any harm and the table still loads just fine.

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-04-06 19:41

palinka wrote:
2021-04-06 19:34
SorenR wrote:
2021-04-06 19:01
Mysqlimport is NOT very informative. Do your SQL IMPORT in a SQL command windows to debug.. :roll:
From phpmyadmin after deleting the first row and inserting same row from csv:

Code: Select all

INSERT INTO geocountry (
	network_start_integer,
	network_last_integer,
	geoname_id,
	registered_country_geoname_id,
	represented_country_geoname_id,
	is_anonymous_proxy,
	is_satellite_provider
) 
VALUES (
	'16777216',
	'16777471',
	'2077456',
	'2077456',
	'',
	'0',
	'0'
)

Code: Select all

Warning: #1366 Incorrect integer value: '' for column `geoip`.`geocountry`.`represented_country_geoname_id` at row 1
So, I was right. It was the blank field.

Interestingly, phpmyadmin would not take the query unless I wrapped all the values in single quotes - including the empty value. This is obviously not a problem for mysqlimport.

Nothing you can do about the empty values unless maxmind changes their converter or you can always use my slow as molasses method: line by line import.

As part of my soon-to-be-abandoned :D maxmind project, I use powershell import-csv, which is capable of spitting out properly wrapped and/or transformed values. It takes almost 10 minutes to load the 320k+ line maxmind csv, but if you do that, you can turn blanks into zeros and you won't get the warnings anymore.

I think I can live with the warnings, since they don't do any harm and the table still loads just fine.
So, you did not check my tabledefinitions ... Network_start and Network_end are bigint's and the rest is varchar. varchar don't care about blanks. I don't plan on using the boolean values but you could define a default value "0" if missing.
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-07 02:51

Here's my fixed up powershell version.

* Added the lightning speed import
* Deleted create scheduled task
* Added colorful email notifications

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

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-12 04:26

I just tried the city data for the first time and ran it on my IP. The supposed accuracy_radius is 1 meter for my IP but the latitude/longitude coordinates were off by several blocks. Still the same town, though. Most online geoip locators put me in the next town over.

I tried searching by latitude/longitude, but those are not unique or keyed columns and the search takes forever. Plus, there were no results. Then I switched to LIKE instead of equals and dropped the last digit of each (latitude LIKE xx.xxx AND longitude LIKE -xx.xxx - 3 digits instead of 4), but that didn't finish before typing this reply. :shock: [edit - still no results, and "Query took 221.7181 seconds"]

Result: more accurate than others, but not anything close to the listed accuracy radius.

My script still needs some cleaning up. I'll post later.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-21 12:23

Finally got around to cleaning up the script. Now does both city and country locations. Pick the one you like or do both.

It takes a little longer than Sorens because for error checking reasons, I count the records in the CSV and that adds several seconds to the country CSV and minutes to the city CSV with over 3 million records. It's still orders of magnitude faster than my old script.

Plus, you get a nice color-coded email at the end.

Screenshot 2021-04-21 061739.png

And of course, you get the debug log as an attachment:

Code: Select all

:::  Backup Routine Wednesday, April 21, 2021 :::
 
4/21/2021 5:00:44 AM : GeoIP Update Start
4/21/2021 5:00:44 AM : ----------------------------
4/21/2021 5:00:44 AM : Checking for script update at GitHub
4/21/2021 5:00:45 AM : Backup & Upload script is latest version: 2.04
4/21/2021 5:00:45 AM : ----------------------------
4/21/2021 5:00:45 AM : Deleting old files
4/21/2021 5:00:45 AM : Folder C:\scripts\GeoLite2SQL\GeoLite2-City-CSV successfully deleted
4/21/2021 5:00:45 AM : Old zip file C:\scripts\GeoLite2SQL\Script-Created-Files\GeoLite2-City-CSV.zip successfully deleted
4/21/2021 5:00:45 AM : ----------------------------
4/21/2021 5:00:45 AM : Downloading MaxMind data
4/21/2021 5:00:49 AM : MaxMind data successfully downloaded in 4 seconds
4/21/2021 5:00:53 AM : MaxMind data successfully unzipped in 3 seconds
4/21/2021 5:00:53 AM : Locations CSV successfully renamed
4/21/2021 5:00:53 AM : ----------------------------
4/21/2021 5:00:53 AM : Counting database records for comparison
4/21/2021 5:00:58 AM : 3,494,500 database records prior to starting update
4/21/2021 5:00:58 AM : ----------------------------
4/21/2021 5:00:58 AM : Counting CSV records for comparison
4/21/2021 5:14:26 AM : Counted 3,495,740 IPv4 records in new CSV in 13 minutes 28 seconds
4/21/2021 5:14:26 AM : ----------------------------
4/21/2021 5:14:26 AM : Converting CSV
4/21/2021 5:14:54 AM : City IP CSV successfully converted to integer-range in 27 seconds
4/21/2021 5:14:54 AM : ----------------------------
4/21/2021 5:14:54 AM : Drop and recreate database tables
4/21/2021 5:14:54 AM : Database tables successfully dropped and created
4/21/2021 5:14:54 AM : ----------------------------
4/21/2021 5:14:54 AM : Import city IP information
4/21/2021 5:19:56 AM : 3,495,740 city IP records imported in 5 minutes 1 second
4/21/2021 5:19:56 AM : ----------------------------
4/21/2021 5:19:56 AM : Import city name information
4/21/2021 5:20:45 AM : 122,058 city name records imported in 49 seconds
4/21/2021 5:20:45 AM : ----------------------------
4/21/2021 5:20:45 AM : Successfully imported 3,495,740 records in 20 minutes 0 seconds
4/21/2021 5:20:45 AM : [INFO] Net change of -1,240 records since last update
4/21/2021 5:20:46 AM : GeoIP update finished
Also, it checks github for the latest version, so if there are changes you get notified. :D

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

gotspatel
Normal user
Normal user
Posts: 123
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-04-21 14:51

Just tested with this ip 45.58.142.12 it says china but it is actually netherlands, any idea Why?

I also tested many other IP They come correct.

Also a small glitch in GeoLite2SQL-Config.dist.ps1 Line 58

It should be $CountryLocationLang = "en"

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-21 15:12

gotspatel wrote:
2021-04-21 14:51
Just tested with this ip 45.58.142.12

it says china but it is actually netherlands, any idea Why?

Also a small glitch in GeoLite2SQL-Config.dist.ps1 Line 58

It should be $CountryLocationLang = "en"
Thanks for the heads up. But it should actually be $LocationLanguage. I changed a couple of config variables because the script is no longer exclusive to the country database version. I just updated github.

As far as the IP being wrong: you'll have to ask MaxMind, I guess. I've noticed many discrepancies as well. My php custom log has a link to ip-api.com for more info on the location. Sometimes the country written into the log (from MaxMind) and the ip-api.com data don't match.

I don't know how many companies actually collect geoip data, but for sure nearly all the geoip websites rely on someone else's data. I believe MaxMind collects their own data. There are probably only a couple other companies that do that.

gotspatel
Normal user
Normal user
Posts: 123
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-04-21 15:35

Thanks for the update will inform the maxmind people for the correction.

Meanwhile $LocationLanguage if used then the CSV with "en" is not parsed. and it throws error.

Code: Select all

21/04/2021 07:04 PM : MaxMind data successfully downloaded in 7 seconds
21/04/2021 07:04 PM : MaxMind data successfully unzipped in less than 1 second
21/04/2021 07:04 PM : [ERROR] : Unable to rename locations CSV : Cannot rename because item at 'C:\Scripts\GeoIP\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv' does not exist.
21/04/2021 07:04 PM : [ERROR] : Quitting Script
21/04/2021 07:04 PM : GeoIP update finished

If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-21 19:18

gotspatel wrote:
2021-04-21 15:35
Thanks for the update will inform the maxmind people for the correction.

Meanwhile $LocationLanguage if used then the CSV with "en" is not parsed. and it throws error.

Code: Select all

21/04/2021 07:04 PM : MaxMind data successfully downloaded in 7 seconds
21/04/2021 07:04 PM : MaxMind data successfully unzipped in less than 1 second
21/04/2021 07:04 PM : [ERROR] : Unable to rename locations CSV : Cannot rename because item at 'C:\Scripts\GeoIP\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv' does not exist.
21/04/2021 07:04 PM : [ERROR] : Quitting Script
21/04/2021 07:04 PM : GeoIP update finished

That's very strange. Sometimes different versions of powershell behave differently.

The below code works for me. Its what should be on github.

GeoLite2SQL-Config.ps1

Code: Select all

$LocationLanguage     = "en"
GeoLite2SQL-Country.ps1

Code: Select all

$CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$LocationLanguage.csv"
$LocationsRenamed = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLocations.csv"



<#  Rename Locations CSV  #>
Try {
	Rename-Item $CountryLocations $LocationsRenamed -ErrorAction Stop
	Debug "Locations CSV successfully renamed"
}
Catch {
	Debug "[ERROR] : Unable to rename locations CSV : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to rename locations CSV. See error log."
	EmailResults
	Exit
}
Alternatively, you could try this:

Code: Select all

$CountryLocations = $PSScriptRoot + "\GeoLite2-Country-CSV\GeoLite2-Country-Locations-" + $LocationLanguage + ".csv"

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-21 20:16

gotspatel wrote:
2021-04-21 15:35
If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/
Are you SURE the error is on MaxMind? :wink:

I would not submit a correction unless I controlled or at least had personal knowledge that the IP in question assuredly contains the wrong data.

Just because most or even all of the others say one thing doesn't mean its accurate.

gotspatel
Normal user
Normal user
Posts: 123
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-04-22 05:50

palinka wrote:
2021-04-21 19:18
gotspatel wrote:
2021-04-21 15:35
Thanks for the update will inform the maxmind people for the correction.

Meanwhile $LocationLanguage if used then the CSV with "en" is not parsed. and it throws error.

Code: Select all

21/04/2021 07:04 PM : MaxMind data successfully downloaded in 7 seconds
21/04/2021 07:04 PM : MaxMind data successfully unzipped in less than 1 second
21/04/2021 07:04 PM : [ERROR] : Unable to rename locations CSV : Cannot rename because item at 'C:\Scripts\GeoIP\GeoLite2-Country-CSV\GeoLite2-Country-Locations-.csv' does not exist.
21/04/2021 07:04 PM : [ERROR] : Quitting Script
21/04/2021 07:04 PM : GeoIP update finished

That's very strange. Sometimes different versions of powershell behave differently.

The below code works for me. Its what should be on github.

GeoLite2SQL-Config.ps1

Code: Select all

$LocationLanguage     = "en"
GeoLite2SQL-Country.ps1

Code: Select all

$CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$LocationLanguage.csv"
$LocationsRenamed = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLocations.csv"



<#  Rename Locations CSV  #>
Try {
	Rename-Item $CountryLocations $LocationsRenamed -ErrorAction Stop
	Debug "Locations CSV successfully renamed"
}
Catch {
	Debug "[ERROR] : Unable to rename locations CSV : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to rename locations CSV. See error log."
	EmailResults
	Exit
}
Alternatively, you could try this:

Code: Select all

$CountryLocations = $PSScriptRoot + "\GeoLite2-Country-CSV\GeoLite2-Country-Locations-" + $LocationLanguage + ".csv"

Sir You are missing the file GeoLite2SQL.ps1 on git

The problem is in L190 $CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$CountryLocationLang.csv"
Last edited by gotspatel on 2021-04-22 05:56, edited 1 time in total.

gotspatel
Normal user
Normal user
Posts: 123
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-04-22 05:52

palinka wrote:
2021-04-21 20:16
gotspatel wrote:
2021-04-21 15:35
If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/
Are you SURE the error is on MaxMind? :wink:

I would not submit a correction unless I controlled or at least had personal knowledge that the IP in question assuredly contains the wrong data.

Just because most or even all of the others say one thing doesn't mean its accurate.
I am sure about that Particular IP as Previously I had hosted my mail server there. :D

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-22 12:25

gotspatel wrote:
2021-04-22 05:50
Sir You are missing the file GeoLite2SQL.ps1 on git

The problem is in L190 $CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$CountryLocationLang.csv"
Its not missing. It was removed. Use GeoLite2SQL-Country.ps1 or GeoLite2SQL-City.ps1 (or both). I probably have to update the readme file.

Both of these scripts create new, separate tables. If you run both, you will end up with 5 tables:

* Your old geolite2sql script table (not removed or overwritten)
* geocountry
* geolocations (geocountry name data)
* geocity
* geocitylocations

This way, you can continue to run the old table while playing with the new ones. After you get the new one going, change the query in eventhandlers.vbs as shown in the info part of the script at the top.

In eventhandlers.vbs, you can use this subroutine to get country info (this one is for COUNTRY - not city lookup):

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.1 Driver}; Server=localhost; Database=databasename; User=username; Password=supersecretpassword;"

    If oConn.State <> 1 Then
		EventLog.Write( "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 country_code, country_name FROM (SELECT * FROM geocountry WHERE INET_ATON('" & sIPAddress & "') <= 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('" & sIPAddress & "') LIMIT 1;")
    Do Until oRecord.EOF
        m_CountryCode = oRecord("country_code")
        m_CountryName = oRecord("country_name")
        oRecord.MoveNext
    Loop
    oConn.Close
    Set oRecord = Nothing
End Sub
Then you can call it like this. I use it at the top of OnHELO, but you can also use it at OnClientConnect.

Code: Select all

	REM	- GeoIP Lookup
	Dim m_CountryCode, m_CountryName
	Call GeoIPLookup(oClient.IPAddress, m_CountryCode, m_CountryName)

	If m_CountryCode = Empty Then m_CountryCode = "NX"
	If m_CountryName = Empty Then m_CountryName = "No.GeoIP.Record"
	If m_CountryCode = "NX" Then EventLog.Write("Error in GeoIP lookup - IP: " & oClient.IPAddress)

	If (oClient.Port = 25) Then
		REM	 - ALLOWED COUNTRIES - Port 25 only... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ|KR|NX)$"
		If Lookup(strBase, m_CountryCode) Then bolGeoIP = True
	Else
		REM	- ALLOWED COUNTRIES - All ports except 25... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
		strBase = "^(US|NX)$"
		If Lookup(strBase, m_CountryCode) Then 
			bolGeoIP = True
		End If
	End If

	If bolGeoIP Then
		REM	- Connection PASSED examination
		' Log it or do nothing since its OK to proceed
	Else
		REM	- Disconnect all others.
		Result.Value = 2
		Result.Message = ". 01 This mail server does not accept mail submission from " & m_CountryName & ". If you believe that this failure is in error, please contact the intended recipient via alternate means."
		' Log it, autoban it, firewall ban it or whatever
		Exit Sub
	End If

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-04-22 12:34

gotspatel wrote:
2021-04-22 05:52
palinka wrote:
2021-04-21 20:16
gotspatel wrote:
2021-04-21 15:35
If you want you can ask them for batch corrections for multiple IP here

Code: Select all

https://support.maxmind.com/correction-faq/submit-a-correction/can-i-submit-batch-corrections/
Are you SURE the error is on MaxMind? :wink:

I would not submit a correction unless I controlled or at least had personal knowledge that the IP in question assuredly contains the wrong data.

Just because most or even all of the others say one thing doesn't mean its accurate.
I am sure about that Particular IP as Previously I had hosted my mail server there. :D
I guess that qualifies. :D

https://www.maxmind.com/en/geoip2-city- ... comparison

The US is listed as only 66% accurate. The only places that are 100% accurate are small islands. :D

gotspatel
Normal user
Normal user
Posts: 123
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-04-22 13:00

palinka wrote:
2021-04-22 12:25
gotspatel wrote:
2021-04-22 05:50
Sir You are missing the file GeoLite2SQL.ps1 on git

The problem is in L190 $CountryLocations = "$PSScriptRoot\GeoLite2-Country-CSV\GeoLite2-Country-Locations-$CountryLocationLang.csv"
Its not missing. It was removed. Use GeoLite2SQL-Country.ps1 or GeoLite2SQL-City.ps1 (or both). I probably have to update the readme file.

Both of these scripts create new, separate tables. If you run both, you will end up with 5 tables:

* Your old geolite2sql script table (not removed or overwritten)
* geocountry
* geolocations (geocountry name data)
* geocity
* geocitylocations

This way, you can continue to run the old table while playing with the new ones. After you get the new one going, change the query in eventhandlers.vbs as shown in the info part of the script at the top.

In eventhandlers.vbs, you can use this subroutine to get country info (this one is for COUNTRY - not city lookup):

Thanks for the Headsup will check it out

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-04-22 14:06

Hmm...
Checked with my online GeoIP provider..

Code: Select all

{
   "ip":"45.58.142.12",
   "continent_code":"NA",
   "continent_name":"North America",
   "country_code2":"US",
   "country_code3":"USA",
   "country_name":"United States",
   "country_capital":"Washington, D.C.",
   "state_prov":"Nevada",
   "district":"Paradise",
   "city":"Las Vegas",
   "zipcode":"89120-3470",
   "latitude":"36.08625",
   "longitude":"-115.10385",
   "is_eu":false,
   "calling_code":"+1",
   "country_tld":".us",
   "languages":"en-US,es-US,haw,fr",
   "country_flag":"https://ipgeolocation.io/static/flags/us_64.png",
   "geoname_id":"5510070",
   "isp":"Sharktech",
   "connection_type":"",
   "organization":"Sharktech",
   "currency":{
       "code":"USD",
       "name":"US Dollar",
       "symbol":"$"},
   "time_zone":{
       "name":"America/Los_Angeles",
       "offset":-8,
       "current_time":"2021-04-22 05:00:37.937-0700",
       "current_time_unix":1619092837.937,
       "is_dst":true,
       "dst_savings":1}
   }
https://www.opentracker.net/feature/ip-tracker/ says

Code: Select all

P address: 45.58.142.12
City: Amsterdam
Region name: Noord-Holland
Country name: Netherlands
Life Expectency: 78.3
Avg income: 23.409 EUR
Timezone: Europe/Amsterdam
Sub continent: Western Europe
Country code: NL
Geo-targeting: true
   ◉ Latitude: 37.751
   ◎ Longitude: -97.822
World currency: EUR
EU member: true
org: Sharktech
isp: Sharktech
Connection: undefined
Continent: Europe
Population: 15.864.000
IP range tracked:
Surface area: 41.526 km sq.
GNP: 371.362 mln.
Demographic data: true
Ad (re)targeting: true
Where do they get their info from?
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2021-04-22 14:17

https://www.iplocation.net/ip-lookup

Suggests Amsterdam, Las Vegas or Luoyang :roll:
SørenR.

Algorithm (noun.)
Word used by programmers when they do not want to explain what they did.

gotspatel
Normal user
Normal user
Posts: 123
Joined: 2013-10-08 05:42
Location: INDIA

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2021-04-22 14:23

Very Confusing

https://www.iplocation.net/ip-lookup

Shows Netherlands, USA, China :!:

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2021-05-05 12:44

I count the number of records in the MaxMind CSV. I think its an important error checking variable. I was using (get-content).count but it takes a long time. Over 10 minutes on the city IP data file with >3M records. It uses a lot of memory too.

I changed the counting method to IO.StreamReader and cut the city IP data count time down to 2 seconds. The country IP data (>300k records) went from around 20 seconds to less than 1 second. A nice speed boost!

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

Post Reply