Make a report with this in it

SELECT DisplayName0, Count(ProdID0) AS 'Count', Publisher0, Version0, @CollID as CollectionID

FROM v_Add_Remove_Programs arp

JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID

WHERE DisplayName0 Like '%Microsoft Office%' and fcm.CollectionID=@CollID

GROUP BY DisplayName0, Publisher0, Version0

ORDER BY Publisher0, Version0

Then a Prompt on that report. Make the Name of the prompt collID and use this for the SQL in the prompt

begin
 if (@__filterwildcard = '')
  select CollectionID, Name from v_Collection order by Name
 else
  select CollectionID, Name from v_Collection
  WHERE CollectionID like @__filterwildcard
  order by Name
end

like this

It will give you a report like this

That drills down to this

,   

From: admin@lists.myITforum.com [mailto:admin@lists.myITforum.com] On Behalf Of Vicki Williams
Sent: Tuesday, July 07, 2009 4:43 PM
To: mssms@lists.myitforum.com
Subject: [mssms] Office Report - SQL Help

I have been asked to do a report of workstations that have multiple versions of Office installed. Anything that I am capable of doing involves manually going thru a spreadsheet and picking the machine names out. I was hoping someone on the list would have a SQL query that would be faster and less tedious.

Something like the following would be great:

Machine1            Microsoft Office Professional Edition 2003

Machine1            Microsoft Office XP Professional

Machine2            Microsoft Office Professional 2007

Machine2            Microsoft Office Enterprise 2007

(I can’t wait to find the tech who did the last install. ;-))

Thanks! I would really appreciate any help that I can get.

Vicki Williams

ConfigMgr Admin

Wake Forest University Baptist Medical Center

Winston-Salem, NC

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/

==============
Missed an email? Check out the list archive:
http://myitforum.com/cs2/blogs/smslist/