Block IPs
Re: Block IPs
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
Re: Block IPs
According to this thread and another one, 1000 IPs maybe a good compromise
https://superuser.com/questions/802355/ ... 534#804534
https://superuser.com/questions/802355/ ... 534#804534
Re: Block IPs
There's no limit to how high i can jump up into the sky...eliassal wrote: ↑2020-02-21 14:28According to this thread and another one, 1000 IPs maybe a good compromise
https://superuser.com/questions/802355/ ... 534#804534
Re: Block IPs
Note to self: learn to read....
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.
Re: Block IPs
Sorry, 1 Rule per month
Re: Block IPs
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`"
}
}
}
Re: Block IPs
eliassal wrote: ↑2020-02-21 19:31Here 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)
Re: Block IPs
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
$ErrorActionPreference = 'SilentlyContinue'
to get rid of the non-terminating error produced by Get-NetFirewallRule
Thanks for the hint
Re: Block IPs
Here's what I have.eliassal wrote: ↑2020-02-21 21:20Palinka, 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
* 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
Re: Block IPs
I just updated all previous months. Works great. Firewall incoming opens faster than ever.
Re: Block IPs
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)
So I will use
in the query
Have you came across a fix/idea for this set problem?
Code: Select all
& netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
Code: Select all
$BanDate = (Get-Date).ToString("yyyy-MM")
so I get all IPs everyday and injects them in the rule$Query = "
SELECT
ipaddress
FROM hm_fwban
WHERE $(DBCastDateTimeFieldAsDate('timestamp')) LIKE '$BanDate%' AND flag IS NULL
ORDER BY timestamp DESC
$(DBLimitRowsWithOffset $($N * $Rows) $Rows)
"
Have you came across a fix/idea for this set problem?
Re: Block IPs
By the way Palinka, in Config.php, I kept
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
In hMailServer, On IP/port 587, I have configured a certificate that I have generated which works fine with my outlook.$SMTPPort = 587 #
$SSL = 'True'
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
I double clicked on the same mycertificate.cer and installed in the server store still getting the same behavior. Anything I am missing here ?"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"
Re: Block IPs
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.eliassal wrote: ↑2020-02-22 21:22By the way Palinka, in Config.php, I keptIn hMailServer, On IP/port 587, I have configured a certificate that I have generated which works fine with my outlook.$SMTPPort = 587 #
$SSL = 'True'
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
I double clicked on the same mycertificate.cer and installed in the server store still getting the same behavior. Anything I am missing here ?"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"
Re: Block IPs
Careful - you could be duplicating rules.
The way I was doing it was:So I will useCode: Select all
& netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
in the queryCode: Select all
$BanDate = (Get-Date).ToString("yyyy-MM")
so I get all IPs everyday and injects them in the rule$Query = "
SELECT
ipaddress
FROM hm_fwban
WHERE $(DBCastDateTimeFieldAsDate('timestamp')) LIKE '$BanDate%' AND flag IS NULL
ORDER BY timestamp DESC
$(DBLimitRowsWithOffset $($N * $Rows) $Rows)
"
Have you came across a fix/idea for this set problem?
* 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.
Re: Block IPs
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 }
Maybe in your script you left out updating hm_fwban.rulename after creating the rule?
Re: Block IPs
But RemRuleIP expects 1 IP and rule.txt file is hard coded
In fact I am following the same logic
- Getting all banned IPs ofr February each time
- insert them in the file....rule.txt
- Then use
so again all ips are inserted again because of SET (it deletes exisiting IPs before inserting)
which is different from the logic inside hmsConsolidateRules, so both needs modification."$PSScriptRoot\fwrulelist.txt"
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"
Code: Select all
& netsh advfirewall firewall set rule name="$RuleName" new remoteip=$(Get-Content "$FilePathName.rule.txt")
Re: Block IPs
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")
[/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")
Re: Block IPs
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
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
I think there is something wrong in the page repeats-IP.php, lines 53, 56, 82 and 85
The date format should be
not'Y-m-d'
http://prodwebserver/PHPBanAdmin/repeat ... ]date=2020[/b]/02/25&repeatIP=45.142.195.6
Of course with the 2nd one nothing is returned as it does not match.'%y/%m/%d'
http://prodwebserver/PHPBanAdmin/repeat ... e=20/02/25[/b]&repeatIP=45.142.195.6
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
Re: Block IPs
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.
* 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.
Re: Block IPs
Yeh, really nice, as usual goog work Palinka, I will do.
Question in Ban reasons section, I see in your demo
Question in Ban reasons section, I see in your demo
I have only IDS in my site. Are those filters you setup in the events handler vbs that write the reason or something else?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.
.......
.....
Re: Block IPs
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.eliassal wrote: ↑2020-03-01 15:39Yeh, really nice, as usual goog work Palinka, I will do.
Question in Ban reasons section, I see in your demo
I have only IDS in my site. Are those filters you setup in the events handler vbs that write the reason or something else?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.
.......
.....
Re: Block IPs
Here's an example:
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.
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
Re: Block IPs
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
By the way I imagine that already you have configured your hMailServer with all those anti spam providers non?
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
For the other 2 filters should I write the exact same code and what to use the IP or the objectzen.spamhaus.org
bl.spamcop.net
b.barracudacentral.org
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
Re: Block IPs
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?
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?
Re: Block IPs
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.
They're called at OnHELO.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?
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.In my hMailServer config for example I haveFor the other 2 filters should I write the exact same code and what to use the IP or the objectzen.spamhaus.org
bl.spamcop.net
b.barracudacentral.org
By the way I imagine that already you have configured your hMailServer with all those anti spam providers non?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
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.
Re: Block IPs
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.eliassal wrote: ↑2020-03-01 18:35I 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?
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.
Re: Block IPs
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
"^(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
Re: Block IPs
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
Re: Block IPs
I noticed that you have added a variableDownload everything
* Create index on timestamp in hm_fwban_rh
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'
);
Re: Block IPs
also, you can configure port 25 to disable AUTH
in which part / where I can do this?
in which part / where I can do this?
Re: Block IPs
I noticed that not including "country" in manual bans (in web admin) caused a problem. I don't remember the problem now. But the solution was to add country info at the time you create manual bans. Therefore, I needed a method of obtaining this information.eliassal wrote: ↑2020-03-03 11:12I noticed that you have added a variableDownload everything
* Create index on timestamp in hm_fwban_rhIs it used ? if yes; where?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' );
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.
Re: Block IPs
Ok, I will wait for your update then download the new version tonightI'll fix that later today.
Re: Block IPs
.See link above
I saw the link I noticed the lines
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.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.
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?
Re: Block IPs
What this does is ban auth logon on whatever ports you choose. You don't disable authentication - you put it on alternate ports.eliassal wrote: ↑2020-03-03 15:35.See link above
I saw the link I noticed the lines
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.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.
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?
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.
Re: Block IPs
More confusion
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
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
Re: Block IPs
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.
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.
Woke is Marxism advancing through Maoist cultural revolution.
Re: Block IPs
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
Am I missing something?
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>
Re: Block IPs
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
I hard coded in dialtodayhits.php
when running in sql directly I get
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
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
again nothing appears in the gaugedailymax daily
3.6 2020-03-03
Re: Block IPs
Something else happened, an IP just banned today (1st 1), Refreshed the page, 1st gauge displayed something very strange
here is what I see in Chrome
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>
Re: Block IPs
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.
Re: Block IPs
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.eliassal wrote: ↑2020-03-04 15:21I hard coded in dialtodayhits.php
when running in sql directly I getCode: 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
again nothing appears in the gaugedailymax daily
3.6 2020-03-03
Re: Block IPs
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)eliassal wrote: ↑2020-03-04 15:21There 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
I hard coded in dialtodayhits.phpCode: 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
when running in sql directly I getCode: 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
again nothing appears in the gaugedailymax daily
3.6 2020-03-03
Re: Block IPs
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).eliassal wrote: ↑2020-03-04 15:39Something else happened, an IP just banned today (1st 1), Refreshed the page, 1st gauge displayed something very strange
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
~~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
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.
Re: Block IPs
I have run it so many times Just saw this in the log several timesm again this is a query that sql cant runits likely that you didn't run hmsUpdateChartTables.ps
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.
Re: Block IPs
No it is 1m that was a test in sqlWhy did you round to decimal? -1 rounds to nearest 10.......
Re: Block IPs
show me a screenshot of your chart for IPs added/blocked.
Re: Block IPs
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.
Here is the results in chrome
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.
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>
Re: Block IPs
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.
If you send me all 6 SQL queries for the dials, I'll make sure everything gets updated to work properly.
Re: Block IPs
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
In dialtodayhits.php,
And 2 queries in hmsUpdateChartTables.ps1
why you indicate 6 queries?
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)
);
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 )
"
Re: Block IPs
Tell me Palinka, why in hmsUpdateChartTables.ps1 in
and# Hits Per Day Combined
you subtract 1 from the month statement# Block Frequency
Code: Select all
($( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%c') - 1) AS month,
Re: Block IPs
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.eliassal wrote: ↑2020-03-04 19:30Tell me Palinka, why in hmsUpdateChartTables.ps1 in
and# Hits Per Day Combinedyou subtract 1 from the month statement# Block FrequencyCode: Select all
($( DBFormatDate (DBCastDateTimeFieldAsDate 'timestamp') '%c') - 1) AS month,
Re: Block IPs
Where, in which files other the ones I mentioned?Because there are two for each dial: today's data and max data
Re: Block IPs
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
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
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
daily year month day ipperdaySELECT
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
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
Re: Block IPs
The table is correct. 0=Jan, 1=Feb, 2=Mar. And you know its working because your charts show the correct dates.eliassal wrote: ↑2020-03-04 19:54Because 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
Re: Block IPs
Ok understand, got itbecause your charts show the correct dates
Re: Block IPs
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")