In this blog post ,we will talk about how to get list of all applications installed on specific computer or at collection level excluding software updates . Running the report on collection level will be huge report because each client will have X number of applications and if there are 100 clients in collection ,it will be 100*X will be huge list of excel spread sheet.
You can also customise to add other sec updates which you dont want them in report by adding AND A.DisplayName0 NOT LIKE "name" at the end of report.
SELECT A.DisplayName0,A.InstallDate0 A.Version0,A.Publisher0
FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B
WHERE A.ResourceID = B.ResourceID
AND A.DisplayName0 NOT LIKE ‘Hotfix for %’
AND A.DisplayName0 NOT LIKE ‘Security Update for %’
AND A.DisplayName0 NOT LIKE ‘Update for Microsoft %’
AND A.DisplayName0 NOT LIKE ‘Update for Office %’
AND A.DisplayName0 NOT LIKE ‘Update for Outlook %’
AND A.DisplayName0 NOT LIKE ‘Update for Windows %’
AND A.DisplayName0 NOT LIKE ‘Windows XP Hotfix%’AND Name0 = @computer
GROUP BY A.DisplayName0,A.InstallDate0,A.Version0, A.Publisher0
ORDER BY A.DisplayName0
Prompt for creating @ computer:
select Name0 from v_GS_COMPUTER_SYSTEM
The following SQL code will help you to get the list of applications against specific collection:
Replace the collection .
SELECT COMP.Name0 AS 'Machine Name', COMP.UserName0 AS 'Username', COMP.Manufacturer0 AS 'Make',
COMP.Model0 AS 'Model', COMP.SystemType0 AS 'Architecture', COMP.TotalPhysicalMemory0/1024 AS 'RAM',
ARP.Publisher0 AS 'Publisher', ARP.DisplayName0 AS 'Program', ARP.Version0 AS 'Version',
ARP.InstallDate0 AS 'Install Date', ARP.ProdID0 AS 'Product ID'
FROM v_Add_Remove_Programs ARP
JOIN v_GS_COMPUTER_SYSTEM COMP on ARP.ResourceID = COMP.ResourceID
JOIN v_FullCollectionMembership FCM on ARP.ResourceID = FCM.ResourceID
WHERE FCM.CollectionID ='PS10000A'
AND ARP.DisplayName0 NOT LIKE 'Hotfix for %'
AND ARP.DisplayName0 NOT LIKE 'Security Update for %'
AND ARP.DisplayName0 NOT LIKE 'Update for Microsoft %'
AND ARP.DisplayName0 NOT LIKE 'Update for Office %'
AND ARP.DisplayName0 NOT LIKE 'Update for Outlook %'
AND ARP.DisplayName0 NOT LIKE 'Update for Windows %'