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 )

    1
    2020-09-21T17:54:28+05:30

    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%’

    Best answer

Leave an answer

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