Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Modify query to include IP address

Created: 14 Oct 2013 | 3 comments

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.

Operating Systems:

Comments 3 CommentsJump to latest comment

HighTower's picture

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

Briandr88's picture

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

kpjernigan's picture

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]

 
 
 
SELECT
vi.Name,I.Name AS 'Location',vct.Name AS 'Computer Type',va.[Serial Number]
,va.Model,va.Manufacturer,va.Status,vi.IsManaged,ip.[IP Address]
 
FROM
vResourceItem vi INNER JOIN ResourceAssociation ra ON ra.ParentResourceGuid = vi.Guid 
LEFT OUTER JOIN vAsset va ON va._ResourceGuid = vi.Guid 
LEFT OUTER JOIN ResourceAssociation loc ON (loc.ChildResourceGuid = vi.Guid OR
loc.ParentResourceGuid = vi.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' 
LEFT OUTER JOIN vItem I ON loc.ChildResourceGuid = I.Guid 
LEFT OUTER JOIN vComputerType vct ON vct._ResourceGuid = ra.ChildResourceGuid 
LEFT OUTER JOIN Inv_AeX_AC_Identification ID ON ID._ResourceGuid = va._ResourceGuid 
LEFT OUTER JOIN Inv_Aex_AC_TCPIP ip ON id._ResourceGuid = ip.[_ResourceGuid]
 
WHERE vct.Name LIKE '%Server%'
ORDER BY vi.Name

-Kev