In my corner of the world I get asked to report on how many different versions of specific products are installed. Garth Jones posted a Blog today on "What is the average number or items within the Add/Remove per PC?” Well, me being the lazy person that I am, I copied it and pasted it into a new view in the 'ole SMS DB and modified it to report on a specific product: Symantec Antivirus. Coolness indeed! I created a new web report and sent that to the Antivirus dude for his edification.
Here is the query that I used:
SELECT TOP 100 PERCENT v_GS_COMPUTER_SYSTEM.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,
v_GS_ADD_REMOVE_PROGRAMS.Version0, v_GS_ADD_REMOVE_PROGRAMS.Publisher0, v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM dbo.v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM INNER JOIN
dbo.v_GS_ADD_REMOVE_PROGRAMS v_GS_ADD_REMOVE_PROGRAMS ON
v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID INNER JOIN
dbo.v_CM_RES_COLL_SMS00001 v_CM_RES_COLL_SMS00001 ON
v_GS_COMPUTER_SYSTEM.ResourceID = v_CM_RES_COLL_SMS00001.ResourceID
WHERE (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = 'Symantec AntiVirus')
ORDER BY v_GS_ADD_REMOVE_PROGRAMS.ProdID0 DESC, v_GS_COMPUTER_SYSTEM.Name0 BTW, Garth's Blog is here: http://smsug.ca/blogs/garth_jones/archive/2007/05/31/417.aspx