Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Add location / phone number to exisiting query

Created: 07 Nov 2013 • Updated: 20 Nov 2013 | 6 comments
This issue has been solved. See solution.

Hi,

What join would I need to do to add location information in? I am not sure if I can bring in a phone number, but I know we have location data. It is just a matter of doing the proper join. Thanks.

SELECT vComputer.Name AS 'Computer Name', vComputer.[User] AS 'Primary User', vComputer.[IP Address], vHWComputerSystem.Model,
vHWComputerSystem.[Identifying Number] AS 'Serial Number', vComputer.[OS Name], acid.[Client Date] AS 'Last Check In'
FROM CollectionMembership INNER JOIN

vComputer ON CollectionMembership.ResourceGuid = vComputer.Guid INNER JOIN

vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid INNER JOIN

Inv_AeX_AC_Identification acid ON vComputer.Guid = acid._ResourceGUID INNER JOIN

vCollection INNER JOIN

Collection ON vCollection.Guid = Collection.Guid ON CollectionMembership.CollectionGuid = Collection.Guid

where vCollection.Name = 'ALL Laptops'

ORDER BY [Computer Name]
 

Operating Systems:

Comments 6 CommentsJump to latest comment

Briandr88's picture

I looked around at some other queries. I think this is close, but it still not working.

SELECT vComputer.Name AS 'Computer Name', vComputer.[User] AS 'Primary User', vComputer.[IP Address], vHWComputerSystem.Model,
vHWComputerSystem.[Identifying Number] AS 'Serial Number', vComputer.[OS Name], acid.[Client Date] AS 'Last Check In',
vl.name AS 'Location', vl.Address, vl.City, vl.State

FROM CollectionMembership INNER JOIN

ResourceAssociation ra ON ra.ParentResourceGuid = va._ResourceGuid and ra.ResourceAssociationTypeGuid ='05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT JOIN

vLocation vl ON ra.ChildResourceGuid = vl._ResourceGuid LEFT JOIN

vComputer ON CollectionMembership.ResourceGuid = vComputer.Guid INNER JOIN

vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid INNER JOIN

Inv_AeX_AC_Identification acid ON vComputer.Guid = acid._ResourceGUID INNER JOIN

vCollection INNER JOIN

Collection ON vCollection.Guid = Collection.Guid ON CollectionMembership.CollectionGuid = Collection.Guid

where vCollection.Name = 'All Laptops'

ORDER BY [Computer Name]

Can someone see the error that would cause it to fail?

kpjernigan's picture

You don't have the 'va' alias for the table defined anywhere.

So this line:

INNER JOIN ResourceAssociation ra ON ra.ParentResourceGuid = va._ResourceGuid and ra.ResourceAssociationTypeGuid ='05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT JOIN
 
There's no table you're defining to be known as va.

-Kev

Briandr88's picture

Thanks. Someone looked at it and suggested the same thing. Someone wrote this query which gives me the data I am looking for. How can I incorporate this into the new query?

SELECT     vLocation.Name, vLocation.Address, vLocation.City, vLocation.State, vLocation.Country, vLocation.Zip, Inv_Phone_Details.[Phone Number],
                      Inv_Subnet.Subnet, Inv_Subnet.[Subnet Mask]
FROM         Inv_Phone_Details INNER JOIN
                      vLocation ON Inv_Phone_Details._ResourceGuid = vLocation._ResourceGuid LEFT OUTER JOIN
                      ResourceAssociation INNER JOIN
                      vRM_Subnet_Item ON ResourceAssociation.ChildResourceGuid = vRM_Subnet_Item.Guid INNER JOIN
                      vRM_Location_Item ON ResourceAssociation.ParentResourceGuid = vRM_Location_Item.Guid INNER JOIN
                      Inv_Subnet ON vRM_Subnet_Item.Guid = Inv_Subnet._ResourceGuid ON vLocation._ResourceGuid = vRM_Location_Item.Guid
WHERE     (vLocation.Name LIKE '%[_]%[_]%')
ORDER BY vLocation.Name  

Briandr88's picture

Hi,

Anyone? Having this query work correctly would be a big help. Thanks.

The Gaffer's picture

Try this:

SELECT   vc.Name AS 'Computer Name'
       , vc.[User] AS 'Primary User'
       , vc.[IP Address]
       , hw.Model
       , hw.[Identifying Number] AS 'Serial Number'
       , vc.[OS Name]
       , vl.Name [Location]
       , ph.[Phone Number]
       , acid.[Client Date] AS 'Last Check In'
FROM vCollection coll
INNER JOIN CollectionMembership cm ON cm.CollectionGuid = coll.Guid
INNER JOIN vComputer vc ON cm.ResourceGuid = vc.Guid
INNER JOIN vHWComputerSystem hw ON vc.Guid = hw._ResourceGuid
INNER JOIN Inv_AeX_AC_Identification acid ON vc.Guid = acid._ResourceGUID
LEFT OUTER JOIN ResourceAssociation ra ON ra.ParentResourceGuid = vc.Guid
            AND ra.ResourceAssociationTypeGuid ='05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vLocation vl ON ra.ChildResourceGuid = vl._ResourceGuid
LEFT OUTER JOIN Inv_Phone_Details ph ON ph._ResourceGuid = vl._ResourceGuid
where coll.Name = 'ALL Laptops'
ORDER BY vc.Name
SOLUTION
Briandr88's picture

Hi,

I will give it a try on Wednesday. I take it with the join straightened out I should be able to add in vl.Address, vl.City, vl.State without any problems?

Thanks again.