Share via

SCOM Performance Model with Configuration Data

  • Article

Configuration data combines very well with performance data collected by SCOM. The usefulness of a configuration dataset generally comes from the server attributes it contains. I usually refer to this as business information (location, environment, owner, role, etc.), but it may contain technical knowledge as well. A server configuration dataset may be added to the performance model from my previous post (blogs.technet.com/b/drewfs/archive/2014/08/12/scom-performance-data-and-power-view.aspx). The new dataset will have a relationship to the Entity dataset using the FQDN or Hostname field. Then Power View reports can then answer questions like the following:

  • Which roles in the production SharePoint environment run low on memory? Which farms and servers within those roles run the lowest?
  • What does "DNS\Total Query Received/sec" look like by site and DNS server? How does the data compare to "DNS\Total Response Sent/sec" over the same time period?

One might consider SCOM Groups useful for organizing report information. That works in SSRS reports; however, in Power Pivot and tabular data models, a relationship is a one-to-one or many-to-one lookup. There can't be duplicates in the dataset where one is looking up a value. A server tends to be a member of many SCOM groups. I found it very difficult to use more than one group in a model. Another option within SCOM is the Managed Entity Type or class. The classes don't generally help with business-defined data, though. In any event, a configuration management database (CMDB) in some form will be the source of record. It makes sense to get as close to the source in order to minimize conflicting or outdated information.

I would consider some questions when it comes to configuration data:

  • How often does it change?
  • Is the change history captured somewhere?
  • How are discrepancies (duplicate or missing entries) in the data resolved?

I found it helpful to capture a weekly snapshot of server configurations and store it in a SQL table along with the date of the snapshot as a column. In addition to presenting relevant historical information about servers in a performance report, I could report on things like the growth of the environment, the rate of OS deployments or decommissions, and the ratio of physical to virtual servers. For this post I will use an Excel file, Server Info, with the following example information:                                        

Hostname

Domain

Role

Environment

Location

Owner

SCOMMS1

SCOM2012.LAB

Application

LAB

Walla Walla, WA

Minnie Vandermink

SCOMDC

SCOM2012.LAB

DC

PROD

Burbank, CA

Theodore Brian

SCOMDB

SCOM2012.LAB

Database

LAB

Walla Walla, WA

Randal B. Mann

SCOMDW

SCOM2012.LAB

Database

LAB

Albuquerque, NM

Randal B. Mann

Overview

A. Add a Configuration Dataset to the Performance Data Model

B. Create a Relationship and Refresh Data

C. Make a View for Roles

D. Make a View for Server Statistics

 

Step by step

A. Add a Configuration Dataset to the Performance Data Model

  1. Open Perf 7d Model in Excel

  2. Select Manage from the Data Model section of the POWERPIVOT menu

  3. In the Power Pivot model, click From Other Sources in the Get External Data section of the ribbon  

  4. Select Excel File from the Table Import Wizard then click Next >  

  5. Click Browse

  6. Select Server Info and click Open

  7. Check Use first row as column headers. then click Next >  

  8. Click Finish then click Close

B. Create a Relationship and Refresh Data

  1. Click the Diagram View in the View section of the ribbon 

  2. Resize and arrange the datasets

  3. Make the relationships by clicking and dragging the field in the From column to the field in the To column 

    From

    To

    Entity: Hostname

    Server Info: Hostname

  4. Select Refresh All from the Refresh pulldown in the ribbon 

  5. Click Close when the refresh is complete

  6. Close the Power Pivot window

  7. Click OK in the Power View dialog window 

  8. Save the workbook

C. Make a View for Roles

  1. Click Power View From the Insert section of the ribbon

  2. Enter Performance Summary by Role in the Click here to add a title field

  3. Adjust the text if desired (I used 20 point and bold)

  4. Expand the Tech Knowledge dataset 

  5. Drag the Technology field from the Tech Knowledge dataset into the Filters section

  6. Select OS 

  7. Drag the Category field from the Tech Knowledge dataset into the Filters section

  8. Select CPU, Memory, Network, and Storage

  9. Expand the Object and Counter dataset

  10. Drag the Object+Counter field from the Object and Counter dataset into the Filters section

  11. Select a representative counter for the LogicalDisk, Memory, Network Adapter, and Processor Information objects 

  12. Expand the Perf dataset

  13. Check the boxes for DateTime and avg_avg

  14. Select Line from the Other Chart pulldown in the Switch Visualization section of the ribbon 

  15. Select None from the Title pulldown in the Labels section of the ribbonin the Layout menu 

  16. Drag Category from the Tech Knowledge dataset into the TILE BY field for the line chart 

  17. Expand the Server Info dataset

  18. Drag Role from the Server Info dataset into the Legend field for the line chart

  19. Resize the tile space and the line chart to make room for a table and a bar chart

  20. Click in the open tile space 

  21. Click ALL in the Power View Fields section 

  22. Check the box for Object+Counter from the Object and Counter dataset

  23. Check the boxes for min_min, avg_min, avg_avg, avg_max, and max_max from the Perf dataset

  24. Select None from the Totals pulldown in the Options section of the ribbon 

  25. Resize the table and arrange it so it doesn't overlap with the line chart

  26. Click in the open tile space

  27. Check the box for Environment from the Server Info dataset

  28. Check the box for avg_avg from the Perf dataset

  29. Select Stacked Bar from the Bar Chart pulldown in the Switch Visualization section of the ribbon 

  30. Select None from the Title pulldown in the Labels section of the ribbonin the Layout menu

  31. Expand the Entity dataset

  32. Drag Hostname from the Entity dataset to the Axis field 

  33. Change the sort order of the bar chart to use avg_avg in descending order 

  34. Resize the bar chart and arrange it so it doesn't overlap with the table 

  35. Rename the tab to Role Summary

D. Make a View for Server Statistics

  1. Click Power View From the Insert section of the ribbon

  2. Enter Basic Performance Summary in the Click here to add a title field

  3. Adjust the text if desired

  4. Drag the Technology field from the Tech Knowledge dataset into the Filters section

  5. Select OS

  6. Drag the Category field from the Tech Knowledge dataset into the Filters section

  7. Select CPU, Memory, Network, and Storage

  8. Drag the Object+Counter field from the Object and Counter dataset into the Filters section

  9. Select any desired counters 

  10. Check the box for Counter from the Object and Counter dataset

  11. Check the box for FQDN from the Entity dataset

  12. Check the boxes for Environment and Role from the Server Info dataset

  13. Check the boxes for min_min, avg_min, avg_avg, avg_max, and max_max from the Perf dataset

  14. Select Matrix from the Table pulldown in the Switch Visualization section of the ribbon

  15. Select None from the Totals pulldown in the Options section of the ribbon

  16. Drag Category from the Tech Knowledge dataset into the TILE BY field for the line chart

  17. Resize the matrix and tile space and leave room for another matrix above it

  18. Select the matrix

  19. Press Ctrl-C to copy the matrix

  20. Press Ctrl-V to paste the matrix

  21. Select Rows – Enable Drill Down One Level at a Time from the Show Levels pulldown from the Options section of the ribbon 

  22. Drag FQDN to the bottom of the rows list 

  23. Resize the table so it doesn't overlap with the other table 

  24. Rename the tab to Basic Summary

  25. Save the workbook

Comments

  • Anonymous
    January 01, 2003
    Thank you.
  • Anonymous
    August 15, 2014
    One can convert a seven day performance model to a 26 week model without a lot of trouble. I chose 182
  • Anonymous
    August 15, 2014
    One can convert a seven day performance model to a 26 week model without a lot of trouble. I chose 182
  • Anonymous
    August 17, 2014
    Power View allows the correlation of a large amount of data as long as a relationship exists. The datasets
  • Anonymous
    September 01, 2014
    I use the SCOM Data Warehouse model for a variety of administrative and analytical activities. Sometimes