A Performance Sentry Portal user emailed support wanting to know how to use Crystal Reports to query the MS-SQL database to generate three basic management reports to illustrate:
- Average CPU usage in a predefined period (monthly, quarterly, etc.)
- Average memory usage (PF usage) in a predefined period (monthly, quarterly, etc.)
- Average disk usage in a predefined period (monthly, quarterly, etc.)
The user wanted to know what fields in the database should be used to generate the report. I thought this information would be valuable to all users so much of our response is posted here.
Here are the metrics you’ll need for each of the reports that you wish to generate:
Average CPU Usage – Reported in the <computername>@System table in the column ‘PCT_Total_Processor_Time’. An additional note, there is a column in the ‘Processor’ table by the same name. This table reports ‘by processor’ meaning the metrics are reported for each logical processor on the machine. In other words, a 4-way machine will have metrics for processors 0,1,2,3 and you can get cpu utilization for the interval by summing the values of each processor for the interval.
Average Memory Usage – Here’s where Performance Sentry shines. Nowhere does Microsoft report the amount of memory being used. It only reports ‘available bytes’ (or Kbytes, or Mbytes). That number by itself is meaningless, unless you know the amount of memory on the machine. Performance Sentry knows that and is able to report ‘Percent Available Bytes’. This is reported in the <computername>@Memory table in the column ‘PCT_Available_Bytes’. Therefore to report memory usage, you would use the formula:
Percent Memory Usage = (1 – (PCT_Available_Bytes/100)) * 100
An additional note, Performance Sentry reports the amount of memory installed on a machine in the ‘Machines’ table in the column ‘sizeofRAM’ If you are using an option to report ‘denominator’ or ‘base’ counters (the values used to get the PCT calculations) then the same value will be in the <computername>@Memory table in the column ‘PCT_Available_Bytes_Base’.
Average Disk Usage – You’ll have to calculate this using the ‘PCT_Free_Space’ column in the <computername>@LogicalDisk table:
Percent Disk Used = (1 – (PCT_Free_Space/100)) * 100
Another thing to consider is the size of the disk. Microsoft doesn’t report this, but if Performance Sentry is recording ‘denominator’ or ‘base’ counters, then the size of the disk can be determined by looking at the ‘PCT_Free_Space_Base’ column in the <computername>@LogicalDisk table. This value is reported in Megabytes, so to report in Gigabytes, divide the value by 1024.
You can use Crystal Reports to produce the averages of the metrics since the database you’re accessing stores the values reported
in one minute collection intervals.