Duplicate rows for computers in the report "Software Compliance Detailed Summary"
Article ID: 172771
Updated On:
Products
Issue/Introduction
Computer show multiple rows in the report "Software Compliance Detailed Summary". This occurs when the status "Not applied" is selected in the parameters. Other status do not show this problem.
Environment
8.1, possibly 8.5
Cause
This is a known issue.
Resolution
The following query will alter the stored procedure used for this report and remove duplicates in the report results. To apply, run the query in SQL Management Studio against the Symantec_CMDB database.
ALTER PROCEDURE [dbo].[sp_GetSoftwareComplianceDetailedSummary]
@in_ScopeCollectionGuid uniqueidentifier = '91C68FCB-1822-E793-B59C-2684E99A64CD', -- all computers for Default OrgView
@in_TrusteeScope nvarchar(max) = 'S-1-1-0',
@in_Policies uniqueidentifier = NULL,
@in_Computers nvarchar(128) = N'%',
@in_CheckLicense int = 0,
@in_Status int = 3, -- In Compliance
@in_MaxRow int = 50000,
@in_StartTime datetime,
@in_EndTime datetime,
@in_Culture varchar(10) = 'en-US'
AS
BEGIN
SET NOCOUNT ON
DECLARE @c_Status nvarchar(64)
IF ( @in_MaxRow > 60000)
SET @in_MaxRow = 60000
SET @c_Status = CASE @in_Status
WHEN 3 THEN ISNULL( dbo.fnLocalizeString( 'item.name.in compliance', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6', @in_Culture ), N'In Compliance' )
WHEN 0 THEN ISNULL( dbo.fnLocalizeString( 'item.name.not in compliance', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6', @in_Culture ), N'Not In Compliance' )
WHEN 4 THEN ISNULL( dbo.fnLocalizeString( 'item.name.rebootrequired', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6', @in_Culture ), N'Reboot Required' )
WHEN 6 THEN ISNULL( dbo.fnLocalizeString( 'item.name.notapplicable', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6', @in_Culture ), N'Not Applicable' )
WHEN 5 THEN ISNULL( dbo.fnLocalizeString( 'item.name.logoffrequired', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6', @in_Culture ), N'Log Off Required' )
ELSE ISNULL( dbo.fnLocalizeString( 'item.name.notapplied', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6', @in_Culture ), N'Not Applied' )
END
if object_id('tempdb..#tmpResources') is not null
drop table #tmpResources
SELECT DISTINCT ci.[Guid] AS [ResourceGuid],
ci.[Name] AS [Name]
INTO #tmpResources
FROM Inv_AeX_AC_Client_Agent ca
JOIN vRM_Computer_Item ci ON ci.[Guid] = ca._ResourceGuid
JOIN vThisNS NS
ON NS.[Guid] = ci.OwnerNSGuid
JOIN dbo.fnGetTrusteeScopedResourcesByScope( @in_TrusteeScope, @in_ScopeCollectionGuid, 1) rq
ON rq.[ResourceGuid] = ca._ResourceGuid
LEFT JOIN LicenseInUse AS lu
JOIN LicenseStatus AS ls
ON ls.LicensingPolicyGuid = lu.LicensingPolicyGuid
AND ls.Status = 'Ok'
AND ls.ProductGuid = 'AD3F5980-D9E9-11D3-A318-0008C7A09198'
ON lu.ResourceGuid=ca._ResourceGuid
LEFT JOIN vSMS_GetAllRetiredMachines ret
ON ret.[Guid] = ca._ResourceGuid
WHERE ca.[Agent Name] = 'Software Management Solution Agent'
AND ( @in_Computers = N'%' OR LOWER( ci.Name ) LIKE @in_Computers )
AND ret.Guid IS NULL -- exclude retired machines
AND ( @in_CheckLicense = 0 OR lu.ResourceGuid IS NOT NULL )
if object_id('tempdb..#tmpPolicies') is not null
drop table #tmpPolicies
SELECT vi.Guid, ISNULL(s.String,vi.Name) as Name, cbc.BaseClassGuid as ClassGuid,
ROW_NUMBER() over ( PARTITION BY vi.Guid order by bc.Priority desc) as rid
INTO #tmpPolicies
FROM ItemClass ic
JOIN Item vi ON vi.Guid = ic.Guid
INNER JOIN ClassBaseClass [cbc] on [cbc].ClassGuid = [ic].ClassGuid
LEFT JOIN [String] s
JOIN dbo.fnGetBaseCultures(@in_Culture) bc
on bc.Culture=s.Culture
on s.BaseGuid=vi.Guid and s.StringRef='item.name'
where (cbc.BaseClassGuid = '2D3A170E-5028-4570-BA0C-3DB775CB8BDE' OR cbc.BaseClassGuid ='b10b4142-bcbf-4071-a7df-a069311f2840')
AND ( @in_Policies = '00000000-0000-0000-0000-000000000000' OR @in_Policies = vi.Guid)
AND vi.CreatedDate BETWEEN @in_StartTime AND @in_EndTime
delete from #tmpPolicies where rid<>1
SET ROWCOUNT @in_MaxRow
if @in_Status = 0
select c.Name [Computer Name], l.Name as [Policy Name],l.Guid as [_PolicyGuid] ,@c_Status as [Status]
FROM #tmpPolicies l
join Inv_Policy_Compliance_Status ps on ps.PolicyGuid=l.Guid
join #tmpResources c on c.ResourceGuid=ps._ResourceGuid
left join (select distinct es._ResourceGuid,ir.ParentItemGuid from Inv_AeX_SWD_Status_Summary es
JOIN ItemReference ir
ON ir.ChildItemGuid = es.AdvertisementId
AND ir.Hint = 'manageddelivery contains delivery item') st
ON st.ParentItemGuid=ps.PolicyGuid and st._ResourceGuid=ps._ResourceGuid
where ps.Compliance=0 and NOT(st._ResourceGuid is NULL AND l.ClassGuid ='2D3A170E-5028-4570-BA0C-3DB775CB8BDE')
order by l.Name,c.Name
else if @in_Status= 1
select distinct c.Name [Computer Name], l.Name as [Policy Name],l.Guid as [_PolicyGuid] ,@c_Status as [Status]
FROM #tmpPolicies l
INNER JOIN ClassBaseClass [cbc] on [cbc].ClassGuid = [l].ClassGuid
join Inv_Policy_Compliance_Status ps on ps.PolicyGuid=l.Guid
join #tmpResources c on c.ResourceGuid=ps._ResourceGuid
left join (select distinct es._ResourceGuid,ir.ParentItemGuid from Inv_AeX_SWD_Status_Summary es
JOIN ItemReference ir
ON ir.ChildItemGuid = es.AdvertisementId
AND ir.Hint = 'manageddelivery contains delivery item') st
ON st.ParentItemGuid=ps.PolicyGuid and st._ResourceGuid=ps._ResourceGuid
where ps.Compliance=1 or (ps.Compliance=0 and st._ResourceGuid IS NULL AND cbc.BaseClassGuid ='2D3A170E-5028-4570-BA0C-3DB775CB8BDE')
union all
select distinct c.Name, l.Name,l.Guid, @c_Status
from #tmpPolicies l
join ItemAppliesTo ia on ia.ItemGuid=l.Guid
join ResourceTargetMembershipCache rt ON rt.ResourceTargetGuid = ia.ResourceTargetGuid
join #tmpResources c on c.ResourceGuid=rt.ResourceGuid
left join Inv_Policy_Compliance_Status ps on ps.PolicyGuid=l.Guid and ps._ResourceGuid=rt.ResourceGuid
where ps.PolicyGuid is null
ORDER BY l.Name,c.Name
else
select c.Name [Computer Name], l.Name as [Policy Name],l.Guid as [_PolicyGuid] ,@c_Status as [Status]
FROM #tmpPolicies l
join Inv_Policy_Compliance_Status ps on ps.PolicyGuid=l.Guid
join #tmpResources c on c.ResourceGuid=ps._ResourceGuid
where [email protected]_Status
ORDER BY l.Name, c.Name
SET ROWCOUNT 0
END
Feedback
