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

[Daniel Ratliff]

Add fcm.Name as your first column. It will pull the name from the 
v_fullcollectionmembership view.

Also, your not likes with double % makes me shudder. Your poor SQL server. I 
would replace those with a not in ('name', 'name', 'name', etc) and get rid of 
the likes altogether.

Daniel Ratliff

From: listsadmin@lists.myitforum.com [mailto:listsadmin@lists.myitforum.com] On 
Behalf Of Brian McDonald
Sent: Friday, April 29, 2016 11:51 AM
To: ms...@lists.myitforum.com
Subject: [mssms] Query Help: Add device name for each installed software


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



The information transmitted is intended only for the person or entity to which 
it is addressed
and may contain CONFIDENTIAL material.  If you receive this 
material/information in error,
please contact the sender and delete or destroy the material/information.