How to send notifications on auto-ban events?
-
- New user
- Posts: 1
- Joined: 2022-05-16 14:21
How to send notifications on auto-ban events?
Hello!
Is it possible to send email notifications when auto-ban feature is enabled and a block event has occured?
Is it possible to send email notifications when auto-ban feature is enabled and a block event has occured?
Re: How to send notifications on auto-ban events?
There's no event for autoban. However, you could write a handler script that checks for new entries in the database, then notifies you if there are new entries, or whatever criteria you want.
If you autoban for 60 minutes and run the handler every 5 minutes - this will give you entries created in the past 5 minutes:
If you autoban for 60 minutes and run the handler every 5 minutes - this will give you entries created in the past 5 minutes:
Code: Select all
SELECT * FROM hm_securityranges WHERE rangeexpirestime > (NOW() + INTERVAL 55 MINUTE) AND rangeexpirestime < (NOW() + INTERVAL 60 MINUTE)
Re: How to send notifications on auto-ban events?
I snatched this from the MySQL.com ... Require MySQL and a SMTP server with a maildrop like IIS SMTP Server or PostFix ... Example comes from Exchange 2007 Beta 2 on Windows Server 2003. I believe you need at least MySQL 5.1 to do thispalinka wrote: ↑2022-05-16 15:15There's no event for autoban. However, you could write a handler script that checks for new entries in the database, then notifies you if there are new entries, or whatever criteria you want.
If you autoban for 60 minutes and run the handler every 5 minutes - this will give you entries created in the past 5 minutes:Code: Select all
SELECT * FROM hm_securityranges WHERE rangeexpirestime > (NOW() + INTERVAL 55 MINUTE) AND rangeexpirestime < (NOW() + INTERVAL 60 MINUTE)
Code: Select all
CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users
FOR EACH ROW BEGIN
SELECT * FROM email_bodies WHERE EmailID = 1;
SELECT * FROM tbl_users WHERE ClientID = @ClientID
INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail)
SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail),
"From: triggers@yourmysqlserver.whatever",
concat("Subject: ",NEW.subject),
"",
email_bodies.EmailContent
INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml"
FIELDS TERMINATED by '\r\n';
END
SørenR.
Woke is Marxism advancing through Maoist cultural revolution.
Woke is Marxism advancing through Maoist cultural revolution.
Re: How to send notifications on auto-ban events?
SørenR.
Woke is Marxism advancing through Maoist cultural revolution.
Woke is Marxism advancing through Maoist cultural revolution.
Re: How to send notifications on auto-ban events?
DANG! That's smart.SorenR wrote: ↑2022-05-16 16:34I snatched this from the MySQL.com ... Require MySQL and a SMTP server with a maildrop like IIS SMTP Server or PostFix ... Example comes from Exchange 2007 Beta 2 on Windows Server 2003. I believe you need at least MySQL 5.1 to do thispalinka wrote: ↑2022-05-16 15:15There's no event for autoban. However, you could write a handler script that checks for new entries in the database, then notifies you if there are new entries, or whatever criteria you want.
If you autoban for 60 minutes and run the handler every 5 minutes - this will give you entries created in the past 5 minutes:Code: Select all
SELECT * FROM hm_securityranges WHERE rangeexpirestime > (NOW() + INTERVAL 55 MINUTE) AND rangeexpirestime < (NOW() + INTERVAL 60 MINUTE)
https://forums.mysql.com/read.php?99,33635,172632Code: Select all
CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users FOR EACH ROW BEGIN SELECT * FROM email_bodies WHERE EmailID = 1; SELECT * FROM tbl_users WHERE ClientID = @ClientID INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail) SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail), "From: triggers@yourmysqlserver.whatever", concat("Subject: ",NEW.subject), "", email_bodies.EmailContent INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml" FIELDS TERMINATED by '\r\n'; END
Combine with this pickup folder script and you can use it with hmailserver.
Re: How to send notifications on auto-ban events?
Did you read the next post ?? How to launch PHP code from a MySQL trigger ??palinka wrote: ↑2022-05-16 16:50DANG! That's smart.SorenR wrote: ↑2022-05-16 16:34I snatched this from the MySQL.com ... Require MySQL and a SMTP server with a maildrop like IIS SMTP Server or PostFix ... Example comes from Exchange 2007 Beta 2 on Windows Server 2003. I believe you need at least MySQL 5.1 to do thispalinka wrote: ↑2022-05-16 15:15There's no event for autoban. However, you could write a handler script that checks for new entries in the database, then notifies you if there are new entries, or whatever criteria you want.
If you autoban for 60 minutes and run the handler every 5 minutes - this will give you entries created in the past 5 minutes:Code: Select all
SELECT * FROM hm_securityranges WHERE rangeexpirestime > (NOW() + INTERVAL 55 MINUTE) AND rangeexpirestime < (NOW() + INTERVAL 60 MINUTE)
https://forums.mysql.com/read.php?99,33635,172632Code: Select all
CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users FOR EACH ROW BEGIN SELECT * FROM email_bodies WHERE EmailID = 1; SELECT * FROM tbl_users WHERE ClientID = @ClientID INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail) SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail), "From: triggers@yourmysqlserver.whatever", concat("Subject: ",NEW.subject), "", email_bodies.EmailContent INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml" FIELDS TERMINATED by '\r\n'; END
Combine with this pickup folder script and you can use it with hmailserver.
SørenR.
Woke is Marxism advancing through Maoist cultural revolution.
Woke is Marxism advancing through Maoist cultural revolution.
Re: How to send notifications on auto-ban events?
Even smarter.SorenR wrote: ↑2022-05-16 16:53Did you read the next post ?? How to launch PHP code from a MySQL trigger ??palinka wrote: ↑2022-05-16 16:50DANG! That's smart.SorenR wrote: ↑2022-05-16 16:34
I snatched this from the MySQL.com ... Require MySQL and a SMTP server with a maildrop like IIS SMTP Server or PostFix ... Example comes from Exchange 2007 Beta 2 on Windows Server 2003. I believe you need at least MySQL 5.1 to do this
https://forums.mysql.com/read.php?99,33635,172632Code: Select all
CREATE TRIGGER send_emailverifier AFTER INSERT, UPDATE ON tbl_users FOR EACH ROW BEGIN SELECT * FROM email_bodies WHERE EmailID = 1; SELECT * FROM tbl_users WHERE ClientID = @ClientID INSERT INTO tbl_emailverify VALUES (UUID, tbl_users.ClientID, OLD.CltEmail, NEW.CltEmail) SELECT concat("To: ",NEW.CltEmail & "," & OLD.CltEmail), "From: triggers@yourmysqlserver.whatever", concat("Subject: ",NEW.subject), "", email_bodies.EmailContent INTO OUTFILE "/inetpub/mailroot/pickup/mail.eml" FIELDS TERMINATED by '\r\n'; END
Combine with this pickup folder script and you can use it with hmailserver.
Re: How to send notifications on auto-ban events?
as palinka told, there is no event for autoban. BUT there are entries in logfiles, i.e:Marat Abdulin wrote: ↑2022-05-16 14:42Hello!
Is it possible to send email notifications when auto-ban feature is enabled and a block event has occured?
Code: Select all
POP : ... "SENT: * Too many invalid logon attempts.[nl]x BAD Goodbye"
IMAP : ...."SENT: -ERR Invalid user name or password. Too many invalid logon attempts."
SMTP : .... "SENT: 535 Authentication failed. Too many invalid logon attempts."
reading (parsing) relevant logfile periodically offers you all autobans of the day - if any.
you must maintain a timestamp/IP pair list in a text file to compare the outcome each time to avoid redundant notifications for same ban. a new or recurring expired IP triggers notification + added to list with its timestamp.
perhaps not most elegant solution but should work. i assume your logfiles are not GB sized by the way
Katip
--
HMS 5.7, MariaDB 10.4.10, SA 4.0.0, ClamAV 0.103.8
--
HMS 5.7, MariaDB 10.4.10, SA 4.0.0, ClamAV 0.103.8
Re: How to send notifications on auto-ban events?
Just saying
Most of my autobans happen overnight, and I have more than a thousand each week
Last thing I'd need is to be told that another one occurred
Most of my autobans happen overnight, and I have more than a thousand each week
Last thing I'd need is to be told that another one occurred
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation
https://www.hmailserver.com/documentation