Instruction for preparing geoip database for geoip script (for Linux):
I. Instruction for GeoLite2 database by MaxMind:
II. Instruction for abandoned GeoLite database by MaxMind:
I. Instruction for GeoLite2 database by MaxMind:
1. Download GeoLite2 Country CSV database from GeoLite2 Free Downloadable Databases (GeoLite2 Country), unzip it.
2. Prepare list of country abbreviations with assigned countries (a - convert non-ASCII letters to ASCII, b - remove first line, c - print country abbreviations and names, d - add anonymous proxy):
3. Print CIDR and country id, remove satellite providers, and remove first line (b):
4. Replace country id by country abbreviation:
5. Add last IP in CIDR range (c - combine 2 files, d - remove dots):
6. Calculate ipnum:
7. a - separate range common for two 3-octets ranges, b - divide range for ranges started from different first octets, c & d - remove duplicates:
8. Assign all countries from the same range to one ipnum, print ipnum and country abbreviations to separate files:
9. Remove temporary files
2. Prepare list of country abbreviations with assigned countries (a - convert non-ASCII letters to ASCII, b - remove first line, c - print country abbreviations and names, d - add anonymous proxy):
[SELECT ALL] Code:
a) iconv -f UTF8 -t US-ASCII//TRANSLIT GeoLite2-Country-Locations-en.csv > copy_GeoLite2-Country-Locations-en.csv
b) sed -i '1d' copy_GeoLite2-Country-Locations-en.csv
c) awk 'BEGIN {FPAT="([^,]+)|(\"[^\"]+\")"} {print $(NF-2), $(NF-1)}' copy_GeoLite2-Country-Locations-en.csv |sort > geoip_countrylist_temp.txt
d) echo "AA \"Anonymous proxy\"" >> geoip_countrylist_temp.txt
e) awk 'BEGIN {print "persistidents 0"; print "const tmp_geoip_countrylist ["} {print $0} END{print "]"; print "persistidents 1"}' geoip_countrylist_temp.txt > geoip_countrylist.txt
[SELECT ALL] Code:
a) awk 'BEGIN {FPAT="([^,]+)|(\"[^\"]+\")"} {if(! $NF) {if(! $(NF-1)) {print $1, $2} else {print $1, "1234"} }}' GeoLite2-Country-Blocks-IPv4.csv > copy_GeoLite2-Country-Blocks-IPv4.csv
b) sed -i '1d' copy_GeoLite2-Country-Blocks-IPv4.csv
[SELECT ALL] Code:
a) awk 'BEGIN {FPAT="([^,]+)|(\"[^\"]+\")"} {print $1, $(NF-2)}' copy_GeoLite2-Country-Locations-en.csv > geoip_countrylist_temp.txt
b) echo "1234 AA" >> geoip_countrylist_temp.txt
c) awk 'FNR==NR{a[$1]=$2;next} {($2 in a)sub($2, a[$2]); print}' geoip_countrylist_temp.txt copy_GeoLite2-Country-Blocks-IPv4.csv > copy2_GeoLite2-Country-Blocks-IPv4.csv
[SELECT ALL] Code:
a) awk '{print $1}' copy_GeoLite2-Country-Blocks-IPv4.csv > endIPrange.txt
b) sipcalc - < endIPrange.txt |awk '/Broadcast address/{print $4}' > endIPrange2.txt
c) paste -d" " copy2_GeoLite2-Country-Blocks-IPv4.csv endIPrange2.txt > copy_GeoLite2-Country-Blocks-IPv4.csv
d) vim "+%s/\./\ /g" "+wq" copy_GeoLite2-Country-Blocks-IPv4.csv
[SELECT ALL] Code:
awk '{print $1, $2, $3, $6, $7, $8, (65536 * $1 + 256 * $2 + $3), $5}' copy_GeoLite2-Country-Blocks-IPv4.csv > copy2_GeoLite2-Country-Blocks-IPv4.csv
[SELECT ALL] Code:
a) awk 'BEGIN {ipnum_p=0; first_p=0; second_p=0; third_p=0; abbr_p=0} {if ($7!=ipnum_p) {if ($1==first_p && $2==second_p && $3==third_p) {print $1, $2, $3, $1, $2, $3, $7, abbr_p; print $1, $2, $3, $1, $2, $3, $7, $8; if ($3!=255) {print $1, $2, $3+1, $4, $5, $6, $7+1, $8} else {a=$1; b=$2+1; if ($2==255) {a=$1+1; b=0}; print a, b, 0, $4, $5, $6, a*65536+b*256, $8}} else {print $0}} else if ($4==first_p && $5==second_p && $6==third_p) {print $0} else {print $1, $2, $3, $1, $2, $3, $7, $8; if ($3!=255) {print $1, $2, $3+1, $4, $5, $6, $7+1, $8} else {a=$1; b=$2+1; if ($2==255) {a=$1+1; b=0}; print a, b, 0, $4, $5, $6, a*65536+b*256, $8}}; first_p=$4; second_p=$5; third_p=$6; ipnum_p=$7; abbr_p=$8}' copy2_GeoLite2-Country-Blocks-IPv4.csv > copy_GeoLite2-Country-Blocks-IPv4.csv
b) awk '{if ($1==$4) {print $0} else {print $1, $2, $3, $1, 255, 255, $7, $8; for (i=$1+1; i<=$4; i++) {if ($4-i>=1) {print i, 0, 0, i, 255, 255, i*65536, $8 } else {print $4, 0, 0, $4, $5, $6, $4*65536, $8}}}}' copy_GeoLite2-Country-Blocks-IPv4.csv > copy2_GeoLite2-Country-Blocks-IPv4.csv
c) sort -n -k 7 copy2_GeoLite2-Country-Blocks-IPv4.csv > copy_GeoLite2-Country-Blocks-IPv4.csv
d) uniq -f 6 copy_GeoLite2-Country-Blocks-IPv4.csv > copy2_GeoLite2-Country-Blocks-IPv4.csv
[SELECT ALL] Code:
a) awk '{x = sprintf("%s %s %s %s %s %s %s", $1, $2, $3, $4, $5, $6, $7); if(x in a) a[x]=sprintf("%s%s", a[x], $8); else a[x]=$8} END {for (k in a) {print k, a[k] } }' copy2_GeoLite2-Country-Blocks-IPv4.csv| sort -n -k 7 > copy_GeoLite2-Country-Blocks-IPv4.csv
b) awk 'BEGIN {a=1; print "persistidents 0"; print "const tmp_geoip_ipnum_1 ["} {if ($1==a) {print $7} else {a=$1; print "]"; print "const tmp_geoip_ipnum_" a " ["; print $7}} END{print "]"; print "persistidents 1"}' copy_GeoLite2-Country-Blocks-IPv4.csv > geoip_ipnum.txt
c) awk 'BEGIN {a=1; print "persistidents 0"; print "const tmp_geoip_abbr_1 ["} {if ($1==a) {print $8} else {a=$1; print "]"; print "const tmp_geoip_abbr_" a " ["; print $8}} END{print "]"; print "persistidents 1"}' copy_GeoLite2-Country-Blocks-IPv4.csv > geoip_abbr.txt
[SELECT ALL] Code:
rm -f copy*.csv geoip_countrylist_temp.txt endIPrange*.txt
II. Instruction for abandoned GeoLite database by MaxMind:
1. Download GeoLite Country CSV database from https://dev.maxmind.com/geoip/legacy/geolite/ ( http://geolite.maxmind.com/download/geoi...tryCSV.zip)
2. Unzip GeoIPCountryCSV.zip and make copy of GeoIPCountryWhois.csv file:
3. Delete comas:
4. Prepare list of country abbreviations with assigned countries (Sattelite Provider is removed) and remove useless white spaces:
5. Isolate IP of ranges and country abbreviation:
6. Remove dots and quotation marks:
7. Calculate ipnum:
8. Remove ranges of "Satellite Providers" and change order:
9. a - separate range common for two 3-octets ranges, b - divide range for ranges started from different first octets, c & d - remove duplicates:
10. Assign all countries from the same range to one ipnum, print ipnum and country abbreviations to separate files:
11. Remove temporary files
2. Unzip GeoIPCountryCSV.zip and make copy of GeoIPCountryWhois.csv file:
[SELECT ALL] Code:
cp GeoIPCountryWhois.csv copy_GeoIPCountryWhois.csv
[SELECT ALL] Code:
vim "+%s/\,/\ /g" "+wq" copy_GeoIPCountryWhois.csv
[SELECT ALL] Code:
a) awk '$5!="\"A2\"" {print $5 " " $6 " " $7 " " $8 " " $9 " " $10 " " $11 " " $12 " " $13 " " $14 " " $15}' copy_GeoIPCountryWhois.csv |sort |uniq > geoip_countrylist_temp.txt
b) vim "+%s/\s\{2,}//" "+wq" geoip_countrylist_temp.txt
c) awk 'BEGIN {print "persistidents 0"; print "const tmp_geoip_countrylist ["} {print $0} END{print "]"; print "persistidents 1"}' geoip_countrylist_temp.txt > geoip_countrylist.txt
[SELECT ALL] Code:
awk '{print $1, $5, $2}' copy_GeoIPCountryWhois.csv > copy2_GeoIPCountryWhois.csv
[SELECT ALL] Code:
vim "+%s/\./\ /g" "+%s/\"\ \"/\ /g" "+%s/\"//g" "+wq" copy2_GeoIPCountryWhois.csv
[SELECT ALL] Code:
awk '{print (65536 * $1 + 256 * $2 + $3), $1, $2, $3, $6, $7, $8, $5}' copy2_GeoIPCountryWhois.csv > copy_GeoIPCountryWhois.csv
[SELECT ALL] Code:
a) awk '$8!="A2" {print $0}' copy_GeoIPCountryWhois.csv > copy2_GeoIPCountryWhois.csv
b) awk '{print $2, $3, $4, $5, $6, $7, $1, $8}' copy2_GeoIPCountryWhois.csv > copy_GeoIPCountryWhois.csv
[SELECT ALL] Code:
a) awk 'BEGIN {ipnum_p=0; first_p=0; second_p=0; third_p=0; abbr_p=0} {if ($7!=ipnum_p) {if ($1==first_p && $2==second_p && $3==third_p) {print $1, $2, $3, $1, $2, $3, $7, abbr_p; print $1, $2, $3, $1, $2, $3, $7, $8; if ($3!=255) {print $1, $2, $3+1, $4, $5, $6, $7+1, $8} else {a=$1; b=$2+1; if ($2==255) {a=$1+1; b=0}; print a, b, 0, $4, $5, $6, a*65536+b*256, $8}} else {print $0}} else if ($4==first_p && $5==second_p && $6==third_p) {print $0} else {print $1, $2, $3, $1, $2, $3, $7, $8; if ($3!=255) {print $1, $2, $3+1, $4, $5, $6, $7+1, $8} else {a=$1; b=$2+1; if ($2==255) {a=$1+1; b=0}; print a, b, 0, $4, $5, $6, a*65536+b*256, $8}}; first_p=$4; second_p=$5; third_p=$6; ipnum_p=$7; abbr_p=$8}' copy_GeoIPCountryWhois.csv > copy2_GeoIPCountryWhois.csv
b) awk '{if ($1==$4) {print $0} else {print $1, $2, $3, $1, 255, 255, $7, $8; for (i=$1+1; i<=$4; i++) {if ($4-i>=1) {print i, 0, 0, i, 255, 255, i*65536, $8 } else {print $4, 0, 0, $4, $5, $6, $4*65536, $8}}}}' copy2_GeoIPCountryWhois.csv > copy_GeoIPCountryWhois.csv
c) sort -n -k 7 copy_GeoIPCountryWhois.csv > copy2_GeoIPCountryWhois.csv
d) uniq -f 6 copy2_GeoIPCountryWhois.csv > copy_GeoIPCountryWhois.csv
[SELECT ALL] Code:
a) awk '{x = sprintf("%s %s %s %s %s %s %s", $1, $2, $3, $4, $5, $6, $7); if(x in a) a[x]=sprintf("%s%s", a[x], $8); else a[x]=$8} END {for (k in a) {print k, a[k] } }' copy_GeoIPCountryWhois.csv | sort -n -k 7 > copy2_GeoIPCountryWhois.csv
b) awk 'BEGIN {a=1; print "persistidents 0"; print "const tmp_geoip_ipnum_1 ["} {if ($1==a) {print $7} else {a=$1; print "]"; print "const tmp_geoip_ipnum_" a " ["; print $7}} END{print "]"; print "persistidents 1"}' copy2_GeoIPCountryWhois.csv > geoip_ipnum.txt
c) awk 'BEGIN {a=1; print "persistidents 0"; print "const tmp_geoip_abbr_1 ["} {if ($1==a) {print $8} else {a=$1; print "]"; print "const tmp_geoip_abbr_" a " ["; print $8}} END{print "]"; print "persistidents 1"}' copy2_GeoIPCountryWhois.csv > geoip_abbr.txt
[SELECT ALL] Code:
rm -f copy_GeoIPCountryWhois.csv copy2_GeoIPCountryWhois.csv geoip_countrylist_temp.txt