EIM 4.3 - System Login - Creating Monitor Alarms

[BarryMclellan72678_2]

Hello,

We have several alarms configured under the 'SA' login for processes going offline.

You can easily check each process to alarm if one of them is not running, however when sending an email notification, I can't figure out how you could email which process was actually offline.

I ended up making several alarm job's just so that my email notifications would be more specific.

Does anyone know if it is possible to create a custom MACRO that could query the database to see what is offline and then return that in an email? I know you can do this via an alarm workflow, but that isn't an option at the system level.

Thanks!

Barry

[Goran Selthofer · Accepted solution]

Hi Barry,

You can use SQL2005 db mail feature.

You can enable/configure the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail and clicking the Configuration (Management -> Database Mail). Just configure it simple, you would need smtp account if your smtp uses auth. then associate to the profile.

Here is the sample job I've just created for this purpose to check DX process status and if is anything else than 3 (which is running) to send email to one recepient - user1@cim43.lab in my case (you need to change that of course - I have indicated below what you might need to change as well).

Also, this is basically done via GUI if easier for you in SQL Server Agent --> Jobs and create 2 step job. One is checking state and another is sending email if first one is successful.  But you can do vice-versa logic and send email only if job fails if you specify another condition (like state=3, instead of state!=3). Also, you can do that inside one step with IF/THEN logic and send emails for each condition with specific subject per process so to know which one failed) hence you can check status of all jobs with that and send only specific emails at the end.

I have shown here very basic job for just checking if DX is anything than running and send email if so. However, this will produce many job failed outcomes on first step as mostly your process will be in running state and frequency of job will dictate how many times job will fail. So, for production you might wanna change this to another condition as mentioned above.

This is an export of the job but below are pictures which will give you better hints maybe...

USE [msdb]

GO

/****** Object:  Job [process_down_email_notification_goran]    Script Date: 01/04/2011 20:02:53 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/04/2011 20:02:53 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'process_down_email_notification_goran',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'monitor service and send email alert',

@category_name=N'[Uncategorized (Local)]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [check status]    Script Date: 01/04/2011 20:02:53 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'check status',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'select * from EGPL_DSM_PROCESS where process_name like ''%dx%'' and state!=3',

@database_name=N'eGMasterDB', <<<<---- CHANGE THIS TO YOUR MASTER DB IF DIFFERS!!!

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [send email]    Script Date: 01/04/2011 20:02:53 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email',

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'EXEC msdb.dbo.sp_send_dbmail

@recipients = ''user1@cim43.lab'', <<<<---- CHANGE THIS TO EMAIL RECEPIENT!!!

@body = ''DX Process DOWN'', <<<<---- CHANGE THIS TO YOUR EMAIL BODY!!!

@subject = ''DX Process DOWN'' ; <<<<---- CHANGE THIS TO YOUR EMAIL SUBJECT!!!

GO',

@database_name=N'eGMasterDB', <<<<---- CHANGE THIS TO YOUR MASTER DB IF DIFFERS!!!

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'goran_process_check',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=4,

@freq_subday_interval=5,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20110104,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Screenshots:

Database email feature in SQL2005:

DO NOT FORGET TO CLICK ON SEND TEST EMAIL ONCE YOU CONFIGURE IT (to be sure if it is working, you need to receive that email first before you continue with jobs below)

check accounts and profiles:

profiles:

create a new job:

then schedule it according to your preference - here is 5 min example:

again, this is simple job and if you want you can combine both queries inside the same step and create one step per process and chose how to continue after each step. in example, if step 1 fails you can continue on step 2 or you can quit (that would depend of your conditions). So, for instance, your step 1 is to check DX process and send email with specific subject if that process is not running inside one step and then continue to next step, check another process and send again specific email if that one is not running and so on...

so in such case step 1 for DX process can be:

if

exists (select * from EGPL_DSM_PROCESS where STATE!=3 and process_name  like'%dx%')

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'user1@cim43.lab',

@body = 'DX Process DOWN',

@subject = 'DX Process DOWN' ;

GO

then select for both outcomes of job result to continue to next step

and then step 2 for RX process can be like:

if

exists (select * from EGPL_DSM_PROCESS where STATE!=3 and process_name  like'%rx%')

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'user1@cim43.lab',

@body = 'RX Process DOWN',

@subject = 'RX Process DOWN' ;

GO

and so on for all processes you need to check...

Please let me know if this answers your question and please rate if you find this valuable...

thanks,

Goran

Cisco TAC

[BarryMclellan72678_2]

This is fantastic, thanks!!

Barry