Understanding 'Applications with High CPU Usage'

mickwearn's picture
mickwearn
January 26th, 2009


Hi Everyone,



Recently I set up automated reporting for applications with high CPU usage over a 7day period.



My manager wanted more information then, just the application name, count of computers and the average CPU Usage.



So I created a report which pulls data from vUser as well as Inv_AeX_AM_Monthly_Summary,



and looks something like this



SELECT vComputer.Name AS 'WorkStation', vUser.[Display Name] AS 'User', vUser.[Job Title], vUser.[Department], Inv_AeX_AM_Monthly_Summary.[File Name], Inv_AeX_AM_Monthly_Summary.[Known As], Inv_AeX_AM_Monthly_Summary.[Manufacturer], Inv_AeX_AM_Monthly_Summary.[Run Count], Inv_AeX_AM_Monthly_Summary.[Month Year], (Inv_AeX_AM_Monthly_Summary.[Total Run Time]/360) AS 'Total Run Time (Minutes)', Inv_AeX_AM_Monthly_Summary.[Avg CPU Usage], vComputer.Guid

FROM vComputer JOIN Inv_AeX_AM_Monthly_Summary ON Inv_AeX_AM_Monthly_Summary._ResourceGuid = vComputer.Guid JOIN AeXAMSummaryDateRanges ON Inv_AeX_AM_Monthly_Summary.[Month Year] = AeXAMSummaryDateRanges.[Month Year] JOIN vUser ON vUser.Name = vComputer.[User]

WHERE Inv_AeX_AM_Monthly_Summary.[Avg CPU Usage] > '10' and DATEDIFF(mm,AeXAMSummaryDateRanges.[Start Date],GETDATE()) <= .25

ORDER BY Inv_AeX_AM_Monthly_Summary.[Avg CPU Usage] DESC



Problem is that if you drop the results into Excel, and sort by Application and then by User, you will see results where it shows something like this







Now what I can't work out is why has acrobat.exe been reported 14 times? with different run counts over the 7day period, all on the same computer, and all by the same user?



I'm hoping that someone out there is able to understand the results.



Thanks in Advanced.



Mick.



Filed under: ,
0 votes
KSchroeder's picture
KSchroeder
42 weeks 6 days ago

Mick,

You need a GROUP BY clause in there, and/or some SUM or AVG aggregate functions to combine the rows (like cpu usage avg and sum run count). It might help to reduce the columns to start.

Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.

0 votes
KSchroeder's picture
KSchroeder
42 weeks 6 days ago

Also, why is this JOIN needed:

JOIN AeXAMSummaryDateRanges ON Inv_AeX_AM_Monthly_Summary.[Month Year] = AeXAMSummaryDateRanges.[Month Year]



You don't use the dateranges table in your query. That may account in part for the dups.



Err, nevermind. It is in the WHERE clause. Shouldn't that be the [Last Start] column from the monthlySummary table?



Does this work:

quote:
SELECT vc.Name AS 'WorkStation', vUser.[Display Name] AS 'User',

vUser.[Job Title], vUser.[Department], ms.[File Name],

ms.[Known As], ms.[Manufacturer],

ms.[Run Count], ms.[Month Year],

(ms.[Total Run Time]/60) AS 'Total Run Time (Minutes)',

ms.[Avg CPU Usage], vc.Guid

FROM vComputer AS vc

JOIN Inv_AeX_AM_Monthly_Summary AS ms ON ms._ResourceGuid = vc.Guid

JOIN AeXAMSummaryDateRanges AS sdr ON ms.[Month Year] = sdr.[Month Year]

JOIN vUser ON vUser.Name = vc.[User]

WHERE ms.[Avg CPU Usage] > 5.0

and DATEDIFF(dd,sdr.[Start Date],GETDATE()) <= 7

ORDER BY vc.name -- Inv_AeX_AM_Monthly_Summary.[Avg CPU Usage] DESC [/q I also used table aliases (the AS xxxx parts) to shorten the code a bit.

Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.

0 votes
KSchroeder's picture
KSchroeder
42 weeks 6 days ago

Oh and total run time is in seconds, so unless you wanted hours, just divide by 60.

Thanks,
Kyle
Symantec Trusted Advisor
If your question has been resolved, please be sure to click "Mark as Solution"! Thank you.

0 votes
mickwearn's picture
mickwearn
42 weeks 6 days ago

Hi Kyle,



Thanks for your reply, I tried your query but didn't get any results until I changed the 'DATEDIFF(dd,sdr.[Start Date],GETDATE()) <= 7 ' from 7 to 28 and any number grater then 28.



I think this is why I'm using the



AeXAMSummaryDateRanges ON Inv_AeX_AM_Monthly_Summary.[Month Year] = AeXAMSummaryDateRanges.[Month Year]





As used in the Altiris report Reports>Software Management >Application Metering > Application Usage >Application CPU Usage



This report is where I started to build mine from, and this is how this report is getting the last 7 days.



I exported the results from your query to Excel (which I'm happy to email you but not post in forums) and still find the same results, where by Acrobat.exe lists multiple entries. I think it is just acrobat.exe I can't see it for any other application. It could be something wired with acrobat.



I also just wanted to check with you, I thought the data in the column of Inv_AeX_AM_Monthly_Summary.[Total Run Time] was in milliseconds and this is why I was dividing it by 360 to get minutes.



So I just wanted to confirm what time unit is being used here..



Ideally we are hope to be able to build a report out of this data to automatically, pro actively show computers with user info which are perhaps under spec'd for the end user. Do you currently use anything like this? Where looking for ways to show business that Altiris is being pro active.



But in the mean time if I can work out an explanation for why acrobat shows multiple entries that would be really good.. or at least better understand how the data is being captured and logged.



Thanks again for your reply.



Mick.



0 votes