Getting SQL Agent job failures into your monitoring tool through the Windows event log

Email notifications rely on Database Mail, an SMTP relay and someone reading a mailbox. The Windows Application event log is already collected by most monitoring agents (SCOM, Azure Monitor Agent, Splunk, Datadog, Zabbix). If every job failure lands there, your monitoring can raise an incident with no extra plumbing.

#Step 1: make every job write to the event log on failure

Each job has a notification level for the event log:

Older or scripted jobs are often set to 0. Find and fix them:

SELECT name, notify_level_eventlog FROM msdb.dbo.sysjobs WHERE notify_level_eventlog NOT IN (2, 3);

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'EXEC msdb.dbo.sp_update_job @job_name = N' + QUOTENAME(name, '''') + N', @notify_level_eventlog = 2;' + CHAR(10)
FROM msdb.dbo.sysjobs WHERE notify_level_eventlog = 0;
PRINT @sql;
-- EXEC sys.sp_executesql @sql;

In SSMS: Job Properties → Notifications → Write to the Windows Application event log → When the job fails.

#Step 2: check what the event looks like

A failed job writes an Application log entry from the SQL Server Agent source. On a default instance the provider is SQLSERVERAGENT; on a named instance it is SQLAgent$INSTANCENAME. The event ID is commonly 208, and the message names the job, the outcome and the last step that ran.

Get-WinEvent -FilterHashtable @{ LogName = 'Application'; ProviderName = 'SQLSERVERAGENT'; StartTime = (Get-Date).AddDays(-1) } |
    Where-Object Id -eq 208 |
    Select-Object TimeCreated, Id, LevelDisplayName, Message | Format-List

Confirm the provider name and event ID on your own server before building monitoring rules. Trigger a test failure and read the entry.

#Step 3: write custom events from a job step

Sometimes the job succeeds but the result is wrong: zero rows loaded, a file missing, a reconciliation difference. Write your own event so monitoring sees it.

-- warning-level entry in the Application log, from inside a T-SQL step
DECLARE @rows int = (SELECT COUNT(*) FROM stage.gl_lines WHERE load_date = CAST(GETDATE() AS date));
IF @rows = 0
BEGIN
    DECLARE @msg nvarchar(2048) = N'Finance ETL: 0 rows loaded into stage.gl_lines for today.';
    RAISERROR(@msg, 10, 1) WITH LOG;                        -- writes to SQL error log + Application log
    EXEC master.dbo.xp_logevent 60001, @msg, WARNING;        -- explicit level: INFORMATIONAL / WARNING / ERROR
END

• RAISERROR ... WITH LOG needs ALTER TRACE or sysadmin. Severity 19+ always needs sysadmin.
• xp_logevent needs a user-defined message ID of 50000 or higher and lets you choose the level. It only logs; add a THROW afterwards if the step should also fail.
• Pick a consistent message prefix (for example Finance ETL:) so monitoring rules can match it.

From a PowerShell step you can write with the SQL Agent source, or your own registered source:

# one-time, as admin: New-EventLog -LogName Application -Source 'AxiomJobs'
Write-EventLog -LogName Application -Source 'AxiomJobs' -EventId 9001 -EntryType Error -Message 'Nightly export: file not found'

#Step 4: forward to your monitoring platform

Example XPath for a data collection rule:

Application!*[System[Provider[@Name='SQLSERVERAGENT'] and (EventID=208)]]

Example KQL alert query:

Event
| where EventLog == "Application" and Source startswith "SQL" and EventID == 208
| where RenderedDescription has "failed"
| project TimeGenerated, Computer, RenderedDescription

#Things to watch

• The Application log has a size limit. On busy servers, raise it (for example to 100 MB) so events aren't overwritten before they're collected.
• If a job retries and then succeeds, only the final outcome is logged at job level.
• Event text is in the server's language. Match on source and event ID, not just English words.
• Event-log notification works even if Database Mail is broken. Use both.

Tip

Test the full path once a quarter. Fail a test job, then check that the incident appears in your monitoring tool, not just in Event Viewer.