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.
