LOCAL GeoIP Test

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
palinka
Senior user
Senior user
Posts: 2189
Joined: 2017-09-12 17:57

LOCAL GeoIP Test

Post by palinka » 2019-10-21 13:12

I use a script called at OnHELO for GeoIP rejection (thanks, Soren!). The script calls ip-api.com, reads the json returned and allows or rejects based on country. I have noticed that sometimes I get mismatch (null) errors on IPs that are clearly in the ip-api.com database. I know this because after looking at the error, I manually try and a result always comes up. Therefore, I believe the errors are caused by network issues due to my crappy ISP supplied router that I do plan on changing.

Anyway, in order to avoid network errors, I am considering creating a local db using maxminds geolite2 csv. Before I spend the time working on that, I was wondering if there is already a solution for LOCAL query for geoip data. The old maxminds COM is no longer supported, but does it still work? My understanding is that the entire db structure (legacy geoip vs geoip2) is different and that's why it does not work/can't be updated/whatever?

I have looked at the geolite2 csv and have a strategy for implementation, but its work... :wink:

tunis
Senior user
Senior user
Posts: 256
Joined: 2015-01-05 20:22
Location: Sweden

Re: LOCAL GeoIP Test

Post by tunis » 2019-10-21 15:47

I use php with local geoip.

https://github.com/maxmind/GeoIP2-php

Use this vbs code

Code: Select all

    ' ###
    ' ### hMailServer GeoAutoban Code
    ' ###
    Function GeoAutoban(oClient)
        Dim oGeoip
        Dim sPort : sPort = Trim(Mid("SMTP IMAP SMTPSSUBM IMAPS", InStr("25   143  465  587  993  ", oClient.Port), 5))
        On Error Resume Next
        With CreateObject("Msxml2.ServerXMLHTTP.6.0")
            .Open "GET", "http://server/geoip.php?ip=" & oClient.IPAddress, False
            .Send
            If (.Status = 200 ) Then
                Set oGeoip = GeoIP(.responseText)
                sRegEx = "(SE|NO)" ' <===== Sweden, Norway
                If (Lookup(sRegEx, oGeoip("countryCode")) = False) Then
                    EventLog.Write(sPort & vbTab & oClient.IPAddress & vbTab & oGeoip("countryCode") & vbTab & oGeoip("countryName"))
                    Call AutoBan(oClient.IPAddress, sPort & "-" & oGeoip("countryCode"), 7, "d")
                    GeoAutoban = True
                End If
            Else
                EventLog.Write("<OnClientConnect.error> Lookup failed, error code: " & .Status & " on IP address " & oClient.IPAddress)
            End If
        End With
        On Error Goto 0
    End Function
   
    ' ###
    ' ### GeoIP
    ' ###
    Function GeoIP(sGeoIP)
        Dim sCode, sName
        Set GeoIP = CreateObject("Scripting.Dictionary")
        iPos = InStr(sGeoIP,":")
        If (iPos > 0) Then
            sCode = Mid(sGeoIP, 1, iPos-1)
            sName = Mid(sGeoIP, iPos+1)
        End If
        GeoIP.Add "countryCode", sCode
        GeoIP.Add "countryName", sName
    End Function 
    
    
HMS 5.6.8 B2494.25 on Windows Server 2019 Core VM.
HMS 5.6.8 B2505.27 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-21 17:54

tunis wrote:
2019-10-21 15:47
I use php with local geoip.

https://github.com/maxmind/GeoIP2-php
How is performance? Are you using a database or it reads a the flat file every time you connect?

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

Re: LOCAL GeoIP Test

Post by SorenR » 2019-10-21 23:09

Well... I downsized to use a simple DNS lookup ;-)

Sometimes there is no country and sometimes there are two countries. I did dig into it a while back. Source data is OK and it appears to be a registration glitch with the local registrars as WHOIS data sometimes show similar discrepancies.

Code: Select all

Function GeoLookup(strIP) : GeoLookup = "zz"
    Dim a, element, group, strLookup
    a = Split(strIP, ".")
    With CreateObject("DNSLibrary.DNSResolver")
        strLookup = .TXT(a(3) & "." & a(2) & "." & a(1) & "." & a(0) & ".zz.countries.nerd.dk")
    End With
    group = Split(strLookup, vbCrLf)
    If UBound(group) = LBound(group) Then
        GeoLookup = group(0)
        Exit Function
    End If
    If UBound(group) > LBound(group) Then
        For Each element In group
            EventLog.Write( LPad("... GEOLookup", 15, " ") & vbTab & LPad(strIP, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & element )
        Next
        GeoLookup = group(0)
        Exit Function
    End If
    EventLog.Write( LPad("... GEOLookup", 15, " ") & vbTab & LPad(strIP, 16, " ") & vbTab & LPad(" ", 3, " ") & vbTab & LPad(" ", 16, " ") & vbTab & GEOLookup )
End Function

Function LPad(str, length, pad)
    LPad = Left(CStr(str) & String(length, pad), length)
End Function
SørenR.

" I will initiate self-destruct. " — IG-11.

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-22 02:19

SorenR wrote:
2019-10-21 23:09
Well... I downsized to use a simple DNS lookup ;-)
That's what I do now. I'm getting a bunch of errors that I used to think were missing db info, but now believe are network errors. That's why I want to make it local.

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-22 04:33

I'm testing my homebrew maxminds now. It took over half an hour to LOAD the database with 330k records. Actually, I stopped looking - it may have taken an hour. Queries take about a second.

I used powershell to calculate the min and max IP from the cidr given in the maxminds database. The query goes as follows:

Code: Select all

"SELECT country_iso_code, country_name FROM hm_geoip WHERE INET_ATON('" & oClient.IPAddress & "') >= INET_ATON(minip) AND INET_ATON('" & oClient.IPAddress & "') <= INET_ATON(maxip)"
I just finished testing the code and just now loaded it into eventhandlers.vbs. Got my first connection which reported correctly. Woohoo!

I still need to automate downloading database updates from maxminds. When I'm done, I'll post here.

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-22 04:36

SorenR wrote:
2019-10-21 23:09
GeoLookup = group(0)
Is there a way to pass two variables back to the call? Specifically, I want to pass country code and country name. Or do I need a function for each?

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

Re: LOCAL GeoIP Test

Post by SorenR » 2019-10-22 09:07

palinka wrote:
2019-10-22 04:36
SorenR wrote:
2019-10-21 23:09
GeoLookup = group(0)
Is there a way to pass two variables back to the call? Specifically, I want to pass country code and country name. Or do I need a function for each?
No, all you get is the country code.

You can create your own "Countrycode<->Country" table here --> http://peric.github.io/GetCountries/

Source: https://github.com/peric/GetCountries
SørenR.

" I will initiate self-destruct. " — IG-11.

tunis
Senior user
Senior user
Posts: 256
Joined: 2015-01-05 20:22
Location: Sweden

Re: LOCAL GeoIP Test

Post by tunis » 2019-10-22 11:45

palinka wrote:
2019-10-21 17:54
tunis wrote:
2019-10-21 15:47
I use php with local geoip.

https://github.com/maxmind/GeoIP2-php
How is performance? Are you using a database or it reads a the flat file every time you connect?
Preformence approx 10-15ms/request. (in firefox)

I have a centos machine with my webmail and GeoIP installed.

As you see in examples on github it's have the path to geoip databas file.
HMS 5.6.8 B2494.25 on Windows Server 2019 Core VM.
HMS 5.6.8 B2505.27 on Windows Server 2016 Core VM.
HMS 5.6.7 B2425.16 on Windows Server 2012 R2 Core VM.

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-22 12:00

Wow that's much faster than I'm getting from mysql. I'll have to rethink the query.

Also, I'm not working with much perfomance in terms of hardware. But still, that's a gigantic gulf of a difference.

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-23 04:09

I got it all running. Just posted in the scripting forum.

Queries take about 1.3 seconds on my old-ish office desktop class hardware running win 10 for both hmailserver and MySQL (among other things). But... Its local so there's no worry about network errors anymore.

User avatar
nitro
Normal user
Normal user
Posts: 36
Joined: 2018-11-08 16:31
Location: Spain

Re: LOCAL GeoIP Test

Post by nitro » 2019-10-23 09:30

I still use a local database (text file), the performance is good, I only do the search if the authentication failed.

Here the updated database.
https://www.miyuru.lk/geoiplegacy

Here old GeoIpCom.
https://github.com/maxmind/geoip-api-mscom


Copy GeoIpComEx.dll to% windir% / SysWOW64
Run from the command line:
cd% windir% / SysWOW64
regsvr32 GeoIpComEx.dll

Code: Select all

Dim geoip
Dim country
Set geoip = CreateObject("GeoIPCOMEx.GeoIPEx") 
geoip.set_db_path("c:\Program Files (x86)\hmailserver\geoip\")
geoip.find_by_addr(ip)
country = geoip.country_code
EventLog.Write("IP " & ip & " country: " &country)
If country <> "ES" ....
Attachments
GeoIP-COM-1.3.zip
GeoIpComEx.dll
(44.2 KiB) Downloaded 83 times
Production 5.6.8-B2489.22.RvDH W.Server 2016 Datace [2x Intel Xeon E5-2660 8GB RAM]
Staging 5.7-B2490 W.Server 2008 R2 Stand [Intel Pentium 4 4GB RAM]

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-24 13:40

nitro wrote:
2019-10-23 09:30
I still use a local database (text file), the performance is good, I only do the search if the authentication failed.

Here the updated database.
https://www.miyuru.lk/geoiplegacy

Here old GeoIpCom.
https://github.com/maxmind/geoip-api-mscom
Heh.. if i saw this before i probably would have used it. But now I've already got this other project going so I'm going to see it through to completion. Thanks, though!

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

Re: LOCAL GeoIP Test

Post by palinka » 2019-10-27 14:31

This project is done as far as I can tell. I think I got rid of all the bugs. Its working great on my system and its totally automated so updates are just a matter of creating a task in task scheduler.

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

Post Reply