Recently we had the need to get a list of all software installed on a group of systems in a collection in ConfigMgr. There was an existing report which provided this for a single computer but we needed to put it together for a collection of computers (not based upon what is in add/remove programs). The results were our query below: (replace <CollectionID> with your actual collection name)
Select DISTINCT SYS.Netbios_Name0,SYS.Resource_Domain_OR_Workgr0,SP.CompanyName, SP.ProductName, SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID = '<CollectionID>'
ORDER By SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SP.ProductVersion
Update 6/7/2017: Chad Simmons reviewed this and found that there may be a cut and paste issue with the original query above with the quotes in the query (I have replaced them so hopefully this isn’t an issue now). This query works on the current version of Configuration Manager per the screenshot below.