BDNA makes this SOOOO much easier! I've been using it for about 5 years now and love it.
My guess is the 1e product is good too but I haven't personally used it. I think 1e may have licensed the BDNA catalog recently but I'm not sure about that. From what I've seen the 1e product is more point and clickey where BDNA simply gives you nice clean data and some canned reports. BDNA is easy to install, literally 10 min and it's done. On Mar 31, 2016, at 1:12 PM, Brian McDonald <mcdonald...@hotmail.com<mailto:mcdonald...@hotmail.com>> wrote: Garth - thank you. Should this be ran from Queries under Monitoring work space or in SQL? 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: Thursday, March 31, 2016 12:08 PM To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com> Subject: RE: [mssms] Microsoft True Up Report The @Collid is meant to be populated by a prompt query. However you can hardcode it like this. Declare @CollID as varchar(16) = 'SMS00001' 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 From: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com> [mailto:listsadmin@lists.myitforum.com] On Behalf Of Brian McDonald Sent: Thursday, March 31, 2016 11:56 AM To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com> Subject: Re: [mssms] Microsoft True Up Report Were you able to get the query Garth blogged about to work? I get "@CollID - "Must declare the scalar variable "@CollID". ________________________________ From: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com> <listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com>> on behalf of tgonza...@tins-swtx.com<mailto:tgonza...@tins-swtx.com> <tgonza...@tins-swtx.com<mailto:tgonza...@tins-swtx.com>> Sent: Thursday, March 31, 2016 10:39 AM To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com> Subject: RE: [mssms] Microsoft True Up Report I am going through one right now, I'm using MAP 9.3 and using custom queries from what garth has blogged. As of yesterday, I wrote a new query that is pulling back when last used and we will be doing a reclamation on those items soon. The map tool is more in line for sql to look at core/proc, and is also used since we have over 15 domains in house. Sent via the Samsung Galaxy S® 6 edge, an AT&T 4G LTE smartphone -------- Original message -------- From: Brian McDonald <mcdonald...@hotmail.com<mailto:mcdonald...@hotmail.com>> Date: 3/31/2016 10:17 AM (GMT-06:00) To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com> Subject: [mssms] Microsoft True Up Report Good day, I have been tasked with putting together the Microsoft True Up report. I've been doing some searching online and it looks like folks are saying there are limitations with what you can do with Asset Intelligence. I saw a query posted by Garth here: http://smsug.ca/blogs/garth_jones/archive/2013/02/07/microsoft-true-up-report.aspx - Although the query isn't working at it states "@CollID - "Must declare the scalar variable "@CollID". Microsoft True Up Report - Garth Jones<http://smsug.ca/blogs/garth_jones/archive/2013/02/07/microsoft-true-up-report.aspx> smsug.ca<http://smsug.ca> Pingback from Microsoft True Up Report | My CMS. June 10, 2013 5:41 PM This Blog I'd like to know how others are doing True Up Reports? Is BDNA Normalize worth looking into? Here is another guide that looks very intriguing. http://myitforum.com/myitforumwp/2012/07/13/performing-a-software-true-up-in-under-5-minutes/ I would need to convince management to purchase the software, of course. Love to hear what people know about this to get me down the right path. Thank you, Brian M. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer.
