The OperationsManagerDW Schema
Applies To: Operations Manager 2007 R2
The OperationsManagerDW database stores data for use in reports. This is in contrast to the OperationsManager database, which stores data for use at the Operations Manager console. A large number of database views are provided in order to make queries easier to write, without requiring you to have detailed knowledge of the schema.
There are five main groupings, or datasets, within the OperationsManagerDW database:
Contains records of alerts raised by the management pack rules and monitors.
Contains records of event data collected by management pack rules.
Contains data regarding the performance of managed entities (objects monitored by Operations Manager), collected by management pack rules.
Contains data collected by monitors about the state of a managed entity.
Provides information about monitored objects, management packs, management groups, and relationships. The views in this dataset are typically joined to other views (Alert, Event, Performance, and so on) to provide the name of the monitored object, or to scope the query to a specific group, management pack, or management group.
Each of these datasets corresponds to a different category of reports in the operator console.
The tables of the OperationsManagerDW database should never be accessed directly. Instead, a number of database views are provided for access. When structural changes are made to the OperationsManagerDW database, every effort is made to ensure that the views are consistent so that existing reports can be used without changes. The tables, however, may be changed, and these changes may cause existing reports that query the tables directly to no longer work properly.
Key Views for Each Dataset
Alerts are generated by rules and monitors in Operations Manager and are stored in the database together with their respective details and parameters. No aggregation or summarization is performed. Alert data can be seen in four views in the Data Warehouse database:
This view contains general information about the alert: Name, Description, Severity, the workflow that created it, the time that it was created, and the managed entity that it is associated with. This view can be joined to the other alert views on the
AlertGuidcolumn. It may also be joined to the
Monitorviews to retrieve additional details.
This view contains the Custom Field, Owner, and TicketID data for the alert. It can be joined to the
Alert.vAlertview on the AlertGuid column.
This view contains information about each Resolution State that the alert has been in, when it was set, and how long it was in the state. This view can be joined to the
Alert.vAlertview on the
This view contains the value for each parameter in the alert. It can be joined to the
Alert.vAlertview on the
Sample Alert Dataset Query
To retrieve the number of alerts generated yesterday, use the following Transact-SQL query:
SELECT COUNT (AlertName) FROM Alert.vAlert WHERE DATEDIFF(dd,0,RaisedDateTime) = DATEDIFF(dd,0,GETDATE()-1)
Events are collected from monitored objects by Event Collection Rules in Operations Manager and are stored in the database together with their respective details and parameters. No aggregation or summarization is performed. Event data can be seen in several views in the Data Warehouse database:
This is the main view for Events. It contains the unique ID for the Event (
EventOriginId), the date/time of the Event, the Event Number, and other unique IDs that can be joined to the other Event views to get the details of the Event.
This view can be joined to the
Event.vEventview on the
EventOriginIdcolumn to provide the event description found in the
This view can be joined to the
Event.vEventview on the
EventOriginIdcolumn to provide the event parameters. This is generally the same information that is in the event description, with part of it in each parameter. Separate joins should be done from the
Event.vEventview for each parameter that is needed.
This view contains the
RuleRowIdof the rule that generated the event and the
ManagedEntityRowIdof the managed entity that the alert came from. This view should be joined to the
vManagedEntityviews to get these details.
This view provides the event category information and can be joined on the
EventCategoryRowIdcolumn in the
This view provides the event Level (Warning, Error, and so on) and can be joined on the
EventLevelIdcolumn in the
This view provides the user name that was logged with the event and can be joined on the
UserNameRowIdcolumn in the
This view provides the channel for the event. This will be either the name of the event log (Application, System, and so on) or a name supplied in a custom event collection rule. This view can be joined on the
EventChannelRowIdcolumn in the
This view provides the publisher for the event. This will be either the Source in the event log or a name supplied in a custom event collection rule. This view can be joined on the
EventPublisherRowIdcolumn in the
This view provides the name of the computer that logged the event and can be joined on the
LoggingComputerRowIdcolumn in the
Sample Event Dataset Query
To retrieve the ten most frequently occurring events in the OperationsManagerDW database, use the following Transact-SQL query:
SELECT TOP 10 EventDisplayNumber, COUNT(*) AS Events FROM Event.vEvent GROUP BY EventDisplayNumber ORDER BY Events DESC
Performance data is available in daily and hourly aggregates, which contain the minimum, maximum, average, and standard deviation of the values collected. You will generally query either the
Perf.vPerfHourly views and join them to other views to get the managed entity and rule information. The
vPerfRaw view can be queried (instead of the hourly or daily views) to get the value of each sample. By default, this data is kept for a shorter time than the hourly and daily aggregated data. For optimal report performance, it is recommended that you use
vPerfHourly instead of
vPerfRaw. The Performance data views are as follows:
- Perf.vPerfDaily and Perf.vPerfHourly
These views contain the hourly and daily aggregated performance data. The minimum, maximum, average, and standard deviation of the values in the
vPerfRawview are calculated and stored in these views. These views can be joined to the
vManagedEntitytable on the
ManagedEntityRowIdcolumn to get the information for the object that the values were collected from. They can then be joined to the
vPerformanceRuleInstanceview on the
PerformanceRuleInstanceRowIdcolumn to get the name of the performance object and counter and the rule that was used to collect them.
This view contains the ID for the instance of the counter that was collected together with the IDs for the performance counters and the rules that collected them. Use this view to link the daily/hourly aggregation views to the
vPerformanceRuleview on the
PerformanceRuleInstanceIdcolumn, and then to the
vRuleview on the
This view contains the counter and object names that were collected and can be joined to the
PerformanceRuleInstanceRowIdcolumn in the
This view contains the name of the rule that collected the performance counter and can be joined on the
RuleRowIdcolumn in the
vPerformanceRuleInstanceview. It also contains a
ManagmentPackRowIdcolumn which can be joined to the
vManagementPackview to get the information about the management pack that contains the rule.
Sample Performance Dataset Query
To retrieve the top ten performance rule instances being monitored, use the following query:
SELECT TOP 10 PerformanceRuleInstanceRowId, COUNT(*) AS Count FROM Perf.vPerfDaily GROUP BY PerformanceRuleInstanceRowId ORDER BY Count DESC
The State data views contain data on how long any given monitor was in each possible state (Healthy, Warning, Critical, and so on). The data is summarized for each object and aggregated daily and hourly. The State data views are described and illustrated below:
- vStateHourlyFull and vSTateDailyFull
These views contain the hourly and daily aggregated state data and contain data on how long each monitor/managed entity combination was in each possible state. The
ManagedEntitycolumns can be joined to the
vManagedEntityviews to get information about the monitor that changed the state and the managed entity that the state was changed for. It is not recommended that you use these views in Availability reports; use
- State.vStateHourly and State.vStateDaily
These views are derived from the
vStateDailyFulland are used in availability reports. These views can be joined to the
This view contains the information about the monitor that caused the state change and can be joined on the
MonitorRowIdcolumn of the
Sample State Dataset Query
To retrieve the ManagedEntityMonitorRowID for any entity that has been in the red (Critical) state for more than one minute, use the following query:
SELECT ManagedEntityMonitorRowId FROM State.vStateHourly WHERE InRedStateMilliseconds > 60000
Managed Entity Dataset
The managed entity dataset provides information about monitored objects, management packs, relationships, and management groups. These views are typically joined to other views (Alert, Event, Performance, or State) to provide the name of the monitored object or to scope the query to a specific group, management pack, or management group.
This view provides the names for all monitored objects in the OperationsManagerDW database. This view is generally joined to views from the other datasets to provide the name of the object that the collected data applies to. This view can also be joined to the
vManagedEntityTypeview on the
ManagedEntityTypeRowIdcolumn to scope the list of objects down to a specific type of object, to the
vRelationshipview on the
ManagementGroupRowIdcolumn to scope the list of objects down to a specific group, and to the
vManagementGroupview on the
ManagementGroupRowIdcolumn to scope the list of objects to a specific management group.
This view provides information about the types of objects that occur in the
vManagedEntityview (operating system, database, and so on). This view can be joined to the
vManagedEntityview on the
ManagedEntityTypeRowIDcolumn. This view can also be joined to the
vManagementPackview on the
ManagementPackRowIdcolumn to show which management pack defines each type of object.
This view provides information about the Management Packs in the Operations Manager environment and can be joined to the
This view provides relationship information, which can be used to retrieve group membership for Managed Entities. This view can be joined to the
vManagedEntityview on the
This view provides information about the type of relationships identified in the
vRelationshipview and can be joined to other views on the
This view provides information about Management Groups that send data to the Data Warehouse and can be joined to several other views on the