Re: [PATCH 2/5] Add database column zabbix_pending in alerts table.

[Michael Tremer <michael.tremer@ipfire.org>]

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=suricata-reporter.git;a=blob;f=src/suricata-reporter.in;h=28b55bc39616f1af2769fb7935a972a1288f69bd;hb=HEAD#l114 and the database will create the index if it isn’t 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 <robin.roevens@disroot.org> wrote:
> 
> 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 <robin.roevens@disroot.org>
> ---
> 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
> -- 
> 2.54.0
> 
> 
> -- 
> Dit bericht is gescanned op virussen en andere gevaarlijke
> inhoud door MailScanner en lijkt schoon te zijn.
> 
>