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.


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 @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

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 = ’’,
@body =  @bodytext ,
@subject = @@MESSAGE ;


– 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.

SCCM – Security updates Compliance Overview report V2

Quick post about some of the changes I’ve made to the security updates compliance overview reports; So here’s some of the enhancement that the new reports will allow you

– can now sort the display by number of missing updates.

– add a column to get total of targeted updates (approved updates).

– Removed the Installed updates column.

1- create a report called  Security Updates Detail based on the following query

Report Sql Query 


2- Create a report called Software Updates Compliance Status by Collection and configure the link to the report created in step 1

Report SQL Query


SCCM – Allow users to postpone deployment (updated script)

here’s a updated version of the hta Script. Which will allow you to notify the users in a language other than the sccm client (if you dont have the ICP installed), set the number of times it may postpone the installation, set a timer on the installation if any responds, kill processes if necessary (possible to enforce if needed) and also simplify the interaction with the user… Also, ATL+F4 is now disabled

you only need to modify the strCommand,  processlist = “”  and processnames = “” value to match with your deployement.


First Part ;

SCCM – Software deployment with autoit

In this post I will show you how you can use autoit Script to deploy an application and silently remove all previous version.

First, you’ll need to install AutoIt, create and compile the script, copy the script and the files in the source directory of your program. Next, create the package, program and advertisement in SCCM.

For my part, I created a collection with clients who already have the software installed, create a sub collection of device which is not member of and add queries to filter the device who doesn’t meet the minimum requirement of the software, then finally advertise the sub collection.

So the script will scan the registry (uninstall key’s) to find the following values; DisplayName, DisplayVersion, QuietUninstall, Uninstall and Based on the value of the $Software variable it will uninstall previos version, but be careful, you must be sure that the value will only affect the targeted software, otherwise …

In this example, we will deploy adobe reader 10.1.0, silently uninstall previous version ( tested for version 6.1 and later).

To be successfull you must modify the value of

$Software = “Adobe reader”

$apps1 = “C:\Adobe\fr\AcroRead.msi”

$Mst = “C:\Adobe\fr\AcroRead.mst”

If $DisplayVersion > “10.1.3”

in the autoit script .


In some situation you may want to allow some users to postpone an software installation, ex; if the application need to close some processes and you want to deploy it while users are logged on.

So here’s a simple way to do it, but note that if you must deploy the program with the administrative rights you will also need to allow the users to interact with this program… therefore you can’t use it from a Task sequence, at least not natively.

1- Use a text editor, copy the following script and save it as a HTA file in the same directory as your package Data source. Then Modify the script Sub StartInstall section, to meet your Program Needs. add an appropriate .ico file in the same directory as the Script.

Download Script ; (PromptReboot)

<!– Author Simon Brouillard Fuction Allow user to postpone Installation




<title>App Launcher</title>






















<SCRIPT Language=”VBScript”>

sub DoResize


window.resizeTo 550,150

screenWidth = Document.ParentWindow.Screen.AvailWidth

screenHeight = Document.ParentWindow.Screen.AvailHeight

posLeft = (screenWidth – 550) / 2

posTop = (screenHeight – 150) / 2

‘move to centerscreen

window.moveTo posLeft, posTop

end sub


Sub Run_Script

Select Case opt_script.Value

Case “opt_1”


Case “opt_2”


Case “opt_3”


End Select

End Sub

Dim intLeft

Dim intTop

Sub HideWindow1

intLeft = window.screenLeft

intTop = window.screenTop

window.moveTo -2000,-2000

idTimer = window.setTimeout(“ShowWindow”, 1800000, “VBScript”)

End Sub

Sub HideWindow2

intLeft = window.screenLeft

intTop = window.screenTop

window.moveTo -2000,-2000

idTimer = window.setTimeout(“ShowWindow”, 3600000, “VBScript”)

End Sub

Sub HideWindow3

intLeft = window.screenLeft

intTop = window.screenTop

window.moveTo -2000,-2000

idTimer = window.setTimeout(“ShowWindow”, 14400000, “VBScript”)

End Sub

Sub ShowWindow

window.moveTo intLeft,intTop


End Sub

Sub StartInstall

On Error Resume Next

Set objShell = CreateObject(“Wscript.Shell”)

Dim msiname : msiname = “AcroRead.msi”

Dim mstname : mstname = “AcroRead.mst”

Dim logname : logname = “Acroreadlog.log”

Dim strCommand : strCommand = “Msiexec /qn /i ” & msiname & ” TRANSFORMS=” & mstname & ” ” & “ALLUSERS=TRUE” & ” ” & “DISABLE_CACHE=1″ & ” ” & “REBOOT=ReallySuppress'” & ” /l*v ” & logname

objShell.Run strCommand

Set wshshell = Nothing


End Sub


<body bgcolor=”#F0F0F0″>

<a><img src=”Install.ico” align=”left”></a>

<strong><p align=”left”>Application X will be installed on your PC. If you want to start the installation now, click on INSTALL NOW</p></strong>

<div align=”right”><select id=”opt_script” name=”opt_script” size=”1″>

<option value=”opt_1″>30minutes</option>

<option value=”opt_2″>1 hours</option>

<option value=”opt_3″>4 hours</option>


<button name=”btn_run” id=”btn_run” accessKey=”R” onclick=”vbs:Run_Script”><u>P</u>ostpone</button>

<input type=”button” value=”Install Now” name=”run_button” onClick=”StartInstall”></div>




2- Create a program for your hta script and set these settings.  Maximum Allowed Run time Unknown, Program Can run whether or not a user is logged on, Run with administrative Rights and Allow users to interact with this program

 Also, Note that if necessary you can change the values that I’ve use in the postpone the installation by changing the value “idTimer =…” and <option value=”opt_3″>4 hours</option>

3- Advertise the collection you want to target with your software deployment. For My part, I create a collection with all workstation who already have the application installed. Next, I create a sub collection which will exclude all the member of this newly collection.