GeoLite2SQL/GeoLite2SQL-Config.dist.ps1 at master · gotspatel/GeoLite2SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
<#
.SYNOPSIS
	Install MaxMind GeoLite2 database to local database server

.DESCRIPTION
	Config file to GeoLite2SQL project

.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!
	--!!!--

	LANGUAGE OPTIONS
	* Options offered by MaxMind - included in GeoLite2 package
	* de, en, es, fr, ja, pt-BR, ru, zh=CN

	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" and "city".

.EXAMPLE
	Run script as follows:
	C:\path\to\Geolite2SQL.ps1 country
	C:\path\to\Geolite2SQL.ps1 city
	C:\path\to\Geolite2SQL.ps1 asn

	C:\Scripts\GeoIP2\Geolite2SQL.ps1 country
	C:\Scripts\GeoIP2\Geolite2SQL.ps1 city
	C:\Scripts\GeoIP2\Geolite2SQL.ps1 asn
.EXAMPLE
	SELECT country_iso_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_iso_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::');
.LINK
	GitHub Repository: https://github.com/palinkas-jo-reggelt/GeoLite2SQL

#>

###   Database Variables   ###
$MySQLUserName        = "geoip"
$MySQLPassword        = "supersecretpassword"
$MySQLDatabase        = "geoip"
$MySQLHost            = "localhost"
$MySQLPort            =  3306
$MySQLSSL             = "none"
$MySQLConnectTimeout  = 300
$MySQLCommandTimeOut  = 9000000        # Leave high if read errors
$MySQLImport          = "C:\xampp\mysql\bin\mysqlimport.exe"

###   Email Variables   ###
#$EmailFrom            = "notify@mydomain.tld"  	## moved to Function PS for updating the name of GEO DATA
$Emailid              = "notify@mydomain.tld"		## Added the Variable for EMAIL ID
$EmailTo              = "admin@mydomain.tld"
# $Subject              = "GeoIP Update"							## moved to Function PS for updating the name of GEO DATA
$SMTPServer           = "mail.mydomain.tld"
$SMTPAuthUser         = "notify@mydomain.tld"
$SMTPAuthPass         = "supersecretpassword"
$SMTPPort             =  587
$UseSSL               = $True          # Use SSL in email relay
$UseHTML              = $True          # If true, email in html format
$AttachDebugLog       = $True          # Attach debug log to email
$MaxAttachmentSize    = 1              # Size in MB

###   Script Variables   ###
$LicenseKey           = "supersecretlicensekey"
$LocationLanguage     = "en"
$GeoIP2CSVConverter   = "$PSScriptRoot\geoip2-csv-converter\geoip2-csv-converter.exe"

###   Verbosity   ###
# You can choose one, both or neither
$VerboseConsole       = $True          # Debug to screen
$VerboseFile          = $True          # Debug to file