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 1 year 2020-07-23T17:04:33+05:30 7 Answers 94 views Beginner 0

Answers ( 7 )

    0
    2020-07-28T20:40:10+05:30

    check below screenshot pasted in snipboard

    https://snipboard.io/zbroT7.jpg

    0
    2020-07-28T20:34:43+05:30

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

    0
    2020-07-28T20:26:50+05:30

    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.

    1
    2020-07-24T12:01:50+05:30

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

    0
    2020-07-24T02:34:09+05:30

    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 .