ProxmoxDash/alert_events.go

package main

import (
	"database/sql"
	"time"
)

type AlertEvent struct {
	EventID           int64  `json:"eventId"`
	ID                string `json:"id"`
	Severity          string `json:"severity"`
	Source            string `json:"source"`
	Title             string `json:"title"`
	Message           string `json:"message"`
	FirstSeen         int64  `json:"firstSeen"`
	LastSeen          int64  `json:"lastSeen"`
	ResolvedAt        *int64 `json:"resolvedAt,omitempty"`
	AcknowledgedAt    *int64 `json:"acknowledgedAt,omitempty"`
	Planned           bool   `json:"planned"`
	MaintenanceReason string `json:"maintenanceReason,omitempty"`
}

type RecoveryEvent struct {
	EventID                              int64
	ID, Severity, Source, Title, Message string
}

func (s *Store) SyncAlerts(alerts []Alert) ([]Alert, error) {
	return s.SyncAlertsWithMaintenance(alerts, nil, "")
}

func (s *Store) SyncAlertsWithMaintenance(alerts, plannedAlerts []Alert, maintenanceReason string) ([]Alert, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return alerts, err
	}
	defer tx.Rollback()
	now := time.Now().Unix()
	rows, err := tx.Query(`SELECT id,alert_key,planned FROM alert_events WHERE resolved_at IS NULL`)
	if err != nil {
		return alerts, err
	}
	active := map[string]int64{}
	for rows.Next() {
		var id int64
		var key string
		var planned bool
		if err := rows.Scan(&id, &key, &planned); err != nil {
			rows.Close()
			return alerts, err
		}
		active[alertEventKey(key, planned)] = id
	}
	rows.Close()
	seen := map[string]bool{}
	syncGroup := func(group []Alert, planned bool) error {
		for _, alert := range group {
			key := alertEventKey(alert.ID, planned)
			seen[key] = true
			reason := ""
			if planned {
				reason = maintenanceReason
			}
			if id, ok := active[key]; ok {
				_, err = tx.Exec(`UPDATE alert_events SET severity=?,source=?,title=?,message=?,last_seen=?,maintenance_reason=? WHERE id=?`, alert.Severity, alert.Source, alert.Title, alert.Message, now, reason, id)
			} else {
				result, insertErr := tx.Exec(`INSERT INTO alert_events(alert_key,severity,source,title,message,first_seen,last_seen,planned,maintenance_reason) VALUES(?,?,?,?,?,?,?,?,?)`, alert.ID, alert.Severity, alert.Source, alert.Title, alert.Message, now, now, planned, reason)
				if insertErr != nil {
					return insertErr
				}
				active[key], _ = result.LastInsertId()
			}
			if err != nil {
				return err
			}
		}
		return nil
	}
	if err = syncGroup(alerts, false); err != nil {
		return alerts, err
	}
	if err = syncGroup(plannedAlerts, true); err != nil {
		return alerts, err
	}
	for key, id := range active {
		if !seen[key] {
			if _, err = tx.Exec(`UPDATE alert_events SET resolved_at=? WHERE id=?`, now, id); err != nil {
				return alerts, err
			}
		}
	}
	if _, err = tx.Exec(`DELETE FROM alert_events WHERE (resolved_at IS NOT NULL OR acknowledged_at IS NOT NULL) AND last_seen < ?`, now-int64(30*24*time.Hour/time.Second)); err != nil {
		return alerts, err
	}
	if err = tx.Commit(); err != nil {
		return alerts, err
	}
	rows, err = s.db.Query(`SELECT id,alert_key,severity,source,title,message,first_seen,last_seen FROM alert_events WHERE resolved_at IS NULL AND acknowledged_at IS NULL AND planned=0 ORDER BY CASE severity WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,first_seen DESC`)
	if err != nil {
		return alerts, err
	}
	defer rows.Close()
	var visible []Alert
	for rows.Next() {
		var a Alert
		if err = rows.Scan(&a.EventID, &a.ID, &a.Severity, &a.Source, &a.Title, &a.Message, &a.FirstSeen, &a.LastSeen); err != nil {
			return alerts, err
		}
		visible = append(visible, a)
	}
	return visible, rows.Err()
}

func alertEventKey(id string, planned bool) string {
	if planned {
		return id + "\x00planned"
	}
	return id + "\x00active"
}

func (s *Store) AcknowledgeAlert(eventID int64) error {
	_, err := s.db.Exec(`UPDATE alert_events SET acknowledged_at=? WHERE id=? AND acknowledged_at IS NULL`, time.Now().Unix(), eventID)
	return err
}

func (s *Store) AlertHistory(limit int) ([]AlertEvent, error) {
	return s.AlertHistoryBetween(limit, 0, 0)
}

func (s *Store) AlertHistoryBetween(limit int, from, to int64) ([]AlertEvent, error) {
	if limit < 1 || limit > 500 {
		limit = 200
	}
	query := `SELECT id,alert_key,severity,source,title,message,first_seen,last_seen,resolved_at,acknowledged_at,planned,maintenance_reason FROM alert_events`
	args := []any{}
	if from > 0 && to > from {
		query += ` WHERE first_seen>=? AND first_seen<?`
		args = append(args, from, to)
	}
	query += ` ORDER BY first_seen DESC LIMIT ?`
	args = append(args, limit)
	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var result []AlertEvent
	for rows.Next() {
		var e AlertEvent
		var resolved, acked sql.NullInt64
		if err = rows.Scan(&e.EventID, &e.ID, &e.Severity, &e.Source, &e.Title, &e.Message, &e.FirstSeen, &e.LastSeen, &resolved, &acked, &e.Planned, &e.MaintenanceReason); err != nil {
			return nil, err
		}
		if resolved.Valid {
			e.ResolvedAt = &resolved.Int64
		}
		if acked.Valid {
			e.AcknowledgedAt = &acked.Int64
		}
		result = append(result, e)
	}
	return result, rows.Err()
}

func (s *Store) PendingEmailAlerts() ([]Alert, error) {
	rows, err := s.db.Query(`SELECT id,alert_key,severity,source,title,message FROM alert_events WHERE resolved_at IS NULL AND acknowledged_at IS NULL AND email_sent_at IS NULL AND planned=0 ORDER BY first_seen`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var result []Alert
	for rows.Next() {
		var a Alert
		if err = rows.Scan(&a.EventID, &a.ID, &a.Severity, &a.Source, &a.Title, &a.Message); err != nil {
			return nil, err
		}
		result = append(result, a)
	}
	return result, rows.Err()
}

func (s *Store) MarkAlertsEmailed(alerts []Alert) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	now := time.Now().Unix()
	for _, a := range alerts {
		if _, err = tx.Exec(`UPDATE alert_events SET email_sent_at=? WHERE id=?`, now, a.EventID); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *Store) PendingImmediateAlerts() ([]Alert, error) {
	rows, err := s.db.Query(`SELECT id,alert_key,severity,source,title,message FROM alert_events WHERE resolved_at IS NULL AND acknowledged_at IS NULL AND email_sent_at IS NULL AND planned=0 AND (severity='critical' OR alert_key='ups-battery') ORDER BY first_seen`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Alert
	for rows.Next() {
		var a Alert
		if err = rows.Scan(&a.EventID, &a.ID, &a.Severity, &a.Source, &a.Title, &a.Message); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}
func (s *Store) PendingRecoveries() ([]RecoveryEvent, error) {
	rows, err := s.db.Query(`SELECT id,alert_key,severity,source,title,message FROM alert_events WHERE resolved_at IS NOT NULL AND email_sent_at IS NOT NULL AND recovery_sent_at IS NULL AND planned=0 ORDER BY resolved_at`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []RecoveryEvent
	for rows.Next() {
		var v RecoveryEvent
		if err = rows.Scan(&v.EventID, &v.ID, &v.Severity, &v.Source, &v.Title, &v.Message); err != nil {
			return nil, err
		}
		out = append(out, v)
	}
	return out, rows.Err()
}
func (s *Store) MarkRecoveriesEmailed(items []RecoveryEvent) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	now := time.Now().Unix()
	for _, v := range items {
		if _, err = tx.Exec(`UPDATE alert_events SET recovery_sent_at=? WHERE id=?`, now, v.EventID); err != nil {
			return err
		}
	}
	return tx.Commit()
}