Wednesday, May 6, 2015

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]
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'
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

No comments:

Post a Comment