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 – Lastlogontimestamp0 has changed in sccm2012

SCCM – lastlogontimestamp0 has changed in sccm2012

Here is an interesting change in the v_R_System view in SCCM 2012.

First the attribute is now named last_logon_timestamp0 rather than lastlogontimestamp0 and more seriously it is now convert to a datetime attribute rather than a bigint…. so it should be much more easy to use in reports…. but why it’s only the case for this attribute? What about the lastpasswordset, lastlogon, etc..?

So here’s a example on how you can use it;

DATEDIFF(day,Last_Logon_Timestamp0,GETDATE()) as ‘Last Logon Timestamp (DAYS)’,
Last_Logon_Timestamp0 as ‘Last Logon Timestamp’
FROM v_R_System

DATEDIFF(day,Last_Logon_Timestamp0,GETDATE()) > 10

Also, if you want, you can get the same result in sccm 2007 with the BigInt attributes, but currently the only way I found is by using a temporary table and convert it.

Here’s the query;


(SELECT User_Name0 as ‘UserName’,
DATEADD(mi,(lastLogontimeStamp0  / 600000000) – 157258080,0) AS ‘LLTS’,
DATEADD(mi,(PwdLastSet0  / 600000000) – 157258080,0) AS ‘PLS’,
GivenName0 as ‘FirstName’ ,
sn0 as ‘LastName’into #SMSClt FROM v_R_User WHERE PwdLastSet0 <> 0)Select
CASE WHEN #SMSClt.FirstName IS NULL THEN ‘Unknow’ ELSE #SMSClt.FirstName END as ‘First Name’,
CASE WHEN #SMSClt.LastName IS NULL THEN ‘Unknow’ ELSE #SMSClt.LastName END as ‘First Name’,
#SMSClt.LLTS as ‘Last Logon Timestamp Date’,
Datediff(DAY, #SMSClt.LLTS, GETDATE()) As ‘Last Logon Time Stamp (DAYS)’,
#SMSClt.PLS as ‘Last Password Set Date’,
DateDiff(Day, #SMSClt.PLS, GETDATE()) As ‘Last Password Set (DAYS)’



Datediff(DAY, #SMSClt.LLTS, GETDATE())  > 30 and Datediff(DAY, #SMSClt.PLS, GETDATE())  > 30

DROP table #SMSClt

reference ;