From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mail02.haj.ipfire.org (localhost [IPv6:::1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4hBMdG3zVjz378m for ; Fri, 31 Jul 2026 10:24:50 +0000 (UTC) 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) (Client CN "mail01.haj.ipfire.org", Issuer "YR2" (not verified)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4hBMd2116rz2xJG for ; Fri, 31 Jul 2026 10:24:38 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4hBMcs5zjMz39m; Fri, 31 Jul 2026 10:24:29 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1785493469; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=wqBkKo9EbM9OlBlXJv6aPiIHwzPcctcQL5vrVCuF+Eg=; b=p6dPCtX7uPAmFqguo5AoMLoFKeax4ysjn8TWt8un2bzai+6L5nVP6kTzNPcI47xo8rWUtH 3fKWandk7Fl4sFDA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1785493469; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=wqBkKo9EbM9OlBlXJv6aPiIHwzPcctcQL5vrVCuF+Eg=; b=dAN7gGZkDKeE6BKmSfR3+br23vG/B8lEEMpCScBPEV1pdHV91XwjNZxc9LSO1f3Ya8B1y0 q9d8wcfkPi4OgRFL45WYSQNJT44wPkJEK/RKaIzDYHjTLnuGag2vP3g/OJ1hN3zskHOL+F BbNOlWGTZTONRjYzPJ5Joe1hDLe1ebyDlMKgb0QmM51er8Wrz3JYaCuWdx11UxgGvO5pPw cJLZmbWNCy1kHYrbN3YH2YUZKFxm3dueJJWsoRINGUpH0HXjEnMaRaXxc4d9+++hgwyuoQ pcbcyBSMxAuSWTg/PPZcwA5G/9+eBi9cLrZMB5evO0dw/3KSozG8u81WOAcGpQ== Content-Type: text/plain; charset=utf-8 Precedence: list List-Id: List-Subscribe: , List-Unsubscribe: , List-Post: List-Help: Sender: Mail-Followup-To: Mime-Version: 1.0 Subject: Re: [PATCH 2/5] Add database column zabbix_pending in alerts table. From: Michael Tremer In-Reply-To: <20260730195148.3278295-3-robin.roevens@disroot.org> Date: Fri, 31 Jul 2026 11:24:21 +0100 Cc: development@lists.ipfire.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <20260730195148.3278295-1-robin.roevens@disroot.org> <20260730195148.3278295-3-robin.roevens@disroot.org> To: Robin Roevens Hello, This works, but it is very over-engineered :) You can simply let the database do what it is doing best and not worry = about it in Python. You are almost there with statements like this: CREATE INDEX IF NOT EXISTS alerts_zabbix_pending ON = alerts(zabbix_pending) Add this to the schema in = https://git.ipfire.org/?p=3Dsuricata-reporter.git;a=3Dblob;f=3Dsrc/suricat= a-reporter.in;h=3D28b55bc39616f1af2769fb7935a972a1288f69bd;hb=3DHEAD#l114 = and the database will create the index if it isn=E2=80=99t there. If it = exists, it will simply do nothing. You can do the same thing for the ADD COLUMN statement. > On 30 Jul 2026, at 20:15, Robin Roevens = wrote: >=20 > 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=20 > Zabbix. This will be tracked in this new column zabbix_pending in the = alerts > table. >=20 > Signed-off-by: Robin Roevens > --- > src/suricata-reporter.in | 23 +++++++++++++++++++++++ > 1 file changed, 23 insertions(+) >=20 > 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 =3D AsyncSender(server=3Dzabbix_server_host, = port=3Dzabbix_server_port) >=20 > + 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 =3D db.execute("PRAGMA table_info(alerts)") > + columns =3D {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 =3D db.execute("PRAGMA index_list(alerts)") > + indexes =3D {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); > """) >=20 > + self._ensure_zabbix_pending_column(db) > + > return db >=20 > @property > --=20 > 2.54.0 >=20 >=20 > --=20 > Dit bericht is gescanned op virussen en andere gevaarlijke > inhoud door MailScanner en lijkt schoon te zijn. >=20 >=20