By popular demand: How to make a report showing %CPU time for a particular process?

A regular reader of this blog asked me a question which I deemed important/good enough to be shared with the community. So this blog posting will be about his question: How to create a report containing a graph on %CPU time for a particular process?

Well, sometimes making such a thing is like baking bread. We need some ingredients which need to be put together in a certain order, than it needs to be processed (baked) before we end up with a ‘consumable’ result (the report with the graph).

Bing Image Search: ‘Baking’

So let’s start baking!. In this example we want to capture the %CPU Time of the sqlservr.exe process (SQL Server process).

The first ingredient we need is:

Performance Collection Rule
This is needed for capturing some kind of performance. in this case the %CPU Time of the process sqlservr.exe.

Sometimes it is made easy, and the Performance Collection Rule is already in place since it is part of a certain MP. Download Boris Yanushpolsky’s MPViewer version 1.7 from here. Open the MP what might contain that rule and check it out. Use your common sense here: The IIS MP won’t contain a Collection Rule for the SQL Server process…

But for this posting let’s assume there isn’t a Performance Collection Rule in place. So we need to make it ourselves.

1. Start the OpsMgr Console > Authoring > Rules. Right click it > New Rule > Collection Rules > Performance Based > Windows Performance:
   
   Make sure to put this Rule into its own MP! Click Next.
   
   
2. Give it a recognizable rule name and select a proper Rule Category (in this case Performance Collection is good)
    
   The Rule Target must be wisely chosen. Since we want to measure/report on sqlservr.exe we can target it at the SQL 2008 DB Engine. This way we don’t have to disable the rule because it won’t be targeted at all computers, only computers running the SQL 2008 DB Engine. Suppose you would target this rule at Windows Computer. Now we would have to disable the rule since it would be targeted at all monitored Windows Computers, whether or not they are running SQL Server 2008. And enable the rule by using an override targeted at a group containing all SQL 2008 servers.
   
   So targeting is a very important issue when making rules/monitors.
   
   Click Next.
   
   
3. Hit the button Select on the next screen and this screen will pop up:
   
   Select a computer running the SQL 2008 DB Engine, select as Performance Counter Object ‘Process’, as counter ‘% Processor Time’ and as instance ‘sqlservr’ and hit OK. Now you are back in the other screen:
   
   As you can see, the Object, Counter and Instance are already filled in. The only thing you have to change is the interval. For this example I have set it to 1 minute, which is way too much in a production environment. The default 15 minutes will suffice in most cases. Do not lower it since much data can be collected from it!
   
   Click Next.
   
   
4. Here Optimization can be chosen. Of course, the accuracy of the collected data will be affected by it. But when having to run collections of many many processes, it is a good thing to think about. In this example I have chosen not to use it.
   
   Click Create.
   
   
5. Go to the Monitoring Pane > _ShowCase JB (name of the MP selected in Step 1) > right click it > New > Performance View. Select ‘Collect by specific rules’ and in the Criteria Description area click the link ‘…specific rules…’ 
   
   
   
6. In the Select Rules screen, select the earlier created rule (Step 2) and click OK twice.
   
   
   
7. Now the Perfomance View for this rule will be shown.
   
   Select the performance rule and in the Actions Pane click Select Time Range. Set it to one hour (just to see whether data is being collected) and hit OK.
   
   
   As you can see, data is coming in:
    

So now we have the most important ingredient in place and a working order.

Report
All we need is a report showing this data. Gladly I have already written a series about that. The fourth posting in that series is at order here. Of course we don’t want reports about disks but about the %CPU time for the sqlservr.exe process so we need to change some items as described in that posting:

1. In Step 6 of the previously mentioned posting you need to select as Performance Object ‘Process’. Hit the button Search and the correct Collection Rule will be shown. Select it and click OK.
   
   
2. Skip Steps 7 and 8 and click OK.
   
   
3. Follow Step 9. Now the report will run successfully (Be patient though! A report needs more data to be collected before it can show anything. So wait a couple of hours first after having built the Performance Collection Rule):