Client Management Suite

Hi,

I am having an issue with getting the correct join that would bring in the 'Primary User'. Can someone tell me which join needs to be added to the following report?

SELECT    COALESCE(os.[Name],ci.[Name]) [Computer Name]
        , ra.[CreatedDate] [Retired Date]   
        , cs.[Model]
        , cs.[Manufacturer]
        , cs.[Identifying Number] [Serial Number]   
        , os.[OS Name]
        , os.[OS Revision]  
        , vi.[Name] [Location]
            
FROM [vRM_Computer] c
INNER JOIN [vRM_Computer_Item] ci
    ON c.[Guid] = ci.[Guid]
INNER JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = c.Guid AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'
LEFT OUTER JOIN ResourceAssociation loc
    ON loc.ParentResourceGuid = c.Guid AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON c.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON c.Guid = cs._ResourceGuid
LEFT JOIN    [Inv_AeX_AC_Identification] os
    ON os.[_ResourceGuid] = ci.[Guid]

ORDER BY  ra.[CreatedDate] 

Try adding:

vc.[User] to your SELECT

and add the following join.

Changed you INNER JOIN'S to LEFT JOINS.  Otherewise I got no data, since we dont use the retired feature.

LEFT JOIN  [vComputer] vc
    ON vc.GUID = os._ResourceGuid

------------

SELECT    COALESCE(os.[Name],ci.[Name]) [Computer Name]
        , ra.[CreatedDate] [Retired Date]   
        , cs.[Model]
        , cs.[Manufacturer]
        , cs.[Identifying Number] [Serial Number]   
        , os.[OS Name]
        , os.[OS Revision]  
        , vi.[Name] [Location]
        , vc.[User]
            
FROM [vRM_Computer] c
LEFT JOIN [vRM_Computer_Item] ci
    ON c.[Guid] = ci.[Guid]
LEFT JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = c.Guid AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'
LEFT OUTER JOIN ResourceAssociation loc
    ON loc.ParentResourceGuid = c.Guid AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON c.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON c.Guid = cs._ResourceGuid
LEFT JOIN    [Inv_AeX_AC_Identification] os
    ON os.[_ResourceGuid] = ci.[Guid]
LEFT JOIN  [vComputer] vc
    ON vc.GUID = os._ResourceGuid

ORDER BY  ra.[CreatedDate] 

@kpjernigan - Since you're joining on the [vComputer] table it makes your projection to [vRM_Computer] redundant. Save yourself the extra join!

SELECT    COALESCE(os.[Name],ci.[Name]) [Computer Name]
        , ra.[CreatedDate] [Retired Date]   
        , cs.[Model]
        , cs.[Manufacturer]
        , cs.[Identifying Number] [Serial Number]   
        , os.[OS Name]
        , os.[OS Revision]  
        , vi.[Name] [Location]
        , c.[User]      
FROM [vComputer] c
LEFT JOIN [vRM_Computer_Item] ci
    ON c.[Guid] = ci.[Guid]
LEFT JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = c.Guid AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'
LEFT OUTER JOIN ResourceAssociation loc
    ON loc.ParentResourceGuid = c.Guid AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON c.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON c.Guid = cs._ResourceGuid
LEFT JOIN    [Inv_AeX_AC_Identification] os
    ON os.[_ResourceGuid] = ci.[Guid]
ORDER BY  ra.[CreatedDate] 

Or you could join on the [Inv_AeX_AC_Primary_User] table directly.

SELECT    COALESCE(os.[Name],ci.[Name]) [Computer Name]
        , ra.[CreatedDate] [Retired Date]   
        , cs.[Model]
        , cs.[Manufacturer]
        , cs.[Identifying Number] [Serial Number]   
        , os.[OS Name]
        , os.[OS Revision]  
        , vi.[Name] [Location]
        , u.[User]
            
FROM [vRM_Computer] c
LEFT JOIN [vRM_Computer_Item] ci
    ON c.[Guid] = ci.[Guid]
LEFT JOIN ResourceAssociation ra
   ON ra.ParentResourceGuid = c.Guid AND ra.ResourceAssociationTypeGuid = '3028166F-C0D6-41D8-9CB7-F64852E0FD01'
   AND ra.ChildResourceGuid = '492C463B-AFA2-4DD6-AE73-6FD2C7B0E489'
LEFT OUTER JOIN ResourceAssociation loc
    ON loc.ParentResourceGuid = c.Guid AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON c.Guid = p._ResourceGuid
LEFT OUTER JOIN vHWComputerSystem cs
    ON c.Guid = cs._ResourceGuid
LEFT JOIN    [Inv_AeX_AC_Identification] os
    ON os.[_ResourceGuid] = ci.[Guid]
LEFT JOIN  [vComputer] vc
    ON vc.GUID = os._ResourceGuid
LEFT JOIN [Inv_AeX_AC_Primary_User] u
	ON c.Guid = u._ResourceGuid
ORDER BY  ra.[CreatedDate]