From patchwork Thu Jul 30 19:15:53 2026 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Robin Roevens X-Patchwork-Id: 10079 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange x25519) (Client CN "mail01.haj.ipfire.org", Issuer "YR2" (not verified)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4hB0K434Kxz3wqJ for ; Thu, 30 Jul 2026 19:54:32 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [IPv6:2001:678:b28::201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange x25519) (Client CN "mail02.haj.ipfire.org", Issuer "YE1" (not verified)) by mail01.ipfire.org (Postfix) with ESMTPS id 4hB0Jw1ppCzyD for ; Thu, 30 Jul 2026 19:54:24 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [IPv6:::1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4hB0GS75C4z37B3 for ; Thu, 30 Jul 2026 19:52:16 +0000 (UTC) X-Original-To: development@lists.ipfire.org Received: from mail01.ipfire.org (mail01.haj.ipfire.org [IPv6:2001:678:b28::25]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange x25519 server-signature ECDSA (secp384r1 raw public key) server-digest SHA384 client-signature RSA-PSS (4096 bits) client-digest SHA256) (Client CN "mail01.haj.ipfire.org", Issuer "YR2" (not verified)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4hB0GK0Zgsz36W7 for ; Thu, 30 Jul 2026 19:52:09 +0000 (UTC) Received: from layka.disroot.org (layka.disroot.org [178.21.23.139]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange x25519) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPS id 4hB0G85Ndrz2l9 for ; Thu, 30 Jul 2026 19:52:00 +0000 (UTC) Authentication-Results: mail01.ipfire.org; dkim=pass header.d=disroot.org header.s=mail header.b=RF1b6yDR; spf=pass (mail01.ipfire.org: domain of robin.roevens@disroot.org designates 178.21.23.139 as permitted sender) smtp.mailfrom=robin.roevens@disroot.org; dmarc=pass (policy=reject) header.from=disroot.org ARC-Seal: i=1; a=rsa-sha256; d=lists.ipfire.org; s=202003rsa; cv=none; t=1785441120; b=BiDFZfOHL9CYFk6YOMX3RVusG0nJTJJHckS/CbUsvI5JqzuokKSvaGdInajqhSQwK/b/Qw 361kDL3IQUmxGNUxOqPCsx7QK3UgRaazk4Li8rdCpOCtwu9RW2vKFbubF2N2/ezy6uVqx4 TGh6tzYjQVroMxNHaxmvhtBvme3Y4EwqUfEOqOTxVfPT0unrcFdxWTwwVmpsN1UGE/madL kSPapFQjUVUT/rmb7XdBMvosK6c+sDxN+lwiMWejJ6POdueENGrV98ai1YwCTD5mvGlZLi 5w6txnR7e9fKEsmv4GEMX14zkIlw8g0guhxCeo8bgDF5SDniqZM9OI0xnfTuTg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=lists.ipfire.org; s=202003rsa; t=1785441120; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references:dkim-signature; bh=wa9UCLJmkFNw5yquMJuhLkMj2qi+T81GDifEheu+vZA=; b=Q+ndkf34HFyU4KnNFjtgHnYlhsZ9JMyMR/RIOSWt4R2Tp4gt5jJA+Ub2K4hph5kgX9x3HD 2e5UniLgolBdAn+eMM57Qk3qO1NeNowjJ+zlyELC1c2wmiVrImK6VzayZoKIxesQVJNjAe 0bamWgczOyJh/ZwSBoy6XOfP5QiRItQGnAkunWhFM4MeMh2CG60ud16oozd/vn1rn96vbK xavMDs1RcNeYdC+AdQsS5lK2FnuYosq4zBcpGitVECOBFDnBQly4jKNf3JgqEF0T8N/MT1 nugCzsJU9aM7J5zIYjmPuJqBP81RpSjpcpBs6ngNYHZBrcJ7+rLsLzTkutqy5Q== ARC-Authentication-Results: i=1; mail01.ipfire.org; dkim=pass header.d=disroot.org header.s=mail header.b=RF1b6yDR; spf=pass (mail01.ipfire.org: domain of robin.roevens@disroot.org designates 178.21.23.139 as permitted sender) smtp.mailfrom=robin.roevens@disroot.org; dmarc=pass (policy=reject) header.from=disroot.org Received: from mail01.layka.lan (localhost [127.0.0.1]) by disroot.org (Postfix) with ESMTP id 7148841C30 for ; Thu, 30 Jul 2026 21:52:00 +0200 (CEST) X-Virus-Scanned: SPAM Filter at disroot.org Received: from layka.disroot.org ([127.0.0.1]) by localhost (disroot.org [127.0.0.1]) (amavis, port 10024) with ESMTP id Za6amd6VHspQ for ; Thu, 30 Jul 2026 21:51:59 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=disroot.org; s=mail; t=1785441119; bh=3wsOZ3EaIg2Ff0uZDfFAgA/sXGNPhZfX6dLLZZ9H+Do=; h=From:To:Cc:Subject:Date:In-Reply-To:References; b=RF1b6yDRTWk1UY4GipNabLwFx5QCpgyiKP8PhRF9+B9SyOyViwlF4Gjoal2+yUEe4 SZaDgAzPR1Q+nDzuYFA6/LNWd1Tqnfr3zMa+YXRr4oHB0/d4fub5CIaOCEqUMfI4Ki foWRc1fAYTA1OCQPy8TyEQ5V8+dUqR2COaGxigNjaF6yWRlYOzbE5SUv7ADtPruE6k KGQeeQdOYC1BREMJuKYzbvJNt1ONLb3oG/q3P8vCIHqJ4iQcucSgu0sBAIUTICNIF2 m8kzL4ZdgALq8IStz+mt9vDLcckHkHhsb/K+HYKMmsCKz5rClY/kUJasYVSvoj+RAV yohUGH6e+UIMw== Received: from chojin.roevenslambrechts.be (chojin.roevenslambrechts.be [192.168.0.50]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) (no client certificate requested) (Authenticated sender) by hachiman (MailScanner Milter) with SMTP id E433C585FDD; Thu, 30 Jul 2026 21:51:54 +0200 (CEST) From: Robin Roevens To: development@lists.ipfire.org Cc: Robin Roevens Subject: [PATCH 2/5] Add database column zabbix_pending in alerts table. Date: Thu, 30 Jul 2026 21:15:53 +0200 Message-ID: <20260730195148.3278295-3-robin.roevens@disroot.org> In-Reply-To: <20260730195148.3278295-1-robin.roevens@disroot.org> References: <20260730195148.3278295-1-robin.roevens@disroot.org> Precedence: list List-Id: List-Subscribe: , List-Unsubscribe: , List-Post: List-Help: Sender: Mail-Followup-To: MIME-Version: 1.0 X-RoevensLambrechts-MailScanner-ID: E433C585FDD.AD543 X-RoevensLambrechts-MailScanner: Found to be clean X-RoevensLambrechts-MailScanner-From: robin.roevens@disroot.org X-RoevensLambrechts-MailScanner-Watermark: 1786045915.34386@nO9qSAUCxwWA4OmOg4jCeA X-Rspamd-Server: mail01.haj.ipfire.org X-Rspamd-Queue-Id: 4hB0G85Ndrz2l9 X-Rspamd-Action: no action X-Spamd-Result: default: False [-5.63 / 11.00]; BAYES_HAM(-3.00)[100.00%]; R_DKIM_ALLOW(-1.65)[disroot.org:s=mail]; MID_CONTAINS_FROM(1.00)[]; DKIM_REPUTATION(-0.92)[-0.92153870218341]; SPF_REPUTATION_HAM(-0.65)[-0.65402885146808]; DMARC_POLICY_ALLOW(-0.50)[disroot.org,reject]; R_MISSING_CHARSET(0.50)[]; R_SPF_ALLOW(-0.20)[+a:c]; MIME_GOOD(-0.10)[text/plain]; MX_GOOD(-0.10)[disroot.org]; RCPT_COUNT_TWO(0.00)[2]; ASN(0.00)[asn:50673, ipnet:178.21.23.0/24, country:NL]; IP_REPUTATION_HAM(0.00)[asn: 50673(0.00), country: NL(-0.01), ip: 178.21.23.139(0.00)]; ARC_NA(0.00)[]; TO_DN_SOME(0.00)[]; MIME_TRACE(0.00)[0:+]; RCVD_COUNT_THREE(0.00)[3]; RCVD_TLS_LAST(0.00)[]; TO_MATCH_ENVRCPT_SOME(0.00)[]; MISSING_XM_UA(0.00)[]; FROM_EQ_ENVFROM(0.00)[]; DKIM_TRACE(0.00)[disroot.org:+]; ARC_SIGNED(0.00)[lists.ipfire.org:s=202003rsa:i=1]; PREVIOUSLY_DELIVERED(0.00)[development@lists.ipfire.org]; FROM_HAS_DN(0.00)[] To prevent possible hammering of the Zabbix server in busy environments, instead of sending alerts immediatly when they arrive, we will send them in bulk every 1 second. For that we need to know what alerts where not yet sent to Zabbix. This will be tracked in this new column zabbix_pending in the alerts table. Signed-off-by: Robin Roevens --- src/suricata-reporter.in | 23 +++++++++++++++++++++++ 1 file changed, 23 insertions(+) diff --git a/src/suricata-reporter.in b/src/suricata-reporter.in index f9da7b4..83e4e97 100644 --- a/src/suricata-reporter.in +++ b/src/suricata-reporter.in @@ -134,6 +134,27 @@ class Reporter(object): return self.zabbix_sender = AsyncSender(server=zabbix_server_host, port=zabbix_server_port) + def _ensure_zabbix_pending_column(self, db): + """ + Ensures the database schema always has the zabbix_pending column and index. + If they are missing, the database is migrated to add them. + """ + cursor = db.execute("PRAGMA table_info(alerts)") + columns = {row[1] for row in cursor.fetchall()} + + if "zabbix_pending" not in columns: + db.execute("ALTER TABLE alerts ADD COLUMN zabbix_pending INTEGER NOT NULL DEFAULT 0") + db.commit() + log.debug("Database: Added zabbix_pending column to alerts table.") + + cursor = db.execute("PRAGMA index_list(alerts)") + indexes = {row[1] for row in cursor.fetchall()} + + if "alerts_zabbix_pending" not in indexes: + db.execute("CREATE INDEX IF NOT EXISTS alerts_zabbix_pending ON alerts(zabbix_pending)") + db.commit() + log.debug("Database: Added alerts_zabbix_pending index on alerts table column zabbix_pending.") + def _open_database(self): """ Opens the database @@ -165,6 +186,8 @@ class Reporter(object): CREATE INDEX IF NOT EXISTS alerts_timestamp ON alerts(timestamp); """) + self._ensure_zabbix_pending_column(db) + return db @property