Application Usage by Computer (Enhanced)
We've all used the out-of-the-box Altiris Application Metering report for Application Usage by Computer. Well while it works OK, it was not quite what we were looking for, particularly when looking at Microsoft applications. It also could get a little tricky if you tried to roll up details by version. It would get tricky because you had different patch revisions of various MS apps (Winword.exe 11.0.6213, 11.0.6234, etc). So I modified that default report to handle Microsoft applications specially and provide a drop-down to select the application you want to report on. It doesn't have any of the date filters the OotB report uses (they always seemed a little goofy to me anyway). Anyway, try it and let me know how it works for you.
Oh, one other thing...you can modify the list of applications in the drop-down by modifying the _AppName parameter. The configuration is [Display Name];Value, where the [Display Name] is optional. The entries you need to add there are the "Product name" field of the application in question. Each item must be separated by a pipe symbol ('|').
Fantastic
I've been after exacty this.
I don't know programing i'm just a logical thinker and can work stuff out! This was a true helping hand i've used this and altered it and the drop downs / values for reporting on Adobe products which like MS reports back alternate versions depending on patching level.
The only issue i have is when i do a basic "Adobe products " search to get the product names if i add into my modified version of your script "Adobe Premiere Elements" it comes back blank. Same for "Adobe Acrobat elements".
If i change the wild card name to just " elements" i get all the "adobe acrobat elements" but no listing for Premiere elements still...
Any idea? the _AppName function has the wild cards for before and after still so i thought this should work :(
oh how do i remove the "how manay times used" bit? as this is handy for us to work out if we can remove applications but not really something needed on an every day report.
Thanks!
Primary User
How do i add the Primary PC user to this? With the option for a specific month? As users move about (call centers) so this would help pin-point who to contact regarding applications.
i've attempted to work out how the coding works to insert what i think is right but it' errors (not a suprise)
IF '%_AppName%' IN ('Adobe Acrobat','Adobe Reader','Adobe Photoshop','Adobe Illustrator','Adobe Acrobat Elements','Adobe Contribute','Adobe Designer','Adobe Dreamweaver','Adobe Flash','Adobe Photoshop','Adobe Premiere Elements',)
BEGIN
SELECT Manufacturer, [Product Name],
CASE LEFT(amms.[Product Version], 2)
WHEN '2.' THEN '2'
WHEN '5.' THEN '5'
WHEN '6.' THEN '6'
WHEN '7.' THEN '7'
WHEN '8.' THEN '8'
ELSE amms.[Product Version]
END AS [Version], acid.Name AS [Computer Name], acid.Domain AS [Computer Domain], Pname.name AS [User],
SUM([Run Count]) AS [# of Uses], MAX(amms.[Last Start]) AS [Last Start] , acid._ResourceGuid
FROM Inv_AeX_AM_Monthly_Summary amms
FROM Inv_AeX_AC_Primary_User Pname
JOIN Inv_AeX_AC_Identification acid
ON amms._ResourceGuid = acid._ResourceGuid
WHERE amms.[Product Name] LIKE '%' + '%_AppName%' + '%'
and amms.Manufacturer LIKE '%Adobe%'
GROUP BY Manufacturer, [Product Name], acid.Name, acid.Domain, Pname.name,
CASE LEFT(amms.[Product Version], 2)
WHEN '2.' THEN '2'
WHEN '5.' THEN '5'
WHEN '6.' THEN '6'
WHEN '7.' THEN '7'
WHEN '8.' THEN '8'
ELSE amms.[Product Version]
END , acid._ResourceGuid
ORDER BY [# of Uses] DESC, [Name] ASC
END
ELSE BEGIN
SELECT Manufacturer, [Product Name], amms.[Product Version] AS [Version],
acid.Name AS [Computer Name], acid.Domain AS [Computer Domain], Pname.user AS [user],
SUM([Run Count]) AS [# of Uses], MAX(amms.[Last Start]) AS [Last Start] , acid._ResourceGuid
FROM Inv_AeX_AM_Monthly_Summary amms
FROM Inv_AeX_AC_Primary_User Pname
JOIN Inv_AeX_AC_Identification acid
ON amms._ResourceGuid = acid._ResourceGuid
WHERE amms.[Product Name] LIKE '%' + '%_AppName%' + '%'
GROUP BY Manufacturer, [Product Name], acid.Name, acid.Domain,
amms.[Product Version], acid._ResourceGuid, Pname.User
ORDER BY [# of Uses] DESC, [Name] ASC
END
what is wrong with the way i have edited this?
Thanks.
You can't have two "FROM"
You can't have two "FROM" statements in a query. So replace the "FROM Inv_AeX_AC_Primary_User Pname" with:
JOIN Inv_AeX_AC_Primary_User PName
ON amms._resourceGuid = PName._ResourceGuid
Actually it might be better to put it under the "acid" JOIN, and instead of JOINing on amms._ResourceGuid, JOIN on acid._ResourceGuid.
The other thing to watch out for with the Application names is that the vendor doesn't necessarily set the [Product Name] field to exactly what you want. You may want to query (in Query Analyzer or SQL Management Studio 2005):
SELECT DISTINCT [Product Name], count(*)
FROM Inv_AeX_AM_Monthly_Summary
WHERE Manufacturer LIKE '%Adobe%'
GROUP BY [Product Name]
* edit: changed "Application Name" to "Product Name"
Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.
Incorrect Syntax near the keyword 'User'
Hi Thanks for above,
That makes sense. I MESSED ABOUT AND GOT IT WORKING!!
I also want this report to allow me to prompt for which Month! I can add the propt box into it with ease but how i link the other requirement in i'm not sure.
IE: Output should be something like, Primary user in June, with Adobe CS4 PC Name and Total number of times used (not just that month) This way we can argue that in 6 months the app has been used once and suggest removal of the application. But also we can contact the "current" owner of that machine going by who the primary user is that month.
SO my code thus far...... (Bolded my additions) I'll try and get the month selection in now...
IF '%_AppName%' IN ('Adobe Acrobat','Adobe Reader','Adobe Photoshop','Adobe Illustrator','Adobe Acrobat Elements','Adobe Contribute','Adobe Designer','Adobe Dreamweaver','Adobe Flash','Adobe Photoshop','Adobe Premiere Elements')
BEGIN
SELECT Manufacturer, [Product Name],
CASE LEFT(amms.[Product Version], 2)
WHEN '2.' THEN '2'
WHEN '5.' THEN '5'
WHEN '6.' THEN '6'
WHEN '7.' THEN '7'
WHEN '8.' THEN '8'
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 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.[Product Name] LIKE '%' + '%_AppName%' + '%'
and amms.Manufacturer LIKE '%Adobe%'
AND Pname.[Month] Like '%' + '%Month%' + '%'
GROUP BY Manufacturer, [Product Name], acid.Name, acid.Domain, Pname.[User], Pname.[Month],
CASE LEFT(amms.[Product Version], 2)
WHEN '2.' THEN '2'
WHEN '5.' THEN '5'
WHEN '6.' THEN '6'
WHEN '7.' THEN '7'
WHEN '8.' THEN '8'
ELSE amms.[Product Version]
END , acid._ResourceGuid
ORDER BY [# of Uses] DESC, [Name] ASC
END
ELSE BEGIN
SELECT Manufacturer, [Product Name], amms.[Product Version] AS [Version],
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 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.[Product Name] LIKE '%' + '%_AppName%' + '%'
GROUP BY Manufacturer, [Product Name], acid.Name, acid.Domain,
amms.[Product Version], acid._ResourceGuid, Pname._ResourceGuid, Pname.[User], Pname.[Month]
ORDER BY [# of Uses] DESC, [Name] ASC
END
Thanks again.
When i have this correct it'll be very easy to just change the variables for software audits.and obviously for others wanting this.
Would you like to reply?
Login or Register to post your comment.