Simple Query like this :Select * from Win32Reg_AddRemovePrograms
Question
How to get the same output by using SQL Query for specific collection Select * from Win32Reg_AddRemovePrograms.
I want to get details like below for each machine in collection from SCCM SQL.
Caption CSName Description FixComments HotFixID InstallDate InstalledBy InstalledOn Name ServicePackInEffect Status
http://support.microsoft.com/?kbid=4576478 Update KB4576478 NT AUTHORITYSYSTEM 9/13/2020
http://support.microsoft.com/?kbid=4561600 Security Update KB4561600 NT AUTHORITYSYSTEM 9/4/2020
http://support.microsoft.com/?kbid=4570334 Security Update KB4570334 NT AUTHORITYSYSTEM 9/5/2020
https://support.microsoft.com/help/4577266 Security Update KB4577266 NT AUTHORITYSYSTEM 9/13/2020
https://support.microsoft.com/help/4571756 Update KB4571756 NT AUTHORITYSYSTEM 9/13/2020
Answer ( 1 )
If you are writing query and want the result based on collection your can use as below, first you need to declare collection and then you need to join the collection.Below is sample query to get installed software report from add and remove program for a particular collection.
Try to change the columns according to your query. Let me know if you find difficult.
Declare @CollID char(8)
Set @CollID = ‘XXXXXXXX’
SELECT
COMP.Name0 AS ‘Machine Name’,
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_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%’