Creating a view for your Reports

The SMSDEF.MOF file update creates the following tables Software_Updates_V4_Data and Software_Updates_V4_HIST. The following SQL script can be used to create a custom SQL view for reports. I have included the computer names from V_R_System and the site code from v_RA_System_SMSInstalledSites

Make sure you change the database name to your Config Manager database name before using. Also the name of the view can be edited to suit you own naming convention.

USE [SMS_XXX]
GO
/****** Object:  View [dbo].[V_Custom_Update_Status]    Script Date: 10/20/2007 17:06:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[V_Custom_Update_Status]
AS
SELECT DISTINCT
TOP (100) PERCENT  dbo.v_R_System.ResourceID, dbo.v_R_System.Netbios_Name0 AS ClientName, dbo.v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 AS Site_Code,
dbo.Software_Updates_v4_DATA.Article00 AS KB_Number, dbo.Software_Updates_v4_DATA.Bulletin00 AS Bulletin_ID,
dbo.Software_Updates_v4_DATA.ScanTime00 AS Scan_Date_Time, dbo.Software_Updates_v4_DATA.Status00 AS Update_Status,
dbo.v_R_System.Operating_System_Name_and0 AS OS, dbo.Software_Updates_v4_DATA.Title00 AS Update_Title
FROM         dbo.v_R_System INNER JOIN
dbo.Software_Updates_v4_DATA ON dbo.v_R_System.ResourceID = dbo.Software_Updates_v4_DATA.MachineID INNER JOIN
dbo.v_RA_System_SMSInstalledSites ON dbo.Software_Updates_v4_DATA.MachineID = dbo.v_RA_System_SMSInstalledSites.ResourceID
ORDER BY Bulletin_ID DESC

Granting Select Permissions on the custom View

The following roles Smsschm_users and webreport_approle need to be granted select permission on any custom views created. Without performing the permissions step, the reports will only work in SQL Server Management Studio.

Sample Report Query

The following is a sample query to create a summary report from the new view (make sure your edit the view name if you did not use the suggested name). Modify to suit your needs.

SELECT Bulletin_ID, KB_Number, OS,(100 * Installed /(Installed+Applicable)) AS ‘%Compliant’, Installed+Applicable AS Total
From (Select Bulletin_ID, OS, Update_Title AS BulletinInfo,
SUM(CASE WHEN Update_Status = ‘Installed’ THEN 1 ELSE 0 END)as ‘Installed’ ,
SUM(CASE WHEN Update_Status = ‘Missing’ THEN 1 ELSE 0 END) AS ‘Applicable’
from v_Custom_Update_Status
where Bulletin_ID like ‘MS08-%’AND OS LIKE ‘%Server%’
group by Bulletin_ID,OS,Update_title) AS ps
Order by Bulletin_ID DESC, OS

The report is filtered on all security updates for the year 2008 and by server operating systems only. Edit the filters to change the year and the operating system as needed.

The release of System Center Configuration manager has seen a vast improvement in security update management. The improvements have introduced new ways of doing familiar tasks

No Security updates compliance at the parent site without a SUP:

Prior to Config Mgr 2007 software update compliance information propagated up the SMS hierarchy following a parent child connection. No additional configuration was required once the parent child relationship was established. Each child site could manage software updates with no dependency on components at the parent site. The new version of SMS has removed this functionality. In order for a parent site to receive software updates compliance information a new component setting has to be enabled (Software Updates Point –SUP). The new SUP role forms its own hierarchy where only the highest SUP in the hierarchy synchronises directly with the internet for new software updates availability. Child site SUPs will not be updated until the parent site SUP is updated.

The ability to use a central site for consolidated reporting is no longer possible without adding this additional layer in you Config Mgr design. Another challenge is the ease of creating summary reports on security update compliance. The new state message based reports though excellent will present challenges for seasoned SMS administrators tasked with creating management summary reports.

How to get compliance information at a parent reporting site without a SUP:

This is how to get summary security update status to your parent sites without creating a SUP hierarchy. The solution uses the hardware inventory process to collect summary security update information. This is basic security update information and ideal for creating your management reports. This does not remove the requirement for a SUP at the client’s assigned site.The status of an update is either installed or missing. The information also depends on the old time lag required to receive hardware inventory information from clients.

Steps are as follows:
Edit the SMS_DEF.MOF file and append the reporting class data at the end of this article
Stop sms_executive on parent site
Change sms_def.mof file in: %ConfigMgrInstallDir%\inboxes\clifiles.scr\hinv (Append the class CCM_UpdateStatus at the end of the file.)
Start sms_executive on the parent site
Repeat steps 2-4 for child site where the SUP is installed
Make sure policy is updated on clients assigned to child site
Run scan/install updates on the clients
Run HINV cycle on client (or wait for cycle to run per schedule)
Check dataldr.log on the child site, once HINV is received; Software_Updates_V4_DATA table is created in the database with all the info as per the class below.
Same table gets created on the parent site’s database as well

NB: There is no need to compile the SMS_DEF.MOF as this is now done by the site server and clients updated through policy changes. Test this on a lab instance before applying to production sites.This has been tested on Config Mgr 2007 SP1. Append this to the SMS_DEF.MOF:
//————————————-

// SMS – Software Update Status

//————————————-

[SMS_Report(TRUE),

SMS_Group_Name("Software Updates v4"),

SMS_Class_ID("MICROSOFT|UPDATESTATUS|1.0"),

Namespace("\\\\\\\\.\\\\root\\\\ccm\\\\SoftwareUpdates\\\\UpdatesStore")]

class CCM_UpdateStatus : SMS_Class_Template
{

[SMS_Report(TRUE), Key]

string UniqueId;

[SMS_Report(TRUE)]

string Title;

[SMS_Report(TRUE)]

string Bulletin;

[SMS_Report(TRUE)]

string Article;

[SMS_Report(TRUE)]

string Language;

[SMS_Report(TRUE)]
string SourceUniqueId;

[SMS_Report(TRUE)]

DateTime ScanTime;

[SMS_Report(TRUE)]

uint32 SourceVersion;

[SMS_Report(TRUE)]

uint32 RevisionNumber;

[SMS_Report(TRUE)]

string Status;

[SMS_Report(FALSE)]
CCM_SourceStatus Sources[];

};

In my next article I will provide details of reports which can be created using data from this class, including an SQL view.