How to set an alert to find deviations from normal behavior when monitoring jobs in a SQL server?


I have SQL server, where are 6 SQL jobs and it's configured to write events to Windows journal every time when job is finished (it does not matter successfully or unsuccessfully).
Normal behavior is that events appears every 5 min for one job, 15 min for another, 7 min for another and etc (duration between events can be with a small deviation).

But sometimes, jobs can halt for a long time, therefore events will not appear.....

I need to setup an alert for such incidents, i.e. find deviation from normal behavior.

alt text

0 Karma


You can try the following which gives duration in minute for last event received for each SQL Job. You can setup alert for duration > SLA or specific JOB not found in the following result i.e. Number of Results < 6.

host=host08 SourceName="SQLAgentHost08" EventCode=208 SQL_JOB_NAME=* | stats count as Count max(_time) as LastReceived by SQL_JOB_NAME | eval LastReceivedTime=strftime(LastReceived,"%Y/%m/%d %H:%M:%S") | eval durInMin=round((now()-LastReceived)/60,1) | fields - LastReceived
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Splunk Lantern | Spotlight on Security: Adoption Motions, War Stories, and More

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...

Tech Talk | One Log to Rule Them All

One log to rule them all: how you can centralize your troubleshooting with Splunk logs We know how important ...