After figuring out how to get the logical drive performance data into the Data Warehouse (demonstrated in part 1), I needed to present the data in a simple report format that was quickly readable and searchable. Since we leverage SharePoint fairly heavily, I also needed to post the published report to the portal.
The first step is to figure out the SQL query to get the data from the DW. This took the longest for me to figure out because I didn’t know exactly which tables were keeping the data being collected. As I mentioned in part 1, I would have saved myself a lot of time if I had knuckled down and slowly read over the examples for custom reporting as they detailed many different SQL scripts that greatly help explain the relationships between the tables as well as where to look to pull the performance data (or event and state data for that matter). The Report Authoring Guide can be found here. The following is the SQL script I used for the Data Source to return the information for all servers from the DW:
SELECT vPerf.DateTime, vPerf.AverageValue, vPerformanceRuleInstance.InstanceName,
vManagedEntity.Name, 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 (vPerformanceRule.CounterName IN ('TotalCapacity')) AND (vPerformanceRuleInstance.InstanceName LIKE '%:') AND (vPerf.DateTime > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
OR
(vPerformanceRule.CounterName IN ('FreeSpace')) AND (vPerformanceRuleInstance.InstanceName LIKE '%:') AND (vPerf.DateTime > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
OR
(vPerformanceRule.CounterName IN ('UsedSpace')) AND (vPerformanceRuleInstance.InstanceName LIKE '%:') AND (vPerf.DateTime > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
ORDER BY vManagedEntity.Name, vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName DESC |
Note: I leveraged views rather than the actual tables to prevent issues with the query should Microsoft change the schema a little in future updates. This is a recommended best practice per the documentation
Next item is to create the report using the Business Intelligence Development Studio. Basic steps are as follows:
1. Create a new Report Server Project Wizard.
2. At the Select Data Source page, enter a name for the data source and click the Edit button to set up the connection to the SQL Data Warehouse server.
3. Click OK to enter the server connection settings and verify them in the Select Data Source page. Click Next
4. At the Design the Query window, copy and past the SQL script mentioned earlier in the post into the Query String window. You can click on the Query Builder button to get a visual breakdown of the tables and relationships as well as run the query in the UI to view what the results will look like. Click Next.
5. At the Select the Report Type window, leave it default Tabular radio button selected and click Next.
6. At the Design the Table screen, group the fields by Name and then place the remaining four fields into the Details window. Click Next.
7. Accept the default Stepped radio button at the Choose the Table Layout window. Click Next.
8. Select whatever style you want to apply on the Choose the Table Style window. Click Next.
9. On the Choose the Deployment Location window, key in the proper server name for the Report Server. The format will be http://SERVER01/ReportServer. Also select a folder to publish the report into and click Next.
10. Name the report on the final page and select the Preview Report checkbox at the bottom to have the report immediately generated.
A quick snapshot of the layout page when you are done with a little applied formatting and spacing might look something like this:
Creating a Report with Parameter Input
Let’s say that you only want the drive space information for a single server instead of all servers. In order to accomplish that, you will need to add input parameters to the report. In the Solution Explorer, right click on the Reports folder and select Add New Report:
Follow the previous steps to create the initial Data Source and configure the basics of the report. When finished, go to the Data tab of the new report and add a New Dataset.
To create the query that will return the list of the servers which have collected performance data related to the Logical Drive Space rule, fill in the Query tab as follows using the SQL snippet below for the Query String (this list will later be used to populate a drop down to select the target parameter):
SELECT DISTINCT UPPER(vManagedEntity.Name) AS Name
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 (vPerformanceRule.CounterName IN ('TotalCapacity')) AND (vPerformanceRuleInstance.InstanceName LIKE '%:')
ORDER BY UPPER(vManagedEntity.Name) |
Click OK to create the Dataset. Next, create an additional Dataset that will be used to query the DW using the select server as the parameter in the same manner. In the Name field put in something relevant like OpsMgrDWbyName and then use the following SQL snippet for the Query String:
SELECT vPerf.DateTime, vPerf.AverageValue, vPerformanceRuleInstance.InstanceName,
vManagedEntity.Name, 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 (vPerformanceRule.CounterName IN ('TotalCapacity')) AND (vPerformanceRuleInstance.InstanceName LIKE '%:') AND (vPerf.DateTime > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND ( vManagedEntity.Name = @servername)
OR
(vPerformanceRule.CounterName IN ('FreeSpace')) AND (vPerformanceRuleInstance.InstanceName LIKE '%:') AND (vPerf.DateTime > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))AND ( vManagedEntity.Name = @servername)
OR
(vPerformanceRule.CounterName IN ('UsedSpace')) AND (vPerformanceRuleInstance.InstanceName LIKE '%:') AND (vPerf.DateTime > DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))AND ( vManagedEntity.Name = @servername)
ORDER BY vManagedEntity.Name, vPerformanceRuleInstance.InstanceName, vPerformanceRule.CounterName DESC |
Select the Fields tab and fill in the fields as follows:
Move over to the Parameters tab and verify that it columns look as follows:
These should auto populate from the query entered in the Query tab, but if nothing appears here you can populate the columns by keying in @servername into the Name column and then clicking the dropdown in the Value column. Select Edit Expression and then select Parameters in the left hand column. This will bring up a third column on the right with the servername parameter. From here, simply click OK to enter the proper value into the field. No other tabs need to be modified, so click OK to create the third DataSet.
We now need to configure the parameters within the report itself so that when the report is run, the user has a way to actually select the specific server they want to run the report against. To accomplish this, select the Layout tab from the main design window in Business Intelligence Development Studio. You should see the graphical representation of the report in a design format. Right click anywhere in the empty space outside the report design inside the Layout tab and select Report Parameters. Fill out the properties page as follows:
To clean up the output of the report a little, let’s change some of the column names to friendlier names and expand some column widths. This is something you’ll need to play with a little, but a basic example of final result might look something like this:
You may also want to format the output of the Size column (or Average if you haven’t changed the name) so that the data is rounded out to two decimal places. To do this, right click the field that starts with =FormatNumber and select Expression from the menu. Paste the following into the expression window to clean up the output when the report is run:
=FormatNumber(Fields!AverageValue.Value, 2) & ” GB”
A good resource for understanding editing expressions can be found at http://msdn.microsoft.com/en-us/library/ms159238.aspx.
Now that the report is done, it needs to be deployed to the Reporting Server. To do this, simply right click on the solution name in the Solution Explorer (top level of the tree) and select Deploy.
Rather than extend this already long post with examples of publishing to SharePoint, I’ll just point you to the link here on the OpsMgr Team Blog. It’s a very simple process with only a few steps.
While this and the previous post are fairly rudimentary examples of how you can collect data and effectively report on it, I hope they mainly served as a basic walkthrough of the entire process which can be extended to fit your personal needs for custom data collection and reporting in Operations Manager 2007.
2 Comments | 
OpsMgr 2007 | 
Permalink
Posted by alan
