[mssms] Query Help: Add device name for each installed software

[Brian McDonald]

Good morning,


I am running the following query and would like to add the device name for each 
instance of the products identified in this query. Anyone know how I can do 
this?


Thanks,

Brian


SELECT distinct
    DisplayName0,
    Count(arp.ResourceID) AS 'Count',
    Publisher0,
    @CollID as CollectionID
FROM
    dbo.v_Add_Remove_Programs arp
JOIN dbo.v_FullCollectionMembership fcm on arp.ResourceID = fcm.ResourceID
WHERE
    fcm.CollectionID = @CollID
    AND (Publisher0 LIKE 'Microsoft%')
    AND DisplayName0 NOT LIKE '%Hotfix%'
    AND DisplayName0 NOT LIKE '%Security Update%'
    AND DisplayName0 NOT LIKE '%Update for%'
    AND DisplayName0 NOT LIKE '%.NET%'
    AND DisplayName0 NOT LIKE '%Viewer%'
    AND DisplayName0 NOT LIKE '%Language Pack%'
    AND DisplayName0 NOT LIKE '%Internet Explorer%'
    AND DisplayName0 NOT LIKE '%MSXML%'
    AND DisplayName0 NOT LIKE '%SDK%'
    AND DisplayName0 NOT LIKE '%C++%'
    AND DisplayName0 NOT LIKE '%Redistributable%'
    AND DisplayName0 NOT LIKE '%Search%'
    AND DisplayName0 NOT LIKE '%SMS%'
    AND DisplayName0 NOT LIKE '%Silverlight%'
    AND DisplayName0 NOT LIKE '%Live Meeting%'
    AND DisplayName0 NOT LIKE '%(KB%'
    AND DisplayName0 NOT LIKE '%Office Web%'
    AND DisplayName0 NOT LIKE '%Office %Proof%'
    AND DisplayName0 NOT LIKE '%Server %Proof%'
    AND DisplayName0 NOT LIKE '%Office %Shared%'
    AND DisplayName0 NOT LIKE '%Baseline Security Analyzer%'
    AND DisplayName0 NOT LIKE '%Compatibility Pack%'
    AND DisplayName0 NOT LIKE '%User State Migration Tools%'
GROUP BY
    DisplayName0,
    Publisher0
ORDER BY
    Publisher0