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]] On
Behalf Of Brian McDonald
Sent: Thursday, March 31, 2016 11:56 AM
To: [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.
