Alert Evaluate + Ack

Status: Reviewed (spike v1)
Path: Spike #7 — last on approved path 1 → 2 → 3 → 7
Stack: PowerShell (optional notify later) → SQL evaluate/ack procs → Blazor Server
Builds on: SQL-10 Alerting & runbooks; signals from SQL-SPIKE-01 (stale inventory), #2 (backup RAG R), #3 (job fail flags)
Goal: Normalize open findings into Alert rows, auto-close when cleared, Blazor board with Ack — no auto-remediation.

In scope (MVP)

AlertRule + Alert + AlertEvent tables
usp_Alert_Evaluate reads flags from spikes 1–3 and upserts/closes alerts
Dedup by (AlertType + EntityKey)
Blazor open-alert board: severity, age, ack, comment
RunbookUrl on rule pointing at SharePoint SQL-0x / spike docs
Seed rules for: InventoryStale, BackupRed, AgentJobFailed, AgentDisabledCritical, AgentLongRunning

Out of scope (later)

Email/webhook dispatcher (stub interface only)
Spikes 4/5/6/8 signal wiring
Snooze/exception UI (table optional; UI can wait)
Kill/failover/patch actions

A. Database objects

CREATE TABLE dbo.AlertSeverity (
  SeverityCode VARCHAR(16) NOT NULL PRIMARY KEY, -- Critical/High/Medium/Low
  SortOrder INT NOT NULL
);

CREATE TABLE dbo.AlertRule (
  AlertType VARCHAR(64) NOT NULL PRIMARY KEY,
  SeverityCode VARCHAR(16) NOT NULL REFERENCES dbo.AlertSeverity(SeverityCode),
  IsEnabled BIT NOT NULL CONSTRAINT DF_AlertRule_Enabled DEFAULT (1),
  RunbookUrl NVARCHAR(512) NULL,
  Description NVARCHAR(256) NOT NULL
);

CREATE TABLE dbo.Alert (
  AlertId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  AlertType VARCHAR(64) NOT NULL REFERENCES dbo.AlertRule(AlertType),
  EntityKey NVARCHAR(256) NOT NULL,         -- e.g. InstanceId|DbId or InstanceId|JobId
  SqlInstanceId INT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  Title NVARCHAR(256) NOT NULL,
  Detail NVARCHAR(1000) NULL,
  SeverityCode VARCHAR(16) NOT NULL,
  Status VARCHAR(16) NOT NULL,              -- Open / Acked / Closed
  FirstSeenAt DATETIME2(0) NOT NULL,
  LastSeenAt DATETIME2(0) NOT NULL,
  AckedAt DATETIME2(0) NULL,
  AckedBy NVARCHAR(128) NULL,
  AckComment NVARCHAR(512) NULL,
  ClosedAt DATETIME2(0) NULL,
  CONSTRAINT UQ_Alert_OpenKey UNIQUE (AlertType, EntityKey) -- MVP: one row per key; reuse after close via evaluate logic below
);

-- Simpler MVP unique: allow history by not unique; instead use ActiveAlert filtered Status IN ('Open','Acked')
-- Prefer:
CREATE UNIQUE INDEX UX_Alert_Active ON dbo.Alert(AlertType, EntityKey) WHERE Status IN ('Open','Acked');

CREATE TABLE dbo.AlertEvent (
  AlertEventId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  AlertId BIGINT NOT NULL REFERENCES dbo.Alert(AlertId),
  EventAt DATETIME2(0) NOT NULL CONSTRAINT DF_AlertEvent_At DEFAULT SYSUTCDATETIME(),
  EventType VARCHAR(32) NOT NULL,           -- Opened/Seen/Acked/Closed
  Actor NVARCHAR(128) NULL,
  Message NVARCHAR(512) NULL
);

Seed severities + rules (examples):

AlertType	Severity	Source	Runbook
InventoryStale	Medium	SqlInstance.LastSeenAt older than 48h	SQL-01 / SPIKE-01
BackupRed	Critical	SqlDatabaseBackupStatus.RagStatus = R	SQL-02 / SPIKE-02
AgentJobFailed	High	IsFailedRecent = 1 (Critical job → Critical sev optional)	SQL-04 / SPIKE-03
AgentDisabledCritical	Critical	IsDisabledCritical = 1	SPIKE-03
AgentLongRunning	High	IsLongRunning = 1	SPIKE-03

B. Evaluate proc (shape)

CREATE PROC dbo.usp_Alert_Evaluate AS
BEGIN
  SET NOCOUNT ON;
  -- 1) Build #Findings (AlertType, EntityKey, SqlInstanceId, Title, Detail, SeverityCode)
  --    FROM SqlInstance stale, Backup status R, Agent status flags
  -- 2) For each finding: if active alert exists → update LastSeenAt + AlertEvent Seen
  --    else INSERT Alert Status=Open + Event Opened
  -- 3) Active alerts whose key NOT IN #Findings → Status=Closed, ClosedAt=now, Event Closed
END;

EntityKey examples:

Inventory: I:{SqlInstanceId}
Backup: B:{SqlDatabaseId}
Job: J:{SqlAgentJobId}

Schedule: SQL Agent job on warehouse or PS after collectors: EXEC dbo.usp_Alert_Evaluate.

Ack:

CREATE PROC dbo.usp_Alert_Ack
  @AlertId BIGINT,
  @AckedBy NVARCHAR(128),
  @AckComment NVARCHAR(512) = NULL
AS
BEGIN
  UPDATE dbo.Alert
    SET Status = 'Acked', AckedAt = SYSUTCDATETIME(), AckedBy = @AckedBy, AckComment = @AckComment
  WHERE AlertId = @AlertId AND Status = 'Open';
  INSERT dbo.AlertEvent (AlertId, EventType, Actor, Message)
  VALUES (@AlertId, 'Acked', @AckedBy, @AckComment);
END;

CREATE PROC dbo.usp_Alert_GetOpenBoard
  @UnackedOnly BIT = 0,
  @MinSeveritySort INT = 0
AS
BEGIN
  SELECT a.AlertId, a.AlertType, a.EntityKey, a.Title, a.Detail, a.SeverityCode, a.Status,
         a.FirstSeenAt, a.LastSeenAt, a.AckedAt, a.AckedBy, r.RunbookUrl,
         i.HostName, i.InstanceName
  FROM dbo.Alert a
  JOIN dbo.AlertRule r ON r.AlertType = a.AlertType
  JOIN dbo.AlertSeverity sev ON sev.SeverityCode = a.SeverityCode
  LEFT JOIN dbo.SqlInstance i ON i.SqlInstanceId = a.SqlInstanceId
  WHERE a.Status IN ('Open','Acked')
    AND (@UnackedOnly = 0 OR a.Status = 'Open')
  ORDER BY sev.SortOrder, a.FirstSeenAt;
END;

CREATE PROC dbo.usp_Alert_GetDetail @AlertId BIGINT AS ... -- alert + last 50 events

C. PowerShell (optional MVP)

Invoke-SqlAlertEvaluate.ps1 — just runs usp_Alert_Evaluate on warehouse after collect scripts. Notify hook: empty function Send-SqlAlertNotifications commented for later.

D. Blazor Server (MVP UI)

Page: /alerts or /sql/alerts

Service: GetOpenBoard, GetDetail, Ack (pass authenticated user name)

Filters: Unacked only; Critical/High; instance

Actions: Ack (comment modal) — no close button (evaluate closes); optional “Re-evaluate now” if user has rights (calls evaluate proc)

Home badge: count Open (unacked) Critical + High

App pool: execute read + ack procs; evaluate can be Agent-only or separate role

E. Acceptance checks

[ ] Seed severities + 5 rules with RunbookUrl placeholders
[ ] Stale instance → InventoryStale open; refresh LastSeen → closed on next evaluate
[ ] Backup R row → BackupRed open; set G → closed
[ ] Failed critical job → alert open; Ack sets Status=Acked; still open until cleared then Closed
[ ] Dedup: second evaluate updates LastSeenAt, does not duplicate active row
[ ] Board shows runbook link; ack requires user identity
[ ] No write path from UI to job start / backup / security revoke

F. Repo layout

/sql/alert/001_tables.sql
/sql/alert/002_procs.sql
/sql/alert/003_seed_rules.sql
/ps/Alert/Invoke-SqlAlertEvaluate.ps1
/src/.../Pages/Alerts/Index.razor
/src/.../Services/AlertService.cs

Path complete (approved)

Spike	Doc
#1 Inventory MVP	`SQL-SPIKE-01-Inventory-MVP.md`
#2 Backup heat map	`SQL-SPIKE-02-Backup-Heatmap.md`
#3 Agent fail board	`SQL-SPIKE-03-Agent-Fail-Board.md`
#7 Alert evaluate + ack	`SQL-SPIKE-07-Alert-Evaluate-Ack.md`

Optional later (not started): #4 Capacity, #5 Perf triage, #6 Security board, #8 Build out-of-date.

SQL Dude — SQL-SPIKE-07 Alert Evaluate + Ack v1