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: 4610
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: 6366
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.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4610
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: 4610
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: 6366
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.

Woke is Marxism advancing through Maoist cultural revolution.

palinka
Senior user
Senior user
Posts: 4610
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: 4610
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: 4610
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
Senior user
Senior user
Posts: 372
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: 4610
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
Senior user
Senior user
Posts: 372
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: 4610
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: 4610
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
Senior user
Senior user
Posts: 372
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
Senior user
Senior user
Posts: 372
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: 4610
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: 4610
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
Senior user
Senior user
Posts: 372
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: 6366
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.

Woke is Marxism advancing through Maoist cultural revolution.

User avatar
SorenR
Senior user
Senior user
Posts: 6366
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.

Woke is Marxism advancing through Maoist cultural revolution.

gotspatel
Senior user
Senior user
Posts: 372
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: 4610
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

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2022-12-07 11:08

palinka wrote:
2021-04-22 12:25

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
Kinda weird to loop through the records when you LIMIT the results to return 1 record (as you either have one or none at all)

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;")
    If Not oRecord.EOF Then
        m_CountryCode = oRecord("country_code")
        m_CountryName = oRecord("country_name")
    End If
    oConn.Close
    Set oRecord = Nothing
End Sub
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2022-12-07 12:04

RvdH wrote:
2022-12-07 11:08
Kinda weird to loop through the records when you LIMIT the results to return 1 record (as you either have one or none at all)
Kinda looks the same to me.

Start loop > only one result, so its the last one > EOF > done

Start looking > only one result, so its the last one > EOF > done

By the way, the purpose of LIMIT 1 is not what you may believe. There are two in the query. The one in the subquery shuts down the table scan of network_last_integer making the query a LOT faster. The 2nd one is not really necessary because by definition you're only going to have one result with or without LIMIT 1. The 2nd one (at the end) was experimental and I never removed it. It does no harm, but it also does no good.

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2022-12-07 12:22

That is not the code i am actually using, i am using the one with IPv6 (INET6_ATON) support

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_iso_code, country_name FROM (SELECT * FROM geocountry WHERE INET6_ATON('" & sIPAddress & "') <= network_end LIMIT 1) AS a INNER JOIN countrylocations AS b on a.geoname_id = b.geoname_id WHERE network_start <= INET6_ATON('" & sIPAddress & "');")
    If Not oRecord.EOF Then
        m_CountryCode = oRecord("country_iso_code")
        m_CountryName = oRecord("country_name")
    End If
    Set oRecord = Nothing
    
    oConn.Close
    Set oConn = Nothing
End Sub
What i have been teached it is better to do:

Code: Select all

If Not oRecord.EOF Then
    Do Until oRecord.EOF
        m_CountryCode = oRecord("country_iso_code")
        m_CountryName = oRecord("country_name")
        oRecord.MoveNext
    Loop
End If
or in this case

Code: Select all

If Not oRecord.EOF Then
    m_CountryCode = oRecord("country_iso_code")
    m_CountryName = oRecord("country_name")
End If
No need to initiate a loop when no records exist or no more then 1 records exists
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2022-12-07 13:16

RvdH wrote:
2022-12-07 12:22
That is not the code i am actually using, i am using the one with IPv6 (INET6_ATON) support
Me too.
What i have been teached it is better to do:
Soren taught me, so take it up with him. :mrgreen:

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

Re: MaxMinds GeoIP for local MySQL

Post by SorenR » 2022-12-07 16:20

Well... Please note the "LIMIT 1" in the SQL query ;-)

Not looping anything :wink:

Code: Select all

'******************************************************************************************************************************
'********** GeoLite 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 GeoLite(strIP) : GeoLite = "VOID"
    Dim i, a, strData
    Dim oRecord, oDB : Set oDB = CreateObject("ADODB.Connection")
    oDB.Open "DRIVER={" & DBDRVR & "};Server=" & DBSERVER & ";Port=" & DBPORT & ";Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
    If oDB.State <> 1 Then
        EventLog.Write( "GeoLite - ERROR: Could not connect to database" )
        Exit Function
    End If
    Set oRecord = oDB.Execute("SELECT country_iso_code FROM (" & _
                              "    SELECT * FROM geocountry " &_
                              "    WHERE INET_ATON('" & strIP & "') >= network_start_integer " & _
                              "    ORDER BY network_start_integer DESC " & _
                              "    LIMIT 1 " & _
                              ") AS country " & _
                              "LEFT JOIN geolocation AS location on country.geoname_id = location.geoname_id " & _
                              "WHERE INET_ATON('" & strIP & "') <= network_last_integer;")
    If IsNull(oRecord("country_iso_code")) Then
        GeoLite = "VOID"
    Else
        If Lookup("^([a-zA-Z]{2})$", oRecord("country_iso_code")) Then GeoLite = oRecord("country_iso_code")
    End If
    Set oRecord = Nothing
    oDB.Close
    Set oDB = Nothing
End Function

'******************************************************************************************************************************
'********** Triggers                                                                                                 **********
'******************************************************************************************************************************

    '
    '   GeoLite ....
    '
    strGeo = GeoLite(oClient.IPAddress)
    If Not Lookup("(DK|GL|FO|VOID)", strGeo) And (oClient.Port > 25) Then
        EventLogX.LogFile = "geo-blocked"
        Result.Value = 1
        If AutoBan(oClient.IPAddress, "GEO Blocked " & strGeo & " " & strPort, 48, "h") Then _
            EventLogX.Write( LPad("GEO Blocked", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
        Disconnect(oClient.IPAddress)
        Set EventLogX = Nothing
        Exit Sub
    End If
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2022-12-07 16:40

SorenR wrote:
2022-12-07 16:20
Well... Please note the "LIMIT 1" in the SQL query ;-)
palinka wrote:
2022-12-07 12:04
By the way, the purpose of LIMIT 1 is not what you may believe. There are two in the query. The one in the subquery shuts down the table scan of network_last_integer making the query a LOT faster. The 2nd one is not really necessary because by definition you're only going to have one result with or without LIMIT 1. The 2nd one (at the end) was experimental and I never removed it. It does no harm, but it also does no good.
Even if you remove both LIMIT 1's, its still impossible to get more than one result.

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2022-12-08 09:33

SorenR wrote:
2022-12-07 16:20
Well... Please note the "LIMIT 1" in the SQL query ;-)

Not looping anything :wink:

Code: Select all

'******************************************************************************************************************************
'********** GeoLite 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 GeoLite(strIP) : GeoLite = "VOID"
    Dim i, a, strData
    Dim oRecord, oDB : Set oDB = CreateObject("ADODB.Connection")
    oDB.Open "DRIVER={" & DBDRVR & "};Server=" & DBSERVER & ";Port=" & DBPORT & ";Database=" & DBEXTRA & ";Uid=" & DBUID & ";Pwd=" & DBPW & ";FOUND_ROWS=1;"
    If oDB.State <> 1 Then
        EventLog.Write( "GeoLite - ERROR: Could not connect to database" )
        Exit Function
    End If
    Set oRecord = oDB.Execute("SELECT country_iso_code FROM (" & _
                              "    SELECT * FROM geocountry " &_
                              "    WHERE INET_ATON('" & strIP & "') >= network_start_integer " & _
                              "    ORDER BY network_start_integer DESC " & _
                              "    LIMIT 1 " & _
                              ") AS country " & _
                              "LEFT JOIN geolocation AS location on country.geoname_id = location.geoname_id " & _
                              "WHERE INET_ATON('" & strIP & "') <= network_last_integer;")
    If IsNull(oRecord("country_iso_code")) Then
        GeoLite = "VOID"
    Else
        If Lookup("^([a-zA-Z]{2})$", oRecord("country_iso_code")) Then GeoLite = oRecord("country_iso_code")
    End If
    Set oRecord = Nothing
    oDB.Close
    Set oDB = Nothing
End Function

'******************************************************************************************************************************
'********** Triggers                                                                                                 **********
'******************************************************************************************************************************

    '
    '   GeoLite ....
    '
    strGeo = GeoLite(oClient.IPAddress)
    If Not Lookup("(DK|GL|FO|VOID)", strGeo) And (oClient.Port > 25) Then
        EventLogX.LogFile = "geo-blocked"
        Result.Value = 1
        If AutoBan(oClient.IPAddress, "GEO Blocked " & strGeo & " " & strPort, 48, "h") Then _
            EventLogX.Write( LPad("GEO Blocked", 15, " ") & vbTab & LPad(oClient.IPAddress, 16, " ") & vbTab & LPad(oClient.Port, 3, " ") & vbTab & strGeo )
        Disconnect(oClient.IPAddress)
        Set EventLogX = Nothing
        Exit Sub
    End If
'If IsNull(oRecord("country_iso_code")) Then' is literally the same as doing 'If Not oRecord.EOF Then'

If Lookup("^([a-zA-Z]{2})$", oRecord("country_iso_code")) Then GeoLite = oRecord("country_iso_code"), is a bit of a redundant check....country code is always 2 chars long if any
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2023-03-02 17:22

@palinka

Yesterday i got this:

Code: Select all

::: MaxMind GeoCountry Database Import Routine woensdag 1 maart 2023 :::
 
1-3-2023 07:00:03 : GeoIP Country Update Start
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Checking for script update at GitHub
1-3-2023 07:00:03 : GeoLite2SQL script is latest version: 3.05
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Deleting old files
1-3-2023 07:00:03 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Downloading MaxMind data
1-3-2023 07:00:11 : [ERROR] : Unable to download MaxMind data : HTTP status 304: The server's response was not valid. The server was not following the defined protocol. Resume the job, and then Background Intelligent Transfer Service (BITS) will try again.

1-3-2023 07:00:11 : [ERROR] : Quitting Script
1-3-2023 07:00:11 : GeoIP Country Update Finished
Any idea? It keeps failing (today again) with HTTP status 304
Previous month(s) no issues whatsoever

If i put a debug on the $URL variable it returns the correct URL and i can download within browser without issue
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 18:07

RvdH wrote:
2023-03-02 17:22
@palinka

Yesterday i got this:

Code: Select all

::: MaxMind GeoCountry Database Import Routine woensdag 1 maart 2023 :::
 
1-3-2023 07:00:03 : GeoIP Country Update Start
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Checking for script update at GitHub
1-3-2023 07:00:03 : GeoLite2SQL script is latest version: 3.05
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Deleting old files
1-3-2023 07:00:03 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
1-3-2023 07:00:03 : ----------------------------
1-3-2023 07:00:03 : Downloading MaxMind data
1-3-2023 07:00:11 : [ERROR] : Unable to download MaxMind data : HTTP status 304: The server's response was not valid. The server was not following the defined protocol. Resume the job, and then Background Intelligent Transfer Service (BITS) will try again.

1-3-2023 07:00:11 : [ERROR] : Quitting Script
1-3-2023 07:00:11 : GeoIP Country Update Finished
Any idea? It keeps failing (today again) with HTTP status 304
Previous month(s) no issues whatsoever

If i put a debug on the $URL variable it returns the correct URL and i can download within browser without issue
I got the same thing. I'm pretty sure its on MaxMind's side. My "plan" was to just wait until next week and see if it still happens.

The error description is coming from BITS, not from powershell or anything in my script.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 18:23

I think I found it. I plugged the URL (including license key, etc) into my browser and got the error "Database not found". On a guess, I capitalized "City" and it worked. So it looks like they made some kind of change on their server that enforces capitalization. I'll add a rule to the script that makes sure "City" and "Country" are properly capitalized.

In the meantime, per the instructions:

Code: Select all

.EXAMPLE
	Run script as follows:

	C:\path\to\Geolite2SQL.ps1 country
	C:\path\to\Geolite2SQL.ps1 city
Change that to the following in order to capitalize "City" and "Country" because the parameter var becomes part of the URL:

Code: Select all

.EXAMPLE
	Run script as follows:

	C:\path\to\Geolite2SQL.ps1 Country
	C:\path\to\Geolite2SQL.ps1 City

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2023-03-02 18:45

I doubt that is the reason, eg:

Code: Select all

	If ($SelectType -match 'country') {
		$Type = "Country"
	} Else {
		$Type = "City"
	}
$Type used in $URL is properly capitalized, not?
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 18:52

^^^^ This is not the problem because I already validate the parameter and change the case. That's already in the script. Something weird is going on and its going to take some experimentation to debug it.

EDIT: ^^^^ refers to my previous post, not RvdH response.
Last edited by palinka on 2023-03-02 18:53, edited 1 time in total.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 18:53

RvdH wrote:
2023-03-02 18:45
I doubt that is the reason, eg:

Code: Select all

	If ($SelectType -match 'country') {
		$Type = "Country"
	} Else {
		$Type = "City"
	}
$Type used in $URL is properly capitalized, not?
Yes. I cross posted - didn't see your post.

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2023-03-02 19:00

I see a *.tmp file of 2822 Kb being created while running the script.... and yes GeoLite2-Country-CSV_20230228.zip is exactly 2822 Kb

[edit]
it's a weird one, eh? Maybe we should sit it out and check if it is fixed next week
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 19:26

RvdH wrote:
2023-03-02 19:00
I see a *.tmp file of 2822 Kb being created while running the script.... and yes GeoLite2-Country-CSV_20230228.zip is exactly 2822 Kb

[edit]
it's a weird one, eh? Maybe we should sit it out and check if it is fixed next week
On Monday I made some changes to the script that had to do with changing the data types in the database so it aligns with MaxMind suggestions. During that process I tested it several times which included downloading the files, of course. I had no issues at all.

My script runs weekly on Wednesday. I got the same error and assumed it was a temporary issue on MaxMind's end. I still think that's probably the case.

So far today, I have downloaded the files using the URL in my browser successfully 100% of the time (I tried 3 times). Using the script, it worked once out of 6 or 7 tries.

Edit - I just tried one more time and noticed something. First, it worked. However, under strange circumstances: I'm running the script from powershell window and the only 2 times the script worked is when I ran the script a second time without closing the window - that gives me file lock issues with the debug log and a bunch of errors related to that file lock. The instructions in the BITS error are "Resume the job, and then Background Intelligent Transfer Service (BITS) will try again." I'm not sure what to make of that, but it doesn't appear to be coincidental.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 19:57

I think I found a fix.

Change line 338 to:

Code: Select all

	Start-BitsTransfer -Source $URL -Destination $DownloadedZip -Asynchronous -ErrorAction Stop
	Get-BitsTransfer | Complete-BitsTransfer
I'm still testing, but it seems to work fine. Since we all (you, me, Gotspatel also reported this on github) all had this error at exactly the same time, something has to have changed at MaxMind.

Edit: "Get-BitsTransfer | Complete-BitsTransfer" removes the tmp files.

I have no idea why this worked since the beginning until Wednesday of this week. Its a mystery.

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2023-03-02 21:20

That doesn't fix anything....

What happens when you delete existing GeoLite2-Country-CSV.zip?

Code: Select all

::: MaxMind GeoCountry Database Import Routine donderdag 2 maart 2023 :::
 
2-3-2023 20:25:09 : GeoIP Country Update Start
2-3-2023 20:25:09 : ----------------------------
2-3-2023 20:25:09 : Checking for script update at GitHub
2-3-2023 20:25:10 : GeoLite2SQL script is latest version: 3.05
2-3-2023 20:25:10 : ----------------------------
2-3-2023 20:25:10 : Deleting old files
2-3-2023 20:25:10 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
2-3-2023 20:25:10 : ----------------------------
2-3-2023 20:25:10 : Downloading MaxMind data
2-3-2023 20:25:15 : MaxMind data successfully downloaded in 5 seconds
2-3-2023 20:25:17 : MaxMind data successfully unzipped in 1 second
2-3-2023 20:25:17 : Locations CSV successfully renamed
2-3-2023 20:25:17 : ----------------------------
2-3-2023 20:25:17 : Counting database records for comparison
2-3-2023 20:25:19 : 666.760 database records prior to starting update
2-3-2023 20:25:19 : ----------------------------
2-3-2023 20:25:19 : Counting CSV records for comparison
2-3-2023 20:25:26 : Counted 666.760 records in new IPv4 & IPv6 CSVs in 7 seconds
2-3-2023 20:25:26 : ----------------------------
2-3-2023 20:25:26 : Converting CSVs to hex-range
2-3-2023 20:25:29 : Country IPv4 CSV successfully converted to hex-range in 2 seconds
2-3-2023 20:25:33 : Country IPv6 CSV successfully converted to hex-range in 3 seconds
2-3-2023 20:25:33 : ----------------------------
2-3-2023 20:25:33 : Drop and recreate database tables
2-3-2023 20:25:34 : Database tables successfully dropped and created
2-3-2023 20:25:34 : ----------------------------
2-3-2023 20:25:34 : Import converted CSVs to database
2-3-2023 20:25:57 : [OK] Country IPv4 data imported in 22 seconds
2-3-2023 20:26:13 : [OK] Country IPv6 data imported in 16 seconds
2-3-2023 20:26:13 : [OK] Country name data imported in less than 1 second
2-3-2023 20:26:14 : ----------------------------
2-3-2023 20:26:14 : Successfully imported 666.760 records
2-3-2023 20:26:14 : Finished update in 1 minute 4 seconds
2-3-2023 20:26:14 : [INFO] Net change of 0 records since last update
2-3-2023 20:26:14 : GeoIP Country Update Finished
Net change of 0 records since last update, not that strange as as it uses existing GeoLite2-Country-CSV.zip dated 21-02-2013 :!: and not the downloaded file


if i delete existing GeoLite2-Country-CSV.zip

Code: Select all

::: MaxMind GeoCountry Database Import Routine donderdag 2 maart 2023 :::
 
2-3-2023 20:26:25 : GeoIP Country Update Start
2-3-2023 20:26:25 : ----------------------------
2-3-2023 20:26:26 : Checking for script update at GitHub
2-3-2023 20:26:26 : GeoLite2SQL script is latest version: 3.05
2-3-2023 20:26:26 : ----------------------------
2-3-2023 20:26:26 : Deleting old files
2-3-2023 20:26:26 : Folder C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV successfully deleted
2-3-2023 20:26:26 : ----------------------------
2-3-2023 20:26:26 : Downloading MaxMind data
2-3-2023 20:26:27 : MaxMind data successfully downloaded in less than 1 second
2-3-2023 20:26:28 : [ERROR] : Unable to unzip MaxMind data : The path 'C:\Tools\GeoLite2SQL\Script-Created-Files\GeoLite2-Country-CSV.zip' either does not exist or is not a valid file system path.
2-3-2023 20:26:28 : [ERROR] : Quitting Script
2-3-2023 20:26:28 : GeoIP Country Update Finished
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 21:48

Back to the drawing board.....

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2023-03-02 21:50

palinka wrote:
2023-03-02 21:48
Back to the drawing board.....
:lol:
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-02 23:47

RvdH wrote:
2023-03-02 21:50
palinka wrote:
2023-03-02 21:48
Back to the drawing board.....
:lol:
Laugh it up. Try and help me find a solution to something i had nothing to do with creating.

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

Re: MaxMinds GeoIP for local MySQL

Post by gotspatel » 2023-03-03 06:40

I may be worng :D but

Instead of this

Code: Select all

Start-BitsTransfer -Source $url -Destination $output -ErrorAction Stop
I tried with this

Code: Select all

Invoke-WebRequest -Uri $url -OutFile $output
and It works, Also to add Start-BitsTransfer works with other websites

so may be problem can be with maxmind and Start-BitsTransfer

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-03 07:36

gotspatel wrote:
2023-03-03 06:40
I may be worng :D but

Instead of this

Code: Select all

Start-BitsTransfer -Source $url -Destination $output -ErrorAction Stop
I tried with this

Code: Select all

Invoke-WebRequest -Uri $url -OutFile $output
and It works, Also to add Start-BitsTransfer works with other websites

so may be problem can be with maxmind and Start-BitsTransfer
BITS is supposed to handle network interruptions. Invoke-WebRequest can work, but will fail on minor interruptions. BITS is superior for larger downloads.

But yeah, its a good temporary solution. I'll figure it out sooner or later.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-03 08:04

The plot thickens... I received an email from MaxMind:

Code: Select all

Dear MaxMind Customer,

Your account has reached the daily limit for database downloads. Any additional download attempts today from your account will fail. Customers are limited to 2,000 downloads of GeoIP databases per day.

To avoid download errors we recommend that you limit your downloads of each database to no more than once per day per server. You can log in to your account and check your GeoIP download history for information on IP addresses you are downloading databases from.

To ensure that you are downloading your databases efficiently, you may consult the update schedule for GeoIP databases. If you have any questions, please contact us at support@maxmind.com.

 Sincerely,
The Team at MaxMind
I definitely did not run this script 2k times for sure. But I logged in at MaxMind and it shows 2k downloads. BITS must have been running all night retrying and retrying. Testing is finished until tomorrow. :oops:

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2023-03-03 10:35

palinka wrote:
2023-03-03 08:04
The plot thickens... I received an email from MaxMind:

Code: Select all

Dear MaxMind Customer,

Your account has reached the daily limit for database downloads. Any additional download attempts today from your account will fail. Customers are limited to 2,000 downloads of GeoIP databases per day.

To avoid download errors we recommend that you limit your downloads of each database to no more than once per day per server. You can log in to your account and check your GeoIP download history for information on IP addresses you are downloading databases from.

To ensure that you are downloading your databases efficiently, you may consult the update schedule for GeoIP databases. If you have any questions, please contact us at support@maxmind.com.

 Sincerely,
The Team at MaxMind
I definitely did not run this script 2k times for sure. But I logged in at MaxMind and it shows 2k downloads. BITS must have been running all night retrying and retrying. Testing is finished until tomorrow. :oops:

I'm nowhere near 2000k, eg:

Date Download
Mar 1, 2023 38
Mar 2, 2023 78
Mar 3, 2023 3
Total 119

Invoke-WebRequest works

Code: Select all

Invoke-WebRequest -Uri $URL -OutFile $DownloadedZip -ErrorAction Stop
Note, file is updated: GeoLite2-Country-CSV_20230303.zip :!:
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-03 16:44

RvdH wrote:
2023-03-03 10:35
I'm nowhere near 2000k, eg:
I know. I was testing some other code and that's apparently what triggered it.

Anyway, I changed the download block to this:

Code: Select all

<#	Download latest GeoLite2 data  #>
Debug "----------------------------"
Debug "Downloading MaxMind data"
$Timer = Get-Date
$URL = "https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-" + $Type + "-CSV&license_key=" + $LicenseKey + "&suffix=zip"
$Try = 1
Do {
	$Success = $False
	Try {
		Invoke-WebRequest -Uri $URL -OutFile $DownloadedZip -Asynchronous -ErrorAction Stop
		If (Test-Path $DownloadedZip) {
			Debug "MaxMind data successfully downloaded in $(ElapsedTime $Timer)"
			Email "[OK] MaxMind data downloaded"
			$Success = $True
			Break
		} Else {
			If ($Try -le 10) {
				Debug "Failed to download MaxMind zip file on try $Try - trying again"
			} Else {
				Throw "Tried 10 times to download MaxMind zip file - giving up"
			}
		}
	}
	Catch {
		Debug "[ERROR] : Unable to download MaxMind data : $($Error[0])"
		Debug "[ERROR] : Quitting Script"
		Email "[ERROR] Failed to download MaxMind data. See error log."
		EmailResults
		Exit
	}
	$Try++
} Until ($Success -eq $True)
The reason I used BitsTransfer is because it will keep trying if there is a hiccup in the transmission. InvokeWebRequest will just timeout and die if there are any issues with transmission.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2023-03-04 11:18

Here's what I ended up with, which I think is the best error handling I can come up with.

Code: Select all

<#	Download latest GeoLite2 data  #>
Debug "----------------------------"
Debug "Downloading MaxMind data"
$Timer = Get-Date
$URL = "https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-" + $Type + "-CSV&license_key=" + $LicenseKey + "&suffix=zip"
$Try = 1
Do {
	Try {
		Invoke-WebRequest -Uri $URL -OutFile $DownloadedZip
		If (Test-Path $DownloadedZip) {
			Debug "MaxMind data successfully downloaded in $(ElapsedTime $Timer)"
			Email "[OK] MaxMind data downloaded"
			Break
		} Else {
			Throw "MaxMind zip file could not be found after apparently successful download"
		}
	}
	Catch {
		Debug "[ERROR] : Unable to download MaxMind data on try # $Try"
		Debug "[ERROR] : Error Message : $($Error[0])"
		If ($Try -le 9) {
			Debug "Trying again in 10 seconds"
			Start-Sleep -Seconds 10
		} Else {
			Debug "Tried 10 times to download MaxMind zip file - giving up"
			Debug "[ERROR] : Quitting Script"
			Email "[ERROR] Failed to download MaxMind data. See error log."
			EmailResults
			Exit
		}
	}
	$Try++
} Until ($Try -gt 10)
I tried this with multiple phoney and real URLs. It seems to work as expected. If there's an error, it tries 10 times with 10 seconds in between before giving up.

I updated github already.

As to BitsTransfer, I tested with multiple files from random sites and it works just fine. I'm convinced MaxMind changed something that causes the error. Again - BitsTransfer is always preferrable because it handles transmission errors very well. You don't need to jump through hoops to make it work - except for this one site on the entire internet: MaxMind.com. I finally gave up.

carlatpg99
New user
New user
Posts: 19
Joined: 2022-08-16 20:53

Re: MaxMinds GeoIP for local MySQL

Post by carlatpg99 » 2024-04-25 05:09

There was an update posted to the github repository recently: https://github.com/palinkas-jo-reggelt/GeoLite2SQL

It fixes errors during import by updating the schema.

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2024-04-25 17:42

carlatpg99 wrote:
2024-04-25 05:09
There was an update posted to the github repository recently: https://github.com/palinkas-jo-reggelt/GeoLite2SQL

It fixes errors during import by updating the schema.
The script has a versioning function which adds a little note to the nightly report email when changes are made on github. :D

carlatpg99
New user
New user
Posts: 19
Joined: 2022-08-16 20:53

Re: MaxMinds GeoIP for local MySQL

Post by carlatpg99 » 2024-05-03 10:31

Oh I didn't know the version check did that. Thanks.

I also pointed it out because the previous version wouldn't be able to import data from MaxMinds.

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2024-07-13 11:57

@palinka

Would something like this (MySQL import) be possible for the ASN CVS database as well?
through the @work hms instance i get complaints more and more from clients that have switched to the Outlook(New) app that they are no longer able to connect due to GEO block as those IP seems to come from everywhere (till now i logged IE, FR, DE, NL)

For usage i am thinking about something like:

Code: Select all

If (ASLookup(40.99.145.253, strResult) then
    REM AS8075 MICROSOFT-CORP-MSN-AS-BLOCK
    If (StrComp(strResult,"AS8075") = 0) Then Exit Sub
end if
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
RvdH
Senior user
Senior user
Posts: 3342
Joined: 2008-06-27 14:42
Location: The Netherlands

Re: MaxMinds GeoIP for local MySQL

Post by RvdH » 2024-07-13 23:32

RvdH wrote:
2024-07-13 11:57
@palinka

Would something like this (MySQL import) be possible for the ASN CVS database as well

Code: Select all

<#

.SYNOPSIS
	Install MaxMind GeoLite2 database to local database server

.DESCRIPTION
	Downloads and unzips MaxMinds csv geoip data, then populates tables on local database

.FUNCTIONALITY
	1) If geoip table does not exist, it gets created
	2) Deletes old files if existing
	3) Downloads MaxMinds geolite2 csv data and converts it
	4) Loads data into database
	5) Feedback on console and by email on weekly updates

.NOTES
	--!!!--   
	Requires user privileges: GRANT FILE ON *.* TO 'db-user'@'%' in order for LOAD DATA INFILE to work!
	Data import will FAIL due to access denied to user without these privileges!
	--!!!--
	
	Run every Wednesday via task scheduler (MaxMinds releases updates on Tuesdays)

	License Key required from MaxMind in order to download data (its free, sign up here: https://www.maxmind.com/en/geolite2/signup)

.PARAMETER SelectType
	Specifies the type of MaxMind data to download and import.
	
	Options are "country", "city" and "asn".
	
.EXAMPLE
	Run script as follows:

	C:\path\to\Geolite2SQL.ps1 country
	C:\path\to\Geolite2SQL.ps1 city
	C:\path\to\Geolite2SQL.ps1 asn

.EXAMPLE
	Example queries to return country code and country name from country database:
	
		SELECT country_code, country_name
		FROM (
			SELECT * 
			FROM geocountry 
			WHERE INET6_ATON('212.186.81.105') <= network_end
			LIMIT 1
		) AS a 
		INNER JOIN countrylocations AS b on a.geoname_id = b.geoname_id
		WHERE network_start <= INET6_ATON('212.186.81.105');
		
		SELECT country_code, country_name
		FROM (
			SELECT * 
			FROM geocountry 
			WHERE INET6_ATON('2001:67c:28a4::') <= network_end
			LIMIT 1
		) AS a 
		INNER JOIN countrylocations AS b on a.geoname_id = b.geoname_id
		WHERE network_start <= INET6_ATON('2001:67c:28a4::');

	Example queries to return all columns from city database:
	
		SELECT *
		FROM (
			SELECT * 
			FROM geocity 
			WHERE INET6_ATON('212.186.81.105') <= network_end
			LIMIT 1
		) AS a 
		INNER JOIN citylocations AS b on a.geoname_id = b.geoname_id
		WHERE network_start <= INET6_ATON('212.186.81.105');
		
		SELECT *
		FROM (
			SELECT * 
			FROM geocity 
			WHERE INET6_ATON('2001:67c:28a4::') <= network_end
			LIMIT 1
		) AS a 
		INNER JOIN citylocations AS b on a.geoname_id = b.geoname_id
		WHERE network_start <= INET6_ATON('2001:67c:28a4::');
		
	Example queries to return a combined string holding the autonomous_system_number and autonomous_system_organization from asn database:
		
		SELECT CONCAT('AS', autonomous_system_number,' ',autonomous_system_organization) AS result 
		FROM geoasn where INET6_ATON('2a02:a45f:1079:1::') between network_start and network_end order by network_end limit 1;
		

.LINK
	GitHub Repository: https://github.com/palinkas-jo-reggelt/GeoLite2SQL

#>

Param(
	[string]$SelectType
)

<#  Include required files  #>
Try {
	.("$PSScriptRoot\GeoLite2SQL-Config.ps1")
}
Catch {
	Write-Output "$(Get-Date -f G) : [ERROR] : Unable to load supporting PowerShell Scripts : $($Error[0])" | Out-File "$PSScriptRoot\PSError.log" -Append
}


<###   FUNCTIONS   ###>
Function Debug ($DebugOutput) {
	If ($VerboseFile) {Write-Output "$(Get-Date -f G) : $DebugOutput" | Out-File $DebugLog -Encoding ASCII -Append}
	If ($VerboseConsole) {Write-Host "$(Get-Date -f G) : $DebugOutput"}
}

Function Email ($Email) {
	If ($UseHTML){
		If ($Email -match "\[OK\]") {$Email = $Email -Replace "\[OK\]","<span style=`"background-color:green;color:white;font-weight:bold;font-family:Courier New;`">[OK]</span>"}
		If ($Email -match "\[INFO\]") {$Email = $Email -Replace "\[INFO\]","<span style=`"background-color:yellow;font-weight:bold;font-family:Courier New;`">[INFO]</span>"}
		If ($Email -match "\[ERROR\]") {$Email = $Email -Replace "\[ERROR\]","<span style=`"background-color:red;color:white;font-weight:bold;font-family:Courier New;`">[ERROR]</span>"}
		If ($Email -match "^\s$") {$Email = $Email -Replace "\s","&nbsp;"}
		Write-Output "<tr><td>$Email</td></tr>" | Out-File $EmailBody -Encoding ASCII -Append
	} Else {
		Write-Output $Email | Out-File $EmailBody -Encoding ASCII -Append
	}	
}

Function EmailResults {
	Debug "GeoIP $Type Update Finished"
	Email " "
	Email "GeoIP $Type Update Finish: $(Get-Date -f G)"
	If ($UseHTML) {
		If ($UseHTML) {Write-Output "</table></body></html>" | Out-File $EmailBody -Encoding ASCII -Append}
	}
	If (($AttachDebugLog) -and (Test-Path $DebugLog)) {
		If (((Get-Item $DebugLog).length/1MB) -gt $MaxAttachmentSize) {
			Email "Debug log too large to email. Please see file in GeoLite2SQL script folder."
		}
	}
	Try {
		$Body = (Get-Content -Path $EmailBody | Out-String )
		If (($AttachDebugLog) -and (Test-Path $DebugLog) -and (((Get-Item $DebugLog).length/1MB) -lt $MaxAttachmentSize)){$Attachment = New-Object System.Net.Mail.Attachment $DebugLog}
		$Message = New-Object System.Net.Mail.Mailmessage $EmailFrom, $EmailTo, $Subject, $Body
		$Message.IsBodyHTML = $UseHTML
		If (($AttachDebugLog) -and (Test-Path $DebugLog) -and (((Get-Item $DebugLog).length/1MB) -lt $MaxAttachmentSize)){$Message.Attachments.Add($DebugLog)}
		$SMTP = New-Object System.Net.Mail.SMTPClient $SMTPServer,$SMTPPort
		$SMTP.EnableSsl = $UseSSL
		$SMTP.Credentials = New-Object System.Net.NetworkCredential($SMTPAuthUser, $SMTPAuthPass); 
		$SMTP.Send($Message)
	}
	Catch {
		Debug "Email ERROR : $($Error[0])"
	}
}

Function EmailInitError {
	$Body = "Failed to provide proper parameter. Use 'city' or 'country'. Script quit on parameter error."
	$Message = New-Object System.Net.Mail.Mailmessage $EmailFrom, $EmailTo, $Subject, $Body
	$Message.IsBodyHTML = $False
	$SMTP = New-Object System.Net.Mail.SMTPClient $SMTPServer,$SMTPPort
	$SMTP.EnableSsl = $UseSSL
	$SMTP.Credentials = New-Object System.Net.NetworkCredential($SMTPAuthUser, $SMTPAuthPass); 
	$SMTP.Send($Message)
}

Function Plural ($Integer) {
	If ($Integer -eq 1) {$S = ""} Else {$S = "s"}
	Return $S
}

Function ElapsedTime ($EndTime) {
	$TimeSpan = New-Timespan $EndTime
	If (([int]($TimeSpan).Hours) -eq 0) {$Hours = ""} ElseIf (([int]($TimeSpan).Hours) -eq 1) {$Hours = "1 hour "} Else {$Hours = "$([int]($TimeSpan).Hours) hours "}
	If (([int]($TimeSpan).Minutes) -eq 0) {$Minutes = ""} ElseIf (([int]($TimeSpan).Minutes) -eq 1) {$Minutes = "1 minute "} Else {$Minutes = "$([int]($TimeSpan).Minutes) minutes "}
	If (([int]($TimeSpan).Seconds) -eq 1) {$Seconds = "1 second"} Else {$Seconds = "$([int]($TimeSpan).Seconds) seconds"}
	
	If (($TimeSpan).TotalSeconds -lt 1) {
		$Return = "less than 1 second"
	} Else {
		$Return = "$Hours$Minutes$Seconds"
	}
	Return $Return
}

Function MySQLQuery($Query) {
	$Today = (Get-Date).ToString("yyyyMMdd")
	$DBErrorLog = "$PSScriptRoot\$Today-DBError.log"
	$ConnectionString = "server=" + $MySQLHost + ";port=" + $MySQLPort + ";uid=" + $MySQLUserName + ";pwd=" + $MySQLPassword + ";database=" + $MySQLDatabase + ";SslMode=" + $MySQLSSL + ";Default Command Timeout=" + $MySQLCommandTimeOut + ";Connect Timeout=" + $MySQLConnectTimeout + ";Allow User Variables=True;AllowLoadLocalInfile=true;"
	$Error.Clear()
	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 {
		Debug "[ERROR] DATABASE ERROR : Unable to run query : $Query `n$($Error[0])`n$($Error[1])`n$($Error[2])`n$($Error[3])"
	}
	Finally {
		$Connection.Close()
	}
}

Function CheckForUpdates {
	Debug "----------------------------"
	Debug "Checking for script update at GitHub"
	$GitHubVersion = $LocalVersion = $NULL
	$GetGitHubVersion = $GetLocalVersion = $False
	$GitHubVersionTries = 1
	Do {
		Try {
			$GitHubVersion = [decimal](Invoke-WebRequest -UseBasicParsing -Method GET -URI https://raw.githubusercontent.com/palinkas-jo-reggelt/GeoLite2SQL/master/version.txt).Content
			$GetGitHubVersion = $True
		}
		Catch {
			Debug "[ERROR] Obtaining GitHub version : Try $GitHubVersionTries : Obtaining version number: $($Error[0])"
		}
		$GitHubVersionTries++
	} Until (($GitHubVersion -gt 0) -or ($GitHubVersionTries -eq 6))
	If (Test-Path "$PSScriptRoot\version.txt") {
		$LocalVersion = [decimal](Get-Content "$PSScriptRoot\version.txt")
		$GetLocalVersion = $True
	}
	If (($GetGitHubVersion) -and ($GetLocalVersion)) {
		If ($LocalVersion -lt $GitHubVersion) {
			Debug "[INFO] Upgrade to version $GitHubVersion available at https://github.com/palinkas-jo-reggelt/GeoLite2SQL"
			If ($UseHTML) {
				Email "[INFO] Upgrade to version $GitHubVersion available at <a href=`"https://github.com/palinkas-jo-reggelt/GeoLite2SQL`">GitHub</a>"
			} Else {
				Email "[INFO] Upgrade to version $GitHubVersion available at https://github.com/palinkas-jo-reggelt/GeoLite2SQL"
			}
		} Else {
			Debug "GeoLite2SQL script is latest version: $GitHubVersion"
		}
	} Else {
		If ((-not($GetGitHubVersion)) -and (-not($GetLocalVersion))) {
			Debug "[ERROR] Version test failed : Could not obtain either GitHub nor local version information"
			Email "[ERROR] Version check failed"
		} ElseIf (-not($GetGitHubVersion)) {
			Debug "[ERROR] Version test failed : Could not obtain version information from GitHub"
			Email "[ERROR] Version check failed"
		} ElseIf (-not($GetLocalVersion)) {
			Debug "[ERROR] Version test failed : Could not obtain local install version information"
			Email "[ERROR] Version check failed"
		} Else {
			Debug "[ERROR] Version test failed : Unknown reason - file issue at GitHub"
			Email "[ERROR] Version check failed"
		}
	}
}


<###   BEGIN SCRIPT   ###>
If ($SelectType -notmatch '^[aA][sS][nN]$|^[cC][iI][tT][yY]$|^[cC][oO][uU][nN][tT][rR][yY]$') {
	Write-Host "Failed to provide proper parameter. Use 'city' or 'country'."
	Write-Host "Quitting Script"
	EmailInitError
	Exit
} Else {
	If ($SelectType -match 'country') {
		$Type = "Country"
	} ElseIf ($SelectType -match 'asn') {
		$Type = "ASN"
	} ElseIf ($SelectType -match 'city') {
		$Type = "City"
	}
}

<#  Clear out any errors  #>
$Error.Clear()

<#  Set file locations  #>
$DownloadFolder = "$PSScriptRoot\Script-Created-Files\GeoLite2-" + $Type + "-CSV"
$DownloadedZip = "$DownloadFolder.zip"
$BlocksIPV4 = "$DownloadFolder\GeoLite2-" + $Type + "-Blocks-IPv4.csv"
$BlocksIPV6 = "$DownloadFolder\GeoLite2-" + $Type + "-Blocks-IPv6.csv"
$BlocksConvertedIPv4 = "$DownloadFolder\Geo" + $Type + "IPv4.csv"
$BlocksConvertedIPv6 = "$DownloadFolder\Geo" + $Type + "IPv6.csv"
$LangLocations = "$DownloadFolder\GeoLite2-" + $Type + "-Locations-" + $LocationLanguage + ".csv"
$LocationsRenamed = "$DownloadFolder\GeoLocations.csv"
$EmailBody = "$PSScriptRoot\Script-Created-Files\EmailBody.txt"
$DebugLog = "$PSScriptRoot\Script-Created-Files\DebugLog.log"

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

<#	Delete old debug log before debugging  #>
If (Test-Path $DebugLog) {Remove-Item -Force -Path $DebugLog}
If (Test-Path $EmailBody) {Remove-Item -Force -Path $EmailBody}
New-Item $DebugLog
New-Item $EmailBody

<#  Fill debug log header  #>
Write-Output "::: MaxMind Geo$Type Database Import Routine $(Get-Date -f D) :::" | Out-File $DebugLog -Encoding ASCII -Append
Write-Output " " | Out-File $DebugLog -Encoding ASCII -Append

<#  Fill email header  #>
If ($UseHTML) {
	Write-Output "
		<!DOCTYPE html><html>
		<head><meta name=`"viewport`" content=`"width=device-width, initial-scale=1.0 `" /></head>
		<body style=`"font-family:Arial Narrow`">
		<table>
		<tr><td style='text-align:center;'>::: MaxMind Geo$Type Database Import Routine $(Get-Date -f D) :::</td></tr>
		<tr><td>&nbsp;</td></tr>
	" | Out-File $EmailBody -Encoding ASCII -Append
} Else {
	Write-Output "::: MaxMind Geo$Type Database Import Routine $(Get-Date -f D) :::" | Out-File $EmailBody -Encoding ASCII -Append
	Write-Output " " | Out-File $EmailBody -Encoding ASCII -Append
}

<#  Set start time  #>
$StartScriptTime = Get-Date
Debug "GeoIP $Type Update Start"
Email "GeoIP $Type Update Start: $(Get-Date -f G)"
Email " "

<#  Check for updates  #>
CheckForUpdates

<#	Delete old MaxMind files if exist  #>
Debug "----------------------------"
Debug "Deleting old files"
If (Test-Path $DownloadFolder) {
	Try {
		Remove-Item -Recurse -Force $DownloadFolder
		If (Test-Path $DownloadFolder) {
			Throw "Test-Path on $DownloadFolder failed - nothing deleted"
		} Else {
			Debug "Folder $DownloadFolder successfully deleted"
		}
	}
	Catch {
		Debug "[ERROR] : Unable to delete old MaxMind data : $($Error[0])"
		Debug "[ERROR] : Quitting Script"
		Email "[ERROR] Failed to delete old MaxMind data. See error log."
		EmailResults
		Exit
	}
} Else {
	Debug "No old files to delete"
}

<#	Download latest GeoLite2 data  #>
Debug "----------------------------"
Debug "Downloading MaxMind data"
$Timer = Get-Date
$URL = "https://download.maxmind.com/app/geoip_download?edition_id=GeoLite2-" + $Type + "-CSV&license_key=" + $LicenseKey + "&suffix=zip"
Try {
	Start-BitsTransfer -Source $URL -Destination $DownloadedZip -ErrorAction Stop
	If (Test-Path $DownloadedZip) {
		Debug "MaxMind data successfully downloaded in $(ElapsedTime $Timer)"
		Email "[OK] MaxMind data downloaded"
	} Else {
		Throw "MaxMind zip file could not be found after apparently successful download"
	}
}
Catch {
	Debug "[ERROR] : Unable to download MaxMind data on try # $Try"
	Debug "[ERROR] : Error Message : $($Error[0])"
	EmailResults
	Exit
}

<#	Unzip fresh GeoLite2 data  #>
$Timer = Get-Date
Try {
	Expand-Archive $DownloadedZip -DestinationPath "$PSScriptRoot\Script-Created-Files" -ErrorAction Stop
	Debug "MaxMind data successfully unzipped in $(ElapsedTime $Timer)"
	Email "[OK] MaxMind data unzipped"
}
Catch {
	Debug "[ERROR] : Unable to unzip MaxMind data : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to unzip MaxMind data. See error log."
	EmailResults
	Exit
}

<#	Rename GeoLite2 data folder so script can find it  #>
Get-ChildItem "$PSScriptRoot\Script-Created-Files" | Where-Object {$_.PSIsContainer -eq $true} | ForEach {
	[regex]$strRegEx = "GeoLite2-" + $Type + "-CSV_[0-9]{8}"
	If ($_.Name -match $strRegEx) {
		$FolderName = $_.FullName
		Rename-Item $FolderName $DownloadFolder
	}
}

<# 	If new downloaded folder does not exist or could not be renamed, then throw error  #>
If (-not (Test-Path $DownloadFolder)){
	Debug "[ERROR] : Unable to rename data folder : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to rename data folder. See error log."
	EmailResults
	Exit
}

<#  Rename Locations CSV  #>
Try {
	If ($Type -notmatch "asn") {
		Rename-Item $LangLocations $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
}

<#  Count database records  #>
Debug "----------------------------"
Debug "Counting database records for comparison"
$Query = "SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_schema = '" + $MySQLDatabase + "' AND table_name = 'geo" + $Type + "'"
MySQLQuery $Query | ForEach {
	[int]$CountTables = $_.count
}
If ($CountTables -gt 0) {
	$Query = "SELECT COUNT(*) AS count FROM geo" + $Type
	MySQLQuery $Query | ForEach {
		[int]$CountDB = $_.count
	}
	If ($CountDB -eq 0) {
		Debug "0 database records prior to starting update"
	} Else { 
		Debug "$(($CountDB).ToString('#,###')) database records prior to starting update"
	}
} Else {
	Debug "No database records to count"
	[int]$CountDB = 0
}

<#  Count CSV records  #>
Debug "----------------------------"
Debug "Counting CSV records for comparison"
$Timer = Get-Date
[int]$CountIPs = 0
$Reader = New-Object IO.StreamReader $BlocksIPV4
While($Reader.ReadLine() -ne $NULL) {$CountIPs++}
$Reader = New-Object IO.StreamReader $BlocksIPV6
While($Reader.ReadLine() -ne $NULL) {$CountIPs++}
$CountIPs = $CountIPs - 2  # Remove headers from count
Debug "Counted $(($CountIPs).ToString('#,###')) records in new IPv4 & IPv6 CSVs in $(ElapsedTime $Timer)"

<#  Convert CSV for import  #>
Debug "----------------------------"
Debug "Converting CSVs to hex-range"
$Timer = Get-Date
Try {
	& $GeoIP2CSVConverter -block-file="$BlocksIPV4" -output-file="$BlocksConvertedIPv4" -include-hex-range
	Debug "$Type IPv4 CSV successfully converted to hex-range in $(ElapsedTime $Timer)"
	Email "[OK] Converted IPv4 $Type block CSV"
}
Catch {
	Debug "[ERROR] : Unable to convert $Type IPv4 CSV : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to convert $Type IPv4 CSV. See error log."
	EmailResults
	Exit
}

$Timer = Get-Date
Try {
	& $GeoIP2CSVConverter -block-file="$BlocksIPV6" -output-file="$BlocksConvertedIPv6" -include-hex-range
	Debug "$Type IPv6 CSV successfully converted to hex-range in $(ElapsedTime $Timer)"
	Email "[OK] Converted IPv6 $Type block CSV"
}
Catch {
	Debug "[ERROR] : Unable to convert $Type IPv6 CSV : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to convert $Type IPv6 CSV. See error log."
	EmailResults
	Exit
}

<#  Drop and add database tables  #>
Debug "----------------------------"
Debug "Drop and recreate database tables"
Try {
	If ($Type -match "country") {
		$GCQuery = "
			DROP TABLE IF EXISTS geocountry;
			CREATE TABLE geocountry (
				network_start VARBINARY(16) NOT NULL,
				network_end VARBINARY(16) NOT NULL,
				geoname_id INT,
				registered_country_geoname_id INT,
				represented_country_geoname_id INT,
				is_anonymous_proxy BOOL,
				is_satellite_provider BOOL,
				INDEX(network_start),
				INDEX(network_end)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		"
	} ElseIf ($Type -match "asn") {
		$GCQuery = "
			DROP TABLE IF EXISTS geoasn;
			CREATE TABLE geoasn (
				network_start VARBINARY(16) NOT NULL,
				network_end VARBINARY(16) NOT NULL,
				autonomous_system_number INT,
				autonomous_system_organization TEXT,
				INDEX(network_start),
				INDEX(network_end)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		"
	} ElseIf ($Type -match "city") {
		$GCQuery = "
			DROP TABLE IF EXISTS geocity;
			CREATE TABLE geocity (
				network_start VARBINARY(16) NOT NULL,
				network_end VARBINARY(16) NOT NULL,
				geoname_id INT,
				registered_country_geoname_id INT,
				represented_country_geoname_id INT,
				is_anonymous_proxy BOOL,
				is_satellite_provider BOOL,
				postal_code TEXT,
				latitude FLOAT,
				longitude FLOAT,
				accuracy_radius INT,				
				INDEX(network_start),
				INDEX(network_end)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		"
	}
	MySQLQuery $GCQuery

	If ($Type -notmatch "asn") {
		If ($Type -match "country") {
			$GLQuery = "
				DROP TABLE IF EXISTS countrylocations;
				CREATE TABLE countrylocations (
					geoname_id INT NOT NULL,
					locale_code TEXT NOT NULL,
					continent_code TINYTEXT NOT NULL,
					continent_name TINYTEXT NOT NULL,
					country_code TINYTEXT,
					country_name TINYTEXT,
					is_in_european_union BOOL,
					PRIMARY KEY (geoname_id, locale_code(5))
				) ENGINE=InnoDB DEFAULT CHARSET=utf8;
			"
		} ElseIf ($Type -match "city") {
			$GLQuery = "
				DROP TABLE IF EXISTS citylocations;
				CREATE TABLE citylocations (
					geoname_id INT NOT NULL,
					locale_code TEXT NOT NULL,
					continent_code TINYTEXT NOT NULL,
					continent_name TINYTEXT NOT NULL,
					country_code TINYTEXT,
					country_name TINYTEXT,
					subdivision_1_iso_code TINYTEXT,
					subdivision_1_name TINYTEXT,
					subdivision_2_iso_code TINYTEXT,
					subdivision_2_name TINYTEXT,
					city_name TINYTEXT,
					metro_code INT,
					time_zone TINYTEXT,
					is_in_european_union BOOL,
					PRIMARY KEY (geoname_id, locale_code(5))
				) ENGINE=InnoDB DEFAULT CHARSET=utf8;
			"
		}
		MySQLQuery $GLQuery
	}
	Debug "Database tables successfully dropped and created"
	Email "[OK] Database tables dropped & recreated"
}
Catch {
	Debug "[ERROR] : Unable to drop/create database tables : $($Error[0])"
	Debug "[ERROR] : Quitting Script"
	Email "[ERROR] Failed to drop/create database tables. See error log."
	EmailResults
	Exit
}

<#  Import to database  #>
Debug "----------------------------"
Debug "Import converted CSVs to database"
$StrFileLocHash = @{
	"IPv4" = "$($BlocksConvertedIPv4 -Replace '\\','\\')"
	"IPv6" = "$($BlocksConvertedIPv6 -Replace '\\','\\')"
}
ForEach ($IPver in $StrFileLocHash.Keys) {
	$Timer = Get-Date
	Try {
		If ($Type -match "country") {
			$ImportIPv4Query = "
				LOAD DATA INFILE '" + $($strFileLocHash[$IPver]) + "'
				INTO TABLE geocountry
				FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '`"' LINES TERMINATED BY '\n' IGNORE 1 ROWS
				(@network_start_hex, @network_last_hex, @geoname_id, @registered_country_geoname_id, @represented_country_geoname_id, @is_anonymous_proxy, @is_satellite_provider)
				SET 
					network_start = UNHEX(@network_start_hex),
					network_end = UNHEX(@network_last_hex),
					geoname_id = NULLIF(@geoname_id, ''),
					registered_country_geoname_id = NULLIF(@registered_country_geoname_id, ''),
					represented_country_geoname_id = NULLIF(@represented_country_geoname_id, ''),
					is_anonymous_proxy = NULLIF(@is_anonymous_proxy, ''),
					is_satellite_provider = NULLIF(@is_satellite_provider, '');
			"
		} ElseIf ($Type -match "asn") {
			$ImportIPv4Query = "
				LOAD DATA INFILE '" + $($strFileLocHash[$IPver]) + "'
				INTO TABLE geoasn 
				FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '`"' LINES TERMINATED BY '\n' IGNORE 1 ROWS 
				( @network_start_hex, @network_last_hex, @autonomous_system_number, @autonomous_system_organization ) 
				SET 
					network_start = UNHEX( @network_start_hex ),
					network_end = UNHEX( @network_last_hex ),
					autonomous_system_number = NULLIF( @autonomous_system_number, '' ),
					autonomous_system_organization = NULLIF( @autonomous_system_organization, '' );
			"
		} ElseIf ($Type -match "city") {
			$ImportIPv4Query = "
				LOAD DATA INFILE '" + $($strFileLocHash[$IPver]) + "'
				INTO TABLE geocity
				FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '`"' LINES TERMINATED BY '\n' IGNORE 1 ROWS
				(@network_start_hex, @network_last_hex, @geoname_id, @registered_country_geoname_id, @represented_country_geoname_id, @is_anonymous_proxy, @is_satellite_provider, @postal_code, @latitude, @longitude, @accuracy_radius)
				SET 
					network_start = UNHEX(@network_start_hex),
					network_end = UNHEX(@network_last_hex),
					geoname_id = NULLIF(@geoname_id, ''),
					registered_country_geoname_id = NULLIF(@registered_country_geoname_id, ''),
					represented_country_geoname_id = NULLIF(@represented_country_geoname_id, ''),
					is_anonymous_proxy = NULLIF(@is_anonymous_proxy, ''),
					is_satellite_provider = NULLIF(@is_satellite_provider, ''),
					postal_code = NULLIF(@postal_code, ''),
					latitude = NULLIF(@latitude, ''),
					longitude = NULLIF(@longitude, ''),
					accuracy_radius = NULLIF(@accuracy_radius, '');
			"
		}
		MySQLQuery $ImportIPv4Query
		DEBUG "[OK] $Type $IPver data imported in $(ElapsedTime $Timer)"
	}
	Catch {
		Debug "[ERROR] : Unable to convert $Type $IPver CSV : $($Error[0])"
		Debug "[ERROR] : Quitting Script"
		Email "[ERROR] Failed to convert $Type $IPver CSV. See error log."
		EmailResults
		Exit
	}
}

<#  Import name data  #>
If ($Type -notmatch "asn") {
	$Timer = Get-Date
	Try {
		If ($Type -match "country") {
			$ImportLocQuery = "
				LOAD DATA INFILE '" + $($LocationsRenamed -Replace '\\','\\') + "'
				INTO TABLE countrylocations
				FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '`"' LINES TERMINATED BY '\n' IGNORE 1 ROWS
				(@geoname_id, @locale_code, @continent_code, @continent_name, @country_code, @country_name, @is_in_european_union)
				SET
					geoname_id = NULLIF(@geoname_id, ''), 
					locale_code = NULLIF(@locale_code, ''), 
					continent_code = NULLIF(@continent_code, ''), 
					continent_name = NULLIF(@continent_name, ''), 
					country_code = NULLIF(@country_code, ''), 
					country_name = NULLIF(@country_name, ''), 
					is_in_european_union = NULLIF(@is_in_european_union, '');
			"
		} ElseIf ($Type -match "city") {
			$ImportLocQuery = "
				LOAD DATA INFILE '" + $($LocationsRenamed -Replace '\\','\\') + "'
				INTO TABLE citylocations
				FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '`"' LINES TERMINATED BY '\n' IGNORE 1 ROWS
				(@geoname_id, @locale_code, @continent_code, @continent_name, @country_code, @country_name, @subdivision_1_iso_code, @subdivision_1_name, @subdivision_2_iso_code, @subdivision_2_name, @city_name, @metro_code, @time_zone, @is_in_european_union)
				SET 
					geoname_id = NULLIF(@geoname_id, ''), 
					locale_code = NULLIF(@locale_code, ''), 
					continent_code = NULLIF(@continent_code, ''), 
					continent_name = NULLIF(@continent_name, ''), 
					country_code = NULLIF(@country_code, ''), 
					country_name = NULLIF(@country_name, ''), 
					subdivision_1_iso_code = NULLIF(@subdivision_1_iso_code, ''), 
					subdivision_1_name = NULLIF(@subdivision_1_name, ''), 
					subdivision_2_iso_code = NULLIF(@subdivision_2_iso_code, ''), 
					subdivision_2_name = NULLIF(@subdivision_2_name, ''), 
					city_name = NULLIF(@city_name, ''), 
					metro_code = NULLIF(@metro_code, ''), 
					time_zone = NULLIF(@time_zone, ''), 
					is_in_european_union = NULLIF(@is_in_european_union, '');
			"
		}
		MySQLQuery $ImportLocQuery
		DEBUG "[OK] $Type name data imported in $(ElapsedTime $Timer)"
	}
	Catch {
		Debug "[ERROR] : Unable to convert $Type name CSV : $($Error[0])"
		Debug "[ERROR] : Quitting Script"
		Email "[ERROR] Failed to convert $Type name CSV. See error log."
		EmailResults
		Exit
	}
}

$CountImportSQL = "SELECT COUNT(*) AS count FROM geo" + $Type + ";"
MySQLQuery $CountImportSQL | ForEach {
	[int]$CountImport = $_.count
}

<#  Now finish up  #>
Debug "----------------------------"
If ($CountImport -eq $CountIPs) {
	Email "[OK] Successfully imported $(($CountImport).ToString('#,###')) records"
	Email "[OK] Finished update in $(ElapsedTime $StartScriptTime)"
	Email ("[INFO] Net change of {0:n0} records since last update" -f ($CountImport - $CountDB))
	Debug "Successfully imported $(($CountImport).ToString('#,###')) records"
	Debug "Finished update in $(ElapsedTime $StartScriptTime)"
	Debug ("[INFO] Net change of {0:n0} records since last update" -f ($CountImport - $CountDB))
} Else {
	If (($CountIPs - $CountImport) -lt 0) {$Mismatch = ($CountImport - $CountIPs)} Else {$Mismatch = ($CountIPs - $CountImport)}
	Email "[ERROR] Count mismatched by $(($Mismatch).ToString('#.###')) records"
	Email "$(($CountImport).ToString('#,###')) records imported to database"
	Email "$(($CountIPs).ToString('#,###')) records in MaxMind CSV"
	Email "Completed update in $(ElapsedTime $StartScriptTime)"
	Debug "[ERROR] record count mismatch:"
	Debug "$(($CountImport).ToString('#,###')) records imported to database"
	Debug "$(($CountIPs).ToString('#,###')) records in MaxMind CSV"
	Debug "Completed update in $(ElapsedTime $StartScriptTime)"
}

<#  Email results  #>
EmailResults

Code: Select all

Function ASNLookup(byVal sIPAddress, ByRef strResult) : ASNLookup = false
    Dim oRecord, oConn : Set oConn = CreateObject("ADODB.Connection")
    oConn.Open "Driver={MariaDB ODBC 3.1 Driver};Server=localhost;Database="&DBNAME&";Uid="&DBUID&";Pwd="&DBPW&";Option=2;"
    If oConn.State <> 1 Then
        EventLog.Write( "GeoIPLookup - ERROR: Could not connect to database" )
        Exit Function
    End If
    
    Set oRecord = oConn.Execute("SELECT CONCAT('AS', autonomous_system_number,' ',autonomous_system_organization) AS result FROM geoasn where INET6_ATON('" & sIPAddress & "') between network_start and network_end order by network_end limit 1;")
    If Not oRecord.EOF Then
        ASNLookup = True
        strResult = oRecord.Fields(0).Value
    End If
    Set oRecord = Nothing
    
    oConn.Close
    Set oConn = Nothing
End Function

Code: Select all

Dim strOutput : strOutput = Empty
If ASNLookup(oClient.IPAddress, strOutput) Then
    If (StrComp(strOutput,"AS8075 MICROSOFT-CORP-MSN-AS-BLOCK") = 0) Then 
        EventLog.Write(strOutput & ": " & oClient.IPAddress)
        Exit Sub
    End if
End If
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

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

Re: MaxMinds GeoIP for local MySQL

Post by palinka » 2024-07-15 13:47

Looks good. (I haven't looked - just looked at the basic idea). :mrgreen:

Post Reply