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.
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.
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
where LastStateName = ‘Failed’ and DATEDIFF(Day, LastStatusTime, Getdate()) <= 7)
Set @yesCom = (select count(*) as clients
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
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
@recipients = ’email@example.com’,
@body = @bodytext ,
@subject = @@MESSAGE ;