Client Management Suite

Hi,

I need to modify the existing query to include IP address. I am not sure which view I need to go and if it requires an additional join.

SELECT
                      vi.Name, I.Name AS 'Location', vct.Name AS 'Computer Type', va.[Serial Number], va.Model, va.Manufacturer, va.Status, vi.IsManaged,     

                     vComputer.[IP Address]
FROM            vResourceItem AS vi INNER JOIN
                      ResourceAssociation AS ra ON ra.ParentResourceGuid = vi.Guid LEFT OUTER JOIN
                      vAsset AS va ON va._ResourceGuid = vi.Guid LEFT OUTER JOIN
                      ResourceAssociation AS loc ON (loc.ChildResourceGuid = vi.Guid OR
                      loc.ParentResourceGuid = vi.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER JOIN
                      vItem AS I ON loc.ChildResourceGuid = I.Guid LEFT OUTER JOIN
                      vComputerType AS vct ON vct._ResourceGuid = ra.ChildResourceGuid LEFT OUTER JOIN
                      Inv_AeX_AC_Identification AS ID ON ID._ResourceGuid = va._ResourceGuid LEFT OUTER JOIN

                     
WHERE     (vct.Name LIKE '%Server%')
ORDER BY vi.Name

Help much appreciated.

I kind of reformatted it a bit for my brain to understand it.  This query resolves but it doesn't work for me probably because we don't have some of this info populated:

SELECT
vi.Name,
I.Name AS 'Location',
vct.Name AS 'Computer Type',
va.[Serial Number],
va.Model,
va.Manufacturer,
va.Status,
vi.IsManaged,
vComputer.[IP Address]

FROM
vResourceItem AS vi INNER
JOIN ResourceAssociation AS ra ON ra.ParentResourceGuid = vi.Guid LEFT OUTER
JOIN vAsset AS va ON va._ResourceGuid = vi.Guid LEFT OUTER
JOIN ResourceAssociation AS loc ON (loc.ChildResourceGuid = vi.Guid OR loc.ParentResourceGuid = vi.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER
JOIN vItem AS I ON loc.ChildResourceGuid = I.Guid LEFT OUTER
JOIN vComputerType AS vct ON vct._ResourceGuid = ra.ChildResourceGuid LEFT OUTER
JOIN Inv_AeX_AC_Identification AS ID ON ID._ResourceGuid = va._ResourceGuid LEFT OUTER
JOIN vComputer ON vComputer.Guid = I.Guid

WHERE (vct.Name LIKE '%Server%')
ORDER BY vi.Name

Hi,

Thanks for the suggestion. Does this look better?

ADD

,ip.[IP Address] AS [IP Address]

LEFT OUTER JOIN
Inv_Aex_AC_TCPIP ip
ON vCom.[Guid] = ip.[_ResourceGuid]

REMOVE

,vComputer.[IP Address]

LEFT OUTER
JOIN vComputer ON vComputer.Guid = I.Guid

 

You haven't defined the "ON vCom.[GUID]" anywhere.  So if you were going to use the Inv_AeX_AC_TCPIP table, then just join it to the Inv_AeX_AC_Identification that you have defined as ID:  LEFT OUTER JOIN Inv_Aex_AC_TCPIP ip ON ID._ResourceGuid = ip.[_ResourceGuid]