you just want the drill down MVLS report. it is built in On Fri, Apr 29, 2016 at 3:42 PM, Garth Jones <ga...@enhansoft.com> wrote:
> 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. > > > > > > > > > > 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 <listsadmin@lists.myitforum.com> > on behalf of Garth Jones <ga...@enhansoft.com> > *Sent:* Friday, April 29, 2016 11:25:27 AM > *To:* 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 <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 > > > > > > > > > >
