Email Job Activity Summary

Question

Post reply

Email Job Activity Summary

Fox87

Default port

Points: 1424
More actions
November 4, 2010 at 3:28 am

#231080

Hi All,
I configured Database mail and the account work. I added myself as an operator and receive mails from SQL. I added notifications on a individual job by going to the Properties - Notifications and completed the email details and I receive the mail when the job complete.
Can you perhaps help me so that I can get a summary of all the jobs completed on a server every 4-6 hours? It will be much easier to see how many jobs completed with success or failure than getting a mail each time a job completes.
I want to do this on SQL 2005 & SQL 2008 Servers.
Thank you in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Try this:

USE msdb;

GO

-- Create a view to transform sqlAgent crappy dates into datetime columns

CREATE VIEW dba_JobHistory

AS

WITH JobHistory AS (

SELECT SUBSTRING(J.name, 1, 35) Job_Name, -- Shortened to fit the email

CAST(STUFF(STUFF(CAST(JH.run_date AS char(8)), 7, 0, '-'), 5, 0, '-')

+ ' ' + STUFF(STUFF(RIGHT('000000' + CAST(JH.run_time AS VARCHAR(6)),

6), 5, 0, ':'), 3, 0, ':') + '.000' AS datetime) AS Run_Date,

CASE WHEN JH.run_status != 1 THEN 'FAILED' ELSE 'SUCCEEDED' END AS Outcome

FROM msdb..sysjobhistory AS JH

JOIN msdb..sysjobs AS J

ON JH.job_id = J.job_id

WHERE JH.step_id = 0

)

SELECT *

FROM JobHistory

WHERE Run_Date BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE()

GO

-- Send the email alert

EXEC msdb.dbo.sp_send_dbmail

@profile_name = @your_dbmail_profile,

@recipients = @your_email,

@subject = 'Failed Job(s) Notification',

@body_format = 'TEXT',

@query = 'SET NOCOUNT ON; SELECT * FROM msdb.dbo.dba_JobHistory'

You can change

WHERE Run_Date BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE() to filter the date range according to your needs.

Once you're set up, you can schedule the sp_send_dbmail call in a job.

Hope this helps

Gianluca

-- Gianluca Sartori

Fox87 Default port Points: 1424 More actions · Answer 2

Hi Gianluca,

Thank you for the help.

It really helps and give a good result set.

I did modify the code a bit. Do you have any other suggestions to move the header so that everything is in line? I just want to move the "Rundate" to be in the center of the date so everything can be aligned.

USE msdb;

GO

Alter VIEW dba_JobHistory

AS

WITH JobHistory AS (

SELECT CAST(J.name AS VARCHAR(50)) AS Job_Name,

CAST(STUFF(STUFF(CAST(JH.run_date AS varchar(25)), 7, 0, '-'), 5, 0, '-')

+ ' ' + STUFF(STUFF(RIGHT('000000' + CAST(JH.run_time AS VARCHAR(6)),

6), 5, 0, ':'), 3, 0, ':') + '.000' AS datetime) AS Run_Date,

CASE WHEN JH.run_status != 1 THEN 'FAILED' ELSE 'SUCCEEDED' END AS 'Outcome', ltrim(rtrim(@@Servername)) AS 'Servername'

FROM msdb..sysjobhistory AS JH

JOIN msdb..sysjobs AS J

ON JH.job_id = J.job_id

WHERE JH.step_id = 0

)

SELECT *

FROM JobHistory

WHERE Run_Date BETWEEN DATEADD(day, -1, GETDATE()) AND GETDATE()

GO

-- Send the email alert

EXEC msdb.dbo.sp_send_dbmail

@profile_name ='DBmailprofile',

@recipients = 'Email@address',

@subject = 'Failed Job(s) Notification',

@body_format = 'TEXT',

@query = 'SET NOCOUNT ON; SELECT * FROM msdb.dbo.dba_JobHistory',

@query_result_separator = '',

@query_result_width = 500,

@query_result_header = 1

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Fox87 (11/4/2010)
Do you have any other suggestions to move the header so that everything is in line? I just want to move the "Rundate" to be in the center of the date so everything can be aligned.

You can use aliases for the columns to add leading spaces, as you did with Outcome. Instead of using ' to quote the name, you can use square brackets (ex: [ Date]).