Part 2: Custom Monitoring for ConfigMgr – Monitoring Client Scan failures

In this post, I am sharing how we implemented a solution to monitor and alert for Scan failures using custom SQL job which will monitor Software Update Point (SUP) Scan failures and report an event which is alerted through OpsMgr. Though we have ConfigMgr Management pack which monitor & alerts for critical SUP site server component for any server errors and this custom monitoring SQL job for Software Update Point is primarily for “outside in” monitoring as its uses client state messages to determine the scan success vs.failures.

Solution: We have created a SQL job which runs a query at each Primary Site once per day (could be scheduled more frequently if needed). The query is designed to look at SUP scan state data in the v_updateScanStatus view and determine if SUP scan compliance is above 95% for the Primary Site. If any site is showing less than 95% scan success, a Windows Application Event will be generated that can be consumed by OpsMgr monitoring. 

Steps to create the SQL job for monitoring Client Scan failures

The SQL job will run on the 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.

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

2. Expand SQL Server Agent, right click JOBS and select ‘New Job’

3. In the New Job Properties dialog,box type SUPScanSuccess in the Name field

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

5. In the New Job Step dialog, Click the NEW button

a. Type this job step name: SUPScanSuccess.

b. In the Type list, click Transact-SQL Script (TSQL).

c. In the Database dropdown list, select the SMS_xxx database

d. In the Command box, paste the Transact-SQL command below. (see below in document)

e. Click Parse to check your syntax.

f. The message "The command was successfully parsed" is displayed when your syntax is correct; if you see this message click OK to clear the message box. . If an error is found, correct the syntax before continuing by retrying the copy/paste procedure and overwriting the previous paste job. (the screenshot below is a generic example)

Transact SQL Script:

DECLARE @@MESSAGE varchar(200)
Set @@MESSAGE = 'SUP Client Scan success rate was less than a 95% success rate in the last 24 hours'
Declare @percent decimal(18,5)
declare @olddate datetime
set @olddate=DATEADD(HOUR,-24, getutcdate())
set @noCom = (select count(*) as clients
                from v_updateScanStatus upp
                join v_statenames stat on stat.stateid = upp.lastscanstate
                join v_RA_System_SMSAssignedSites site on site.resourceid = upp.resourceid
                and stat.topictype ='501'
                and upp.lastscanpackagelocation like'http%'
                where stat.StateName in ('Scan Failed ')
                and upp.ScanTime > @olddate
                group by upp.lastscanstate,stat.statename,site.sms_assigned_sites0)
set @yesCom = (select count(*)as clients
                from v_updateScanStatus upp
                join v_statenames stat on stat.stateid = upp.lastscanstate
                join v_RA_System_SMSAssignedSites site on site.resourceid = upp.resourceid
                and stat.topictype ='501' and upp.lastscanpackagelocation like'http%' where stat.StateName in ('Scan Completed ') and upp.ScanTime > @olddate
                group by upp.lastscanstate,stat.statename,site.sms_assigned_sites0)
set @percent = ((@noCom/@yesCom)*100)
If @percent > 5
Begin
EXEC xp_logevent 60020, @@MESSAGE, Warning
END

6. Click OK to the New Job Step dialog box to return to the New Job pane.

7. Under “Select a Page” choose Schedules and click New to open the New Job Schedule pane

a. In the name field type SUPScanSuccess

b. Under Frequency change the value to Daily

c. Under Daily Frequency choose the “Occurs once at” radio button

               Change the setting to occur at 2:30 a.m.

d Verify the Description states the job will run every day at 2:30 A.M.

8. Click OK to close the New Job box.

Validation Steps: Verify the new job exists in SQL Management Studio>SQL Server Agent>JOBS, you can verify settings of the job by right clicking the job and choosing Properties.

Last but not the least I want to acknowledge one of team member – Scott Elieff who helped us in writing this custom monitoring SQL job. Special THANKS to him.

Please share your comments for these custom monitoring task and I would be glad to answer any queries.

What’s next for this custom monitoring series?

Custom monitoring for SUP Client Installation

Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of any included script samples are subject to the terms specified in the Terms of Use