I copied and pasted what Garth had typed originally into a new report in SCCM console and it worked like a dream, maybe try that?
Andrew Hurst Systems Engineer Mourant Ozannes BVI | CAYMAN ISLANDS | GUERNSEY | HONG KONG | JERSEY | LONDON D +44 1534 676 772 | T +44 1534 676 000 | F +44 1534 676 333 mourantozannes.com<http://www.mourantozannes.com/> LinkedIn<http://www.linkedin.com/company/mourant-ozannes/> | Twitter<http://www.twitter.com/MourantOzannes/> From: listsadmin@lists.myitforum.com [mailto:listsadmin@lists.myitforum.com] On Behalf Of Brian McDonald Sent: 04 April 2016 16:18 To: ms...@lists.myitforum.com Subject: Re: [mssms] Microsoft True Up Report When I run the query without hardcoding it, the query fails. I'm using SQL mgmt studio and pasting the code into a query and selecting execute against the SCCM DB. When I do this, however, I get: "Must declare the scalar variable "@CollID". I must not be doing something right. I'm not getting a prompt for the collection ID. When I hardcode the query as mentioned below. I get the following error: "The SELECT permission was denied on the object 'v_FullCollectionMembership', database 'CM_LV1', schema 'dbo'. Any help would be appreciated. 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<mailto:%22The%20SELECT%20permission%20was%20denied%20on%20the%20object%20'v_FullCollectionMembership',%20database%20'CM_LV1',%20schema%20'dbo'.%0b%0bAny%20help%20would%20be%20appreciated.%0b%0bBrian%0b________________________________%0bFrom:%20listsad...@lists.myitforum.com%3cmailto:listsadmin@lists.myitforum.com%3e%20%3clistsad...@lists.myitforum.com%3cmailto:listsadmin@lists.myitforum.com%3e%3e%20on%20behalf%20of%20garth%20jones%20%3cga...@enhansoft.com%3cmailto:ga...@enhansoft.com%3e%3e%0bSent:%20Thursday,%20March%2031,%202016%2012:08%20PM%0bTo:%20ms...@lists.myitforum.com%3cmailto:ms...@lists.myitforum.com%3e%0bSubject:%20RE:%20[mssms]%20Microsoft%20True%20Up%20Report%0b%0b%0bThe%20@Collid%20is%20meant%20to%20be%20populated%20by%20a%20prompt%20query.%20However%20you%20can%20hardcode%20it%20like%20this.%0b%0b%0b%0bDeclare%20@CollID%20as%20varchar(16)%20=%20'SMS00001'%0b%0b%0b%0bSELECT%20distinct%0b%0bDisplayName0,%0b%0bCount(arp.ResourceID)%20AS%20'Count',%0b%0bPublisher0,%0b%0b@CollID%20as%20CollectionID%0b%0bFROM%0b%0bdbo.v_Add_Remove_Programs%20arp%0b%0bJOIN%20dbo.v_FullCollectionMembership%20fcm%20on%20arp.ResourceID%20=%20fcm.ResourceID%0b%0bWHERE%0b%0bfcm.CollectionID%20=%20@CollID%0b%0bAND%20(Publisher0%20LIKE%20'Microsoft%25')%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Hotfix%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Security%20Update%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Update%20for%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25.NET%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Viewer%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Language%20Pack%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Internet%20Explorer%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25MSXML%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25SDK%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25C++%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Redistributable%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Search%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25SMS%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Silverlight%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Live%20Meeting%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25(KB%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Office%20Web%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Office%20%25Proof%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Server%20%25Proof%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Office%20%25Shared%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%Baseline%20Security%20Analyzer%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25Compatibility%20Pack%25'%0b%0bAND%20DisplayName0%20NOT%20LIKE%20'%25User%20State%20Migration%20Tools%25'%0b%0bGROUP%20BY%0b%0bDisplayName0,%0b%0bPublisher0%0b%0bORDER%20BY%0b%0bPublisher0%0b%0b%0b%0b%0b%0b%0b%0b%0b%0bFrom:%20listsadmin@lists.myitfor> - "Must declare the scalar variable "@CollID". ________________________________ From: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com%3cmailto:listsadmin@lists.myitforum.com>> <listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com%3cmailto:listsadmin@lists.myitforum.com>>> on behalf of tgonza...@tins-swtx.com<mailto:tgonza...@tins-swtx.com<mailto:tgonza...@tins-swtx.com%3cmailto:tgonza...@tins-swtx.com>> <tgonza...@tins-swtx.com<mailto:tgonza...@tins-swtx.com<mailto:tgonza...@tins-swtx.com%3cmailto:tgonza...@tins-swtx.com>>> Sent: Thursday, March 31, 2016 10:39 AM To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com%3cmailto: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(r) 6 edge, an AT&T 4G LTE smartphone -------- Original message -------- From: Brian McDonald <mcdonald...@hotmail.com<mailto:mcdonald...@hotmail.com<mailto:mcdonald...@hotmail.com%3cmailto:mcdonald...@hotmail.com>>> Date: 3/31/2016 10:17 AM (GMT-06:00) To: ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com%3cmailto: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<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<http://smsug.ca/blogs/garth_jones/archive/2013/02/07/microsoft-true-up-report.aspx>> smsug.ca<http://smsug.ca><http://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/<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. The information contained in this e-mail (and any attachments) is strictly confidential and may also be legally privileged. If you are not the intended recipient of this e-mail please do not read, print, re-transmit, store, or act in reliance on it or any attachments. Instead, please notify the sender by return e-mail and then immediately permanently delete it from your system. We are not liable for any views or opinions expressed by the sender where this is a non-business e-mail. We may monitor email communications in accordance with applicable law and regulations. Mourant Ozannes is a Jersey partnership. A list of partners' names is open to inspection at 22 Grenville Street, St Helier, Jersey JE4 8PX, Channel Islands. Additional terms and conditions and information on other Mourant Ozannes network members are available on our website: www.mourantozannes.com
