Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.

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