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.
eliassal
Normal user
Normal user
Posts: 221
Joined: 2010-08-15 18:05
Contact:

Re: Block IPs

Post by eliassal » 2020-02-21 14:24

Let me see, a rule per day is too much and will not be easy to manage. On my side, I will check also on the limits

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

Re: Block IPs

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

According to this thread and another one, 1000 IPs maybe a good compromise
https://superuser.com/questions/802355/ ... 534#804534

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

Re: Block IPs

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

eliassal wrote:
2020-02-21 14:24
Let me see, a rule per day is too much and will not be easy to manage. On my side, I will check also on the limits
It's super easy to manage. It just causes problems when the rule count grows.

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

Re: Block IPs

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

eliassal wrote:
2020-02-21 14:28
According to this thread and another one, 1000 IPs maybe a good compromise
https://superuser.com/questions/802355/ ... 534#804534
There's no limit to how high i can jump up into the sky... :roll:

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

Re: Block IPs

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

palinka wrote:
2020-02-21 14:32
eliassal wrote:
2020-02-21 14:24
Let me see, a rule per day is too much and will not be easy to manage. On my side, I will check also on the limits
It's super easy to manage. It just causes problems when the rule count grows.
Note to self: learn to read.... :roll:

I just noticed you wrote rule per DAY (for some reason I read "IP per rule").

Anyway, Its not too much. Remember, I have experience with single IP per rule with 15k rules. I know when my system starts to act up based on the number of rules. 365 rules per year is 3650 after 10 years. Its not going to be a problem, although there is definitely no harm in turning ~30 rules into ~10 (depending on your volume, of course). If you're banning several hundred IPs per day, then it makes a lot more sense. In fact, now that I think about it, maybe I'll give a run at changing it to monthly.

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

Re: Block IPs

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

Sorry, 1 Rule per month

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

Re: Block IPs

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

Here is the code that is not yet finished (I am not using this limit and Rows variables

Code: Select all

 $BanDate = (Get-Date).AddDays(-1).ToString("yyyy-MM")
$RegexName = '^hMS\sFWBan\s202[0-9]\-[0-9]{2}\-[0-9]{2}(_[0-9]{1,3})?\.csv$'
$RegexIP = '(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)'
Get-ChildItem $ConsFolder | Where-Object {$_.name -match "hMS FWBan $BanDate_"} | ForEach {
	$FileName = $_.name
	$FilePathName = "$ConsFolder\$FileName"
	$RuleName = ($FileName).Replace(".csv", "")
	import-csv -Path $FilePathName | ForEach {
		$IP = $_.ipaddress
		$Query = "UPDATE hm_fwban SET rulename = '$RuleName' WHERE ipaddress = '$IP'"
		RunSQLQuery($Query)
		Write-Output $IP
	}  | Out-File "$FilePathName.txt"
    # Salam
    If (-Not (Test-Path "$FilePathName.count")) { New-Item "$FilePathName.count" -ItemType "file" }
    [int] $countLimit = Get-Content "$FilePathName.count"
    #	Make sure txt file path exists
	If (Test-Path "$FilePathName.txt"){
		$RuleData = Get-Content "$FilePathName.txt" | Select-Object -First 1
		#	Make sure txt file is populated with IP data (if not, you'll have a rule banning all local and all remote IPs)
		If ($RuleData -match $RegexIP){

			#	Replace all newlines and last comma in order to create a single string that can be used to populate firewall rule remoteaddress	
			$NL = [System.Environment]::NewLine
			$Content=[String] $Template= [System.IO.File]::ReadAllText("$FilePathName.txt")
			$Content.Replace($NL,",") | Out-File "$FilePathName.rule.txt"
			(Get-Content -Path "$FilePathName.rule.txt") -Replace ',$','' | Set-Content -Path "$FilePathName.rule.txt"


            # Check if rule exists, then add to it otherwise create and add
			
			$ErrorActionPreference = 'SilentlyContinue'
            If ($(Get-NetFirewallRule –DisplayName $firewallRuleName)){
	           
               & netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
            }
            Else {
                #	Add firewall rule with string containing all IPs from yesterday's bans
                & netsh advfirewall firewall add rule name="$RuleName" description="FWB Rules for $BanDate" dir=in interface=any action=block remoteip=$(Get-Content "$FilePathName.rule.txt")
            }
            $ErrorActionPreference = 'Stop'

			#	Read csv and delete each of yesterday's individual IP firewall rules
			Import-CSV $FilePathName | ForEach {
				$IP = $_.ipaddress
				& netsh advfirewall firewall delete rule name=`"$IP`"
			}
		}
	} 

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

Re: Block IPs

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

eliassal wrote:
2020-02-21 19:31
Here is the code that is not yet finished (I am not using this limit and Rows variables

Code: Select all

 $BanDate = (Get-Date).AddDays(-1).ToString("yyyy-MM")
$RegexName = '^hMS\sFWBan\s202[0-9]\-[0-9]{2}\-[0-9]{2}(_[0-9]{1,3})?\.csv$'
$RegexIP = '(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)'
Get-ChildItem $ConsFolder | Where-Object {$_.name -match "hMS FWBan $BanDate_"} | ForEach {
	$FileName = $_.name
	$FilePathName = "$ConsFolder\$FileName"
	$RuleName = ($FileName).Replace(".csv", "")
	import-csv -Path $FilePathName | ForEach {
		$IP = $_.ipaddress
		$Query = "UPDATE hm_fwban SET rulename = '$RuleName' WHERE ipaddress = '$IP'"
		RunSQLQuery($Query)
		Write-Output $IP
	}  | Out-File "$FilePathName.txt"
    # Salam
    If (-Not (Test-Path "$FilePathName.count")) { New-Item "$FilePathName.count" -ItemType "file" }
    [int] $countLimit = Get-Content "$FilePathName.count"
    #	Make sure txt file path exists
	If (Test-Path "$FilePathName.txt"){
		$RuleData = Get-Content "$FilePathName.txt" | Select-Object -First 1
		#	Make sure txt file is populated with IP data (if not, you'll have a rule banning all local and all remote IPs)
		If ($RuleData -match $RegexIP){

			#	Replace all newlines and last comma in order to create a single string that can be used to populate firewall rule remoteaddress	
			$NL = [System.Environment]::NewLine
			$Content=[String] $Template= [System.IO.File]::ReadAllText("$FilePathName.txt")
			$Content.Replace($NL,",") | Out-File "$FilePathName.rule.txt"
			(Get-Content -Path "$FilePathName.rule.txt") -Replace ',$','' | Set-Content -Path "$FilePathName.rule.txt"


            # Check if rule exists, then add to it otherwise create and add
			
			$ErrorActionPreference = 'SilentlyContinue'
            If ($(Get-NetFirewallRule –DisplayName $firewallRuleName)){
	           
               & netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
            }
            Else {
                #	Add firewall rule with string containing all IPs from yesterday's bans
                & netsh advfirewall firewall add rule name="$RuleName" description="FWB Rules for $BanDate" dir=in interface=any action=block remoteip=$(Get-Content "$FilePathName.rule.txt")
            }
            $ErrorActionPreference = 'Stop'

			#	Read csv and delete each of yesterday's individual IP firewall rules
			Import-CSV $FilePathName | ForEach {
				$IP = $_.ipaddress
				& netsh advfirewall firewall delete rule name=`"$IP`"
			}
		}
	} 

I didn't go over it in detail, but a couple of notes:

$ErrorActionPreference = 'SilentlyContinue' is generally a bad idea when working with the firewall. I've been locked out many times due to errors, and then I had to go physically to the machine to find and delete the firewall rule that got screwed up.

Get-NetFirewallRule is "deprecated" in this project because it will not work with win server 2008 and below. Just like adding MSSQL support, I'm trying to make it as widely available as possible. The replacement for Get-NetFirewallRule is Get-NetshFireWallrule function in CommonCode.ps1. I truncated it to only produce parameters RuleName and RemoteIP. The link to the place I found it is there, if you need to add more parameters to the custom object.

Lastly, I'm also working on a version of this task. I'm taking a different approach, though. I'm making it consolidate daily rules into monthly rules on the first of the month. So you'll have this cascade of rules: today's rules are 1 IP/rule, yesterday's (and for all the current month) are 1 rule per day, then on the first of the month, the previous month's daily rules will get rolled into a series of rules not greater than 400 remote IPs per rule.

I'm not trying to poo-poo your concept. In fact, I did the same thing for daily rules at one point. But I found that troubleshooting is very cumbersome because adding IPs becomes more complex and its a lot of processing for very little practical improvement.

You can see here how I handled inserting rules: https://github.com/palinkas-jo-reggelt/ ... allBan.ps1

Back then I was still working with date only (no "rulename" in the database yet)

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

Re: Block IPs

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

Palinka, this was just a quick and dirty code in order to make my environment do 1 rule /month. I will look at your recommendations. I have just used
$ErrorActionPreference = 'SilentlyContinue'
to get rid of the non-terminating error produced by Get-NetFirewallRule

Thanks for the hint

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

Re: Block IPs

Post by palinka » 2020-02-21 23:04

eliassal wrote:
2020-02-21 21:20
Palinka, this was just a quick and dirty code in order to make my environment do 1 rule /month. I will look at your recommendations. I have just used
$ErrorActionPreference = 'SilentlyContinue'
to get rid of the non-terminating error produced by Get-NetFirewallRule

Thanks for the hint
Here's what I have.

* As mentioned, works the same as daily consolidation except for last month
* All lines that actually "do stuff" are commented so you can test it without fear of breaking anything.

hmsConsolidateRulesMonthly.ps1

Code: Select all

<#
_  _ _  _  _  _ _    ____ ____ ____ _  _ ____ ____     
|__| |\/| /_\ | |    [__  |___ |__/ |  | |___ |__/     
|  | |  |/   \| |___ ___] |___ |  \  \/  |___ |  \     
____ _ ____ ____ _ _ _  _  _    _       ___   _  _  _ 
|___ | |__/ |___ | | | /_\ |    |       |__] /_\ |\ | 
|    | |  \ |___ |_|_|/   \|___ |___    |__]/   \| \| 

.SYNOPSIS
	Powershell script to consolidate daily firewall rules into monthly firewall rules

.DESCRIPTION
	Powershell script to consolidate daily firewall rules into monthly firewall rules

.FUNCTIONALITY
	* Queries firewall for previous month's rules
	* Creates new firewall rules containing all of previous month's banned IPs 
	* Deletes all of previous month's daily firewall rules

.NOTES
	* Create scheduled task to run once per day at 12:01 am
	
.EXAMPLE

#>
# 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
}

#	Run nightly but only trigger on the first of the month
# If ((Get-Date).ToString("dd") -notmatch '01') { Exit }

$ConsFolder = "$PSScriptRoot\ConsolidateRulesMonthly"
$EmailBody = "$PSScriptRoot\ConsolidateRulesMonthly\EmailBody.txt"

#	Create ConsolidateRules folder if it doesn't exist
If (-not(Test-Path $ConsFolder)) {
	md $ConsFolder
}

#	Delete all files in the Consolidated Rules folder before beginning
Get-ChildItem -Path $ConsFolder -Include * | foreach { $_.Delete()}

#	Get BanDate (last month) and establish csv
$BanDate = (Get-Date).AddMonths(-1).ToString("yyyy-MM")

#	Set up email result
Write-Output "hMailServer Firewall Ban `n`nMonthly Rule Consolidation `n`n" | Out-File $EmailBody -Append

#	Establish files and regex
$FWRuleList = "$ConsFolder\fwrulelist.txt"
$RemoteIPList = "$ConsFolder\remoteiplist.txt"
$RegexConsName = 'hMS\sFWBan\s20[0-9]{2}\-[0-9]{2}\-[0-9]{2}(_[0-9]{2})?'
$NL = [System.Environment]::NewLine

#	Read rules from firewall and output only ones from last month
Get-NetshFireWallrule ("all") | ForEach {
	If (($_.RuleName -match $RegexConsName) -and ($_.RuleName -match $BanDate)){
		Write-Output $_.RuleName
	}
} | Out-File $FWRuleList

#	Get list of rulenames, read RemoteIP from each rule, then output to a single list
Get-Content -Path $FWRuleList | ForEach {
	$RuleDay = $_
	Get-NetshFireWallrule ($RuleDay) | ForEach {
		$RemoteIP = $_.RemoteIP
		$ReplaceCIDR = ($RemoteIP).Replace("/32", "")
		Write-Output $ReplaceCIDR 
	}
} | Out-File $RemoteIPList

#	convert list to single string
(Get-Content -Path $RemoteIPList) -Replace '$',',' | Set-Content -NoNewline -Path $RemoteIPList
(Get-Content -Path $RemoteIPList) -Replace ',$','' | Set-Content -NoNewline -Path $RemoteIPList

#	Create new list for the purpose of updating rulename in database
(Get-Content -Path $RemoteIPList) -Replace ',',$NL | Out-File "$RemoteIPList.updater.txt"
$CountRemoteIPs = (Get-Content -Path "$RemoteIPList.updater.txt").Count
Write-Output "There are $CountRemoteIPs IPs contained in $((Get-Content $FWRuleList).Count) firewall rules in the month of $((Get-Date $BanDate).ToString("MMMM")) $((Get-Date $BanDate).ToString("yyyy"))`n`n" | Out-File $EmailBody -Append

$N = 0
$Rows = 400
$Limit = [math]::ceiling($CountRemoteIPs / $Rows)

If ($Limit -eq 0){
	Write-Output "WARNING - there are no rules to consolidate `n`nExiting operation - Bye..." | Out-File $EmailBody -Append
	EmailResults
	Exit
}
ElseIf ($Limit -eq 1){
	#	Add rule for last month
	$MonthRuleName = "hMS FWBan $BanDate"
	& netsh advfirewall firewall add rule name="$MonthRuleName" description="FWB Rules for $BanDate" dir=in interface=any action=block remoteip=$(Get-Content $RemoteIPList)
	Write-Output "Creating firewall rule : $MonthRuleName" | Out-File $EmailBody -Append

	#	Delete last month's rules
	Get-Content -Path $FWRuleList | ForEach {
		$RuleToDelete = $_
		& netsh advfirewall firewall delete rule name=`"$RuleToDelete`"
		Write-Output "Deleting firewall rule : $RuleToDelete" | Out-File $EmailBody -Append
	}

	#	Update rulenames in database
	Get-Content -Path "$RemoteIPList.updater.txt" | ForEach {
		$IP = $_
		$Query = "UPDATE hm_fwban SET rulename='$MonthRuleName' WHERE ipaddress='$IP'"
		RunSQLQuery $Query
	}
}
Else {
	Do {
		#	Split up IPs into chunks for multiple rule creation
		$X = ($N).ToString("00")
		Get-Content "$RemoteIPList.updater.txt" | select -first $Rows -skip $($N * $Rows) | Out-File "$RemoteIPList.IPBatchForRemoteIP_$X.txt"
		(Get-Content "$RemoteIPList.IPBatchForRemoteIP_$X.txt") -Replace '$',',' | Set-Content -NoNewline -Path "$RemoteIPList.IPBatchForRuleCreation_$X.txt"
		(Get-Content "$RemoteIPList.IPBatchForRuleCreation_$X.txt") -Replace ',$','' | Set-Content -Path "$RemoteIPList.IPBatchForRuleCreation_$X.txt"
		
		#	Create chunk rule
		$MonthRuleName = "hMS FWBan "+$BanDate+"_"+$X
		& netsh advfirewall firewall add rule name="$MonthRuleName" description="FWB Rules for $BanDate" dir=in interface=any action=block remoteip=$(Get-Content "$RemoteIPList.IPBatchForRuleCreation_$X.txt")
		Write-Output "Creating firewall rule : $MonthRuleName" | Out-File $EmailBody -Append
		
		#	Update rulenames in database
		Get-Content "$RemoteIPList.IPBatchForRemoteIP_$X.txt" | ForEach {
			$IP = $_
			$Query = "UPDATE hm_fwban SET rulename='$MonthRuleName' WHERE ipaddress='$IP'"
			RunSQLQuery $Query
		}

		$N++
	}
	Until ($N -eq $Limit)

	#	Delete last month's rules
	Get-Content -Path $FWRuleList | ForEach {
		$RuleToDelete = $_
		& netsh advfirewall firewall delete rule name=`"$RuleToDelete`"
		Write-Output "Deleting firewall rule : $RuleToDelete" | Out-File $EmailBody -Append
	}
}

EmailResults

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

Re: Block IPs

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

I just updated all previous months. Works great. Firewall incoming opens faster than ever. :mrgreen:

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

Re: Block IPs

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

Great and good work. Well in order not to clutter the firwall, for the time being, I have updated the script to enter ips per month but I faced the problem of existing IPs in a rule get removed when I use the "SET" keyword (I was not aware of that)

Code: Select all

& netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
So I will use

Code: Select all

$BanDate = (Get-Date).ToString("yyyy-MM")
in the query
$Query = "
SELECT
ipaddress
FROM hm_fwban
WHERE $(DBCastDateTimeFieldAsDate('timestamp')) LIKE '$BanDate%' AND flag IS NULL
ORDER BY timestamp DESC
$(DBLimitRowsWithOffset $($N * $Rows) $Rows)
"
so I get all IPs everyday and injects them in the rule

Have you came across a fix/idea for this set problem?

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

Re: Block IPs

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

By the way Palinka, in Config.php, I kept
$SMTPPort = 587 #
$SSL = 'True'
In hMailServer, On IP/port 587, I have configured a certificate that I have generated which works fine with my outlook.

When I send an email from the powershell script on the machine itself, I get a timeout in Powershell and the following in hMail server log
"DEBUG" 3660 "2020-02-22 20:02:56.286" "Creating session 321"
"TCPIP" 3660 "2020-02-22 20:02:56.286" "TCP - 208.168.10.34 connected to 208.168.10.34:587."
"DEBUG" 3660 "2020-02-22 20:02:56.286" "Executing event OnClientConnect"
"DEBUG" 3660 "2020-02-22 20:02:56.411" "Event completed"
"DEBUG" 3660 "2020-02-22 20:02:56.411" "TCP connection started for session 318"
"DEBUG" 3660 "2020-02-22 20:02:56.411" "Performing SSL/TLS handshake for session 318. Verify certificate: False"
"TCPIP" 3660 "2020-02-22 20:04:36.604" "TCPConnection - TLS/SSL handshake failed. Session Id: 318, Remote IP: 208.168.10.34, Error code: 335544539, Message: short read"
"DEBUG" 3660 "2020-02-22 20:04:36.604" "Ending session 318"
I double clicked on the same mycertificate.cer and installed in the server store still getting the same behavior. Anything I am missing here ?

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

Re: Block IPs

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

eliassal wrote:
2020-02-22 21:22
By the way Palinka, in Config.php, I kept
$SMTPPort = 587 #
$SSL = 'True'
In hMailServer, On IP/port 587, I have configured a certificate that I have generated which works fine with my outlook.

When I send an email from the powershell script on the machine itself, I get a timeout in Powershell and the following in hMail server log
"DEBUG" 3660 "2020-02-22 20:02:56.286" "Creating session 321"
"TCPIP" 3660 "2020-02-22 20:02:56.286" "TCP - 208.168.10.34 connected to 208.168.10.34:587."
"DEBUG" 3660 "2020-02-22 20:02:56.286" "Executing event OnClientConnect"
"DEBUG" 3660 "2020-02-22 20:02:56.411" "Event completed"
"DEBUG" 3660 "2020-02-22 20:02:56.411" "TCP connection started for session 318"
"DEBUG" 3660 "2020-02-22 20:02:56.411" "Performing SSL/TLS handshake for session 318. Verify certificate: False"
"TCPIP" 3660 "2020-02-22 20:04:36.604" "TCPConnection - TLS/SSL handshake failed. Session Id: 318, Remote IP: 208.168.10.34, Error code: 335544539, Message: short read"
"DEBUG" 3660 "2020-02-22 20:04:36.604" "Ending session 318"
I double clicked on the same mycertificate.cer and installed in the server store still getting the same behavior. Anything I am missing here ?
I had the same problem which is why I gave the option for SSL. Ultimately, there is absolutely no need to encrypt when sending to localhost.

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

Re: Block IPs

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

eliassal wrote:
2020-02-22 19:50
Great and good work. Well in order not to clutter the firwall, for the time being, I have updated the script to enter ips per month but I faced the problem of existing IPs in a rule get removed when I use the "SET" keyword (I was not aware of that)
Careful - you could be duplicating rules.

Code: Select all

& netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
So I will use

Code: Select all

$BanDate = (Get-Date).ToString("yyyy-MM")
in the query
$Query = "
SELECT
ipaddress
FROM hm_fwban
WHERE $(DBCastDateTimeFieldAsDate('timestamp')) LIKE '$BanDate%' AND flag IS NULL
ORDER BY timestamp DESC
$(DBLimitRowsWithOffset $($N * $Rows) $Rows)
"
so I get all IPs everyday and injects them in the rule

Have you came across a fix/idea for this set problem?
The way I was doing it was:
* find existing rule
* export remote ip list
* add new ip to list
* delete old rule
* create new rule with updated list

And the same for removal which is still part of the project, see function remIP. Only difference is removing instead of adding an IP to the list.

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

Re: Block IPs

Post by palinka » 2020-02-23 00:04

eliassal wrote:
2020-02-22 19:50
Great and good work. Well in order not to clutter the firwall, for the time being, I have updated the script to enter ips per month but I faced the problem of existing IPs in a rule get removed when I use the "SET" keyword (I was not aware of that)
Now that I thought about it some more, existing function RemRuleIP should handle this out of the box. It should already be working for you.

Function queries db for rulename, then gets the list of remote IPs, then:

Code: Select all

Get-Content $RuleList | where { $_ -ne $IP }
"$IP" in this case is the one you want to remove. So it takes the entire list AND SKIPS $IP, then prepares it for re-insertion into a new firewall rule of the same name. Then it deletes the existing firewall rule and creates the new one with the new list that does not contain $IP.

Maybe in your script you left out updating hm_fwban.rulename after creating the rule?

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

Re: Block IPs

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

But RemRuleIP expects 1 IP and rule.txt file is hard coded
"$PSScriptRoot\fwrulelist.txt"
which is different from the logic inside hmsConsolidateRules, so both needs modification.

In fact I am following the same logic
- Getting all banned IPs ofr February each time
- insert them in the file....rule.txt

Code: Select all

(Get-Content -Path "$FilePathName.rule.txt") -Replace ',$','' | Set-Content -Path "$FilePathName.rule.txt"
- Then use

Code: Select all

& netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
so again all ips are inserted again because of SET (it deletes exisiting IPs before inserting)

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

Re: Block IPs

Post by palinka » 2020-02-23 21:11

so again all ips are inserted again because of SET (it deletes exisiting IPs before inserting)
[/quote]

& netsh advfirewall firewall add rule name=`"$RuleName`" description="FWB Rules for $DateIP" dir=in interface=any action=block remoteip=$(Get-Content "$RuleList.rule.txt")

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

Re: Block IPs

Post by eliassal » 2020-03-01 14:32

Hello Palinka, how are you?
I think there is something wrong in the page repeats-IP.php, lines 53, 56, 82 and 85

The date format should be
'Y-m-d'
http://prodwebserver/PHPBanAdmin/repeat ... ]date=2020[/b]/02/25&repeatIP=45.142.195.6
not
'%y/%m/%d'
http://prodwebserver/PHPBanAdmin/repeat ... e=20/02/25[/b]&repeatIP=45.142.195.6
Of course with the 2nd one nothing is returned as it does not match.

I visited your github and found that you have done several fixes/updates, do I need again to downlaod everything and reconfigure or can just get copies of the updated files?
Thanks

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

Re: Block IPs

Post by palinka » 2020-03-01 15:05

Download everything. I fixed that. Plus I made most of the repeats queries more efficient. Plus I added some cool dial gauges to the homepage.

* Download everything
* Create index on timestamp in hm_fwban_rh
* Run hmsUpdateChartTables.ps1 (or else you'll get errors for undefined variables)
* Enjoy

Check out the demo to see the changes. Page loads are faster.
https://firewallban.dynu.net/

The dials are cool. The yellow and red zones are not arbitrary. They're based on your historical data.

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

Re: Block IPs

Post by eliassal » 2020-03-01 15:39

Yeh, really nice, as usual goog work Palinka, I will do.
Question in Ban reasons section, I see in your demo
15,329 hits for GeoIP.
2,367 hits for Spamhaus.
556 hits for Manual.
464 hits for Dyn-PTR.
392 hits for No-PTR.
199 hits for HELO-Inv.
.......
.....
I have only IDS in my site. Are those filters you setup in the events handler vbs that write the reason or something else?

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

Re: Block IPs

Post by palinka » 2020-03-01 16:50

eliassal wrote:
2020-03-01 15:39
Yeh, really nice, as usual goog work Palinka, I will do.
Question in Ban reasons section, I see in your demo
15,329 hits for GeoIP.
2,367 hits for Spamhaus.
556 hits for Manual.
464 hits for Dyn-PTR.
392 hits for No-PTR.
199 hits for HELO-Inv.
.......
.....
I have only IDS in my site. Are those filters you setup in the events handler vbs that write the reason or something else?
Yes, in eventhandlers.vbs. Create any filter you want for banning, and where you call fwban, the is a place for "ban_reason". You can put anything you want. There are examples in the github eventhandlers.vbs.

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

Re: Block IPs

Post by palinka » 2020-03-01 16:53

Here's an example:

Code: Select all

' Spamhaus Zen detection
	If IsInSpamHausZEN(oClient.IPAddress) Then
		Result.Value = 2
		Result.Message = ". 02 This server does not accept connections blacklisted by Spamhaus.org. 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, "Spamhaus - " & oClient.IpAddress, 1, "h")
		Call FWBan(oClient.IPAddress, "Spamhaus", oClient.HELO)
		Exit Sub
	End If
That's part of the beauty of this project. You can ban (or not) anything you want for any reason you want. You're not stuck with whatever I give you.

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

Re: Block IPs

Post by eliassal » 2020-03-01 18:27

I have If IsInSpamHausZEN(oClient.IPAddress) this in the
Sub OnHELO(oClient)
as well as IsInSpamHausDBL(oClient.HELO)
Only

Now can you please tell me why they have different arguments? one receives the IP the 2nd an object?

As I said earlier, I did not go through the vbs, when are they called?, for each connection if I am not mistaken as Helo is the 1st command in smtP?

In my hMailServer config for example I have
zen.spamhaus.org
bl.spamcop.net
b.barracudacentral.org
For the other 2 filters should I write the exact same code and what to use the IP or the object

Code: Select all

If bl.spamcop.net(???????) Then
		Result.Value = 2
		Result.Message = ". 02 This server does not accept connections blacklisted by spamcop. 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, "Spamhaus - " & oClient.IpAddress, 1, "h")
		Call FWBan(oClient.IPAddress, "Spamhaus", oClient.HELO)
		Exit Sub
	End If
By the way I imagine that already you have configured your hMailServer with all those anti spam providers non?

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

Re: Block IPs

Post by eliassal » 2020-03-01 18:35

I have just noticed that you check for port 25 as follows
If (oClient.Port = 25) Then
' ALLOWED COUNTRIES - Port 25 only... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
strBase = "^(US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ)$"
If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
Else
' ALLOWED COUNTRIES - All ports except 25... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
strBase = "^(US)$"
If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
End If

I use other ports such as 465 and 587m what is the reason behind this restriction ? what happens if a hacke from a country not in the list?

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

Re: Block IPs

Post by palinka » 2020-03-01 19:23

eliassal wrote:
2020-03-01 18:27
I have If IsInSpamHausZEN(oClient.IPAddress) this in the
Sub OnHELO(oClient)
as well as IsInSpamHausDBL(oClient.HELO)
Only

Now can you please tell me why they have different arguments? one receives the IP the 2nd an object?
IsInSpamHausZEN(oClient.IPAddress)
IsInSpamHausDBL(oClient.HELO)

2 different functions. One tests the IP against Spamhaus Zen (https://www.spamhaus.org/zen/). The other tests the helo against Spamhaus DBL (https://www.spamhaus.org/dbl/). Zen is for IPs and DBL is for FQDN. They are completely different.
As I said earlier, I did not go through the vbs, when are they called?, for each connection if I am not mistaken as Helo is the 1st command in smtP?
They're called at OnHELO.
In my hMailServer config for example I have
zen.spamhaus.org
bl.spamcop.net
b.barracudacentral.org
For the other 2 filters should I write the exact same code and what to use the IP or the object

Code: Select all

If bl.spamcop.net(???????) Then
		Result.Value = 2
		Result.Message = ". 02 This server does not accept connections blacklisted by spamcop. 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, "Spamhaus - " & oClient.IpAddress, 1, "h")
		Call FWBan(oClient.IPAddress, "Spamhaus", oClient.HELO)
		Exit Sub
	End If
By the way I imagine that already you have configured your hMailServer with all those anti spam providers non?
Be careful with using other RBLs. In my experience, SpamCop generates a LOT of false positives. You don't want false positives when permanently banning. Spamhaus - to my knowledge - has never given me a false positive. The only other RBL I use is UCE-Protect, but even there, I exclude certain known false positives (amazonses, sendgrid, facebook). And its last in line of filters on OnHELO, so its really just a backstop to all other filters. Its rarely triggers anything.

SpamCop and those others that are very aggressive are fine for scoring in hmailserver, but you probably shouldn't use them for banning.

If you want to test on other RBLs, you'll need to create a function for each. You can copy the ones in the example eventhandlers.vbs. Make sure the return codes are valid.

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

Re: Block IPs

Post by palinka » 2020-03-01 19:31

eliassal wrote:
2020-03-01 18:35
I have just noticed that you check for port 25 as follows
If (oClient.Port = 25) Then
' ALLOWED COUNTRIES - Port 25 only... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
strBase = "^(US|CA|AT|BE|CH|CZ|DE|DK|ES|FI|FR|GB|GL|GR|HR|HU|IE|IS|IT|LI|MC|NL|NO|PL|PT|RO|RS|SE|SI|SK|SM|AU|NZ)$"
If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
Else
' ALLOWED COUNTRIES - All ports except 25... Check Alpha-2 Code here -> https://en.wikipedia.org/wiki/ISO_3166-1
strBase = "^(US)$"
If Lookup(strBase, oGeoip("countryCode")) Then bolGeoIP = True
End If

I use other ports such as 465 and 587m what is the reason behind this restriction ? what happens if a hacke from a country not in the list?
The logic is that port 25 is incoming mail. This is the port that 100% of your spam is coming from. The above excludes all countries not on the list - meaning it rejects them (and firewall bans them). In other words, only allow incoming mail from those countries on the list.

All other ports REQUIRE authentication. These are the password guessing ports. ONLY AUTHORIZED USERS should be connecting to those ports. In reality, spammers are trying to hack you all the time. Make these ports more restricted. Only allow connections from people in the countries your users are physically in. For me, its just one country. I have no users outside the US.

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

Re: Block IPs

Post by eliassal » 2020-03-01 20:50

Ok in this case I should change the
"^(US)$"
by
"^(FR)$"

2nd, even port 25, it needs user and password, I don't where is the difference except the other posts I configured to used certificates

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

Re: Block IPs

Post by palinka » 2020-03-01 21:46

eliassal wrote:
2020-03-01 20:50
Ok in this case I should change the
"^(US)$"
by
"^(FR)$"

2nd, even port 25, it needs user and password, I don't where is the difference except the other posts I configured to used certificates
Yes. And also, you can configure port 25 to disable AUTH, and then it would become receive only. This helps cut down the number of passwords guessers.

https://www.hmailserver.com/forum/viewtopic.php?t=30900

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

Re: Block IPs

Post by eliassal » 2020-03-03 11:12

Download everything
* Create index on timestamp in hm_fwban_rh
I noticed that you have added a variable

Code: Select all

$GeoIPDatabase = array (
	'use_geoip'   => 'false',
	'dbtype'      => 'mysql',
	'host'        => 'localhost',
	'username'    => 'geoip',
	'password'    => 'supersecretpassword',
	'dbname'      => 'geoip',
	'driver'      => 'mysql',
	'port'        => '3306',
	'dsn'         => 'MariaDB ODBC 3.0 Driver'
);
Is it used ? if yes; where?

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

Re: Block IPs

Post by eliassal » 2020-03-03 11:14

also, you can configure port 25 to disable AUTH

in which part / where I can do this?

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

Re: Block IPs

Post by palinka » 2020-03-03 15:06

eliassal wrote:
2020-03-03 11:14
also, you can configure port 25 to disable AUTH

in which part / where I can do this?
See link above.

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

Re: Block IPs

Post by palinka » 2020-03-03 15:17

eliassal wrote:
2020-03-03 11:12
Download everything
* Create index on timestamp in hm_fwban_rh
I noticed that you have added a variable

Code: Select all

$GeoIPDatabase = array (
	'use_geoip'   => 'false',
	'dbtype'      => 'mysql',
	'host'        => 'localhost',
	'username'    => 'geoip',
	'password'    => 'supersecretpassword',
	'dbname'      => 'geoip',
	'driver'      => 'mysql',
	'port'        => '3306',
	'dsn'         => 'MariaDB ODBC 3.0 Driver'
);
Is it used ? if yes; where?
I noticed that not including "country" in manual bans (in web admin) caused a problem. I don't remember the problem now. :lol: But the solution was to add country info at the time you create manual bans. Therefore, I needed a method of obtaining this information.

There are 2 choices:
'use_geoip' => 'false' will call ip-api.com
'use_geoip' => 'true' will call the max mind IP database, but you would need to install this first.

The problem with using ip-api.com is that they rate limit at 150/ minute. Therefore, you will be rate limited for attempting to ban /24 or more IPs.

But I'm glad you brought this up because I just thought of a simple solution, which is to call ip-api.com only once for the entire net range. I think it's safe to assume all IPs in the same range are located un the same country. I'll fix that later today.

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

Re: Block IPs

Post by eliassal » 2020-03-03 15:31

I'll fix that later today.
Ok, I will wait for your update then download the new version tonight

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

Re: Block IPs

Post by eliassal » 2020-03-03 15:35

See link above
.
I saw the link I noticed the lines
DisableAUTHList=25,587
; Comma delimited list of SMTP ports to disable AUTH EHLO response banner & command
; Default if not defined is SMTP AUTH enabled on all SMTP ports
; NOTE: Disables AUTH Plain as well.
; Particularly effective on blocking all attempted authentication attempts on the stated ports.
; Recommend applying it to port 25 and setting all clients to connect and authenticate on port 587.
I am a little bit confused, I have already configured my emails with login/password to send/receive which is the normal situation as for any other mail server.
If I disable, authentication, 1st do I need to remove login and passwords, 2nd, in this case anybody can connect and send an email, non?

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

Re: Block IPs

Post by palinka » 2020-03-03 16:14

eliassal wrote:
2020-03-03 15:31
I'll fix that later today.
Ok, I will wait for your update then download the new version tonight
Fixed and merged.

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

Re: Block IPs

Post by palinka » 2020-03-03 16:25

eliassal wrote:
2020-03-03 15:35
See link above
.
I saw the link I noticed the lines
DisableAUTHList=25,587
; Comma delimited list of SMTP ports to disable AUTH EHLO response banner & command
; Default if not defined is SMTP AUTH enabled on all SMTP ports
; NOTE: Disables AUTH Plain as well.
; Particularly effective on blocking all attempted authentication attempts on the stated ports.
; Recommend applying it to port 25 and setting all clients to connect and authenticate on port 587.
I am a little bit confused, I have already configured my emails with login/password to send/receive which is the normal situation as for any other mail server.
If I disable, authentication, 1st do I need to remove login and passwords, 2nd, in this case anybody can connect and send an email, non?
What this does is ban auth logon on whatever ports you choose. You don't disable authentication - you put it on alternate ports.

Example: port 25: disabling auth logon will prevent password guessers AND all mail relay through port 25. It will also prevent your users from sending email through port 25. Your users will have to use an alternate port to send mail. Eg. 587, 465. Generally speaking, port 25 is not used for mail submission in the modern email world. If you look at google, Hotmail, etc, they forced users onto 587 & 465 a LONG time ago for the very same reason this logic exists in hmailserver.

Example: port 587: this is a standard mail submission port. You would only disable auth logon if you have created a custom port for your users to submit mail on. I think this is very rarely done because if you're using custom ports in an attempt to confuse spammers, then why would you have port 587 open in the first place? Its not like port 25, which is REQUIRED to be open if you want to receive mail from the outside world.

I only have port 25 listed on this setting, and I think 99.99% of admins that use this setting also only have port 25 listed.

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

Re: Block IPs

Post by eliassal » 2020-03-03 16:39

More confusion
:D
Okm but if hackers always scan for open portsm if today you prevent port 25 they will switch to the other ports no?
Also I have a couple of users from outside my networkm so would it be 25 or other port hackers will figure it out 1 day or another if i am not mistaken
I thought of stoping port 25 and use only 587 with certificate following 1 of oiur discussions but if you remember a user on the machine was not able to send and you told me that there was no need if he is sending from the machine itselfm that is why i kept 25

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

Re: Block IPs

Post by SorenR » 2020-03-03 19:31

To make this VERY short....

IF you have REQUIRE AUTH on SMTP local-to-local or local-to-external then DisableAUTHList=25 means you CANNOT use port 25 TO SEND email, the AUTH command simply do not work anymore. Your server WILL be able to receive emails on port 25 from your friends as they are external-to-local.

I have port 25 and 465 open and DisableAUTHList=25. My users use 465 (SSL) to send and I receive (external-to-local) on port 25.

My Internet IP Range is set to SMTP+IMAP, require AUTH on local-to-local and local-to-external, allow DELIVERIES on local-to-local, local-to-external and external-to-local.
SørenR.

Woke is Marxism advancing through Maoist cultural revolution.

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

Re: Block IPs

Post by eliassal » 2020-03-04 15:01

Fixed and merged.

I udpated my site this morning, (I will post a 2nd thread regarding an issue with 1st gauge and sql server right after this one which I fixed temporarily), couple of things. can you please confirm that the 3rd dial gauge "Total Blocks" get its data inside the page dialtodayblocks.php?

It is not displayed in spite of the fact when I kkon the source page in chrome I see

Code: Select all

<script type="text/javascript">
	google.charts.load('current', {'packages':['gauge']});
	google.charts.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = google.visualization.arrayToDataTable([
		['Label', 'Value'],
['Blocks', 57]]);var options = { width: 100, height: 100, min: 0, max: , redFrom: 0, redTo: , yellowFrom: 0, yellowTo: 0, 		minorTicks: 10
	};

	var chart = new google.visualization.Gauge(document.getElementById('todays_blocks_dial'));

	chart.draw(data, options);

	}
</script>
Am I missing something?
Image

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

Re: Block IPs

Post by eliassal » 2020-03-04 15:21

There is a big issue in dialtodayhits.php
The following does not work in sql serverm if you remember I already mentioned this and was fixed in some pages; in sql we cant use an alias in the group by keyword

Code: Select all

SELECT ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), -1) AS dailymax, 
CAST(timestamp AS DATE) AS daily 
FROM hm_fwban 
GROUP BY daily 
ORDER BY dailymax DESC 
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
I hard coded in dialtodayhits.php

Code: Select all

SELECT TOP 1 ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), 1) AS dailymax,
CAST(timestamp AS DATE) AS daily
FROM hm_fwban 
GROUP BY CAST(timestamp AS DATE) 
ORDER BY CAST(timestamp AS DATE)  DESC
when running in sql directly I get
dailymax daily
3.6 2020-03-03
again nothing appears in the gauge

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

Re: Block IPs

Post by eliassal » 2020-03-04 15:39

Something else happened, an IP just banned today (1st 1), Refreshed the page, 1st gauge displayed something very strange

Image

here is what I see in Chrome

Code: Select all

<script type="text/javascript">
	google.charts.load('current', {'packages':['gauge']});
	google.charts.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = google.visualization.arrayToDataTable([
		['Label', 'Value'],
['Bans', 1]]);var options = { width: 100, height: 100, min: 0, max: 1.2, redFrom: 1, redTo: 1.2, yellowFrom: 0.75, yellowTo: 1, 		minorTicks: 10
	};

	var chart = new google.visualization.Gauge(document.getElementById('todays_hits_dial'));

	chart.draw(data, options);

	// setInterval(function() {
		// data.setValue(0, 1, 40 + Math.round(60 * Math.random()));
		// chart.draw(data, options);
	// }, 13000);
	}
</script>

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

Re: Block IPs

Post by palinka » 2020-03-04 15:59

eliassal wrote:
2020-03-04 15:01
can you please confirm that the 3rd dial gauge "Total Blocks" get its data inside the page dialtodayblocks.php?
The dials have a couple of datasets each. One is for the dial reading (the needle position). The other is for the color values. The yellow is 75% to 100% of the largest number of IPs added/IPs blocked/total blocks. The red is 100% to 120% of the same data.

Yes, the 3rd dial "Total Blocks" comes from dialtodayblocks.php. The 2 sources of data are:
1) sql query for the number of current (today's) blocks (updated every time you refresh the page)
2) "$redToBlock" (color data) coming from blocksdata.php.
2a) blocksdata.php gets its data from hmsUpdateChartTables.ps1, which should be run nightly with the other nightly scripts. The query for the color data is expensive (slow) AND its only historical data, so I decided to "cache" it in blocksdata.php.

This also applies to the "IPs Blocked" dial (color data is historical and cached in blocksdata.php).

If you're not seeing data, its likely that you didn't run hmsUpdateChartTables.ps1 and therefore blocksdata.php either doesn't exist or its not updated with the color data. You can run it any time to update blocksdata.php, but all the data is from yesterday and previous, so just add it to the list of scripts to run at 12:01 am.

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

Re: Block IPs

Post by palinka » 2020-03-04 16:05

eliassal wrote:
2020-03-04 15:21
I hard coded in dialtodayhits.php

Code: Select all

SELECT TOP 1 ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), 1) AS dailymax,
CAST(timestamp AS DATE) AS daily
FROM hm_fwban 
GROUP BY CAST(timestamp AS DATE) 
ORDER BY CAST(timestamp AS DATE)  DESC
when running in sql directly I get
dailymax daily
3.6 2020-03-03
again nothing appears in the gauge
Why did you round to decimal? -1 rounds to nearest 10. Nobody cares about decimals for the redline limit. Its easier to read with zeros. That's why its -1 for the IPs added/blocked and -3 (round to thousands "NN,000") for total blocks.

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

Re: Block IPs

Post by palinka » 2020-03-04 16:20

eliassal wrote:
2020-03-04 15:21
There is a big issue in dialtodayhits.php
The following does not work in sql serverm if you remember I already mentioned this and was fixed in some pages; in sql we cant use an alias in the group by keyword

Code: Select all

SELECT ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), -1) AS dailymax, 
CAST(timestamp AS DATE) AS daily 
FROM hm_fwban 
GROUP BY daily 
ORDER BY dailymax DESC 
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
I hard coded in dialtodayhits.php

Code: Select all

SELECT TOP 1 ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), 1) AS dailymax,
CAST(timestamp AS DATE) AS daily
FROM hm_fwban 
GROUP BY CAST(timestamp AS DATE) 
ORDER BY CAST(timestamp AS DATE)  DESC
when running in sql directly I get
dailymax daily
3.6 2020-03-03
again nothing appears in the gauge
I think there's something wrong with your SQL. I don't know enough about SQL queries to say exactly. But let me describe logically what I see. If your result is only 3.6, that means the real maximum number of hits in a single day is 3 (=3.6/1.2). That sounds very, very low to me. Its easy enough to confirm visually - show me a screenshot of your chart for IPs added/blocked. If any previous day is more than 3, then your query is messed up. The result of the query should match the highest value on your chart (x 1.2 and rounded to nearest TEN, not tenth)

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

Re: Block IPs

Post by palinka » 2020-03-04 16:40

eliassal wrote:
2020-03-04 15:39
Something else happened, an IP just banned today (1st 1), Refreshed the page, 1st gauge displayed something very strange

Image

here is what I see in Chrome
var data = google.visualization.arrayToDataTable([
['Label', 'Value'],
['Bans', 1]]);var options = { width: 100, height: 100, min: 0, max: 1.2, redFrom: 1, redTo: 1.2, yellowFrom: 0.75, yellowTo: 1, minorTicks: 10
The needle is at 100%. 1/1 = 100%. You did something to your maximum value query to make it equal 1 OR make it equal today's hits (1 so far). If you get another hit, you'll know which what you did wrong because 2/1 = 200%. The needle will be off the chart (I don't know how it will display, but you'll know it when you see it). This means your max data is static at 1, or its rounded to 1 instead of 10 (if your max hits fall in that range).

~~OR~~

You will see the needle in the same location. 2/2 = 100%. This would mean your max data query is the same as your current data query.

Either way, something is messed up. I can't tell you how to fix the syntax of your query, but I can tell you what the outcome should be and you can fix it based on that.

On dialtodayshits.php:

Code: Select all

		SELECT	
			ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), -1) AS dailymax,
			DATE(timestamp) AS daily
		FROM hm_fwban
		GROUP BY daily
		ORDER BY dailymax DESC
		LIMIT 1
This groups count(IP) by day in descending order with limit 1 so we take only the highest value: the maximum number of IPs added in a single day since inception. Then it multiplies by 1.2 and rounds to the nearest 10. So if the maximum number of IPs added in a single day is 32 (look at your IPs added/blocked chart for visual check), then the result would be 32x1.2=38.4 -> rounded to 10 = 40.

40 is the $redTo (120% limit rounded for readability).
33.33 is the $redFrom AND $yellowTo (40/1.2 = the 100% line)
25 is the $yellowFrom (40/1.2x.75 = the 75% line)

Get your query to find the max number of IPs added in any single day. Get rid of the rounding and the multiplication and make your query result match the highest peak on the IPs added line chart. Then you will know it works.

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

Re: Block IPs

Post by eliassal » 2020-03-04 17:39

its likely that you didn't run hmsUpdateChartTables.ps
I have run it so many times Just saw this in the log several timesm again this is a query that sql cant run

Code: Select all

Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'." Exception calling "Fill" with "2" argument(s): "Invalid column name 'daily'."[0]
20/03/04 16:21:04.66 : ERROR : Unable to run query : 
	SELECT	
		ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), -1) AS dailymax,
		CAST(timestamp AS DATE) AS daily
	FROM hm_fwban_rh
	GROUP BY daily
	ORDER BY dailymax DESC
	OFFSET 0 ROWS 
		   	           FETCH NEXT 1 ROWS ONLY
Last edited by eliassal on 2020-03-04 17:49, edited 1 time in total.

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

Re: Block IPs

Post by eliassal » 2020-03-04 17:41

Why did you round to decimal? -1 rounds to nearest 10.......
No it is 1m that was a test in sql

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

Re: Block IPs

Post by eliassal » 2020-03-04 17:45

show me a screenshot of your chart for IPs added/blocked.
Image

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

Re: Block IPs

Post by eliassal » 2020-03-04 18:14

I replaced
GROUP BY daily
by
CAST(timestamp AS DATE)
everywhere
I have the 3 gauges but with strange visuel snapshot hereunder, Of cours I ran the updatechart powershell.

Image
Here is the results in chrome

Code: Select all

<script type="text/javascript">
	google.charts.load('current', {'packages':['gauge']});
	google.charts.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = google.visualization.arrayToDataTable([
		['Label', 'Value'],
['Bans', 1]]);var options = { width: 100, height: 100, min: 0, max: 10.0, redFrom: 8.3333333333333, redTo: 10.0, yellowFrom: 6.25, yellowTo: 8.3333333333333, 		minorTicks: 10
	};

	var chart = new google.visualization.Gauge(document.getElementById('todays_hits_dial'));

	chart.draw(data, options);

	// setInterval(function() {
		// data.setValue(0, 1, 40 + Math.round(60 * Math.random()));
		// chart.draw(data, options);
	// }, 13000);
	}
</script>
<script type="text/javascript">
	google.charts.load('current', {'packages':['gauge']});
	google.charts.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = google.visualization.arrayToDataTable([
		['Label', 'Value'],


['Repeats', 2]]);var options = { width: 100, height: 100, min: 0, max: 10.0, redFrom: 8.3333333333333, redTo: 10.0, yellowFrom: 6.25, yellowTo: 8.3333333333333, 		minorTicks: 10
	};

	var chart = new google.visualization.Gauge(document.getElementById('todays_repeats_dial'));

	chart.draw(data, options);

	}
</script>
<script type="text/javascript">
	google.charts.load('current', {'packages':['gauge']});
	google.charts.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = google.visualization.arrayToDataTable([
		['Label', 'Value'],
['Blocks', 264]]);var options = { width: 100, height: 100, min: 0, max: 7000.0, redFrom: 5833.3333333333, redTo: 7000.0, yellowFrom: 4375, yellowTo: 5833.3333333333, 		minorTicks: 10
	};

	var chart = new google.visualization.Gauge(document.getElementById('todays_blocks_dial'));

	chart.draw(data, options);

	}
</script>

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

Re: Block IPs

Post by palinka » 2020-03-04 18:39

eliassal wrote:
2020-03-04 18:14
I replaced
GROUP BY daily
by
CAST(timestamp AS DATE)
everywhere
I have the 3 gauges but with strange visuel snapshot hereunder, Of cours I ran the updatechart powershell.

Image
Looks good to me. If I'm reading the charts correctly:

max IPs added in a single day = 8 x 1.2 = 9.6 rounded to nearest 10 = 10
max IPs blocked in a single day = 11 x 1.2 = 13.2 rounded to nearest 10 = 10
max total blocks in a single day = ~ 5900 x 1.2 = 7080 rounded to nearest 1000 = 7000

I assume the current data queries are correct, so everything looks good. The rounding will make more sense when you start getting more data and the counts grow. Then, when you're in the yellow zone, you'll know you're having a "banner" day. When you're in the red zone, you'll be having a record breaking day. :D

If you send me all 6 SQL queries for the dials, I'll make sure everything gets updated to work properly.

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

Re: Block IPs

Post by eliassal » 2020-03-04 19:04

OK got it, thanks. Yes data are up-to-date
As sql does not accept aliases in a GRoup by for the queries I replaced "daily" by CAST(timestamp AS DATE) as follows

Code: Select all

//Get guage max
	$sql = $pdo->prepare("
		SELECT	
			ROUND(((COUNT(DISTINCT(ipaddress))) * 1.2), -1) AS dailymax,
			".DBCastDateTimeFieldAsDate('timestamp')." AS daily
		FROM hm_fwban
		GROUP BY CAST(timestamp AS DATE)
		".DBLimitRowsWithOffset('dailymax','DESC',0,0,0,1)
);
In dialtodayhits.php,
And 2 queries in hmsUpdateChartTables.ps1

Code: Select all

 $Query = "
	SELECT	
		ROUND(((COUNT(ipaddress)) * 1.2), -3) AS dailymax,
		$( DBCastDateTimeFieldAsDate 'timestamp' ) AS daily
	FROM hm_fwban_rh
	GROUP BY CAST(timestamp AS DATE)
	ORDER BY dailymax DESC
	$( DBLimitRowsWithOffset 0 1 )
"
why you indicate 6 queries?

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

Re: Block IPs

Post by eliassal » 2020-03-04 19:30

Tell me Palinka, why in hmsUpdateChartTables.ps1 in
# Hits Per Day Combined
and
# Block Frequency
you subtract 1 from the month statement

Code: Select all

($( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%c') - 1) AS month,

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

Re: Block IPs

Post by palinka » 2020-03-04 19:38

eliassal wrote:
2020-03-04 19:30
Tell me Palinka, why in hmsUpdateChartTables.ps1 in
# Hits Per Day Combined
and
# Block Frequency
you subtract 1 from the month statement

Code: Select all

($( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%c') - 1) AS month,
Because javascript month format is 0-11, not 1-12. If you don't do this, you'll get the wrong date. This only applies to the charts, which are js.

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

Re: Block IPs

Post by palinka » 2020-03-04 19:40

eliassal wrote:
2020-03-04 19:04
why you indicate 6 queries?
Because there are two for each dial: today's data and max data. They are different for each dial, of course.

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

Re: Block IPs

Post by eliassal » 2020-03-04 19:49

Because there are two for each dial: today's data and max data
Where, in which files other the ones I mentioned?

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

Re: Block IPs

Post by eliassal » 2020-03-04 19:54

Because javascript month format is 0-11, not 1-12.

but in sql this is returning data as 2months earlier earlier as follows and an error in the last 3 records as they are in March not Februray
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') - 1) AS month,
FORMAT(CAST(timestamp AS DATE), 'dd', 'en-US') AS day,
COUNT(ipaddress) AS ipperday
FROM hm_fwban_rh
WHERE CAST(timestamp AS DATE) < CAST(GETDATE() AS DATE)
GROUP BY CAST(timestamp AS DATE)
ORDER BY daily ASC
daily year month day ipperday
2020-02-17 2020 1 17 506
2020-02-18 2020 1 18 2
2020-02-19 2020 1 19 539
2020-02-20 2020 1 20 2088
2020-02-21 2020 1 21 2358
2020-02-22 2020 1 22 3981
2020-02-23 2020 1 23 1128
2020-02-24 2020 1 24 3813
2020-02-25 2020 1 25 5794
2020-02-26 2020 1 26 342
2020-02-27 2020 1 27 5
2020-02-29 2020 1 29 8
2020-03-01 2020 2 01 25
2020-03-02 2020 2 02 6
2020-03-03 2020 2 03 34


I will check sql and let you know or let me know if there is a solution in javascript to remove the -1 in order to have up-todate figures

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

Re: Block IPs

Post by palinka » 2020-03-04 20:39

eliassal wrote:
2020-03-04 19:54
Because javascript month format is 0-11, not 1-12.

Code: Select all

daily		year	month	day	ipperday
2020-02-17	2020	1	17	506
2020-02-18	2020	1	18	2
2020-02-19	2020	1	19	539
2020-02-20	2020	1	20	2088
2020-02-21	2020	1	21	2358
2020-02-22	2020	1	22	3981
2020-02-23	2020	1	23	1128
2020-02-24	2020	1	24	3813
2020-02-25	2020	1	25	5794
2020-02-26	2020	1	26	342
2020-02-27	2020	1	27	5
2020-02-29	2020	1	29	8
2020-03-01	2020	2	01	25
2020-03-02	2020	2	02	6
2020-03-03	2020	2	03	34
The table is correct. 0=Jan, 1=Feb, 2=Mar. And you know its working because your charts show the correct dates. :D

Image

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

Re: Block IPs

Post by eliassal » 2020-03-04 21:03

because your charts show the correct dates
Ok understand, got it

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

Re: Block IPs

Post by palinka » 2020-03-04 21:04

eliassal wrote:
2020-03-04 19:49
Because there are two for each dial: today's data and max data
Where, in which files other the ones I mentioned?
The "today" query is located in each of the dial....php.
The "max" query is located in hmsUpdateChartTables.ps1 (except one is in dialtodayshits.php, which has both "today" and "max")

Post Reply