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.

clip_image002

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.

Tagged on: