The OperationsManagerDW Schema

The OperationsManagerDW Schema

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.

Datasets

There are five main groupings, or datasets, within the OperationsManagerDW database:

Alert

Contains records of alerts raised by the management pack rules and monitors.

Event

Contains records of event data collected by management pack rules.

Performance

Contains data regarding the performance of managed entities (objects monitored by Operations Manager), collected by management pack rules.

State

Contains data collected by monitors about the state of a managed entity.

ManagedEntity

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.

Database Views

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

Alert 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:

Alert.vAlert

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 AlertGuid column. It may also be joined to the ManagedEntity, Rule, and Monitor views to retrieve additional details.

Alert.vAlertDetail

This view contains the Custom Field, Owner, and TicketID data for the alert. It can be joined to the Alert.vAlert view on the AlertGuid column.

Alert.vAlertResolutionState

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.vAlert view on the AlertGuid column.

Alert.vAlertParameter

This view contains the value for each parameter in the alert. It can be joined to the Alert.vAlert view on the AlertGuid column.

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)

Event Dataset

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:

Event.vEvent

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.

Event.vEventDetail

This view can be joined to the Event.vEvent view on the EventOriginId column to provide the event description found in the EventData column.

Event.vEventParameter

This view can be joined to the Event.vEvent view on the EventOriginId column 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.vEvent view for each parameter that is needed.

Event.vEventRule

This view contains the RuleRowId of the rule that generated the event and the ManagedEntityRowId of the managed entity that the alert came from. This view should be joined to the Event.vEvent, vRule, and vManagedEntity views to get these details.

vEventCategory

This view provides the event category information and can be joined on the EventCategoryRowId column in the Event.vEvent view.

vEventLevel

This view provides the event Level (Warning, Error, and so on) and can be joined on the EventLevelId column in the Event.vEvent view.

vEventUserName

This view provides the user name that was logged with the event and can be joined on the UserNameRowId column in the Event.vEvent view.

vEventChannel

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 EventChannelRowId column in the Event.vEvent view.

vEventPublisher

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 EventPublisherRowId column in the Event.vEvent view.

vEventLoggingComputer

This view provides the name of the computer that logged the event and can be joined on the LoggingComputerRowId column in the Event.vEvent view.

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 Dataset

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.vPerfDaily or 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 vPerfDaily or 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 vPerfRaw view are calculated and stored in these views.  These views can be joined to the vManagedEntity table on the ManagedEntityRowId column to get the information for the object that the values were collected from. They can then be joined to the vPerformanceRuleInstance view on the PerformanceRuleInstanceRowId column to get the name of the performance object and counter and the rule that was used to collect them.

vPerformanceRuleInstance

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 vPerformanceRule view on the PerformanceRuleInstanceId column, and then to the vRule view on the RuleRowId column.

vPerformanceRule

This view contains the counter and object names that were collected and can be joined to the PerformanceRuleInstanceRowId column in the vPerformanceRuleInstance view.

vRule

This view contains the name of the rule that collected the performance counter and can be joined on the RuleRowId column in the vPerformanceRuleInstance view.  It also contains a ManagmentPackRowId column which can be joined to the vManagementPack view 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

State Dataset

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 MonitorRowId and ManagedEntity columns can be joined to the vMonitor and vManagedEntity views 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 instead.

State.vStateHourly and State.vStateDaily

These views are derived from the vStateHourlyFull and vStateDailyFull and are used in availability reports. These views can be joined to the vManagedEntityMonitor on the ManagedEntityMonitorRowId column.

vMonitor

This view contains the information about the monitor that caused the state change and can be joined on the MonitorRowId column of the vStateHourlyFull and vStateHourlyDaily views.

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.

vManagedEntity

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 vManagedEntityType view on the ManagedEntityTypeRowId column to scope the list of objects down to a specific type of object, to the vRelationship view on the ManagementGroupRowId column to scope the list of objects down to a specific group, and to the vManagementGroup view on the ManagementGroupRowId column to scope the list of objects to a specific management group.

vManagedEntityType

This view provides information about the types of objects that occur in the vManagedEntity view (operating system, database, and so on).  This view can be joined to the vManagedEntity view on the ManagedEntityTypeRowID column.  This view can also be joined to the vManagementPack view on the ManagementPackRowId column to show which management pack defines each type of object.

vManagementPack

This view provides information about the Management Packs in the Operations Manager environment and can be joined to the vManagedEntityType, vMonitor, and vRule views.

vRelationship

This view provides relationship information, which can be used to retrieve group membership for Managed Entities.  This view can be joined to the vManagedEntity view on the SourceManagedEntityRowId and TargetManagedEntityRowId columns.

vRelationshipType

This view provides information about the type of relationships identified in the vRelationship view and can be joined to other views on the RelationshipTypeRowId column.

vManagementGroup

This view provides information about Management Groups that send data to the Data Warehouse and can be joined to several other views on the ManagementGroupRowId column.