need to pull the all installed software for each machines in a device collection

Question

Syntax error - How To Manage Devices Community Forum - Welcome to the world of Device Management! This is community build by Device Management Admins for Device Management Admins❤️ Ask your questions!! We are here to help you! - need to pull the all installed software for each machines in a device collectionI 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

in progress 0
VAISHNAV. K 2 months 7 Answers 62 views Beginner 0

Answers ( 7 )

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

  2. 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.

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

  4. 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/

Leave an answer

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