need to pull the all installed software for each machines in a device collection
Question
I have a requirement to pull the all installed software for each machine in a device collection. Please help me with the SQL query.
I have tried with default reports, but it didn’t meet my requirements
Answers ( 7 )
check below screenshot pasted in snipboard
https://snipboard.io/zbroT7.jpg
It is only the inverted commas when copying and pasting, try below one again
Declare @CollID char(8)
Set @CollID = ‘C0001AF3’
select distinct COMP.Name0 as ‘Hostname’,
ARP.DisplayName0 as ‘Installed Applications’,
ARP.Version0 as ‘Version’,
ARP.InstallDate0 as ‘Install Date’
FROM v_Add_Remove_Programs ARP
JOIN v_FullCollectionMembership FCM
on ARP.ResourceID = FCM.ResourceID
JOIN v_GS_COMPUTER_SYSTEM COMP
on ARP.ResourceID = COMP.ResourceID
WHERE FCM.CollectionID = @CollID
AND ARP.DisplayName0 IS NOT NULL
AND ARP.ProdId0 NOT LIKE ‘%(KB%)%’
AND ARP.DisplayName0 NOT LIKE ‘%Update%’
AND ARP.DisplayName0 NOT LIKE ‘%Service Pack%’
AND ARP.displayname0 NOT LIKE ‘%Hotfix%’
AND ARP.displayname0 NOT LIKE ‘%Microsoft Office%’
What syntax error you are getting, I am using same query in my environment and it runs perfectly fine. Please provide the screenshot of the page where you are running this query.
added the screenshot
Try below query, change the collection ID accordingly, be careful on running collection having lot of devices, first try on a small collection.If you want Microsoft office result also remove the last line of query.
Declare @CollID char(8)
Set @CollID = ‘C0001AF3’
select distinct COMP.Name0 as ‘Hostname’,
ARP.DisplayName0 as ‘Installed Applications’,
ARP.Version0 as ‘Version’,
ARP.InstallDate0 as ‘Install Date’
FROM v_Add_Remove_Programs ARP
JOIN v_FullCollectionMembership FCM
on ARP.ResourceID = FCM.ResourceID
JOIN v_GS_COMPUTER_SYSTEM COMP
on ARP.ResourceID = COMP.ResourceID
WHERE FCM.CollectionID = @CollID
AND ARP.DisplayName0 IS NOT NULL
AND ARP.ProdId0 NOT LIKE ‘%(KB%)%’
AND ARP.DisplayName0 NOT LIKE ‘%Update%’
AND ARP.DisplayName0 NOT LIKE ‘%Service Pack%’
AND ARP.displayname0 NOT LIKE ‘%Hotfix%’
AND ARP.displayname0 NOT LIKE ‘%Microsoft Office%’
It’s very easy And I think if you look at the attached app list and you have an option to change the collection
https://www.anoopcnair.com/sccm-application-deployment-custom-report-sql-query-configmgr/
Ankit Shukla, I have tried to test the query in a test collection. But ur query have few syntax errors.Can u plz check those