This is a SQL query... so you should run it from SQL server Management Studio.
From: [email protected] [mailto:[email protected]] On Behalf Of Brian McDonald Sent: Thursday, March 31, 2016 2:12 PM To: [email protected] Subject: Re: [mssms] Microsoft True Up Report Garth - thank you. Should this be ran from Queries under Monitoring work space or in SQL? Brian ________________________________ From: [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>> on behalf of Garth Jones <[email protected]<mailto:[email protected]>> Sent: Thursday, March 31, 2016 12:08 PM To: [email protected]<mailto:[email protected]> 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: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Brian McDonald Sent: Thursday, March 31, 2016 11:56 AM To: [email protected]<mailto:[email protected]> 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: [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>> on behalf of [email protected]<mailto:[email protected]> <[email protected]<mailto:[email protected]>> Sent: Thursday, March 31, 2016 10:39 AM To: [email protected]<mailto:[email protected]> 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(r) 6 edge, an AT&T 4G LTE smartphone -------- Original message -------- From: Brian McDonald <[email protected]<mailto:[email protected]>> Date: 3/31/2016 10:17 AM (GMT-06:00) To: [email protected]<mailto:[email protected]> 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 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.
