Firewall Ban

Use this forum if you have problems with a hMailServer script, such as hMailServer WebAdmin or code in an event handler.
Post Reply
palinka
Senior user
Senior user
Posts: 986
Joined: 2017-09-12 17:57

Firewall Ban

Post by palinka » 2019-06-14 18:22

Edit 2019/06/25:

hMailServer Firewall Ban - Ban rejected IPs to Windows Defender Firewall

The basic functionality is EventHandlers.vbs calls the firewall ban function based on the trigger of your choice. I'm using GeoIP and Spamhaus rejection mainly, plus a couple of other things that don't get triggered often. The IP, country and reason for the ban are written to the database. Then a powershell script on a scheduled task reads the new database entries and creates a new rule for Windows Defender Firewall. The powershell script also deletes firewall rules that you designate as "released" in the webadmin as well as auto-expire entries.

This project is now in beta phase. Please feel free to try it out. There is a demo here: http://hmsfirewallbandemo.ddns.net/

New rejected IPs from my system get added to the demo database so the content is always fresh, but the demo does not control my firewall. :) However, the demo webadmin is fully functional. Feel free to click anything and play with it to see what it can do. If anything is broken or if you have suggestions, use this thread to let me know.

Files are located here: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

ORIGINAL POST
I'm giving a run at a simple firewall ban script.

1) create table on hmailserver: hm_fwban
2) trigger records IP and reason to db
3) powershell script run by task scheduler 1) checks new IPs on db and adds each IP as a rule to windows firewall, and 2) checks db for IP entries older than __ days and removes them from firewall rules.

I wanted to do the whole thing within hmailserver eventhandlers but I got hung up on the fact that firewall changes require UAC. I got around that by running it from powershell through task scheduler, which can use any credentials you want.

Bear in mind - I'm not very good at this. :mrgreen: I pieced together some of Soren's stuff and snippets of code I found on the interwebs. There could be something missing from within eventhandlers due to that. Some outside testing and/or review would be great. But it is working on my system.

Create table in hmailserver db:

Code: Select all

	CREATE TABLE hm_FWBan (
		ID int NOT NULL AUTO_INCREMENT,
		ipaddress VARCHAR (192) NOT NULL,
		timestamp TIMESTAMP,
		ban_reason VARCHAR (192)
		PRIMARY KEY (ID)
	); 
Column ID is not really necessary. I added it before I sorted out all the details. It doesn't hurt anything, but if you want to get rid of it, make "timestamp" the primary key.

Eventhandlers.vbs:

Code: Select all

Function FWBan(sIPAddress, sReason)
   Dim strSQL, oDB : Set oDB = GetDatabaseObject
   strSQL = "INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason) VALUES (NOW(),'" & sIPAddress & "','" & sReason & "');"
   Call oDB.ExecuteSQL(strSQL)
End Function
Then just call it when you want to ban some slimy low down dirty spammer. For example, I call it when testing spamhaus.

Code: Select all

	   Result.Value = 1
       Call FWBan(oClient.IPAddress, "Spamhaus")
This will insert the IP address, the time and the ban_reason. The ban_reason may or may not be important to you. I added it was so that I could change the expiration for different categories. Maybe I want ban_reason "Spamhaus" to expire after a week and another ban_reason to expire after a month or not at all.

hmsFirewallBan.ps1:

Code: Select all

Function MySQLQuery($Query) {
	$MySQLAdminUserName = 'hmailserver'
	$MySQLAdminPassword = 'supersecretpassword'
	$MySQLDatabase = 'hmailserver'
	$MySQLHost = 'localhost'
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
	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] | Out-File C:\scripts\hmailserver\FWBan\IP.txt #<<<<<<< CHANGE PATH IF YOU WANT
	  }
	Catch {
	  Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
	 }
	Finally {
	  $Connection.Close()
	  }
}

#	Look for new entries and add them to firewall
$Query = "SELECT ipaddress FROM hm_fwban WHERE timestamp >= now() - interval 5 minute"
MySQLQuery $Query

$regex = "(((\d){1,3})\.((\d){1,3})\.((\d){1,3})\.((\d){1,3}))"
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
	$IPAddress = $IPAddress.replace('\s','')
	& netsh advfirewall firewall add rule name=`"$IPAddress`" dir=in interface=any action=block remoteip=$IPAddress	
	}
}

#	Look for old entries and remove them from firewall
$Days = "30" 
$Query = "SELECT ipaddress FROM hm_fwban WHERE timestamp < now() - interval $Days day AND ban_reason = 'Spamhaus'"
MySQLQuery $Query

$regex = "(((\d){1,3})\.((\d){1,3})\.((\d){1,3})\.((\d){1,3}))"
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
	$IPAddress = $IPAddress.replace('\s','')
	& netsh advfirewall firewall delete rule name=`"$IPAddress`"
	}
}

Notice that the db query creates a text file with the relevant IPs found in the query. There may be a way to process those IPs without needing to output them to another file, but, like I said, I'm not the world's greatest scripter. :cry: But it does work. Change the txt file path to whatever works for you.

Now create a task in windows task scheduler that runs every 5 minutes. !!HIGHEST PRIVELEGES REQUIRED!! in order to get around UAC and actually add a rule to the firewall.

If you change the scheduled task to run at a different interval than 5 minutes, be sure to update the first query to match the interval.

If you want to add another query for expiration with a different interval, just repeat the last section (after "# Look for old entries and remove them from firewall") and change $Days and the ban_reason in $Query.

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

Re: Firewall Ban

Post by palinka » 2019-06-15 21:41

Its working nicely so far. I made a couple of improvements. Also, I knew I was missing something from Eventhandlers.vbs in the post above. Like I said, I pieced together some of Soren's stuff which I had in my Eventhandlers.vbs but due to my inexperience, overlooked a couple of important things that you would need if installing from a fresh system. I'm including those things below. BUT!!! My improvements are also included.

* Added GeoIP lookup
* Created simple website to query and search

Here we go....

New file added for GeoIP lookup: VbsJson.vbs which is necessary for the GeoIP lookup.

PHPMyAdmin - add table hm_FWBan

Code: Select all

CREATE TABLE hm_FWBan (
	ID int NOT NULL AUTO_INCREMENT,
	ipaddress VARCHAR (192) NOT NULL,
	timestamp TIMESTAMP,
	ban_reason VARCHAR (192)
	countrycode VARCHAR (4)
	country VARCHAR (192)
	PRIMARY KEY (ID)
); 
Eventhandlers.vbs

Code: Select all

Private Const ADMIN = "Administrator"
Private Const PASSWORD = "sOoPerSecRetPa$$Word"
Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")

Function Include(sInstFile)
   Dim f, s, oFSO
   Set oFSO = CreateObject("Scripting.FileSystemObject")
   On Error Resume Next
   If oFSO.FileExists(sInstFile) Then
      Set f = oFSO.OpenTextFile(sInstFile)
      s = f.ReadAll
      f.Close
      ExecuteGlobal s
   End If
   On Error Goto 0
End Function

Function GetDatabaseObject()
   Dim oApp : Set oApp = CreateObject("hMailServer.Application")
   Call oApp.Authenticate(ADMIN, PASSWORD)
   Set GetDatabaseObject = oApp.Database
End Function

Function FWBan(sIPAddress, sReason)
   Dim ReturnCode, Json, oGeoip, oXML
   Set Json = New VbsJson
   On Error Resume Next
   Set oXML = CreateObject ("Msxml2.XMLHTTP.3.0")
   oXML.Open "GET", "http://ip-api.com/json/" & sIPAddress, False
   oXML.Send
   Set oGeoip = Json.Decode(oXML.responseText)
   ReturnCode = oXML.Status
   On Error Goto 0

   Dim strSQL, oDB : Set oDB = GetDatabaseObject
   strSQL = "INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country) VALUES (NOW(),'" & sIPAddress & "','" & sReason & "','" & oGeoip("countryCode") & "','" & oGeoip("country") & "');"
   Call oDB.ExecuteSQL(strSQL)
End Function


Call FWBan Function from within Eventhandlers.vbs

Code: Select all

Call FWBan(oClient.IPAddress, "GeoIP") '<< SUBSTITUTE "GeoIP" with any reason you want.

hmsFirewallBan.ps1

Code: Select all

Function MySQLQuery($Query) {
	$MySQLAdminUserName = 'hmailserver'
	$MySQLAdminPassword = 'supersecretpassword'
	$MySQLDatabase = 'hmailserver'
	$MySQLHost = 'localhost'
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
	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] | Out-File C:\scripts\hmailserver\FWBan\IP.txt
	  }
	Catch {
	  Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
	 }
	Finally {
	  $Connection.Close()
	  }
}

#	Look for new entries and add them to firewall
$Query = "SELECT ipaddress FROM hm_fwban WHERE timestamp >= now() - interval 5 minute"
MySQLQuery $Query

$regex = "(((\d){1,3})\.((\d){1,3})\.((\d){1,3})\.((\d){1,3}))"
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
	$IPAddress = $IPAddress.replace('\s','')
	& netsh advfirewall firewall add rule name=`"$IPAddress`" dir=in interface=any action=block remoteip=$IPAddress	
	}
}

#	Look for old entries and remove them from firewall
$Days = "30" 
$Query = "SELECT ipaddress FROM hm_fwban WHERE timestamp < now() - interval $Days day AND ban_reason = 'Spamhaus'"
MySQLQuery $Query

$regex = "(((\d){1,3})\.((\d){1,3})\.((\d){1,3})\.((\d){1,3}))"
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
	$IPAddress = $IPAddress.replace('\s','')
	& netsh advfirewall firewall delete rule name=`"$IPAddress`"
	}
}

VbsJson.vbs

Code: Select all

Class VbsJson
    'Author: Demon
    'Date: 2012/5/3
    'Website: http://demon.tw
    Private Whitespace, NumberRegex, StringChunk
    Private b, f, r, n, t

    Private Sub Class_Initialize
        Whitespace = " " & vbTab & vbCr & vbLf
        b = ChrW(8)
        f = vbFormFeed
        r = vbCr
        n = vbLf
        t = vbTab

        Set NumberRegex = New RegExp
        NumberRegex.Pattern = "(-?(?:0|[1-9]\d*))(\.\d+)?([eE][-+]?\d+)?"
        NumberRegex.Global = False
        NumberRegex.MultiLine = True
        NumberRegex.IgnoreCase = True

        Set StringChunk = New RegExp
        StringChunk.Pattern = "([\s\S]*?)([""\\\x00-\x1f])"
        StringChunk.Global = False
        StringChunk.MultiLine = True
        StringChunk.IgnoreCase = True
    End Sub
    
    'Return a JSON string representation of a VBScript data structure
    'Supports the following objects and types
    '+-------------------+---------------+
    '| VBScript          | JSON          |
    '+===================+===============+
    '| Dictionary        | object        |
    '+-------------------+---------------+
    '| Array             | array         |
    '+-------------------+---------------+
    '| String            | string        |
    '+-------------------+---------------+
    '| Number            | number        |
    '+-------------------+---------------+
    '| True              | true          |
    '+-------------------+---------------+
    '| False             | false         |
    '+-------------------+---------------+
    '| Null              | null          |
    '+-------------------+---------------+
    Public Function Encode(ByRef obj)
        Dim buf, i, c, g
        Set buf = CreateObject("Scripting.Dictionary")
        Select Case VarType(obj)
            Case vbNull
                buf.Add buf.Count, "null"
            Case vbBoolean
                If obj Then
                    buf.Add buf.Count, "true"
                Else
                    buf.Add buf.Count, "false"
                End If
            Case vbInteger, vbLong, vbSingle, vbDouble
                buf.Add buf.Count, obj
            Case vbString
                buf.Add buf.Count, """"
                For i = 1 To Len(obj)
                    c = Mid(obj, i, 1)
                    Select Case c
                        Case """" buf.Add buf.Count, "\"""
                        Case "\"  buf.Add buf.Count, "\\"
                        Case "/"  buf.Add buf.Count, "/"
                        Case b    buf.Add buf.Count, "\b"
                        Case f    buf.Add buf.Count, "\f"
                        Case r    buf.Add buf.Count, "\r"
                        Case n    buf.Add buf.Count, "\n"
                        Case t    buf.Add buf.Count, "\t"
                        Case Else
                            If AscW(c) >= 0 And AscW(c) <= 31 Then
                                c = Right("0" & Hex(AscW(c)), 2)
                                buf.Add buf.Count, "\u00" & c
                            Else
                                buf.Add buf.Count, c
                            End If
                    End Select
                Next
                buf.Add buf.Count, """"
            Case vbArray + vbVariant
                g = True
                buf.Add buf.Count, "["
                For Each i In obj
                    If g Then g = False Else buf.Add buf.Count, ","
                    buf.Add buf.Count, Encode(i)
                Next
                buf.Add buf.Count, "]"
            Case vbObject
                If TypeName(obj) = "Dictionary" Then
                    g = True
                    buf.Add buf.Count, "{"
                    For Each i In obj
                        If g Then g = False Else buf.Add buf.Count, ","
                        buf.Add buf.Count, """" & i & """" & ":" & Encode(obj(i))
                    Next
                    buf.Add buf.Count, "}"
                Else
                    Err.Raise 8732,,"None dictionary object"
                End If
            Case Else
                buf.Add buf.Count, """" & CStr(obj) & """"
        End Select
        Encode = Join(buf.Items, "")
    End Function

    'Return the VBScript representation of ``str(``
    'Performs the following translations in decoding
    '+---------------+-------------------+
    '| JSON          | VBScript          |
    '+===============+===================+
    '| object        | Dictionary        |
    '+---------------+-------------------+
    '| array         | Array             |
    '+---------------+-------------------+
    '| string        | String            |
    '+---------------+-------------------+
    '| number        | Double            |
    '+---------------+-------------------+
    '| true          | True              |
    '+---------------+-------------------+
    '| false         | False             |
    '+---------------+-------------------+
    '| null          | Null              |
    '+---------------+-------------------+
    Public Function Decode(ByRef str)
        Dim idx
        idx = SkipWhitespace(str, 1)

        If Mid(str, idx, 1) = "{" Then
            Set Decode = ScanOnce(str, 1)
        Else
            Decode = ScanOnce(str, 1)
        End If
    End Function
    
    Private Function ScanOnce(ByRef str, ByRef idx)
        Dim c, ms

        idx = SkipWhitespace(str, idx)
        c = Mid(str, idx, 1)

        If c = "{" Then
            idx = idx + 1
            Set ScanOnce = ParseObject(str, idx)
            Exit Function
        ElseIf c = "[" Then
            idx = idx + 1
            ScanOnce = ParseArray(str, idx)
            Exit Function
        ElseIf c = """" Then
            idx = idx + 1
            ScanOnce = ParseString(str, idx)
            Exit Function
        ElseIf c = "n" And StrComp("null", Mid(str, idx, 4)) = 0 Then
            idx = idx + 4
            ScanOnce = Null
            Exit Function
        ElseIf c = "t" And StrComp("true", Mid(str, idx, 4)) = 0 Then
            idx = idx + 4
            ScanOnce = True
            Exit Function
        ElseIf c = "f" And StrComp("false", Mid(str, idx, 5)) = 0 Then
            idx = idx + 5
            ScanOnce = False
            Exit Function
        End If
        
        Set ms = NumberRegex.Execute(Mid(str, idx))
        If ms.Count = 1 Then
            idx = idx + ms(0).Length
            ScanOnce = CDbl(ms(0))
            Exit Function
        End If
        
        Err.Raise 8732,,"No JSON object could be ScanOnced"
    End Function

    Private Function ParseObject(ByRef str, ByRef idx)
        Dim c, key, value
        Set ParseObject = CreateObject("Scripting.Dictionary")
        idx = SkipWhitespace(str, idx)
        c = Mid(str, idx, 1)
        
        If c = "}" Then
            idx = idx + 1
            Exit Function
        ElseIf c <> """" Then
            Err.Raise 8732,,"Expecting property name"
        End If

        idx = idx + 1
        
        Do
            key = ParseString(str, idx)

            idx = SkipWhitespace(str, idx)
            If Mid(str, idx, 1) <> ":" Then
                Err.Raise 8732,,"Expecting : delimiter"
            End If

            idx = SkipWhitespace(str, idx + 1)
            If Mid(str, idx, 1) = "{" Then
                Set value = ScanOnce(str, idx)
            Else
                value = ScanOnce(str, idx)
            End If
            ParseObject.Add key, value

            idx = SkipWhitespace(str, idx)
            c = Mid(str, idx, 1)
            If c = "}" Then
                Exit Do
            ElseIf c <> "," Then
                Err.Raise 8732,,"Expecting , delimiter"
            End If

            idx = SkipWhitespace(str, idx + 1)
            c = Mid(str, idx, 1)
            If c <> """" Then
                Err.Raise 8732,,"Expecting property name"
            End If

            idx = idx + 1
        Loop

        idx = idx + 1
    End Function
    
    Private Function ParseArray(ByRef str, ByRef idx)
        Dim c, values, value
        Set values = CreateObject("Scripting.Dictionary")
        idx = SkipWhitespace(str, idx)
        c = Mid(str, idx, 1)

        If c = "]" Then
            idx = idx + 1
            ParseArray = values.Items
            Exit Function
        End If

        Do
            idx = SkipWhitespace(str, idx)
            If Mid(str, idx, 1) = "{" Then
                Set value = ScanOnce(str, idx)
            Else
                value = ScanOnce(str, idx)
            End If
            values.Add values.Count, value

            idx = SkipWhitespace(str, idx)
            c = Mid(str, idx, 1)
            If c = "]" Then
                Exit Do
            ElseIf c <> "," Then
                Err.Raise 8732,,"Expecting , delimiter"
            End If

            idx = idx + 1
        Loop

        idx = idx + 1
        ParseArray = values.Items
    End Function
    
    Private Function ParseString(ByRef str, ByRef idx)
        Dim chunks, content, terminator, ms, esc, char
        Set chunks = CreateObject("Scripting.Dictionary")

        Do
            Set ms = StringChunk.Execute(Mid(str, idx))
            If ms.Count = 0 Then
                Err.Raise 8732,,"Unterminated string starting"
            End If
            
            content = ms(0).Submatches(0)
            terminator = ms(0).Submatches(1)
            If Len(content) > 0 Then
                chunks.Add chunks.Count, content
            End If
            
            idx = idx + ms(0).Length
            
            If terminator = """" Then
                Exit Do
            ElseIf terminator <> "\" Then
                Err.Raise 8732,,"Invalid control character"
            End If
            
            esc = Mid(str, idx, 1)

            If esc <> "u" Then
                Select Case esc
                    Case """" char = """"
                    Case "\"  char = "\"
                    Case "/"  char = "/"
                    Case "b"  char = b
                    Case "f"  char = f
                    Case "n"  char = n
                    Case "r"  char = r
                    Case "t"  char = t
                    Case Else Err.Raise 8732,,"Invalid escape"
                End Select
                idx = idx + 1
            Else
                char = ChrW("&H" & Mid(str, idx + 1, 4))
                idx = idx + 5
            End If

            chunks.Add chunks.Count, char
        Loop

        ParseString = Join(chunks.Items, "")
    End Function

    Private Function SkipWhitespace(ByRef str, ByVal idx)
        Do While idx <= Len(str) And _
            InStr(Whitespace, Mid(str, idx, 1)) > 0
            idx = idx + 1
        Loop
        SkipWhitespace = idx
    End Function

End Class


Also attached is a zip file that contains the web pages for the monitoring site. Be sure to change the password/db info.
Attachments
fwban.zip
(4.57 KiB) Downloaded 31 times

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

Re: Firewall Ban

Post by palinka » 2019-06-16 17:02

Fixed up the website part, which is attached. Can sort and search.
Attachments
fwban.zip
(6.48 KiB) Downloaded 31 times

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

Re: Firewall Ban

Post by palinka » 2019-06-16 20:38

Web version 0.003 with geoip lookup links on IP addresses. :mrgreen:
Attachments
fwban-003.zip
(6.55 KiB) Downloaded 32 times

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

Re: Firewall Ban

Post by palinka » 2019-06-17 12:33

Had a bug in the powershell script which due to improperly formatted IP addresses, did not add all addresses. I fixed that by adding: $IPAddress -replace $regex, '$1' which replaces the text file LINE with the match result from the regex. There must have been spaces at the end of the line or something. I also tweaked the regex to hit only properly formatted IP addresses.

I also added a timestamp to the firewall rule description. That might come in handy later.

hmsFirewallBan.ps1:

Code: Select all

Function MySQLQuery($Query) {
	$MySQLAdminUserName = 'hmailserver'
	$MySQLAdminPassword = 'SuperSecretPassword'
	$MySQLDatabase = 'hmailserver'
	$MySQLHost = 'localhost'
	$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
	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] | Out-File C:\scripts\hmailserver\FWBan\IP.txt
	  }
	Catch {
	  Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
	 }
	Finally {
	  $Connection.Close()
	  }
}

#	Look for new entries and add them to firewall
$Query = "SELECT ipaddress FROM hm_fwban WHERE timestamp >= now() - interval 5 minute"
MySQLQuery $Query
$timestamp = "{yy/MM/dd HH:mm}" -f (get-date)
$regex = "((?:[0-9]{1,3}\.){3}[0-9]{1,3})"
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
	$IPAddress -replace $regex, '$1'
	& netsh advfirewall firewall add rule name="$IPAddress" description="Rule added $timestamp" dir=in interface=any action=block remoteip=$IPAddress
	}
}

#	Look for old entries and remove them from firewall
$Days = "30" 
$Query = "SELECT ipaddress FROM hm_fwban WHERE timestamp < now() - interval $Days day AND ban_reason = 'Spamhaus'"
MySQLQuery $Query

$regex = "((?:[0-9]{1,3}\.){3}[0-9]{1,3})"
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
	$IPAddress -replace $regex, '$1'
	& netsh advfirewall firewall delete rule name="$IPAddress"
	}
}

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

Re: Firewall Ban

Post by SorenR » 2019-06-17 14:18

A word of advice... Move the Include statement to the FWban function like this:

Code: Select all

Function FWBan(sIPAddress, sReason)
   Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")
   Dim ReturnCode, Json, oGeoip, oXML
We have seen errors (*) previously when executing the "Include" in the root of the script.

(*)

Code: Select all

"ERROR"	4840	"2017-08-08 22:58:28.767"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: An error occurred processing the request. - Line: XXX Column: X - Code: (null)"
SørenR.

The quantum rule of insecurity which states that the act of observing how vulnerable a host or service is changes the insecurity level of the service.

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

Re: Firewall Ban

Post by palinka » 2019-06-17 15:19

SorenR wrote:
2019-06-17 14:18
A word of advice... Move the Include statement to the FWban function like this:

Code: Select all

Function FWBan(sIPAddress, sReason)
   Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")
   Dim ReturnCode, Json, oGeoip, oXML
We have seen errors (*) previously when executing the "Include" in the root of the script.

(*)

Code: Select all

"ERROR"	4840	"2017-08-08 22:58:28.767"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: An error occurred processing the request. - Line: XXX Column: X - Code: (null)"
Thanks. I haven't had any errors like that and I've been running it like this for a long time. Maybe it's your environment?

Maybe a dumb question, but if i move the include to within the function - and i have a subroutine that also uses it, will potentially loading it twice be a problem?

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

Re: Firewall Ban

Post by SorenR » 2019-06-17 15:29

palinka wrote:
2019-06-17 15:19
SorenR wrote:
2019-06-17 14:18
A word of advice... Move the Include statement to the FWban function like this:

Code: Select all

Function FWBan(sIPAddress, sReason)
   Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")
   Dim ReturnCode, Json, oGeoip, oXML
We have seen errors (*) previously when executing the "Include" in the root of the script.

(*)

Code: Select all

"ERROR"	4840	"2017-08-08 22:58:28.767"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: An error occurred processing the request. - Line: XXX Column: X - Code: (null)"
Thanks. I haven't had any errors like that and I've been running it like this for a long time. Maybe it's your environment?

Maybe a dumb question, but if i move the include to within the function - and i have a subroutine that also uses it, will potentially loading it twice be a problem?
I did not have the problem, jimimaseye did and it makes sense. Executing the code in the "root" of the script means it is ALWAYS executed, also when it is not needed.

https://www.hmailserver.com/forum/viewt ... 24#p197824

If both functions that need "Include" are used inside the same trigger you can move "Include" to the trigger.
SørenR.

The quantum rule of insecurity which states that the act of observing how vulnerable a host or service is changes the insecurity level of the service.

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

Re: Firewall Ban

Post by palinka » 2019-06-17 16:10

SorenR wrote:
2019-06-17 15:29
palinka wrote:
2019-06-17 15:19
SorenR wrote:
2019-06-17 14:18
A word of advice... Move the Include statement to the FWban function like this:

Code: Select all

Function FWBan(sIPAddress, sReason)
   Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")
   Dim ReturnCode, Json, oGeoip, oXML
We have seen errors (*) previously when executing the "Include" in the root of the script.

(*)

Code: Select all

"ERROR"	4840	"2017-08-08 22:58:28.767"	"Script Error: Source: hMailServer COM library - Error: 800403E9 - Description: An error occurred processing the request. - Line: XXX Column: X - Code: (null)"
Thanks. I haven't had any errors like that and I've been running it like this for a long time. Maybe it's your environment?

Maybe a dumb question, but if i move the include to within the function - and i have a subroutine that also uses it, will potentially loading it twice be a problem?
I did not have the problem, jimimaseye did and it makes sense. Executing the code in the "root" of the script means it is ALWAYS executed, also when it is not needed.

https://www.hmailserver.com/forum/viewt ... 24#p197824

If both functions that need "Include" are used inside the same trigger you can move "Include" to the trigger.
Will do. Thanks!

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

Re: Firewall Ban

Post by palinka » 2019-06-17 17:13

palinka wrote:
2019-06-17 12:33
Had a bug in the powershell script which due to improperly formatted IP addresses, did not add all addresses. I fixed that by adding: $IPAddress -replace $regex, '$1' which replaces the text file LINE with the match result from the regex. There must have been spaces at the end of the line or something. I also tweaked the regex to hit only properly formatted IP addresses.
^^^THIS^^^ did not work. Replacing string with itself does not change a darned thing. :mrgreen:

This does work:

Code: Select all

$timestamp = Get-Date -format 'yy/MM/dd HH:mm'
$regex = '([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})'
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
	$IP = $IPAddress -replace '\s|\n|\r|\n\r', ''
	& netsh advfirewall firewall add rule name="$IP" description="Rule added $timestamp" dir=in interface=any action=block remoteip=$IP
	}
}
I've been monitoring it a few hours to make sure. All 68 hits are both in the database and set as a firewall rule. I think I can put this to bed finally.

And speaking of putting things to bed, attached is the all the files you'll need: Eventhandlers.vbs, vbsjson.vbs, hmsFirewallBan.ps1 as well as the www files for monitoring the db for hits. All in one convenient package.
Attachments
FWBan-0.05.zip
(13.67 KiB) Downloaded 31 times

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

Re: Firewall Ban

Post by palinka » 2019-06-17 21:58

Added stats page to the website with top 5 spammer countries and top 5 spammer IPs. :mrgreen:

Created a demo here: http://hmsfirewallbandemo.ddns.net/

The data is only from today. This morning I wiped the db while fixing the issue I had with creating firewall rule entries. Its been going strong since then.
Attachments
FWBan-0.06.zip
(14.09 KiB) Downloaded 32 times

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

Re: Firewall Ban

Post by palinka » 2019-06-18 17:36

I realized that the simple way I was deleting rules would not work well because there was no mechanism to try to delete ONLY not-already-deleted firewall rules. So I added a new column "flag" to the db which will be NULL or have an integer value. I assigned "1" to signify that the IP has already been deleted from the firewall rules, so don't try to delete that one. If flag IS NULL (and the IP is more than X days old) then delete the firewall rule and update the db record flag to "1" so it won't get picked up again on the next scheduled query.

Code: Select all

#	Look for old entries and remove them from firewall
$Days = "30" 
$Query = "SELECT ipaddress, id FROM hm_fwban WHERE timestamp < now() - interval $Days day AND ban_reason = 'Spamhaus' AND flag IS NULL"
MySQLQuery $Query
$regexIP = '([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})'
$regexID = '(\s{0,}[0-9]+\s{0,}$)'
$IPList = Get-Content C:\scripts\hmailserver\FWBan\IP.txt
foreach ($IPAddress in $IPList) {
	if ($IPAddress -match $regex){
		$IP = [regex]::matches($IPAddress, $regexIP)
		& netsh advfirewall firewall delete rule name="$IP"
		$ID = (([regex]::matches($IPAddress, $regexID)) -replace '\s','')
		$Query = "UPDATE hm_fwban SET flag=1 WHERE id='$ID'"
		MySQLQueryUpdate $Query
	}
}
I've been testing this a few hours (using 12 hours as the expiration) and it seems to be working fine. I need to put some error checking in somehow. I'll figure that out later.

A couple of other tweaks in there too. I finally figured out how to replace an entire string with its own match from within the string.

Also, more tweaking on the website. Its coming along.

http://hmsfirewallbandemo.ddns.net/
Attachments
FWBan-0.07.zip
(15.48 KiB) Downloaded 32 times

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

Re: Firewall Ban

Post by palinka » 2019-06-19 01:49

OK! Everything has been running smoothly with adding and deleting firewall rules, so I'm putting this project to bed. As far as I'm concerned it works perfectly. Of course, there may some objections to that to which I welcome constructive criticism. :mrgreen:

Demo: http://hmsfirewallbandemo.ddns.net/

:D
Attachments
FWBan-0.08.zip
(15.49 KiB) Downloaded 34 times

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

Re: Firewall Ban

Post by palinka » 2019-06-19 17:52

Website changes: http://hmsfirewallbandemo.ddns.net/

Minor tweaking to powershell script.
Attachments
FWBan-0.09.zip
(16.98 KiB) Downloaded 30 times

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

Re: Firewall Ban

Post by palinka » 2019-06-19 23:08

More website changes.

Demo here: http://hmsfirewallbandemo.ddns.net/
Attachments
FWBan-0.10.zip
(22.88 KiB) Downloaded 30 times

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

Re: Firewall Ban

Post by palinka » 2019-06-20 12:09

More website changes. Demo here: http://hmsfirewallbandemo.ddns.net/

Tweaks to the stats page and I deleted the ID column because its not particularly necessary and I wanted to make room for the mobile view, which now fits nicely in mobile screens (portrait viewport).

At this point, unless someone points out bugs or improvements, the only changes are going to be to the website. And believe me - pointing out bugs or improvements will be greatly appreciated! :D
Attachments
FWBan-0.11.zip
(22.93 KiB) Downloaded 28 times

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

Re: Firewall Ban

Post by palinka » 2019-06-22 18:56

BIG CHANGES to this version. :mrgreen:

I added various methods of manually releasing IPs through the website. The way it works is as follows.

1) click to release on the website
2) UPDATE set flag=2 which signifies a manual release
3) powershell script at scheduled task looks for flag=2, removes the firewall rule and sets the flag to 1 (released).

Pretty simple. A lot of new pages to handle various methods. You can now manually release the following:

* a single instance of a single IP
* all instances of a single IP
* an IP range
* a few predetermined date ranges (today, yesterday, two days ago, etc)
* a custom date range
* all hits for a specified ban reason
* all hits for a specified country

You can demo it here: http://hmsfirewallbandemo.ddns.net/

The demo runs off an updated copy of my actual database. As IPs are added to my actual database, they also get added to the demo database, so there will always be fresh IPs to try and poke around with. Feel free to use any of the release commands. It has no effect on my actual setup.

However, I have disabled the update flag commands for date/date range and ban_reason in the demo because a couple of clicks could release everything, and that would not make the demo very useful to the next person to try it out. Everything works, including releasing except the flag doesn't actually get updated for date/ban_reason. The other release commands DO update the flag and you can see the results in the history or searches where the column for Release Status will change from NO (with link to release) to YES (already released).

Since the website now has the ability to change things (vs simply viewing before), I STRONGLY recommend taking precautions to safeguard it by, for example, password protecting the directory and not linking to the URL.
Attachments
FWBan-0.12.zip
(29.58 KiB) Downloaded 27 times

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

Re: Firewall Ban

Post by palinka » 2019-06-22 19:13

Just found a flaw in the demo logic. Does not affect actual usage.

When you release an IP the flag gets updated to "2" which signifies "ready to be released". Then when the scheduled task runs, it looks for flag=2, removes the firewall rule and updates the flag to "1", which signifies the firewall rule has been removed already.

There is no scheduled task running for the demo, so the flag will never be updated to "1". This causes some math errors on the stats page.

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

Re: Firewall Ban

Post by palinka » 2019-06-23 00:13

palinka wrote:
2019-06-22 19:13
Just found a flaw in the demo logic. Does not affect actual usage.

When you release an IP the flag gets updated to "2" which signifies "ready to be released". Then when the scheduled task runs, it looks for flag=2, removes the firewall rule and updates the flag to "1", which signifies the firewall rule has been removed already.

There is no scheduled task running for the demo, so the flag will never be updated to "1". This causes some math errors on the stats page.
I can't edit, sooooo…. I fixed this by adding a scheduled task for the demo. All it does is UPDATE hm_fwban_demo SET flag=1 WHERE id='$ID'.

But now the stats display is accurate. The task runs every 5 minutes, so if you don't see a change, check back in 5 minutes and the math will be correct and the IPs that are supposed to have been released will show up as released. So now the demo is fully functioning insofar as the database displays correctly (except for release of date range/ban_reason for reasons I explained above).

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

Re: Firewall Ban

Post by palinka » 2019-06-23 16:59

Every time I think I'm done, I start adding more features. :mrgreen:

What good is having release options without having re-ban options? That's what I've added in this round. The demo is updated to the latest changes:

http://hmsfirewallbandemo.ddns.net/

Feel free to poke around and try out anything. I removed the restrictions mentioned above because re-banning is now easy. If someone decides to release EVERYTHING, it can all be re-banned with a click or two. :D

Good thing too because I released an entire day on my production site thinking it was the demo site. That was kind of the inspiration for the re-ban. :roll:
Attachments
FWBan-0.13.zip
(36.97 KiB) Downloaded 25 times

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

Re: Firewall Ban

Post by palinka » 2019-06-24 01:34

I signed up for GitHub. From here out, any changes will be here:

https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

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

Re: Firewall Ban

Post by palinka » 2019-06-25 03:20

v.0.15 pushed to GitHub: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

demo updated: http://hmsfirewallbandemo.ddns.net/

changes: added review before release/re-ban

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

Re: Firewall Ban

Post by palinka » 2019-06-25 12:48

v.0.16 pushed to GitHub: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

demo updated: http://hmsfirewallbandemo.ddns.net/

changes: bug fixes related to last night's changes.

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

Re: Firewall Ban

Post by palinka » 2019-06-25 13:40

v.0.17 pushed to GitHub: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

demo updated: http://hmsfirewallbandemo.ddns.net/

changes: bug fixes. Ugghhhhh........ You'd think the obvious ones wouldn't get through.... Time for breakfast.

Very, very close to beta stage. I think all the functionality I want is in there. Some feedback/bug reports would be great.

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

Re: Firewall Ban

Post by palinka » 2019-06-25 20:34

v.018 up on GitHub. Demo updated. I can't find any more bugs so I'm going to call this Beta v1.

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

Re: Firewall Ban

Post by palinka » 2019-06-26 17:18

v.0.19 is up on GitHub: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

Demo updated: http://hmsfirewallbandemo.ddns.net/

changes: freshened up search pages

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

Re: Firewall Ban

Post by palinka » 2019-06-27 16:45

v.0.21 is on GitHub and demo updated.

changes: added chart to stats page.

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

Re: Firewall Ban

Post by palinka » 2019-06-28 12:35

v.0.22 is up on GitHub and demo: http://hmsfirewallbandemo.ddns.net/

changes: added minDate to jquery datepicker to match oldest record in database so you can't pick date ranges with no data. Well, you can by typing in an older date, but what's the point of that? :mrgreen:

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

Re: Firewall Ban

Post by palinka » 2019-06-29 16:30

v.0.24:
* added proper credits for eventhandlers.vbs functions
* converted chart data from string to number/date/timeofday for better formatting
* added regression to hits-per-day chart - now its easy to see how well its working for you: declining regression means fewer hits/day which also means the firewall is turning more and more repeat connections away :)

demo: http://hmsfirewallbandemo.ddns.net/

files: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

I don't think I ever mentioned this, but the webadmin formats well on mobile devices. Its been that way since the beginning and also why the number of columns in the tables are limited: so they fit a mobile phone screen in portrait view mode.

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

Re: Firewall Ban

Post by palinka » 2019-07-04 21:22

Added duplicate IP control.

* If multiple hits from the same IP are added to the database between scheduled task interval, duplicates get deleted from the database in order to reduce the number of duplicate firewall rules.
* If IPs are re-banned, duplicates are deleted first from the database in order to reduce the number of duplicate firewall rules.

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

Re: Firewall Ban

Post by palinka » 2019-07-05 13:14

HOW TO DELETE ALL DUPLICATE ENTRIES FROM BOTH DATABASE AND FIREWALL

When I started this project, I had no plan except to BAN! BAN! BAN! And as things developed, I discovered I was getting lots of duplicates. Since then I have implemented strategies to reduce (and now eliminate completely) duplicate entries but those strategies only apply to NEW database entries, not existing ones. So I was left with many duplicates in both my firewall rules and database. Here's how I dealt with those old duplicates.

You need the latest webadmin and powershell package to make this work (no changes to EventHandlers.vbs). Get it from: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

1) Pause hMailServer so you don't get new entries. This is not critically important, but it will prevent the powershell script from missing entries due to being out of interval. This process will take longer than 5 minutes.

2) Disable the scheduled task that runs the powershell script.

3) Go to the release page and select the date range that covers all of your entries. Release 100% of your IPs. This will set the flag to all unreleased IPs to "2", which means "marked for release".

4) Go to the reban page and select the date range that covers all of your entries. Reban 100% of your IPs. This will look for flag=1 or flag=2, so it is not dependent on whether the released entries in step 3 were processed by the powershell script. Rebanning sets the flag to "3" which means "marked for reban".

5) Open windows advanced firewall, select the first IP entry, scroll to the end of the IP list, hit shift and select the last. This will select all firewall rules that were created by this script. Delete them. BE PATIENT. It took over 5 minutes on my machine to delete about 5,000 rules. Go have an espresso and come check it afterward.

6) Manually run the scheduled task (or run it from powershell in administrator mode) which will delete all duplicate entries with flag=3 and create new firewall rules that will not have duplicated entries. BE PATIENT. As with deleting thousands of firewall rules, it also takes time to re-create them. Go have another espresso and check when it afterward. There is no way to know when it is finished. However, using phpmyadmin or your choice of interacting with MySQL, you can run a query to find flag=3. The powershell script rolls through the list one by one, so when there are no more flag=3 entries, you're done and all the firewall rules are in place. Now that I think about it, I should add "unprocessed IPs" to the stats page so you can see what IP entries haven't been added or removed as firewall rules due to being between the powershell interval.

7) Re-enable the scheduled task and un-pause hMailServer.

No more duplicates. And with the new "delete new duplicates" in the powershell script (at add new rules), you won't have duplicates in the future unless you release an IP and get a hit for it later.

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

Re: Firewall Ban

Post by SorenR » 2019-07-05 19:25

This should prevent hMailServer from sending duplicate records... By defining 'ipaddress' as UNIQUE the SQL database will manage duplicates :twisted:

Code: Select all

   Dim strSQL, oDB : Set oDB = GetDatabaseObject
   strSQL = "INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country) " & _
            "VALUES (NOW(),'" & sIPAddress & "','" & sReason & "','" & oGeoip("countryCode") & "','" & oGeoip("country") & "') " & _
            "ON DUPLICATE KEY UPDATE timestamp=NOW();"
   Call oDB.ExecuteSQL(strSQL)
   
   
   CREATE TABLE hm_fwban (
	id INT NOT NULL AUTO_INCREMENT UNIQUE,
	ipaddress VARCHAR (192) NOT NULL,
	timestamp TIMESTAMP,
	ban_reason VARCHAR (192),
	countrycode VARCHAR (4),
	country VARCHAR (192),
	flag INT (1) NULL DEFAULT,
	PRIMARY KEY (id),
	UNIQUE KEY ipaddress (ipaddress)
); 
SørenR.

The quantum rule of insecurity which states that the act of observing how vulnerable a host or service is changes the insecurity level of the service.

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

Re: Firewall Ban

Post by palinka » 2019-07-05 20:36

SorenR wrote:
2019-07-05 19:25
This should prevent hMailServer from sending duplicate records... By defining 'ipaddress' as UNIQUE the SQL database will manage duplicates :twisted:

Code: Select all

   Dim strSQL, oDB : Set oDB = GetDatabaseObject
   strSQL = "INSERT INTO hm_FWBan (timestamp,ipaddress,ban_reason,countrycode,country) " & _
            "VALUES (NOW(),'" & sIPAddress & "','" & sReason & "','" & oGeoip("countryCode") & "','" & oGeoip("country") & "') " & _
            "ON DUPLICATE KEY UPDATE timestamp=NOW();"
   Call oDB.ExecuteSQL(strSQL)
   
   
   CREATE TABLE hm_fwban (
	id INT NOT NULL AUTO_INCREMENT UNIQUE,
	ipaddress VARCHAR (192) NOT NULL,
	timestamp TIMESTAMP,
	ban_reason VARCHAR (192),
	countrycode VARCHAR (4),
	country VARCHAR (192),
	flag INT (1) NULL DEFAULT,
	PRIMARY KEY (id),
	UNIQUE KEY ipaddress (ipaddress)
); 
Hmmm... I don't want that. Here's the reasoning:

A) If I release or auto-expire an IP, I want the firewall rule removed but not the record of the hit. That way, I can see if I'm releasing/expiring the wrong IPs/too soon/etc.

B) due to A, I only want to remove duplicates within the powershell scheduled task interval, which runs every 5 minutes. In those 5 minutes, I might get several connections from the same IP that get rejected and banned. I only want one instance. Then later, also due to A, I can see if those buggers reappeared.

C) a single IP can be banned AND released due to expiring/releasing the firewall rule.

I *think* I've come up with a good solution toward that effort. The way I have it set up now - if you installed this today, you wouldn't have duplicates due to the interval. The only duplicates should come from releasing/expiring and then reappearing.

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

Re: Firewall Ban

Post by palinka » 2019-07-05 20:57

I just committed a "major" change to GitHub. I added a way to count IPs that have been newly added/released/re-banned but have not had their associated firewall rule added/removed. I've added a new flag=4 which signifies NEW ENTRY. That was required to count them. It also means that there are changes to function fwban in EventHandlers.vbs AND hmsFirewallBan.ps1 as well as a few pages of the webadmin.

So I consider this a major change because it requires changes more than simply the webadmin.

As usual, the demo is updated: http://hmsfirewallbandemo.ddns.net/

And the files are located here: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

The graph on the demo is a reflection of my own installation. I'm starting to see results after only a few weeks. The trend line shows me going from 300 to 200 hits per day. As the world's botnets are added, I'm expecting to get well below this.

From what I've seen, nearly all spamhaus rejections are botnets on residential or corporate IPs. This means that IF they are found to have viruses by their owners and cleaned of the bot virus: well... they are still not mail servers. So blocking them forever is 99.99998% reliable (unlikely to give a false positive) even if they stop sending botnet spam because they are not and never will be operating a real mail server.

User avatar
mattg
Moderator
Moderator
Posts: 20026
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Firewall Ban

Post by mattg » 2019-07-06 03:47

Is the stats page intended to be public facing? I'm guessing not, but customer manual unbanning is an interesting concept - this could be protected against bots, and you could build a custom URL in the SMTP rejection text for each sending IP.

I will look at seeing if I can mod it to send to my network firewall, rather than the machine firewall.
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

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

Re: Firewall Ban

Post by palinka » 2019-07-06 13:00

mattg wrote:
2019-07-06 03:47
Is the stats page intended to be public facing? I'm guessing not, but customer manual unbanning is an interesting concept - this could be protected against bots, and you could build a custom URL in the SMTP rejection text for each sending IP.

I will look at seeing if I can mod it to send to my network firewall, rather than the machine firewall.
Stats could be public facing, no problem. Just throw index.php and a few pages referenced by include (header, footer, the charts) into a public directory. You'd have to disable most of the links, but that's no biggie. Maybe I'll throw in a "display only" stats page that only references cred.php (db user, pw, etc.) to make it easy.

Adapting it for your appliance firewall would be a snap. Since the entire backend runs off the db and php, the only place actual firewall rules are changed is in the powershell script. So just change the cmd lines for netsh to whatever your firewall will accept.

The only reason the powershell script exists is because is the only way i could figure out how to actually get rules into the firewall. You could run the same netsh cmd from eventhanlers.vbs and php, but it won't work unless you turn off UAC, which I'm personally unwilling to do. I bet you could change a few lines in the php pages that change the flags (which are just indicators for the powershell script to do stuff) to manipulate your hardware firewall directly and get rid of powershell completely. There's a huge benefit to that because updates would be immediate instead of on a scheduled task interval. That also would have eliminated a few hoops i had to jump through to keep redundant IPs limited.

The whole thing is kind of experimental insofar as i had no idea if there would be any real world impact or if many thousands of firewall rules might slow things down. But now I'm starting to see a payoff and there hasn't been any noticeable effects from having so many rules. After only 5,000 IPs blocked, I'm starting to see a slowdown.
Screenshot_20190706-062330_Brave.jpg
This shows me that it's working as intended. I think by the time i reach 10k rules, there will only be a trickle of connections.

Maybe I'll look into reading the firewall log to check against the database. That would provide the real proof of how it's working. Right now i have no idea how many repeat offenders are getting blocked.

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

Re: Firewall Ban

Post by palinka » 2019-07-06 13:54

Another thing I'd like to add is the HELO. I'm hesitant to add it because as it stands, the tables fit nicely in mobile view and i don't want to screw that up. But it would be valuable information. Maybe v.2 will have different layouts for mobile and big screen.

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

Re: Firewall Ban

Post by palinka » 2019-07-06 23:07

mattg wrote:
2019-07-06 03:47
Is the stats page intended to be public facing?
Done.

http://hmsfirewallbandemo.ddns.net/stats.php

All in one stats page with no links to the rest of the site. Requires cred.php, but no other dependencies/includes.

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

Re: Firewall Ban

Post by palinka » 2019-07-10 11:54

v.0.35 up on GitHub. See readme for changes.

files: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

demo: http://hmsfirewallbandemo.ddns.net/

It's all coming together.
Screenshot_20190710-055605_Brave.jpg

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

Re: Firewall Ban

Post by palinka » 2019-07-11 01:42

Experimental feature: read firewall log to see how many "repeat customers" the are.

I haven't committed it to github yet. Still trying to find out if it's worth anything. It looks a little weird when it's only a day's worth of log collection - like the firewall isn't blocking much, but it's ticking away. I wish i thought of this in the beginning.

It's working on the demo.

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

Re: Firewall Ban

Post by palinka » 2019-07-11 19:54

If you woke up this morning and saw your hits per day chart looking like this:
Screenshot_20190711-133003_Brave.jpg
You would not be alone... I'll commit this to github later, but for now, in charthitsperday.php change this:

Code: Select all

	$query = "SELECT DATE_FORMAT(timestamp, '%Y, %c, %e') AS daily, DATE_FORMAT(timestamp, '%Y') AS year, (DATE_FORMAT(timestamp, '%c') - 1) AS month, DATE_FORMAT(timestamp, '%e') AS day, COUNT(id) AS ipperday FROM hm_fwban WHERE DATE(timestamp) < DATE(NOW()) GROUP BY daily ASC";
to this:

Code: Select all

	$query = "SELECT DATE(timestamp) AS daily, DATE_FORMAT(timestamp, '%Y') AS year, (DATE_FORMAT(timestamp, '%c') - 1) AS month, DATE_FORMAT(timestamp, '%e') AS day, COUNT(id) AS ipperday FROM hm_fwban_demo WHERE DATE(timestamp) < DATE(NOW()) GROUP BY daily ASC";
The way I formatted the date caused it to be out of order (June 30, July 1, July 10, July 2, July 3, etc). 1 comes before 2, right? :oops: Simple date function put it back in date order.

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

Re: Firewall Ban

Post by palinka » 2019-07-13 00:38

A bunch of changes coming up. Not committed yet because I'm working through bugs, but I added something cool and it can be seen on the demo. I worked out how to parse the firewall log and add those entries to a separate (new) database. Now my main chart has 2 sets of data: IPs added and IPs blocked. With only one day's worth of data it looks a little funky, but it will fill in over time.

I'm actually considering deleting all my data and starting from scratch to see how these two data points trend together.

See the chart on the demo: http://hmsfirewallbandemo.ddns.net/

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

Re: Firewall Ban

Post by palinka » 2019-07-13 15:24

I committed the changes last night.

Added firewall log parsing to see how effective firewall banning actually is.

Also, i deleted all of my firewall rules and everything on my database so as to get a fresh start to see how the bans and connection drops interact. I did NOT delete the demo database because i want data there for people to play with. However, since the demo is derived from my installation and im starting from scratch, i expect to see an uptick in the number of daily hits in the demo. The graphs and data will look weird for a couple of weeks. Then, when i have a solid trend going on my installation in maybe 2 weeks, I'll replace the demo data with mine.

The firewall log thing is interesting. There are lots of repeat hits, but a few IPs just won't give up, with hundreds of dropped connections each. This seems to be valuable information.

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

Re: Firewall Ban

Post by palinka » 2019-07-16 21:47

Just updated github.

New feature: mark an ip SAFE, which is a permanent release. If marked safe, firewall rule will be deleted and any future hits from the same ip wil be marked safe with no firewall rule added. Safe designation can be removed also. In that case a new firewall rule will be created and any duplicate IPs in the database will be deleted.

This is a "major" change because hmsFireWallBan.ps1 has numerous changes to accommodate the new feature, plus i was able to squeeze some lines out after learning more about powershell objects. Efficiency!

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

Re: Firewall Ban

Post by palinka » 2019-07-19 10:45

Screenshot_20190719-043807_Brave.jpg
The recent spike is from me deleting all my firewall rules and starting starting my own installation from scratch after adding firewall log parsing and not yet released EHLO collection. I wanted a "clean" database, not one partially filled with blank records. Necessarily, the demo was affected. In a couple of weeks I'll replace the demo data with my installation data so it looks normal.

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

Re: Firewall Ban

Post by palinka » 2019-09-11 02:47

v0.40 is up on GitHub: https://github.com/palinkas-jo-reggelt/ ... rewall-Ban

* Updated stats.php (no links overall view - anyone can look but not manipulate)
* Streamlined SQL because it was taking a while to load charts
* Added total blocks per day from inception chart
* Minor housekeeping

Demo: http://hmsfirewallbandemo.ddns.net/

New stats.php page demo: http://hmsfirewallbandemo.ddns.net/stats.php

One small bug. The new sql for "hits per day" only returns data for days where there are both the firewall rules and firewall log data. Normally, this shouldn't be an issue, but the firewall log reader was created well after the project started, so the demo chart does not display about a month's worth of data (pre firewall log parsing). :(

I'm learning as i go and I'm not an expert on creating mysql queries. However, it is MUCH faster than before.

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

Re: Firewall Ban

Post by palinka » 2019-09-11 12:35

Fixed combined chart to show all data. :)

Post Reply