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

[Robin Roevens <robin.roevens@disroot.org>]

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.