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.

 

Report;

SQL Queryselect
sys.Netbios_Name0,
CASE WHEN SYS.Active0 = 1 THEN ‘Yes’ ELSE ‘No’ END as ‘Active’,
LastStatusTime,
LastStatusMessageIDName,
LastStateName,
AdvertisementID,
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

Begin

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 = ‘simon.brouillard@arcelormittal.com’,
@body =  @bodytext ,
@subject = @@MESSAGE ;

END

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