OpsMgr: Choose source table based on date range

Written by Ingmar Verheij on April 10th, 2012. Posted in Operations Manager

When creating a report in System Center Operations Manager (SCOM) showing performance data you’ll need to make a decision about the data you’re going to show. Will you use raw data (Perf.vPerfRaw), hourly aggregated data (Perf.vPerfHourly) or daily aggregated data (Perf.vPerfDaily).

Do you want to show detailed information or for a longer period? The most detail can be achieved with the data stored in Perf.vPerfRaw but this comes at a cost, the time to query the database and render the report increases massive. So if you want to show data for a longer period (like over a week) you’ll probably better use the data stored in Perf.vPerfDaily.

 

But what if you want the user the ability to change the date range? If the user specifies a small range (for instance a day) you want high detail, but when the range is increased (for instance a month) less detail is required.

Unfortunately the reports created in the Business Intelligence Development Studio (BIDS) does not allow you to create a conditional SQL statement. So in order to achieve this, a stored procedure needs to be created.

Stored procedure

Let’s create a stored procedure in the OperationsManagerDW database. The stored procedure queries performance data for the rule with object name ‘Processor’, instance name ‘_Total’ and counter name ‘% Processor Time’. 

There are only two parameters, the start and end time.

Now if we run the stored procedure with a start and end time with a range of less then one day (23 hours) then the data is retrieved from Perf.vPerfRaw. Since there is no average, minimum, maximum of standard deviation I’ve changed the source fields so it always returns the same columns (which is required if you want to create a universal report.

If you look at the Message tab (where the SQL query is printed) you’ll see that the source table is Perf.vPerfRaw.

If we change the date range to more than a day (5 days)…

…the data is retrieved from Perf.vPerfHourly .

Dataset

 

Now add a dataset to the ‘Report Project’ report in the Business Intelligence Development Studio (BIDS) and select the stored procedure you’ve created earlier. Don’t forget to add the parameters for the begin and end date.


And create the report as you normally would.

 

Authoring the management pack

The next step is to add the report and the stored procedure to the management. This is done by creating a Data Warehouse Script.

Install script

Uninstall script

Upgrade script


Finally you need to reference the data warehouse script from the report that’s using the stored procedure.

 

Pitfalls

Here are some pitfalls that you might encounter when creating your own.

 
Upgrade script not set

If you don’t set the upgrade script (the fourth tab) the management pack won’t import and an Event is logged from source OpsMgr SDK Service with event ID 26319.


 
Permissions not granted

If you forget to grant the execute permissions to the OpsMgrReader login (see the last two lines in the install / upgrade script) the report is unable to retrieve data and the following error is thrown.

 

No data in chart shown with raw data

When the data is shown in a chart and the source data is from Perf.vPerfRaw the data might not be shown. However, if you add markers you do see the data (so there is data). This happens when the following conditions apply:

  • The data from Perf.vPerfRaw
  • Displayed in a (line) chart
  • Category Groups is [DateTime]
  • There is a series group with more than one instance.

If you export the report to a comma separated file (CSV) you’ll notice that for each instance multiple records are stored but only one has a value, the number of records match the number of instances.

This is caused by the category group datetime. The line chart tries to show the data for each instance in time. Since the data is stored in raw format the timestamp doesn’t match exactly (probably off by a few milliseconds).

The is “solved” by removing the milliseconds from the datetime with the CONVERT statement and using style 120 (ODBC canonical).

Ingmar Verheij

At the time Ingmar wrote this article he worked for PepperByte as a Senior Consultant (up to May 2014). His work consisted of designing, migrating and troubleshooting Microsoft and Citrix infrastructures. He was working with technologies like Microsoft RDS, user environment management and (performance) monitoring. Ingmar is User Group leader of the Dutch Citrix User Group (DuCUG). RES Software named Ingmar RES Software Valued Professional in 2014.

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Tags: , , , ,

Comments (1)

  • Interesting
    13 May 2015 at 15:48 |

    I want the Schema of OperationManager & OperationManagerDW, for reprting.
    please suggest for reporting how should I start.
    I want to show some meaningfull/usefull report(s).
    please guide me.
    Thanks

Leave a comment

*

Donate

%d bloggers like this: