SCOM 2012 Report Builder 3 Custom Performance Report

This guide will give you a dropdown menu for server name and an option to enter a relative time period then create a performance chart for that data. This is my first blog post so any feedback is appreciated.

Browse to http://yourreportserver/reports and click ‘Report Builder’

Click the top left circle and select ‘New’

clip_image002

With New Report highlighted select ‘Blank Report’

We’ll need to connect to our Datawarehouse first.

Right click ‘Data Sources’ and select ‘Add Data Source…’

clip_image004

Now select ‘User a connection embedded in my report’ – ensure Microsoft SQL Server is highlighted under ‘Select connection type:’ and click ‘Build…’

clip_image006

Enter the ‘Server name’ of your datawarehouse and select the OperationsManagerDW under ‘Connect to a database’

clip_image007

Click ‘Test Connection’ to test the connection. If succeeded click ‘OK’

clip_image009

Click OK again.

Back on the main page; right click ‘Datasets’ and ‘Add dataset…’

clip_image010

Select ‘Use a dataset embedded in my report’ and select the data source created earlier.

clip_image011

Now in the ‘Query type’ field make sure Text is selected then cut and paste the following into the blank Query field:

SELECT

vPerf.DateTime,

vPerf.SampleCount,

vPerf.AverageValue,

vPerf.MinValue,

vPerf.MaxValue,

vPerf.StandardDeviation,

vPerformanceRuleInstance.InstanceName,

vManagedEntity.Path,

vPerformanceRule.ObjectName,

vPerformanceRule.CounterName

FROM Perf.vPerfHourly AS vPerf INNER JOIN

vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId

WHERE

vPerf.DateTime >= GETDATE() -@DaysPrevious

and vPerf.DateTime < GETDATE()

AND vManagedEntity.Path = @ServerName

AND (vPerformanceRule.ObjectName IN (‘LogicalDisk’))

AND (vPerformanceRule.CounterName IN (‘Free Megabytes’))

ORDER BY vPerformanceRuleInstance.InstanceName,vPerf.DateTime

clip_image012

Note: this example is for LogicalDisk/Free Megabytes. You can choose any counters by changing these two lines:

AND (vPerformanceRule.ObjectName IN (‘LogicalDisk’))

AND (vPerformanceRule.CounterName IN (‘Free Megabytes’))

Click OK to create dataset.

We’ll create one more dataset to get the data organised nicely. From the main page right click ‘Datasets’ and ‘Add Dataset…’

Connect to Data Source created previously then past the following into the blank Query field:

SELECT

DISTINCT vManagedEntity.Path AS dPath

FROM Perf.vPerfHourly AS vPerf INNER JOIN

vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId

WHERE

vPerf.DateTime >= GETDATE() -1

and vPerf.DateTime < GETDATE()

AND (vPerformanceRule.ObjectName IN (‘LogicalDisk’))

AND (vPerformanceRule.CounterName IN (‘Free Megabytes’))

ORDER BY vManagedEntity.Path

clip_image013

Click OK to create dataset.

Let’s create the parameters that the user will select to choose the desired server and timeframe.

From the main screen expand ‘Parameters’ and you will see to parameters that were created when you added the datasets:

clip_image015

Double click @ServerName under parameters:

clip_image016

Select ‘Available Values’ on the right then choose ‘Get values from a query’. Select Dataset as DataSet2, ‘Value field’ as dPath and ’Label field’ as dPath.

clip_image017

Click OK.

Back on the main screen double click @DaysPrevious under Parameters, then on the General tab change the ‘Data type:’ to Integer.

clip_image018

On the ‘Default Values’ page select ‘Specify values’ then ‘Add’ and enter 1 in the Value field.

clip_image019

Click OK.

Back on the main page. Select the ‘Insert’ tab at the top, then click ‘Chart ‘ – ‘Chart Wizard’

clip_image021

Select ‘DataSet1’ and click ‘Next’

clip_image023

Select ‘Line’ then click ‘Next’

clip_image025

Drag the fields from the left into the boxes as shown below:

clip_image027

Click Next. Select your Style on the next screen, I’m sticking with the default.

clip_image029

Click ‘Finish’

clip_image031

Right click at the bottom of the page near ExecutionTime (but not in its little box) and select ‘remove page footer’

Now back on the main page we want to expand the page and chart to fill the screen. Click and drag the bottom right corner of the page and make as large as possible.

clip_image033

Click in the chart background to select it, then click and drag the bottom right corner to make larger.

clip_image035

It’s a good idea to save the report now. Click the floppy disk at the top left and save to a location.

Once save click the Run button up the top left. Hopefully you will have the following screen:

clip_image037

Select a server from the drop down menu, change the Days Previous field if required and click ‘View Report’. You should get something like below.

clip_image039

You now have a working report. Click the ‘Design’ button up the top left to get back to the main page. Double click the Axis Title text on the chart and label accordingly.

Right Click ‘Chart Title’ in the top of the chart and select ‘Title Properties…’

clip_image040

Click the fx (function) button next to ‘Title text’ then on the expression page select the category ‘Parameters’ then double click ‘ServerName’ under Values. Click OK.

clip_image041

Click OK again. Add a title and Save.

clip_image043

This should now be available on http://yourreportserver/reports or through the SCOM Console.

10 thoughts on “SCOM 2012 Report Builder 3 Custom Performance Report

  1. Tom Gerald

    This is great stuff! Do you normally package your reports or post for redistribution? I am not so good with Visual Studio, so if you were to zip and post to the Downloads section, I bet many like myself would be very appreciative.

    Nice work!

  2. Pingback: SCOM: Custom JEE Performance Report (Heap Memory) - SysManBlog

  3. Vincent Langlais

    I’ve got an SQL Server Report Builder error when I paste the text in the query field : Incorrect syntax near ‘‘’.

    But if I delete the folowing 2 lines, the error disappear.

    AND (vPerformanceRule.ObjectName IN (‘LogicalDisk’))

    AND (vPerformanceRule.CounterName IN (‘Free Megabytes’))

    Could you help me?

    Thanks,

    Vincent

  4. Vince McShane Post author

    I think it has the wrong ‘ symbol. Delete the ones you used from my page and retype them. I’m not sure this will show correctly but

    AND (vPerformanceRule.ObjectName IN (‘LogicalDisk’))

    will become:

    AND (vPerformanceRule.ObjectName IN (‘LogicalDisk’))

    ‘ and ‘ are different characters and this site may have changed them.

  5. Tom Gerald

    WordPress and Live Writer sometimes turn a quote into smart quotes. This also happens when you copy and paste from Word into anything else.

  6. Pingback: SCOM ACS ile Windows Güvenlik Kayıtları İzlenmesi « SİBER GÜVENLİK

  7. Pingback: SCOM ACS ile Windows Güvenlik Kayıtları İzlenmesi | SİBER GÜVENLİK

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.