Client Management Suite

Hi,

My company wants to report on usage of applictions PER month so that we can see our users are, or are not using applications. This is buy seeing that in January they used an app say 10 times and in February they used it 20 times.

Any given report should show  :-

Total usage 30 Times
Reported on "February" ONLY,  it should show a run total of 20 times NOT the incremental 30.


What is wrong with the way i am filtering data? Here is an example of the output i get reporting on a product. As you can see the total run times are different! suposedly for the same filtered month (September) but the dates totally different for the last run, and so are the "discovered" dates - these should match for the same PC (W-043776)

This makes it hard to beleive the reports. can anyone help?

Manufacturer	File Name	Discovered	Version	User	Month	Computer Name	Computer Domain	# of Uses	Last Start	User1	Month1
Microsoft Corporation	msaccess.exe	12/02/2009 13:14	10.0.6771	a28701	September	W-043776		2	12/02/2009 13:15	a28701	September
Microsoft Corporation	msaccess.exe	21/04/2009 14:07	10.0.6771	a28701	September	L-036558		1	21/04/2009 14:07	a28701	September
Microsoft Corporation	msaccess.exe	11/06/2009 10:58	10.0.6771	a28701	September	W-043776		1	11/06/2009 10:58	a28701	September

Code i'm using:



IF '%_AppName%' IN ('Microssoft Office(All)','Microsoft Office With Access','Microsoft FrontPage','Microsoft Visio','Microsoft Publisher','Microsoft Project',    
'Microsoft One Note','Adobe Acrobat Reader (Old)','Adobe Acrobat Reader (New)','Adobe Acrobat Distiller')                
BEGIN                  
SELECT Manufacturer, [File Name],                         
CASE LEFT(amms.[Product Version], 2)                        
 WHEN '7.' THEN '95'                        
 WHEN '8.' THEN '97'                        
 WHEN '9.' THEN '2000'                        
 WHEN '10' THEN 'XP'                        
 WHEN '11' THEN '2003'                        
 WHEN '12' THEN '2007'                
 WHEN '(u' THEN '2007'                        
 ELSE amms.[Product Version]         
END AS [Version],   acid.Name  AS [Computer Name], acid.Domain AS [Computer Domain],Pname.[User] AS [User], Pname.[Month] AS [Month],                     
SUM([Run Count]) AS [# of Uses], MAX(amms.[Last Start]) AS [Last Start] , acid._ResourceGuid                              
FROM Inv_AeX_AM_Monthly_Summary_Archive amms                             
JOIN Inv_AeX_AC_Identification acid                     
ON amms._ResourceGuid = acid._ResourceGuid        
JOIN Inv_AeX_AC_Primary_User Pname        
ON amms._resourceGuid = PName._ResourceGuid        
WHERE  amms.[File Name] LIKE '%' + '%_AppName%' + '%'           
and amms.Manufacturer LIKE '%Microsoft%'           
AND Pname.[Month] Like '%' + '%Month%' + '%'           
GROUP BY Manufacturer, [File Name],   acid.Name,  acid.Domain, Pname.[User], Pname.[Month],                   
CASE LEFT(amms.[Product Version], 2)                        
 WHEN '7.' THEN '95'                        
 WHEN '8.' THEN '97'                        
 WHEN '9.' THEN '2000'                        
 WHEN '10' THEN 'XP'                        
 WHEN '11' THEN '2003'                        
 WHEN '12' THEN '2007'                
 WHEN '(u' THEN '2007'                        
 ELSE amms.[Product Version]                        
END  , acid._ResourceGuid            
ORDER BY [# of Uses] DESC, [Name] ASC                  
END                  
ELSE BEGIN                  
SELECT Manufacturer, [File Name],[Discovered], amms.[Product Version] AS [Version], Pname.[User] AS [User], Pname.[Month] AS [Month],                  
acid.Name  AS [Computer Name], acid.Domain AS [Computer Domain],                    
SUM([Run Count]) AS [# of Uses], MAX(amms.[Last Start]) AS [Last Start] , acid._ResourceGuid, PName._ResourceGuid, Pname.[User], Pname.[Month]                
 FROM Inv_AeX_AM_Monthly_Summary_Archive amms        
JOIN Inv_AeX_AC_Primary_User Pname        
ON amms._resourceGuid = PName._ResourceGuid        
JOIN Inv_AeX_AC_Identification acid                     
ON amms._ResourceGuid = acid._ResourceGuid                      
WHERE amms.[File Name] LIKE '%' + '%_AppName%' + '%'      
AND Pname.[Month] Like '%' + '%Month%' + '%'            
GROUP BY Manufacturer, [File Name],   acid.Name,  acid.Domain,                    
amms.[Product Version],[Discovered], acid._ResourceGuid, Pname._ResourceGuid, Pname.[User], Pname.[Month]                                     
ORDER BY [# of Uses] DESC, [Name] ASC                  
END

Xander,
Glad to see you're using my report code for App Metering.  Your report output cannot possibly be from the code you've pasted though since that code does not include the "Discovered Date" column.  I'm not sure why you're getting semi-duplicate rows, but it may be due to the MAX(amms.[Last Start]) being in there; that part may not be necessary (it has been a while since I wrote this code).  Try removing the "MAX()" function and the "GROUP BY" clause and see what you get. 

NOTE: Be sure to clone your existing report; too many times I have had a nice functional report and started messing with it to add columns, etc...then before you know it that report is completely broken and won't run anymore.

Hi,

thanks for the reply!  Very handy your report, it's enabled me (as a person who doesn't code) to modify it around what i want.

i'll give it a try taking those out.

Good luck.  Also, in the "ELSE" section, remove the PNAME._ResourceGuid; there is no need to include this and it could be contributing to the duplicates.