Simple Query like this :Select * from Win32Reg_AddRemovePrograms


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  Update KB4576478 NT AUTHORITYSYSTEM 9/13/2020  Security Update KB4561600 NT AUTHORITYSYSTEM 9/4/2020 Security Update KB4570334 NT AUTHORITYSYSTEM 9/5/2020  Security Update KB4577266 NT AUTHORITYSYSTEM 9/13/2020  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’
    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
    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%’

    Best answer

Leave an answer

Sorry, you do not have permission to answer to this question .