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

Fri, 29 Apr 2016 13:50:18 -0700 

I hear what you are ask for but it is a dumb request. Whoever is asking for 
this, doesn't know what they will get when you run this query.  
http://www.enhansoft.com/blog/how-to-perform-a-basic-software-audit


I have 37 computers in my lab and I get 1700+ rows for this query.


[cid:image001.png@01D1A235.718EB7C0]


SELECT distinct
    FCM.Name,
    DisplayName0,
    Count(distinct arp.ResourceID) AS 'Count',
    Publisher0
FROM
    dbo.v_Add_Remove_Programs arp
       JOIN dbo.v_FullCollectionMembership fcm on arp.ResourceID = 
fcm.ResourceID
WHERE
    fcm.CollectionID = 'sms00001'
    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
    FCM.Name,
    DisplayName0,
    Publisher0
ORDER BY
    FCM.Name,
    Publisher0






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


This is what I really need at this point. If there is a better way to get this, 
I'd love to know.



I need a query to find all versions of the following Products:



Microsoft Office, Viso, Project, Exchange Server, Dynamics CRM, SharePoint 
Server, Project Server, SQL Server, Visual Studio


This must include the device name for each installed instance of the above 
products.



Thanks,

Brian

________________________________
From: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com> 
<listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com>> on 
behalf of Garth Jones <ga...@enhansoft.com<mailto:ga...@enhansoft.com>>
Sent: Friday, April 29, 2016 11:25:27 AM
To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com>
Subject: [mssms] RE: Query Help: Add device name for each installed software

Err why do you want to add the Name to this query? Did you know that there is 
an average of 189 row per computer? Did you know that if you add computer name 
all of your counts will be 1 and in a few case 2?




From: listsadmin@lists.myitforum.com<mailto: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<mailto: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

Reply via email to