Block IPs

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.
palinka
Senior user
Senior user
Posts: 4461
Joined: 2017-09-12 17:57

Re: Block IPs

Post by palinka » 2020-02-18 02:15

eliassal wrote:
2020-02-18 01:29
FORMAT(CAST(timestamp AS DATE), 'System.Collections.Hashtable[%Y]', 'en-US') AS year,
(FORMAT(CAST(timestamp AS DATE), 'System.Collections.Hashtable[%c]', 'en-US') ) AS month,
FORMAT(CAST(timestamp AS DATE), 'System.Collections.Hashtable[%e]', 'en-US') AS day,
Something is wrong on your end. I just tested the functions to make sure they work for both MySQL and MSSQL.

testfunctions.ps1:

Code: Select all

# Include required files
Try {
	.("$PSScriptRoot\Config.ps1")
	.("$PSScriptRoot\CommonCode.ps1")
}
Catch {
	Write-Output "$((get-date).ToString(`"yy/MM/dd HH:mm:ss.ff`")) : ERROR : Unable to load supporting PowerShell Scripts : $query `n$Error[0]" | out-file "$PSScriptRoot\PSError.log" -append
}

$sql = "
	$( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%Y') AS year,
	($( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%c') $(If ($DatabaseType -eq 'MYSQL'){Write "- 1"})) AS month,
	$( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%e') AS day,
"
Write-Host $sql
Console output:

Code: Select all

PS C:\Users\palinka> C:\scripts\hmailserver\FWBan\testfunctions.ps1

        DATE_FORMAT(DATE(timestamp), '%Y') AS year,
        (DATE_FORMAT(DATE(timestamp), '%c') - 1) AS month,
        DATE_FORMAT(DATE(timestamp), '%e') AS day,

PS C:\Users\palinka> C:\scripts\hmailserver\FWBan\testfunctions.ps1

        FORMAT(CAST(timestamp AS DATE), 'yyyy', 'en-US') AS year,
        (FORMAT(CAST(timestamp AS DATE), 'MM', 'en-US') ) AS month,
        FORMAT(CAST(timestamp AS DATE), 'dd', 'en-US') AS day,

PS C:\Users\palinka>
Its working for me. Maybe you should re-download and replace all the powershell files.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 13:31

OK, I replaced the whole code in commoncode, it is working now

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 13:32

Palinka, can you please let me know which part inserts records in the hm_ids table? VBS in hmailserver or the powershell script "hmsFirewallBan.ps1"?

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

Re: Block IPs

Post by palinka » 2020-02-18 14:02

eliassal wrote:
2020-02-18 13:32
Palinka, can you please let me know which part inserts records in the hm_ids table? VBS in hmailserver or the powershell script "hmsFirewallBan.ps1"?
EventHandlers.vbs

Functions

Code: Select all

Function idsAddIP(sIPAddress)
	Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")
	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 idsTable
	idsTable = ConfigIni.GetKeyValue("hMailServer","idsTable")

	Dim strSQL, oDB : Set oDB = GetDatabaseObject
	If IsMySQL Then
		strSQL = "INSERT INTO " & idsTable & " (timestamp,ipaddress,hits,country) VALUES (" & DBGetCurrentDateTime() & ",'" & sIPAddress & "',1,'" & oGeoip("country") & "') ON DUPLICATE KEY UPDATE hits=(hits+1),timestamp=" & DBGetCurrentDateTime() & ";"
	ElseIf IsMSSQL Then
		strSQL = "IF NOT EXISTS (SELECT 1 FROM " & idsTable & " WHERE ipaddress = '" & sIPAddress & "') INSERT INTO " & idsTable & " (timestamp,ipaddress,hits,country) VALUES (" & DBGetCurrentDateTime() & ",'" & sIPAddress & "',1,'" & oGeoip("country") & "') ELSE UPDATE " & idsTable & " SET hits=(hits+1), timestamp=" & DBGetCurrentDateTime() & " WHERE ipaddress= '" & sIPAddress & "';"
	End If
	Call oDB.ExecuteSQL(strSQL)
	Set oDB = Nothing
End Function

Function idsDelIP(sIPAddress)
    Dim strSQL, oDB : Set oDB = GetDatabaseObject
    strSQL = "DELETE FROM " & ConfigIni.GetKeyValue("hMailServer","idsTable") & " WHERE ipaddress = '" & sIPAddress & "';"
    Call oDB.ExecuteSQL(strSQL)
    Set oDB = Nothing
End Function
Call idsAddIP at OnClientConnect

Code: Select all

Sub OnClientConnect(oClient)
	'	Exclude Backup-MX & local LAN from test
	If (Left(oClient.IPAddress, 12) = "184.105.182.") Then Exit Sub
	If (Left(oClient.IPAddress, 8) = "192.168.") Then Exit Sub
	If oClient.IPAddress = "127.0.0.1" Then Exit Sub

	' Call IDS 
    Call idsAddIP(oClient.IPAddress)

End Sub

All connections get recorded into hm_ids. Then, if they successfully send a message or successfully logon, the IP gets deleted. Only failures remain.

Code: Select all

Sub OnClientLogon(oClient)

	'	Successful logons get IDS entry removed
	If oClient.Authenticated Then
		Call idsDelIP(oClient.IPAddress)
	End If

End Sub

Sub OnAcceptMessage(oClient, oMessage)

	'	Successfully received mail gets IDS entry removed
	'	Should be the very last in line (if other tests present in OnAcceptMessage)
    Call idsDelIP(oClient.IPAddress)

End Sub

Failures are assumed to be password guessers or other bots we don't like.

Powershell picks them up if an IP has > 2 hits (no login, no message) or deletes them after expiration interval.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 14:08

Palinka, can you please tell me how to trace what is happening in the VBS file.
In hmailserver log I see

"TCPIP" 5768 "2020-02-18 11:24:23.963" "TCP - 87.245.132.52 connected to 192.168.1.34:25."
"DEBUG" 5768 "2020-02-18 11:24:23.963" "Executing event OnClientConnect"
"DEBUG" 5768 "2020-02-18 11:24:24.150" "Event completed"

but noting is written to ids table. I replaced it with contents from here and the other github, no way nothing is written

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

Re: Block IPs

Post by palinka » 2020-02-18 14:08

eliassal wrote:
2020-02-18 13:31
OK, I replaced the whole code in commoncode, it is working now
You should replace ALL powershell files. Just to be sure.

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

Re: Block IPs

Post by palinka » 2020-02-18 14:09

eliassal wrote:
2020-02-18 14:08
Palinka, can you please tell me how to trace what is happening in the VBS file.
In hmailserver log I see

"TCPIP" 5768 "2020-02-18 11:24:23.963" "TCP - 87.245.132.52 connected to 192.168.1.34:25."
"DEBUG" 5768 "2020-02-18 11:24:23.963" "Executing event OnClientConnect"
"DEBUG" 5768 "2020-02-18 11:24:24.150" "Event completed"

but noting is written to ids table. I replaced it with contents from here and the other github, no way nothing is written
Can you post your entire eventhandlers.vbs? Remove passwords first.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 14:12

Yes agre understood this, please see my precedent email, in spite of failure nothing is logged, in VBS I saw you always do
On error resume next
and on error goto 0
but no writing to a log file or ...

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

Re: Block IPs

Post by palinka » 2020-02-18 14:13

Also, do you have an error log?

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

Re: Block IPs

Post by palinka » 2020-02-18 14:14

eliassal wrote:
2020-02-18 14:12
Yes agre understood this, please see my precedent email, in spite of failure nothing is logged, in VBS I saw you always do
On error resume next
and on error goto 0
but no writing to a log file or ...
Comment on error and see if it produces an error log entry.

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

Re: Block IPs

Post by palinka » 2020-02-18 14:16

eliassal wrote:
2020-02-18 14:08
Palinka, can you please tell me how to trace what is happening in the VBS file.
In hmailserver log I see

"TCPIP" 5768 "2020-02-18 11:24:23.963" "TCP - 87.245.132.52 connected to 192.168.1.34:25."
"DEBUG" 5768 "2020-02-18 11:24:23.963" "Executing event OnClientConnect"
"DEBUG" 5768 "2020-02-18 11:24:24.150" "Event completed"

but noting is written to ids table. I replaced it with contents from here and the other github, no way nothing is written
Successful function operation would not result in anything written to debug log. It should just appear in hm_ids table.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 14:28

OK, I will check and let you know, lets assume everything working fine and we have a record inserted in hm-ids table, then the ps script runs in 5 minutes, find a record in this table with more than 2 hits, it creates the rule then delete the record from it without any record for the IP in the 2 tables
hm_fwban_blocks_ip
hm_fwban_rh

In the web search page, we have a link ide which lands us on search.php. In this page there a is a join query between
hm_fwban and hm_fwban_blocks_ip
which will not allow this record to appear in this page, am I correct?

Code: Select all

SELECT
			a.tsf,
			a.ipaddress,
			a.ban_reason,
			a.country,
			a.flag,
			a.helo,
			a.ptr,
			b.returnhits
		FROM
		(
			SELECT 
				FORMAT(timestamp, ''yy-MM-dd HH:mm:ss'', ''en-US'') AS tsf, 
				timestamp, 
				ipaddress, 
				ban_reason, 
				country, 
				flag, 
				helo, 
				ptr
			FROM hm_fwban 
			WHERE (CAST(timestamp AS DATE) LIKE ''%20-02-18%'' OR ipaddress LIKE ''%20-02-18%'' OR ban_reason LIKE ''%20-02-18%'' OR country LIKE ''%20-02-18%'' OR helo LIKE ''%20-02-18%'' OR ptr LIKE ''%20-02-18%'') 
		)  a
		LEFT JOIN
		(
			SELECT 
				hits AS returnhits, 
				ipaddress
			FROM hm_fwban_blocks_ip
			-- GROUP BY ipaddress
		)  b
		ON a.ipaddress = b.ipaddress
		  ORDER BY a.tsf DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY'

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 14:35

Palinka, please, you did not respond to one of my questions I asked yesterday when running the hmsFirewallBan.ps1 but not inserting any record in hm-ids table, the script works fine now and it inserts records in 2 tables but not hm-ids table, here is the question again. Did I misunderstand something?
Here you records in hm_fwban_blocks_ip and hm_fwban_rh tqbles but nothing in hm_ids.
In section
#######################################
# #
# IDS #
# (Intrusion Detection System) #
# #
#######################################
you query hm_ids as follows
$Query = "SELECT ipaddress, country FROM hm_ids WHERE hits > 2"
no records no rules created and scripts finishes without creating the rules
Top

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 14:37

some new issues I discovered this morning

1 - This cant run in sql, I commented GROUP BY ipaddress in order to make it work

Code: Select all

SELECT 
				hits AS returnhits, 
				ipaddress
			FROM hm_fwban_blocks_ip
			-- GROUP BY ipaddress
In page repeatsview.php, line 22 should be chanqed as well to accomodate with sql

if ($search==""){$search_sql="";}else{$search_sql=" WHERE ipaddress LIKE '{$search}%' OR ".(IsMSSQL() ? DBCastDateTimeFieldAsDate('timestamp') : 'timestamp')." LIKE '{$search}%'";}

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 14:46

Comment on error and see if it produces an error log entry.
I did , there was no error logged in hmailserver
I stopped/restarted hMailServer and it is working now and I got an entry in the hm-ids table with hit = 1

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

Re: Block IPs

Post by palinka » 2020-02-18 15:52

eliassal wrote:
2020-02-18 14:28
OK, I will check and let you know, lets assume everything working fine and we have a record inserted in hm-ids table, then the ps script runs in 5 minutes, find a record in this table with more than 2 hits, it creates the rule then delete the record from it without any record for the IP in the 2 tables
hm_fwban_blocks_ip
hm_fwban_rh

In the web search page, we have a link ide which lands us on search.php. In this page there a is a join query between
hm_fwban and hm_fwban_blocks_ip
which will not allow this record to appear in this page, am I correct?
You're confusing how the tables work.

* hm_fwban is where we keep track of IPs that have been banned/released/marked safe.
* hm_fwban_rh and hm_fwban_blocks_ip track the firewall log - in other words, effectively measuring the success of the project overall.
* hm_ids is only to track IDS. If hits>2, then the IP gets TRANSFERRED to hm_fwban (and deleted from hm_ids)

So to answer your question, yes, the join query on search.php ignores hm_ids. There is no need to query it.

Treat hm_ids as you would any other trigger in eventhandlers.

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

Re: Block IPs

Post by palinka » 2020-02-18 15:54

eliassal wrote:
2020-02-18 14:35
Palinka, please, you did not respond to one of my questions I asked yesterday when running the hmsFirewallBan.ps1 but not inserting any record in hm-ids table, the script works fine now and it inserts records in 2 tables but not hm-ids table, here is the question again. Did I misunderstand something?
Here you records in hm_fwban_blocks_ip and hm_fwban_rh tqbles but nothing in hm_ids.
In section
#######################################
# #
# IDS #
# (Intrusion Detection System) #
# #
#######################################
you query hm_ids as follows
$Query = "SELECT ipaddress, country FROM hm_ids WHERE hits > 2"
no records no rules created and scripts finishes without creating the rules
Top
I answered it multiple times, actually. Including in detail here: http://hmailserver.com/forum/viewtopic. ... 47#p218347

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

Re: Block IPs

Post by palinka » 2020-02-18 16:08

eliassal wrote:
2020-02-18 14:37
some new issues I discovered this morning

1 - This cant run in sql, I commented GROUP BY ipaddress in order to make it work

Code: Select all

SELECT 
				hits AS returnhits, 
				ipaddress
			FROM hm_fwban_blocks_ip
			-- GROUP BY ipaddress
Isn't GROUP BY syntax the same for MySQL and MSSQL? Anyway, that's a remnant from when I was querying hm_fwban_rh. I'll remove it even though it shouldn't hurt anything. You can group a single return value as well as multiple ones.

In page repeatsview.php, line 22 should be chanqed as well to accomodate with sql

if ($search==""){$search_sql="";}else{$search_sql=" WHERE ipaddress LIKE '{$search}%' OR ".(IsMSSQL() ? DBCastDateTimeFieldAsDate('timestamp') : 'timestamp')." LIKE '{$search}%'";}
There's an issue with that. Maybe you can suggest a solution. Some links on repeats.php send date=YYYY-DD only, which is to search repeats-view.php by month. This works fine in MySQL but I think maybe that would error out in sqlsrv? Can you try it? Go to repeats.php and click one of the "This Year's Monthly Blocks:" links.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 16:10

So in this case there will be no records in the other 2 ytables if it is enetered by VBS then processed and deleted by ban script, am i correct?

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 16:12

Isn't GROUP BY syntax the same for MySQL and MSSQL? Anyway, that's a remnant from when I was querying hm_fwban_rh. I'll remove it even though it shouldn't hurt anything. You can group a single return value as well as multiple ones.

No it is not possible with sql, here is the error that you get
Msg 8120, Level 16, State 1, Line 9
Column 'hm_fwban_blocks_ip.hits' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
.

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

Re: Block IPs

Post by palinka » 2020-02-18 16:13

eliassal wrote:
2020-02-18 14:46
Comment on error and see if it produces an error log entry.
I did , there was no error logged in hmailserver
I stopped/restarted hMailServer and it is working now and I got an entry in the hm-ids table with hit = 1
Ah... OK I understand. The ids functions use hmailserver's built in sql execution. hmailserver probably only sees the tables that exist at startup. I'll make a note of that in the install instructions.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 16:14

There's an issue with that. Maybe you can suggest a solution. Some links on repeats.php send date=YYYY-DD only, which is to search repeats-view.php by month. This works fine in MySQL but I think maybe that would error out in sqlsrv? Can you try it? Go to repeats.php and click one of the "This Year's Monthly Blocks:" links.
Sorry, the statement I put, I meant it should be like this, it is working fine

.(IsMSSQL() ? DBCastDateTimeFieldAsDate('timestamp')

was missing by which I replaced the exisiting code with then it worked

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

Re: Block IPs

Post by palinka » 2020-02-18 16:16

eliassal wrote:
2020-02-18 16:10
So in this case there will be no records in the other 2 ytables if it is enetered by VBS then processed and deleted by ban script, am i correct?
Correct. Eventhandlers.vbs will add and delete ids entries all day long until a connection fails to send a message or logon, in which case it will simply not delete the entry it created at OnClientConnect.

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

Re: Block IPs

Post by palinka » 2020-02-18 16:17

eliassal wrote:
2020-02-18 16:14
There's an issue with that. Maybe you can suggest a solution. Some links on repeats.php send date=YYYY-DD only, which is to search repeats-view.php by month. This works fine in MySQL but I think maybe that would error out in sqlsrv? Can you try it? Go to repeats.php and click one of the "This Year's Monthly Blocks:" links.
Sorry, the statement I put, I meant it should be like this, it is working fine

.(IsMSSQL() ? DBCastDateTimeFieldAsDate('timestamp')

was missing by which I replaced the exisiting code with then it worked
So you're able to search months in repeats-view.php by clicking a link under "This Year's Monthly Blocks:"?

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

Re: Block IPs

Post by palinka » 2020-02-18 16:18

eliassal wrote:
2020-02-18 16:12
Isn't GROUP BY syntax the same for MySQL and MSSQL? Anyway, that's a remnant from when I was querying hm_fwban_rh. I'll remove it even though it shouldn't hurt anything. You can group a single return value as well as multiple ones.

No it is not possible with sql, here is the error that you get
Msg 8120, Level 16, State 1, Line 9
Column 'hm_fwban_blocks_ip.hits' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
.
OK, I'll remove it. Its not needed by MySQL either.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 16:25

So you're able to search months in repeats-view.php by clicking a link under "This Year's Monthly Blocks:"?
On the index page when I hit the link "3 hits so far this month" under "This Year's Monthly Hits: ", Yes I get

Code: Select all

Results for search term "2020-02": 3 Hits (Page: 1 of 1)
Timestamp	        IP Address	        Reason	Country	HELO	FB	RS
20-02-18 10:53:40	92.154.95.236	IDS	      France	        lstlambert-656-1-48-236.w92-154.abo.wanadoo.fr	3	No
20-02-18 10:53:04	139.162.99.243	IDS	      Japan	        scan-42.security.ipip.net	36	No
20-02-18 10:40:05	185.36.81.78	IDS	      Republic of Lithuania	No.PTR.Record	10	No
What do you mean by monthly, something like searching 02 for february or it is another thing

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 16:28

That was on search.php, also on repeats-view.php I get the same thing

Code: Select all

7 IPs repeatedly dropped at firewall matching "2020-02". (Page: 1 of 1)
Last Hit	IP Address	Reason	Country	FB
20-02-18 10:53:40	92.154.95.236	IDS	France	1
20-02-18 10:40:05	185.36.81.78	IDS	Republic of Lithuania	1
20-02-17 18:18:34	139.162.99.243	IDS	Japan	36

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

Re: Block IPs

Post by palinka » 2020-02-18 16:34

eliassal wrote:
2020-02-18 16:28
That was on search.php, also on repeats-view.php I get the same thing

Code: Select all

7 IPs repeatedly dropped at firewall matching "2020-02". (Page: 1 of 1)
Last Hit	IP Address	Reason	Country	FB
20-02-18 10:53:40	92.154.95.236	IDS	France	1
20-02-18 10:40:05	185.36.81.78	IDS	Republic of Lithuania	1
20-02-17 18:18:34	139.162.99.243	IDS	Japan	36
OK, perfect. I'll look on other pages for the same issue and make sure they're changed as well.

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

Re: Block IPs

Post by palinka » 2020-02-18 16:44

It looks like everything is working for you now. I see your only bans are IDS at the moment. You can ban anything for any reason you want. That's the beauty of the eventhandlers fwban function.

For example, if you decide you simply don't like facebook and instagram, just ban them.

Code: Select all

strRegEx = "facebook\.com$|Instagram\.com$"
If (oClient.Port = 25) Then
	If Lookup(strRegEx, oClient.HELO) Then
		Result.Value = 2
		Result.Message = ". 05 Your access to this mail system has been rejected due to the sending MTA's poor reputation. If you believe that this failure is in error, please contact the intended recipient via alternate means."
		Call Disconnect(oClient.IPAddress)
		Call AutoBan(oClient.IPAddress, "Evil FakeBorg - " & oClient.HELO, 1, "h")
		Call FWBan(oClient.IPAddress, "Evil FakeBorg", oClient.HELO, PTR_Record)
		Exit Sub
	End If
End If

You can ban anything for any reason. Be creative. I have lots of triggers like no PTR, dynamic looking PTR, dynamic looking HELO, TOR exit node, of course spamhaus listings are rejected, and plenty of others.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 17:36

Wonderful, can you share them please?

By the way, when you develop your vbs scripts, do you ghave a function or routine to log some messages? I have done this long time ago and can search in my archives but if you have one so I can right away incoporate in the events vbs as I would like to log from time to time that will help .

Last question , I still have rule created for each IP, I thought that new version allows grouping of IPs, am I right, if yes, how this can be done?

Also a question that has nothing to do with this topic, on the server I can access the administration GUI . I have installed the admin tool on my laptop which I used instead of logging to the server. Since 2 weeks, whenever I try to connect from laptop (right password,right login tried ip instead of server name),) no way, always getting
hMailServer
Unable to connect to the specified server
Any idea or if you can tell me how to toruble shoot it? I ping the server and connect through RDP.....

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 17:41

Its worth mentioning that it might be a good idea to start a new github with last version and start maybe making the code more robust from sql perspective instead of IFs everywhere.
Forexample, thinking loudly, using a session flag in PS or the we, put sql code in file and mysql code in another file then according to session flag will use one of them instead of mixing both codes for the 2 DBs.
I will be more than happy to help

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

Re: Block IPs

Post by palinka » 2020-02-18 18:22

eliassal wrote:
2020-02-18 17:41
Its worth mentioning that it might be a good idea to start a new github with last version and start maybe making the code more robust from sql perspective instead of IFs everywhere.
Forexample, thinking loudly, using a session flag in PS or the we, put sql code in file and mysql code in another file then according to session flag will use one of them instead of mixing both codes for the 2 DBs.
I will be more than happy to help
Fork it! :mrgreen:

By the way, the reason the code is like it is, is because a) it kind of grew organically (read the fwban thread to see how it progressed from the very beginning as a single small and not very functional script) and b) i basically learned 90% of everything I know about PHP and MySQL from doing this project. So just because I did it a certain way doesn't mean that's the best or even appropriate way. Its the way I could produce the outcomes I wanted.

Anyway, its not like the web admin will get a lot of traffic. Its just for me. Or for you. Or any other single or small group of administrators. Its not going to get a lot of traffic, so if its not efficient to perfection, that's ok. I just want it to load quickly in order to satisfy myself. However, if you're able to make calling the powershell queries more efficient, I'd be very eager to see how.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 18:45

I agree and the tool is working perfectly and as I said earlier, very good ideas behind it useful for any serious admin, I really congratulate you for the work you have done. I completely agree with you when things start small very basic for 1 need then starts to grow step by step....
I am not so versed with php but I will see next week if I can do anything for powershell.

By the way, hMailServer is written in VB6?

User avatar
Dravion
Senior user
Senior user
Posts: 2071
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: Block IPs

Post by Dravion » 2020-02-18 18:53

eliassal wrote:
2020-02-18 18:45
By the way, hMailServer is written in VB6?
No.
The Main Server (hMailServer.exe) is written in C/C++ and is 240.000 Lines of code. It relies heavy on the OpenSource BOOST Framework and uses a Asynchronous Networkinng
approach, which scales verry well.

For Remote Administration and Script Event Processing
it uses Microsoft Componet Object Model.

The Client tools like hMailAdmin.exe are written in C#.NET
and depends on NET 2.0 Runtime.

A old Version had a VB6 GUI Admin but thats long ago.

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

Re: Block IPs

Post by palinka » 2020-02-18 19:13

eliassal wrote:
2020-02-18 18:45
I agree and the tool is working perfectly and as I said earlier, very good ideas behind it useful for any serious admin, I really congratulate you for the work you have done. I completely agree with you when things start small very basic for 1 need then starts to grow step by step....
I am not so versed with php but I will see next week if I can do anything for powershell.

By the way, hMailServer is written in VB6?
Thank you! Would you mind coming back in a couple of months and let me know if you still feel the same way? The best test is well lived in. :D

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 19:38

Ok got it, still I need your help for this question
Last question , I still have rule created for each IP, I thought that new version allows grouping of IPs, am I right, if yes, how this can be done?

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

Re: Block IPs

Post by palinka » 2020-02-18 19:54

eliassal wrote:
2020-02-18 19:38
Ok got it, still I need your help for this question
Last question , I still have rule created for each IP, I thought that new version allows grouping of IPs, am I right, if yes, how this can be done?
Run hmsConsolidateRules.ps1 at 12:01 am daily.

All new rules (including rebans and manual bans) are created with the IP as name, then hmsConsolidateRules.ps1 sweeps them all up into a single rule (or multiple rules if the IP count is > 400) with "hMS FWBan <BanDate>" as the name.

In both cases, the rulename is inserted into the database in case you decide to release the IP, since it somehow has to find which rule the IP is in before it can delete the IP from the rule, or delete the rule altogether. The database rulename is updated whenever there is activity in the firewall.

You can see more if you look at hmsConsolidateRules.ps1 and also at Function RemRuleIP in CommonCode.ps1.

One other thing - hmsDuplicateRuleFinder.ps1 exists because I had some issues breaking in the rule consolidation. Its a backup defense against code screwups in hmsConsolidateRules.ps1 which I'm pretty sure is good now. I still run it but I haven't seen it do anything in a while. Better safe than sorry.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 20:24

Ok I will do.

I was browsing the index source as one of the sql statements caujhgt my attention as it seemed to me is not returning any result

Code: Select all

SELECT 
				DBFormatDate(DBCastDateTimeFieldAsDate('MIN(lasttimestamp)'), '%M %D, %Y')." AS mindate,
				
				COUNT(ipaddress) AS countip,
				SUM(hits) AS counthits
			FROM hm_fwban_blocks_ip
This gives
2 D, YYYY

In SQL if you would like to have Month day, 4 digit years the format is

Code: Select all

FORMAT(CAST(MIN(lasttimestamp) AS DATE), 'MM dd, yyyy', 'en-US') AS mindate, 
and this returns correcly

mindate countip counthits
02 17, 2020 7 519

Is this the expected result? if yes why this format? for graphs reasons?

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-18 21:11

Run hmsConsolidateRules.ps1 at 12:01 am daily.

Before scheduling, I debugged this file, I was sure that there was no record was entered yesterday and the query returned nothing by the code continued 400 loops even though there were no record to export to csv file, so it created 400 empty csv file

Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 2/18/2020 7:43 PM 0 hMS FWBan 2020-02-17.csv
-a---- 2/18/2020 7:44 PM 0 hMS FWBan 2020-02-17_1.csv
-a---- 2/18/2020 7:44 PM 0 hMS FWBan 2020-02-17_2.csv
-a---- 2/18/2020 7:44 PM 0 hMS FWBan 2020-02-17_3.csv
....
....
...

I think there is an issue here. If I am not mistaken, code should break (gets out) if no records are found.
2nd, the should, I thing linked to number of records and not the Limit which is 400

What do you think?

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

Re: Block IPs

Post by palinka » 2020-02-18 22:53

eliassal wrote:
2020-02-18 20:24
Ok I will do.

I was browsing the index source as one of the sql statements caujhgt my attention as it seemed to me is not returning any result

Code: Select all

SELECT 
				DBFormatDate(DBCastDateTimeFieldAsDate('MIN(lasttimestamp)'), '%M %D, %Y')." AS mindate,
				
				COUNT(ipaddress) AS countip,
				SUM(hits) AS counthits
			FROM hm_fwban_blocks_ip
This gives
2 D, YYYY

In SQL if you would like to have Month day, 4 digit years the format is

Code: Select all

FORMAT(CAST(MIN(lasttimestamp) AS DATE), 'MM dd, yyyy', 'en-US') AS mindate, 
and this returns correcly

mindate countip counthits
02 17, 2020 7 519

Is this the expected result? if yes why this format? for graphs reasons?
The output is supposed to be "February 17, 2020". As in: 7,541 IPs attempted to connect but were dropped at the firewall a total of 743,579 times since February 17, 2020

The function has this in the array for mssql:

Code: Select all

			'%M %D, %Y'         => 'Month D, YYYY',
I don't have any way to test that. But it works for MySQL.

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

Re: Block IPs

Post by palinka » 2020-02-18 22:59

eliassal wrote:
2020-02-18 21:11
Run hmsConsolidateRules.ps1 at 12:01 am daily.

Before scheduling, I debugged this file, I was sure that there was no record was entered yesterday and the query returned nothing by the code continued 400 loops even though there were no record to export to csv file, so it created 400 empty csv file

I think there is an issue here. If I am not mistaken, code should break (gets out) if no records are found.
2nd, the should, I thing linked to number of records and not the Limit which is 400

What do you think?
I never had 0 rules to consolidate. I'll look at it. Definitely don't want this thing going haywire. You didn't have any firewall rules created yesterday?

What its *supposed* to do is stop at the limit or when it runs out and then start new csv if IPs > limit. Not sure how to debug this. I don't want to delete any of my firewall rules. :D

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

Re: Block IPs

Post by palinka » 2020-02-18 23:14

This should do it:

Code: Select all

If ($Limit -eq 0){
	Exit
}
ElseIf ($Limit -eq 1){
	$ConsRules = "$ConsFolder\hMS FWBan "+$BanDate+".csv"
	$Query = "
		SELECT 
			ipaddress 
		FROM hm_fwban 
		WHERE $(DBCastDateTimeFieldAsDate('timestamp')) LIKE '$BanDate%' AND flag IS NULL 
		ORDER BY timestamp DESC
		$(DBLimitRowsWithOffset $($N * $Rows) $Rows)
	"
	RunSQLQuery $Query | Export-CSV $ConsRules
}
Else {
	Do {
		$X = ($N).ToString("0")
		$ConsRules = "$ConsFolder\hMS FWBan "+$BanDate+"_"+$X+".csv"
		$Query = "
			SELECT 
				ipaddress 
			FROM hm_fwban 
			WHERE $(DBCastDateTimeFieldAsDate('timestamp')) LIKE '$BanDate%' AND flag IS NULL 
			ORDER BY timestamp DESC
			$(DBLimitRowsWithOffset $($N * $Rows) $Rows)
		"
		RunSQLQuery $Query | Export-CSV $ConsRules
		
		$N++
	}
	Until ($N -eq $Limit)
}
I tested

Code: Select all

$Limit = [math]::ceiling($CountIP / $Rows) 
If $CountIP (queried number of bans/rules) is 0, $Limit will be 0 and that should stop it dead in its tracks.

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

Re: Block IPs

Post by palinka » 2020-02-18 23:46

I just pushed a bug fix commit to GitHub.

You should replace all powershell files (except config.ps1) and all php files (except config.php).

There are still a few things to do, but we're closing in on perfection.

And since you're a mssql guru, I have a working query that outputs weekly instead of daily data. This is a something that I eventually want to use to replace the chart data when daily becomes too dense to read. You'd have the choice between daily or weekly output in config.php. Can you help convert this query to mssql?

Code: Select all

		SELECT 
			a.week_beginning,
			a.year,
			a.month,
			a.day,
			a.ipperweek,
			b.blockperweek
		FROM
		(
			SELECT 
				FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)) AS week_beginning,
				DATE_FORMAT(FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)), '%Y') AS year,
				(DATE_FORMAT(FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)), '%c') - 1) AS month,
				DATE_FORMAT(FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)), '%e') AS day,
				COUNT(id) AS ipperweek 
			FROM hm_fwban
			GROUP BY FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7))
			ORDER BY FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7))
		) AS a
		LEFT JOIN
		(
			SELECT 
				FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)) AS week_beginning,
				COUNT(DISTINCT(ipaddress)) AS blockperweek
			FROM hm_fwban_rh 
			GROUP BY FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7))
		) AS b
		ON a.week_beginning = b.week_beginning
		ORDER BY a.week_beginning


eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-19 10:23

I just pushed a bug fix commit to GitHub.
Sorry for late response as I went to bed early yesterday as I have meetings the whole day today.
Oh my god, I spent à lot of time yesterday updating some small things but it took some time. Can you tell me what was the update, then I will give it a go.I will give it a go

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-19 10:26

And since you're a mssql guru, sql code

Sure I will look at it either this evening or tomorrow morning. Currently out of office. I looked quickly at your query, it is just a question of replacing mysql functions

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-19 10:33

FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)) AS week_beginning,
DATE_FORMAT(FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)), '%Y') AS year,
(DATE_FORMAT(FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)), '%c') - 1) AS month,

Can you share for each statement the input and expected output
example timestamp value in mysql, week beginning (number or name)

for each statement

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

Re: Block IPs

Post by palinka » 2020-02-19 14:46

eliassal wrote:
2020-02-19 10:33
FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)) AS week_beginning,
DATE_FORMAT(FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)), '%Y') AS year,
(DATE_FORMAT(FROM_DAYS(TO_DAYS(timestamp) -MOD(TO_DAYS(timestamp) -1, 7)), '%c') - 1) AS month,

Can you share for each statement the input and expected output
example timestamp value in mysql, week beginning (number or name)

for each statement
Those are mysql functions. I found an example here: https://stackoverflow.com/questions/173 ... k-in-mysql

The query works. 7 is the week length and -1 is Sunday. (I think). I would never have figured that out. Thank God for the internet. :D

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

Re: Block IPs

Post by palinka » 2020-02-19 14:50

eliassal wrote:
2020-02-19 10:23
I just pushed a bug fix commit to GitHub.
Sorry for late response as I went to bed early yesterday as I have meetings the whole day today.
Oh my god, I spent à lot of time yesterday updating some small things but it took some time. Can you tell me what was the update, then I will give it a go.I will give it a go
Lots of small things including some of the items you brought up. I didn't document it, but you can always look on github for the change manifest.

You should fork it at github, dump your changes and then I'll merge it and we all have the same copy.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-19 21:37

Hello Palinka, just came back home, I will look on both tomorrow

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-19 21:53

Very quickly,
Does the following meets your expectation

Code: Select all

SELECT 
				DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS week_beginning,
				year(CAST(DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS DATE)) AS [Year],
				month(CAST(DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS DATE)) AS [Month],
				day(CAST(DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS DATE)) AS [Day],
				COUNT(ID) AS ipperweek 
			FROM hm_fwban
			--GROUP BY week_beginning
			ORDER BY week_beginning
When I run it now, result (my system starts on MOnday and not Sunday) :
week_beginning Year Month Day ipperweek
2020-02-17 00:00:00.000 2020 2 17 4
Group By in sql has some issues with named columns, for now it is commented

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-19 22:58

Try this and tell me if it fits in your script. Please note that I commented the 1st order by as it is not needed

Code: Select all

SELECT 
			a.week_beginning,
			a.year,
			a.month,
			a.day,
			a.ipperweek,
			b.blockperweek
		FROM
		(
			SELECT 
				DATEADD(ww, DATEDIFF(ww,0,timestamp), 0) AS week_beginning,
				year(CAST(DATEADD(ww, DATEDIFF(ww,0,timestamp), 0) AS DATE)) AS [Year],
				month(CAST(DATEADD(ww, DATEDIFF(ww,0,timestamp), 0) AS DATE)) AS [Month],
				day(CAST(DATEADD(ww, DATEDIFF(ww,0,timestamp), 0) AS DATE)) AS [Day],
				COUNT(ID) AS ipperweek 
			FROM hm_fwban
			GROUP BY DATEADD(ww, DATEDIFF(ww,0,timestamp), 0)
			--ORDER BY week_beginning
		) AS a
		LEFT JOIN
		(
			SELECT 
				DATEADD(ww, DATEDIFF(ww,0,timestamp), 0) AS week_beginning,
				COUNT(DISTINCT(ipaddress)) AS blockperweek
			FROM hm_fwban_rh 
			GROUP BY DATEADD(ww, DATEDIFF(ww,0,timestamp), 0)
		) AS b
		ON a.week_beginning = b.week_beginning
		ORDER BY a.week_beginning

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-20 00:46

Palinka, I have an issue with the 1st graph, it displays the number of ips correctly but the date is 1 month in advance. You can see a snapshot at

Image

If I am not mistaken, it is this query that populates it which returns 18 February, 4 ipperday but the graph says March 18
daily year month day ipperday blockperday
2020-02-18 2020 02 18 4 2

Code: Select all

---- The query I think ----
SELECT 
		a.daily,
		a.year,
		a.month,
		a.day,
		a.ipperday,
		b.blockperday
	FROM
	(
		SELECT 
			CAST(timestamp AS DATE) AS daily,
			FORMAT(CAST(timestamp AS DATE), 'yyyy', 'en-US') AS year,
			(FORMAT(CAST(timestamp AS DATE), 'MM', 'en-US') ) AS month,
			FORMAT(CAST(timestamp AS DATE), 'dd', 'en-US') AS day,
			COUNT(id) AS ipperday 
		FROM hm_fwban 
		WHERE CAST(timestamp AS DATE) < CAST(GETDATE() AS DATE)
		GROUP BY CAST(timestamp AS DATE)
		
	) AS a
	LEFT JOIN
	(
		SELECT 
			CAST(timestamp AS DATE) AS daily, 
			COUNT(DISTINCT(ipaddress)) AS blockperday  

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

Re: Block IPs

Post by palinka » 2020-02-20 01:18

eliassal wrote:
2020-02-19 21:53
Very quickly,
Does the following meets your expectation

Code: Select all

SELECT 
				DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS week_beginning,
				year(CAST(DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS DATE)) AS [Year],
				month(CAST(DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS DATE)) AS [Month],
				day(CAST(DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) AS DATE)) AS [Day],
				COUNT(ID) AS ipperweek 
			FROM hm_fwban
			--GROUP BY week_beginning
			ORDER BY week_beginning
When I run it now, result (my system starts on MOnday and not Sunday) :
week_beginning Year Month Day ipperweek
2020-02-17 00:00:00.000 2020 2 17 4
Group By in sql has some issues with named columns, for now it is commented
Beautiful!

Only one small issue - month should be month -1 because JS month format is 0-11.

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

Re: Block IPs

Post by palinka » 2020-02-20 01:21

eliassal wrote:
2020-02-20 00:46
Palinka, I have an issue with the 1st graph, it displays the number of ips correctly but the date is 1 month in advance. You can see a snapshot at

Image

If I am not mistaken, it is this query that populates it which returns 18 February, 4 ipperday but the graph says March 18
daily year month day ipperday blockperday
2020-02-18 2020 02 18 4 2

Code: Select all

---- The query I think ----
SELECT 
		a.daily,
		a.year,
		a.month,
		a.day,
		a.ipperday,
		b.blockperday
	FROM
	(
		SELECT 
			CAST(timestamp AS DATE) AS daily,
			FORMAT(CAST(timestamp AS DATE), 'yyyy', 'en-US') AS year,
			(FORMAT(CAST(timestamp AS DATE), 'MM', 'en-US') ) AS month,
			FORMAT(CAST(timestamp AS DATE), 'dd', 'en-US') AS day,
			COUNT(id) AS ipperday 
		FROM hm_fwban 
		WHERE CAST(timestamp AS DATE) < CAST(GETDATE() AS DATE)
		GROUP BY CAST(timestamp AS DATE)
		
	) AS a
	LEFT JOIN
	(
		SELECT 
			CAST(timestamp AS DATE) AS daily, 
			COUNT(DISTINCT(ipaddress)) AS blockperday  

Code: Select all

			(FORMAT(CAST(timestamp AS DATE), 'MM', 'en-US') ) AS month,
should be

Code: Select all

			(FORMAT(CAST(timestamp AS DATE), 'MM', 'en-US') - 1) AS month,
because JS date format is 0-11. I already fixed it and merged it.

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-20 13:15

You should replace all powershell files (except config.ps1) and all php files (except config.php).
I did it is working fine for the moment

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-20 15:39

I forgot to tell about 1 note I have noted earlier, Please also update the script for the creation of the hm_fwban table (it gets created with 1)

BEGIN
CREATE TABLE hm_fwban (
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ipaddress varchar NOT NULL,
timestamp datetime NOT NULL,
ban_reason varchar(192) DEFAULT NULL,
country varchar(192) DEFAULT NULL,
flag int DEFAULT NULL,
helo varchar(192) DEFAULT NULL,
ptr varchar(192) DEFAULT NULL,
rulename varchar(192) DEFAULT NULL
)
END;
it should be varchar(15)

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

Re: Block IPs

Post by palinka » 2020-02-21 01:43

eliassal wrote:
2020-02-20 15:39
I forgot to tell about 1 note I have noted earlier, Please also update the script for the creation of the hm_fwban table (it gets created with 1)

BEGIN
CREATE TABLE hm_fwban (
ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ipaddress varchar NOT NULL,
timestamp datetime NOT NULL,
ban_reason varchar(192) DEFAULT NULL,
country varchar(192) DEFAULT NULL,
flag int DEFAULT NULL,
helo varchar(192) DEFAULT NULL,
ptr varchar(192) DEFAULT NULL,
rulename varchar(192) DEFAULT NULL
)
END;
it should be varchar(15)
OK, that's done.

Go get the latest commit. I added an IP map to index.php. Its really cool. Hover over a country to display info, click on a country to take you to the search page for that country. Also, its resizeable for mobile. Works great. Have a look at the demo: http://hmsfirewallbandemo.ddns.net/

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-21 11:07

I replaced all files in www, yeh very well done, nice, yes now this tool can suggested to all hMailServer Users.

I think next week I will fork and start separating sql statements in 2 different files, one for sql one for mysql. I might need a small help regarding the functions

eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-21 11:11

Also maybe this evening, I will share a new version of hmsConsolidateRules.ps1.
The new vesrion will
- create 1 rule per month
add all IPs of everyday in this rule

So the name will be for ex 2020-02, 2020-03......

There will be a check if the rule exists so ip will be added

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

Re: Block IPs

Post by palinka » 2020-02-21 14:16

eliassal wrote:
2020-02-21 11:11
Also maybe this evening, I will share a new version of hmsConsolidateRules.ps1.
The new vesrion will
- create 1 rule per month
add all IPs of everyday in this rule

So the name will be for ex 2020-02, 2020-03......

There will be a check if the rule exists so ip will be added
Ok but just make sure you keep the number of remote IPs < 400 per rule. Too many IPs can create problems while creating the rule. I read that - I have not actually experienced it (never had 400 in one day).

I looked all over and never found what the actual maximum number of remote IPs per rule is. Maybe there's no theoretical limit but I assure you there is a practical limit.

When I was doing single IP per rule, after ~10k rules I noticed odd behavior. Particularly with mysql having issues connecting. That's the reason I changed it to daily consolidated rules. All those odd issues went away immediately.

Post Reply