Trying to perform a query on a group of network servers, but getting an odd syntax error. The top query will run successfully, but as soon as the Add/Remove Progs is added, "The multi-part identifier "SYS.ResourceID" could not be bound." In the end I want to be able to query a group of computers and view the Add/Remove Program list from each one.
Working Code to pull server type, service pack, etc:
SELECT DISTINCT SYS.Name, OPSYS.Caption0, OPSYS.CSDVersion0, OPSYS.InstallDate0,OPSYS.LastBootUpTime0 FROM v_FullCollectionMembership SYS JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID WHERE SYS.CollectionID = @CollID ORDER BY SYS.Name,OPSYS.Caption0
Not working:
SELECT DISTINCT SYS.Name, OPSYS.Caption0, OPSYS.CSDVersion0, OPSYS.InstallDate0,OPSYS.LastBootUpTime0, ARP.DisplayName0, ARP.Version0 FROM v_FullCollectionMembership SYS , v_Add_Remove_Programs ARP JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID WHERE SYS.CollectionID = @CollID ORDER BY SYS.Name,OPSYS.Caption0