Hi,
Thanks. One thing I have noticed once I had a chance to run this in SQL Management Studio is I am getting NULL for manufacturer, model, serial number, and ram. With regards to Processor I am getting some data on the rows returned, but I am also getting NULL on some rows as well.
Here is a query similiar to the one you revised which I know works (by itself) in an attempt to integrate it with yours.
I don't know if the code will test out clean. What is messing me up is with the needed JOINS. I am not too great with queries so knowing which JOINS are needed can be a pain in the butt.
Can you tell me what you think?
SELECT DISTINCT
vCom.[Name] as [Computer Name]
,vCom.[Domain] AS [Domain Name]
,vCom.[OS Name] AS [Operating System]
,vCom.[OS Revision] AS [Service Pack]
,cs.[Model]
,cs.[Total Physical Memory (Bytes)]
,cs.[Identifying Number] AS [Serial Number]
,ac.[Last Logon User]
,pr.Model AS 'Processor'
, I.Name AS 'Location'
,vc.[User] AS 'Primary Owner'
FROM
CollectionMembership cm
INNER JOIN
vCollection vColl
ON cm.[CollectionGuid] = vColl.[Guid]
INNER JOIN ResourceAssociation AS loc ON (loc.ChildResourceGuid = vc.Guid OR
loc.ParentResourceGuid = vc.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
INNER JOIN Inv_HW_Chassis AS ch
ON ch._ResourceGuid = vc.Guid
INNER JOIN vItem AS I
ON loc.ChildResourceGuid = I.Guid
INNER JOIN vHWProcessor AS pr
ON vc.Guid = pr._ResourceGuid
INNER JOIN
vComputer vCom
ON cm.[ResourceGuid] = vCom.[Guid]
INNER JOIN
Inv_AeX_AC_Identification ac
on ac.[_ResourceGuid] = vCom.[Guid]
LEFT JOIN
vHWComputerSystem cs
ON cs.[_ResourceGuid] = vCom.[Guid]
WHERE (ch.[Chassis Package Type] IS NULL) OR (ch.[Chassis Package Type] = 3) OR (ch.[Chassis Package Type] = 4) OR (ch.[Chassis Package Type] = 5) OR (ch.[Chassis Package Type] = 6) OR (ch.[Chassis Package Type] = 7) AND vColl.[Name] = 'Windows 2000/XP/2003/Vista/2008/7 Computers' and [Identifying Number] is Not Null AND vCom.[IsManaged] = 1
ORDER BY
vCom.[Name]