Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.
Endpoint Management Community Blog

Patch Compliance Report that only includes updates contained in policies

Created: 11 Dec 2012 • 6 comments
Michael Grueber's picture
+2 2 Votes
Login to vote

It is essential to understand how the reports in the Patch Management Solution calculate compliance.  For example, the Compliance by Computer report calculates compliance based on all bulletins applicable to that computer. 

If you download the applicability/detection rules for a particular bulletin and the Patch Management Solution finds that an update associated with that bulletin is applicable to a particular computer but not installed on that computer, that computer is considered to not be in compliance with respect to that bulletin.  This is true even if you have not created any policies to distribute the updates associated with the bulletin to that computer.

While the Patch Management Solution reports provide you with an "absolute" measure of compliance, there may be occasions in which you want to see another view of compliance.  For example, you may want to see the compliance status of a particular computer with respect to only those bulletins associated with policies that target that computer rather, than all bulletins that are applicable to that computer.  In order to do so, you can build a custom report by creating a stored procedure using the SQL in the attachment to this post according to the following instructions:

  1. Download the attached file and change the name of the file extension from "txt" to "sql"
  2. Run the file in Management Studio.  It will create the new stored procedure needed for the report (spPMWindows_TargetedUpdateComplianceByComputer).
  3. Clone the default Windows Compliance By Computer report and name it Windows Targeted Update Compliance By Computer.
  4. Export the report to a convenient location.
  5. Open the XML file in a program such as SQL Management Studio or Visual Studio.  Search for 'spPMWindows' and you will find a reference to the current stored procedure being used (spPMWindows_ComplianceByComputer).
  6. Simply replace the call to that stored procedure with the new one (spPMWindows_TargetedUpdateComplianceByComputer).  No other changes are required.
  7. Save the file
  8. Right click on the Compliance folder under Patch and choose Import - select the XML file we just changed

Comments 6 CommentsJump to latest comment

callend's picture

nice one

is it SMP 7.1 SP2 compliant?

-2
Login to vote
Michael Grueber's picture

Yes, it was designed based on 7.1 SP2.

+2
Login to vote
callend's picture

thx Michael

great job! - I was looking for such report

-2
Login to vote
Michael Grueber's picture

Unfortunately, I can't take credit for creating the stored procedure. It was created by one of my colleagues.  I just posted it.

Please keep in mind that stored procedure is provided "As Is", meaning that it is not an officially supported part of the product.  There is a possibility, however, slight, that the stored procedure could be broken by future changes to the product.

We are considering the possibility of adding similar functionality to the product itself at some point in the future, but I am not in a position to provide more information at this time regarding if, and when, that may occur.

+2
Login to vote
Mistral's picture

It is a pity, the drilldowns stay the same. So I cannot really find out, which updates are still missing from my created policies.

So it gives a nice overview, but if you are still not compliant to your policies, it does not give you a helping hand.

0
Login to vote
reddeppa.999@gmail.com's picture

Subject:I need to add bulletin name in the procedure.

Can you please help me on this query.

CREATE PROCEDURE [dbo].[spPMWindows_ComplianceByComputer]

@VendorGuid uniqueidentifier = '9D5F6BB8-8ADF-49D1-9D84-2932CA46CE1E',

@CategoryGuid uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- All

@SupersedenceStatus NVARCHAR(36) = 'non-superseded', -- not used now

@OperatingSystem NVARCHAR(128) = '%',

@DistributionStatus NVARCHAR(16) = 'active',

@FilterCollection uniqueidentifier = 'EB3A1A12-E1C7-4431-B060-F0333E4E488C', -- All Computer

@StartDate DATETIME = NULL,

@EndDate DATETIME = NULL,

@pCulture nvarchar(10) = 'en-US',

@ScopeCollectionGuid uniqueidentifier = '91C68FCB-1822-E793-B59C-2684E99A64CD', -- Computers

@TrusteeScope varchar(max) = 'S-1-1-0'

AS

BEGIN

-- Need to set start and end date defaults, if values not passed in from calling function

IF (@StartDate IS NULL)

SET @StartDate = DATEADD(Year, -1, GETDATE())

IF (@EndDate IS NULL)

SET @EndDate = GETDATE()

DECLARE @c_Yes nvarchar(32),

@c_No nvarchar(32)

SELECT @c_Yes = ISNULL( dbo.fnLocalizeString('yes', '746A8B51-A570-43AB-8D36-AEF36D0C8041', @pCulture), 'Yes' ),

@c_No = ISNULL( dbo.fnLocalizeString('no', '746A8B51-A570-43AB-8D36-AEF36D0C8041', @pCulture), 'No' )

SELECT b2u.ChildResourceGuid AS [UpdateGuid]

INTO #tempBulletinUpd

FROM Inv_Software_Bulletin sb

JOIN ItemActive asb ON asb.Guid = sb._ResourceGuid

JOIN ResourceAssociation b2v ON b2v.ParentResourceGuid = sb._ResourceGuid

AND b2v.ResourceAssociationTypeGuid = '2FFEB9F0-601E-4746-A830-BDB200076503'

JOIN ResourceAssociation b2u ON b2u.ParentResourceGuid = sb._ResourceGuid

AND b2u.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293' -- SWB to SWU

LEFT JOIN ResourceAssociation u2u ON u2u.ChildResourceGuid = b2u.ChildResourceGuid

AND u2u.ResourceAssociationTypeGuid = '644A995E-211A-4D94-AA8A-788413B7BE5D'-- Software Update Supersedes Software Update

LEFT JOIN Inv_Software_Update_Category cat ON cat._ResourceGuid = b2u.ChildResourceGuid

WHERE sb.FirstReleaseDate BETWEEN @StartDate AND @EndDate

AND u2u.ParentResourceGuid IS NULL

AND ( @DistributionStatus = '%' OR asb.Enabled = 1 )

AND ( @VendorGuid = '00000000-0000-0000-0000-000000000000' OR b2v.ChildResourceGuid = @VendorGuid )

AND ( @CategoryGuid = '00000000-0000-0000-0000-000000000000' OR cat.Category = @CategoryGuid )

-- Create a temp table to contain the scoped resources (computers) in it. This

-- table will also have joined in the CollectionMembership, OS filtering, and

-- removal of retired computers.

-- By putting these ResourceGuids into a temporary table (to be joined later)

-- we avoid a large hit of performing these joins and functions calls against

-- a potentially huge number of rows as was being done in an earlier version.

--

SELECT tsc.ResourceGuid

INTO #tempScopedResources

FROM [fnGetTrusteeScopedResourcesByScope](@TrusteeScope, @ScopeCollectionGuid, 1) tsc

JOIN Inv_AeX_AC_Identification cid ON cid._ResourceGuid = tsc.ResourceGuid

AND cid.[OS Name] LIKE @OperatingSystem

JOIN CollectionMembership cms ON cms.ResourceGuid = tsc.ResourceGuid

AND cms.CollectionGuid = @FilterCollection

LEFT JOIN vPMCore_GetAllRetiredMachines ret ON ret.Guid = tsc.ResourceGuid

WHERE ret.Guid IS NULL -- exclude the retired machine

SELECT [_ResourceGuid] = cbc.ComputerGuid

,[Computer Name] = vc.Name

,[Compliance] = CONVERT(numeric(6,2), CASE ISNULL(cbc.Applicable, 0) WHEN 0 THEN 0 ELSE CONVERT(NUMERIC(6,2), CONVERT(FLOAT, cbc.Installed) / CONVERT(FLOAT, cbc.Applicable) * 100.0) END )

,[Applicable (Count)] = cbc.Applicable

,[Installed (Count)] = cbc.Installed

,[Not Installed (Count)] = cbc.Applicable - cbc.Installed

,[Restart Pending] = CASE WHEN cbc.RestartPending = 0 THEN @c_No ELSE @c_Yes END

,_OperatingSystem = @OperatingSystem

,_DistributionStatus = @DistributionStatus

,_StartDate = @StartDate

,_EndDate = @EndDate

FROM vRM_Computer_Item AS vc

JOIN ( SELECT ComputerGuid = ua._ResourceGuid

,Applicable = COUNT(ua.UpdateGuid)

,Installed = SUM( CASE WHEN ui.UpdateGuid IS NULL THEN 0

WHEN ses.PendingSince IS NULL THEN 1 ELSE 0 END )

,RestartPending = COUNT(ses.PendingSince)

FROM #tempBulletinUpd bu

JOIN vPMWindows_UpdateApplicable ua ON ua.UpdateGuid = bu.UpdateGuid

JOIN #tempScopedResources rq ON rq.ResourceGuid = ua._ResourceGuid

LEFT JOIN vPMWindows_UpdateInstalled ui ON ui.UpdateGuid = ua.UpdateGuid

AND ui._ResourceGuid = ua._ResourceGuid

LEFT JOIN vPMCore_ComputersPendingRebootByPackage ses ON ses.SoftwareUpdateGuid = ua.UpdateGuid

AND ses._ResourceGuid = ua._ResourceGuid

GROUP BY ua._ResourceGuid

) AS cbc ON cbc.ComputerGuid = vc.Guid

ORDER BY [Computer Name]

DROP TABLE #tempBulletinUpd

DROP TABLE #tempScopedResources

ENDCREATE PROCEDURE [dbo].[spPMWindows_ComplianceByComputer]

@VendorGuid uniqueidentifier = '9D5F6BB8-8ADF-49D1-9D84-2932CA46CE1E',

@CategoryGuid uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- All

@SupersedenceStatus NVARCHAR(36) = 'non-superseded', -- not used now

@OperatingSystem NVARCHAR(128) = '%',

@DistributionStatus NVARCHAR(16) = 'active',

@FilterCollection uniqueidentifier = 'EB3A1A12-E1C7-4431-B060-F0333E4E488C', -- All Computer

@StartDate DATETIME = NULL,

@EndDate DATETIME = NULL,

@pCulture nvarchar(10) = 'en-US',

@ScopeCollectionGuid uniqueidentifier = '91C68FCB-1822-E793-B59C-2684E99A64CD', -- Computers

@TrusteeScope varchar(max) = 'S-1-1-0'

AS

BEGIN

-- Need to set start and end date defaults, if values not passed in from calling function

IF (@StartDate IS NULL)

SET @StartDate = DATEADD(Year, -1, GETDATE())

IF (@EndDate IS NULL)

SET @EndDate = GETDATE()

DECLARE @c_Yes nvarchar(32),

@c_No nvarchar(32)

SELECT @c_Yes = ISNULL( dbo.fnLocalizeString('yes', '746A8B51-A570-43AB-8D36-AEF36D0C8041', @pCulture), 'Yes' ),

@c_No = ISNULL( dbo.fnLocalizeString('no', '746A8B51-A570-43AB-8D36-AEF36D0C8041', @pCulture), 'No' )

SELECT b2u.ChildResourceGuid AS [UpdateGuid]

INTO #tempBulletinUpd

FROM Inv_Software_Bulletin sb

JOIN ItemActive asb ON asb.Guid = sb._ResourceGuid

JOIN ResourceAssociation b2v ON b2v.ParentResourceGuid = sb._ResourceGuid

AND b2v.ResourceAssociationTypeGuid = '2FFEB9F0-601E-4746-A830-BDB200076503'

JOIN ResourceAssociation b2u ON b2u.ParentResourceGuid = sb._ResourceGuid

AND b2u.ResourceAssociationTypeGuid = '7EEAB03A-839C-458D-9AF2-55DB6B173293' -- SWB to SWU

LEFT JOIN ResourceAssociation u2u ON u2u.ChildResourceGuid = b2u.ChildResourceGuid

AND u2u.ResourceAssociationTypeGuid = '644A995E-211A-4D94-AA8A-788413B7BE5D'-- Software Update Supersedes Software Update

LEFT JOIN Inv_Software_Update_Category cat ON cat._ResourceGuid = b2u.ChildResourceGuid

WHERE sb.FirstReleaseDate BETWEEN @StartDate AND @EndDate

AND u2u.ParentResourceGuid IS NULL

AND ( @DistributionStatus = '%' OR asb.Enabled = 1 )

AND ( @VendorGuid = '00000000-0000-0000-0000-000000000000' OR b2v.ChildResourceGuid = @VendorGuid )

AND ( @CategoryGuid = '00000000-0000-0000-0000-000000000000' OR cat.Category = @CategoryGuid )

-- Create a temp table to contain the scoped resources (computers) in it. This

-- table will also have joined in the CollectionMembership, OS filtering, and

-- removal of retired computers.

-- By putting these ResourceGuids into a temporary table (to be joined later)

-- we avoid a large hit of performing these joins and functions calls against

-- a potentially huge number of rows as was being done in an earlier version.

--

SELECT tsc.ResourceGuid

INTO #tempScopedResources

FROM [fnGetTrusteeScopedResourcesByScope](@TrusteeScope, @ScopeCollectionGuid, 1) tsc

JOIN Inv_AeX_AC_Identification cid ON cid._ResourceGuid = tsc.ResourceGuid

AND cid.[OS Name] LIKE @OperatingSystem

JOIN CollectionMembership cms ON cms.ResourceGuid = tsc.ResourceGuid

AND cms.CollectionGuid = @FilterCollection

LEFT JOIN vPMCore_GetAllRetiredMachines ret ON ret.Guid = tsc.ResourceGuid

WHERE ret.Guid IS NULL -- exclude the retired machine

SELECT [_ResourceGuid] = cbc.ComputerGuid

,[Computer Name] = vc.Name

,[Compliance] = CONVERT(numeric(6,2), CASE ISNULL(cbc.Applicable, 0) WHEN 0 THEN 0 ELSE CONVERT(NUMERIC(6,2), CONVERT(FLOAT, cbc.Installed) / CONVERT(FLOAT, cbc.Applicable) * 100.0) END )

,[Applicable (Count)] = cbc.Applicable

,[Installed (Count)] = cbc.Installed

,[Not Installed (Count)] = cbc.Applicable - cbc.Installed

,[Restart Pending] = CASE WHEN cbc.RestartPending = 0 THEN @c_No ELSE @c_Yes END

,_OperatingSystem = @OperatingSystem

,_DistributionStatus = @DistributionStatus

,_StartDate = @StartDate

,_EndDate = @EndDate

FROM vRM_Computer_Item AS vc

JOIN ( SELECT ComputerGuid = ua._ResourceGuid

,Applicable = COUNT(ua.UpdateGuid)

,Installed = SUM( CASE WHEN ui.UpdateGuid IS NULL THEN 0

WHEN ses.PendingSince IS NULL THEN 1 ELSE 0 END )

,RestartPending = COUNT(ses.PendingSince)

FROM #tempBulletinUpd bu

JOIN vPMWindows_UpdateApplicable ua ON ua.UpdateGuid = bu.UpdateGuid

JOIN #tempScopedResources rq ON rq.ResourceGuid = ua._ResourceGuid

LEFT JOIN vPMWindows_UpdateInstalled ui ON ui.UpdateGuid = ua.UpdateGuid

AND ui._ResourceGuid = ua._ResourceGuid

LEFT JOIN vPMCore_ComputersPendingRebootByPackage ses ON ses.SoftwareUpdateGuid = ua.UpdateGuid

AND ses._ResourceGuid = ua._ResourceGuid

GROUP BY ua._ResourceGuid

) AS cbc ON cbc.ComputerGuid = vc.Guid

ORDER BY [Computer Name]

DROP TABLE #tempBulletinUpd

DROP TABLE #tempScopedResources

END

-1
Login to vote