Your company's ad could live here and reach over 50,000 people a month!

Share This Post

Get email notification when advertisement failure exceed a threshold

Recently, a client asked me to provide a way to receive email notifications when advertisement failures have exceeded a certain threshold. So here’s the basic step I’ve followed to meet that need; Create a report, which containt all the failures informations, create a weekly SQL agent job, which will send a email if the failure count exceed the threshold. Note that, if you want to be able to get email notification, you need to configure SQL Database Mail profile.



SQL Queryselect
CASE WHEN SYS.Active0 = 1 THEN ‘Yes’ ELSE ‘No’ END as ‘Active’,
ld.size0 as ‘Disk Size (C:)’,
ld.FreeSpace0*100/ld.Size0  as ‘% Free Disk Space (C:)’

from v_ClientAdvertisementStatus sta
left join v_R_System sys on sta.ResourceID = sys.ResourceID
left Join v_GS_LOGICAL_DISK ld on ld.ResourceID = sys.ResourceID

where LastStateName = ‘failed’ and lastState != 0 and Datediff(day, LastStatusTime,getdate()) > 7 and  ld.DeviceID0 = ‘C:’

Order By sys.Netbios_name0

SQL Job;
Declare @noCom as decimal

Declare @yesCom as decimal
Declare @percent decimal(18,2)
Declare @Threshold as int
Declare @bodytext as varchar(MAX)
Declare @@MESSAGE varchar(200)Set @@MESSAGE = ‘Software Distribution failure has exceed the threshold’
Set @noCom = (select count(*) as clients
from v_ClientAdvertisementStatus
where LastStateName =  ‘Failed’ )
Set @yesCom = (select count(*) as clients
from v_ClientAdvertisementStatus
where LastStateName =  ‘Succeeded’ )
Set @percent = ((@noCom/@yesCom)*100)

Set @Threshold = 5  — enter the % of failure accepted

If @percent >= @Threshold


Set @bodytext = ‘Software distribution failure has exceed the threshold. ‘ +CAST (@percent as Varchar(100)) +
‘ % of advertisement has failed in the last week. Click on the link for detail http://isi-ccm01/SMSReporting_001/Report.asp?ReportID=(REPORTID)’

— Create EventID
EXEC xp_logevent 60020, @@MESSAGE, Warning

–Send Email
EXEC msdb.dbo.sp_send_dbmail
@recipients = ‘’,
@body =  @bodytext ,
@subject = @@MESSAGE ;


Also,if needed you can modify the query to monitor by advertisement ID or use this procedure to monitor scan failure, etc


Share This Post

Leave a Reply