Sccm – Get email notification when advertisement failure exceed a threshold

Recently, a client asked me to provide a way to monitor the status of advertisements based on a threshold and email him if the threshold is exceeded. Here’s a solution that allowed me to meet his request. So basicly, I’ve Create a report, which containt all the advertisement failure information, then create a weekly SQL Job, which will send a email if the failure count exceed the threshold. Also, if you want to be able to get email motification, you need to configure SQL Database Mail profile.

– http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

1- First, create a reports for all the advertisements failures during the last 7 (SQLQuery) , also note the report ID.

2- According to your SCCM infrastructure,  you have to create the Sql job from your Central Site database server and the following steps assume you have logged onto the Central Site database server and have opened SQL Server Management Studio from the SQL Management studio of your sccm site database server. Then;

– Under ‘Select a Page’ Click the Steps page, and then click New.

– In the New Job Step dialog, Click the NEW button, give a appropriate name, ex; MonitoringAdvertisementFailure and  in the Database dropdown list, select the sccm database

– In the Command box, paste the T-SQL command below

– Also, you can Click Parse to check your syntax.

T-SQL;

******************************************************************************
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’ and DATEDIFF(Day, LastStatusTime, Getdate()) <= 7)
Set @yesCom = (select count(*) as clients
from v_ClientAdvertisementStatus
where LastStateName =  ‘Succeeded’ and DATEDIFF(Day, LastStatusTime, Getdate()) <= 7 )Set @percent = case When @noCom = 0 Then 0 Else ((@noCom/@yesCom)*100) End

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)’

— Send Email
EXEC msdb.dbo.sp_send_dbmail
@recipients = ’email@address.com’,
@body =  @bodytext ,
@subject = @@MESSAGE ;

END

******************************************************************************
– Under “Select a Page” choose Schedules and enter the appropriate schedule (Must match the datediff value in the t-sql script).
Then voila, wait to see if you’ll get some email like this (Note, that if you want to test this procedure, you can configure the threshold with a value of 0 then  start the SQL job manually);
Finally, in the next part I’ll show your how I’ve used this procedure to get email notification based on client health information ,such as Hardware and Software Scan, Software Update Scan and more.
reference;
Advertisements

One thought on “Sccm – Get email notification when advertisement failure exceed a threshold

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s