Agent Fail Board

Status: Reviewed (spike v1)
Path: Spike #3 (after #1 Inventory, #2 Backup heat map; next #7 Alert evaluate + ack)
Stack: PowerShell → SQL/stored procs → Blazor Server
Builds on: SQL-04 SQL Agent jobs & schedules; requires dbo.SqlInstance from SQL-SPIKE-01
Goal: Census Agent jobs on inventoried instances; show failed / disabled-critical / long-running on a Blazor fail board.

In scope (MVP)

Per active instance: job list (enabled, category, owner) + recent history outcomes
Criticality tag (override table + simple name/category heuristics)
Status flags: LastOutcome, LastSuccessAt, IsDisabledCritical, IsLongRunning, IsOverdue
Blazor fail board (failed 24h, disabled critical, long-running)
Reuse CollectRun from spike #1 (CollectorName = N'AgentJobs')

Out of scope (later)

Start/stop jobs from UI
Full schedule parser UI / next-run calendar
Overlap matrix across hosts
Alert evaluate (spike #7 wires these flags)

A. Database objects

CREATE TABLE dbo.SqlAgentJobCriticality (
  CriticalityCode VARCHAR(16) NOT NULL PRIMARY KEY, -- Critical/High/Standard/Ignore
  SortOrder INT NOT NULL
);

CREATE TABLE dbo.SqlAgentJobCatalog (
  -- Optional overrides: map job name pattern or exact name → criticality
  CatalogId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  JobNamePattern NVARCHAR(256) NOT NULL,  -- exact or prefix; MVP: exact match first
  CriticalityCode VARCHAR(16) NOT NULL REFERENCES dbo.SqlAgentJobCriticality(CriticalityCode),
  Notes NVARCHAR(256) NULL
);

CREATE TABLE dbo.SqlAgentJob (
  SqlAgentJobId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  JobId UNIQUEIDENTIFIER NOT NULL,
  JobName NVARCHAR(256) NOT NULL,
  IsEnabled BIT NOT NULL,
  CategoryName NVARCHAR(128) NULL,
  OwnerName NVARCHAR(256) NULL,
  CriticalityCode VARCHAR(16) NOT NULL,
  DateModified DATETIME2(0) NULL,
  LastSeenAt DATETIME2(0) NOT NULL,
  CONSTRAINT UQ_SqlAgentJob UNIQUE (SqlInstanceId, JobId)
);

CREATE TABLE dbo.SqlAgentJobStatus (
  SqlAgentJobId INT NOT NULL PRIMARY KEY REFERENCES dbo.SqlAgentJob(SqlAgentJobId),
  LastRunAt DATETIME2(0) NULL,
  LastOutcome TINYINT NULL,     -- 0=fail 1=succeed 2=retry 3=cancel 5=unknown (msdb style)
  LastRunDurationSec INT NULL,
  LastSuccessAt DATETIME2(0) NULL,
  LastMessage NVARCHAR(1024) NULL,
  BaselineDurationSec INT NULL, -- simple rolling avg MVP
  IsRunning BIT NOT NULL CONSTRAINT DF_Job_IsRunning DEFAULT (0),
  IsLongRunning BIT NOT NULL CONSTRAINT DF_Job_IsLongRunning DEFAULT (0),
  IsDisabledCritical BIT NOT NULL CONSTRAINT DF_Job_IsDisabledCritical DEFAULT (0),
  IsFailedRecent BIT NOT NULL CONSTRAINT DF_Job_IsFailedRecent DEFAULT (0),
  EvaluatedAt DATETIME2(0) NOT NULL
);

Criticality heuristics (MVP if no catalog hit):

Name/category contains Backup / Log Backup / Database Backup → Critical
Contains Index / Optimize / Maintenance / CheckDB / Integrity → High
Else Standard

Long-running rule: IsRunning = 1 AND BaselineDurationSec known AND running longer than max(2× baseline, baseline + 1800) → IsLongRunning = 1 (if no baseline yet: duration > 3600s → flag).

Failed recent: last outcome failed/canceled within 24h → IsFailedRecent = 1.

Disabled critical: IsEnabled = 0 AND Criticality = Critical → IsDisabledCritical = 1.

Procs:

CREATE PROC dbo.usp_SqlAgentJob_Upsert
  @SqlInstanceId INT, @JobId UNIQUEIDENTIFIER, @JobName NVARCHAR(256),
  @IsEnabled BIT, @CategoryName NVARCHAR(128) = NULL, @OwnerName NVARCHAR(256) = NULL,
  @DateModified DATETIME2(0) = NULL AS ...  -- sets CriticalityCode via catalog/heuristic

CREATE PROC dbo.usp_SqlAgentJobStatus_Apply
  @SqlAgentJobId INT,
  @LastRunAt DATETIME2(0) = NULL,
  @LastOutcome TINYINT = NULL,
  @LastRunDurationSec INT = NULL,
  @LastSuccessAt DATETIME2(0) = NULL,
  @LastMessage NVARCHAR(1024) = NULL,
  @IsRunning BIT = 0,
  @RunningForSec INT = NULL
AS ...  -- updates baseline lightly; sets flags

CREATE PROC dbo.usp_SqlAgent_GetFailBoard
  @Hours INT = 24,
  @CriticalOnly BIT = 0
AS
BEGIN
  SELECT i.HostName, i.InstanceName, j.JobName, j.IsEnabled, j.CategoryName, j.CriticalityCode,
         s.LastRunAt, s.LastOutcome, s.LastRunDurationSec, s.LastSuccessAt, s.LastMessage,
         s.IsRunning, s.IsLongRunning, s.IsDisabledCritical, s.IsFailedRecent, s.EvaluatedAt,
         j.SqlAgentJobId, i.SqlInstanceId
  FROM dbo.SqlAgentJobStatus s
  JOIN dbo.SqlAgentJob j ON j.SqlAgentJobId = s.SqlAgentJobId
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = j.SqlInstanceId
  WHERE i.IsActive = 1
    AND (s.IsFailedRecent = 1 OR s.IsDisabledCritical = 1 OR s.IsLongRunning = 1)
    AND (@CriticalOnly = 0 OR j.CriticalityCode = 'Critical')
  ORDER BY CASE j.CriticalityCode WHEN 'Critical' THEN 0 WHEN 'High' THEN 1 ELSE 2 END,
           s.IsFailedRecent DESC, i.HostName, j.JobName;
END;

CREATE PROC dbo.usp_SqlAgent_GetJobDetail @SqlAgentJobId INT AS ...

B. PowerShell collector (shape)

Inputs: warehouse connection; active SqlInstance rows.

Per instance (try/catch; continue):

If Agent service not running → upsert a synthetic status note optional; still try msdb if online
Job list from msdb.dbo.sysjobs + categories + sysjobservers
Last run: outer apply latest sysjobhistory step 0 (or max instance_id) for outcome/duration/message
Last success: max history where run_status = 1
Running: sysjobactivity where start_execution_date IS NOT NULL AND stop_execution_date IS NULL
Upsert job + apply status

Script: Collect-SqlAgentJobs.ps1
Rights: read msdb job tables; warehouse execute upsert/apply.

C. Blazor Server (MVP UI)

Page: /jobs or /sql/agent

Service: usp_SqlAgent_GetFailBoard, GetJobDetail

Filters: Critical only; Failed only; instance; search name

Actions v1: none destructive — link to SQL-04 runbook; copy job name

App pool: read procs only

D. Acceptance checks

[ ] Seed criticality codes + 1–2 catalog overrides (e.g. exact backup job name)
[ ] Collector pulls jobs from lab/real instance
[ ] Force-disable a Critical-tagged job → appears IsDisabledCritical
[ ] Failed job in last 24h → IsFailedRecent on board
[ ] Long-running: simulate or wait — flag sets when rule met
[ ] Second collect updates without duplicate JobId rows
[ ] Board empty when healthy (no false spam from Ignore jobs)

E. Repo layout

/sql/agent/001_tables.sql
/sql/agent/002_procs.sql
/sql/agent/003_seed_criticality.sql
/ps/Agent/Collect-SqlAgentJobs.ps1
/src/.../Pages/Agent/FailBoard.razor
/src/.../Services/AgentJobService.cs

Tie-in to spike #2

Backup heat map Red cells often pair with Critical backup jobs on this board — same instance filter helps operators jump between /backups and /jobs.

Next spike (wait for go-ahead)

#7 Alert evaluate + ack — normalize flags from #1–#3 (and later) into Alert + Blazor ack (SQL-10).

SQL Dude — SQL-SPIKE-03 Agent Fail Board v1