SCCM report for all software installed on all computers

The following report will give you all the installed software’s (applications from the environment) and removing the installed hotfixes, security updates and any other updates. If you want to remove any other product from the report, use DisplayName0 NOT LIKE 'Product name %'.
All the inventoried software will be grouped using displayname, version and publisher to remove all the duplicate records.

**************************************************************************
SELECT    DisplayName0 AS [Product Name], Version0 AS [Product Version], Publisher0 AS [Publisher Name]
FROM      v_GS_ADD_REMOVE_PROGRAMS
WHERE     (DisplayName0 NOT LIKE 'Hotfix for %') AND (DisplayName0 NOT LIKE 'Security Update for %') AND (DisplayName0 NOT               LIKE 'Update %')
GROUP BY  DisplayName0, Version0, Publisher0
**************************************************************************
To include machine name in the report;
**************************************************************************
Select DISTINCT SYS.Netbios_Name0 as 'Machine name', AR.DisplayName0 AS 'Application name', AR.Publisher0 as 'Vendor', AR.Version0 as 'Application Version'
FROM v_GS_ADD_REMOVE_PROGRAMS AR
JOIN v_R_System SYS ON AR.ResourceID=SYS.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
-- change the collection ID to relevent collection ID
WHERE fcm.CollectionID = 'SMS00001'
ORDER By SYS.Netbios_Name0, AR.DisplayName0, AR.Publisher0, AR.Version0
**************************************************************************

More SCCM custom reports can be found here