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:
Is this compatible with Altiris Patch Management Solution 7.6 and SQL 2008 SP3?
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]
END
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.
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.
thx Michael
great job! - I was looking for such report
Yes, it was designed based on 7.1 SP2.
nice one
is it SMP 7.1 SP2 compliant?