ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Duplicate rows for computers in the report "Software Compliance Detailed Summary"

book

Article ID: 172771

calendar_today

Updated On:

Products

Software Management Solution

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.

Cause

This is a known issue.

Environment

8.1, possibly 8.5

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