Appendix C - Data Types and Sample Queries

Article
05/13/2011

Applies To: Operations Manager 2007 R2, Operations Manager 2007 SP1

This appendix provides information about specific data types and sample queries for operational, performance, and discovery data that can be used when creating custom reports.

For State, Alert, and Performance data, you need to know which managed entity you want to query. To find this information, you get the ManagedEntityRowID from the vManagedEntity view. For groups, you can use the following query to return all managed entities within the group:

First, retrieve the ManagedEntityRowID for the group:
select * from vmanagedentity
where fullname like 'Microsoft.SystemCenter.AllComputersGroup'
Note the ManagedEntityRowID.
Run the following query, with these changes:
- Update the start(1st)and end(2nd) dates to return all entities that were a member of that group during these dates
- Replace the Containment Value below (166) with the ManagedEntityRowID returned in Step 1.
```
EXEC  [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectLis tParse]'2008-01-01 10:50:23.150','2008-02-05 10:50:23.150', '<Data><Objects><Object Use="Containment">166</Object></Objects></Data>
```
You can join the resulting list of managed entities to the performance, state, and alert tables to retrieve relevant information.

Operational Data

State

State information is derived from different types of monitoring and is stored directly in the OperationsManagerDW database. State is summarized per object and per time, and it is made available in different time units: hourly, sub-hourly, and raw.

vStateHourlyFull

Column	Datatype
DateTime	datetime
Date	datetime
Time	char(8)
ManagedEntityMonitorRowId	int
ManagedEntityRowID	int
IntervalDurationMilliseconds	int
InYellowStateMilliseconds	int
InRedStateMilliseconds	int
InPlannedMaintenanceMilliseconds	int
InUnplannedMaintenanceMilliseconds	int
InDisabledStateMilliseconds	int
HealthServiceUnavailableMilliseconds	int
InWhiteStateMilliseconds	int
InGreenStateMilliseconds	int

vStateDailyFull

Column	Datatype
DateTime	datetime
Date	datetime
Time	char(8)
ManagedEntityMonitorRowId	int
ManagedEntityRowID	int
IntervalDurationMilliseconds	int
InYellowStateMilliseconds	int
InRedStateMilliseconds	int
InPlannedMaintenanceMilliseconds	int
InUnplannedMaintenanceMilliseconds	int
InDisabledStateMilliseconds	int
HealthServiceUnavailableMilleseconds	int
InWhiteStateMilliseconds	int
InGreenStateMilliseconds	int

vStateRaw

Column	Datatype
EventOriginId	uniqueidentifier
ManagedEntityMonitorRowId	int
DateTime	datetime
OldHealthState	tinyint
NewHealthState	tinyint

Query:

SELECT

vStateHourlyFull.DateTime,

vStateHourlyFull.InRedStateMilliseconds,

vStateHourlyFull.InYellowStateMilliseconds,

vStateHourlyFull.InGreenStateMilliseconds,

vStateHourlyFull.InWhiteStateMilliseconds,

vStateHourlyFull.InDisabledStateMilliseconds,

vStateHourlyFull.IntervalDurationMilliseconds,

vStateHourlyFull.InPlannedMaintenanceMilliseconds,

vStateHourlyFull.InUnplannedMaintenanceMilliseconds,

vStateHourlyFull.HealthServiceUnavailableMilliseconds,

vManagedEntity.ManagedEntityGuid,

vStateHourlyFull.ManagedEntityMonitorRowId,

vManagedEntityTypeImage.Image,

vManagedEntity.ManagedEntityDefaultName,

vManagedEntityType.ManagedEntityTypeGuid

FROM

vStateHourlyFull INNER JOIN

vManagedEntity ON vStateHourlyFull.ManagedEntityRowId =

vManagedEntity.ManagedEntityRowId INNER JOIN

vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId =

vManagedEntityType.ManagedEntityTypeRowId INNER JOIN

vMonitor ON vStateHourlyFull.MonitorRowId =

vMonitor.MonitorRowId LEFT OUTER JOIN

vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId

= vManagedEntityTypeImage.ManagedEntityTypeRowId AND

vManagedEntityTypeImage.ImageCategory = N'u16x16Icon'

WHERE

(vManagedEntity.ManagedEntityRowId in (/*List of Managed Entities*/)

AND (vMonitor.MonitorSystemName = @MonitorName)

AND (vStateHourlyFull.DateTime >= @StartDate)

AND (vStateHourlyFull.DateTime < @EndDate)

AND (vStateHourlyFull.Date

BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate))

Performance

Performance data is stored directly in the OperationsManagerDW. Performance data is available in daily and hourly aggregates, and with calculated minimum, maximum, and average values, and in the standard deviation.

Perf.vPerfDaily

Column	Datatype
Datetime	datetime
PerformanceRuleInstanceRowID	int
ManagedEntityRowID	int
SampleCount	int
AverageValue	float
MinValue	float
MaxValue	float
StandardDeviation	float

Perf.vPerfHourly

Column	Datatype
Datetime	datetime
PerformanceRuleInstanceRowID	int
ManagedEntityRowID	int
SampleCount	int
AverageValue	float
MinValue	float
MaxValue	float
StandardDeviation	float

Perf.vPerfRaw

Column	Datatype
Datetime	datetime
PerformanceRuleInstanceRowID	int
ManagedEntityRowID	int
SampleCount	int

Query:

SELECT   
   vManagedEntityTypeImage.Image, 
   vPerfHourly.DateTime, 
   vPerfHourly.SampleCount, 
   vPerfHourly.AverageValue, 
   vPerfHourly.StandardDeviation, 
   vPerfHourly.MaxValue, 
   vManagedEntity.FullName, 
   vManagedEntity.Path, 
   vManagedEntity.Name, 
   vManagedEntity.DisplayName, 
   vManagedEntity.ManagedEntityDefaultName, 
   vPerformanceRuleInstance.InstanceName,
   vPerformanceRule.ObjectName,
   vPerformanceRule.CounterName
FROM     
   Perf.vPerfHourly INNER JOIN
   vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId =  
   vManagedEntity.ManagedEntityRowId INNER JOIN
   vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = 
   vManagedEntityType.ManagedEntityTypeRowId LEFT OUTER JOIN
   vManagedEntityTypeImage ON vManagedEntityType.ManagedEntityTypeRowId =  
   vManagedEntityTypeImage.ManagedEntityTypeRowId INNER JOIN
   vPerformanceRuleInstance ON   
   vPerformanceRuleInstance.PerformanceRuleInstanceRowId =  
   Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
   vPerformanceRule ON vPerformanceRuleInstance.RuleRowId =    
   vPerformanceRule.RuleRowId
WHERE     
   (vManagedEntity.ManagedEntityRowId in (/*List of Managed Entities*/))
   AND(vPerformanceRule.ObjectName IN ('MEMORY'))
   AND (vPerformanceRule.CounterName IN ('Available MBytes'))
ORDER BY vPerfHourly.DateTime

Discovery

There are two types of data that are discovered: objects and views.

Objects

Operations Manager discovers the objects that have discovery rules defined for them. Discovery data is then stored in the OperationsManager and in the OperationsManagerDW databases. Besides up-to-date discovery data, the OperationsManagerDW database also contains historical discovery data even after the respective objects are no longer discovered. Using that data, you can then search the OperationsManagerDW database for objects for which the OperationsManager database no longer has active monitoring data.

Views

This view describes which management group the object belongs to. This view allows you to specify a range in which to search for an object. A single object can have multiple rows in vManagedEntity, as it can be discovered and removed multiple times in a distinct management group.

Column	Datatype
ManagedEntityManagementGroupRowId	int identity
ManagedEntityRowId	int
FromDateTime	datetime
ToDateTime	datetime
DWCreatedDateTime	datetime
DWLastModifiedTime	datetime

This view displays objects that were discovered by Operations Manager. The OperationsManagerDW contains data about objects that are no longer being actively managed by a management group. There can be multiple rows per managed entity because an entity can be discovered and removed within a management group multiple times.

Column	Datatype
ManagedEntityRowId	int identity
ManagementGroupRowId	int
ManagedEntityGuid	uniqueidentifier
ManagedEntityTypeRowId	int
FullName	ntext
Path	ntext
Name	ntext
DisplayName	ntext
ManagedEntityDefaultName	ntext
DWCreatedDateTime	datetime
TopLevelHostManagedEntityRowId	int

Object Properties

Object properties are discovered by discovery rules from different management packs. The OperationsManagerDW stores the discovery data and tracks when the discovery finds changes and what old and new values are.

Events

Events are stored directly in the OperationsManagerDW database with their respective details and parameters. No aggregation or summarization is done.

Event.vEvent

Column	Datatype
EventOriginId	uniqueidentifier
DateTime	datetime
EventPublisherRowId	int
EventChannelRowId	smallint
EventCategoryRowId	smallint
EventLevelId	tinyint
LoggingComputerRowId	int
EventNumber	bigint
EventDisplayNumber	int
UserNameRowId	int
RawDescriptionHash	uniqueidentifier
ParameterHash	uniqueidentifier
EventDataHash	uniqueidentifier

vEventCategory

Column	Datatype
EventCategoryRowId	smallint identity
EventPublisherRowId	int
EventCategoryId	smallint
EventCategoryTitle	nvarchar
LastReceivedDateTime	smalldatetime

vEventChannel

Column	Datatype
EventChannelRowId	smallint identity
EventChannelTitle	nvarchar
LastReceivedDateTime	smalldatetime

vEventDetail

Column	Datatype
EventOriginId	uniqueidentifier
RawDescription	ntext
RenderedDescription	ntext
EventData	xml

vEventParameter

Column	Datatype
EventOriginId	uniqueidentifier
ParameterIndex	tinyint
ParameterValue	nvarchar

vEventUserName

Column	Datatype
EventUserNameRowId	int identity
UserName	nvarchar
LastReceivedDateTime	smalldatetime

vEventLoggingComputer

Column	Datatype
EventLoggingComputerRowId	smallint identity
ComputerName	nvarchar
LastReceivedDateTime	smalldatetime

Query:

SELECT            
      vEvent.DateTime,
      vEventPublisher.EventPublisherName as 'EventSource', 
      vEventLoggingComputer.ComputerName as 'Computer', 
      vEventLevel.EventLevelTitle as 'Type', 
      vEvent.EventDisplayNumber as 'EventID',
      vEventChannel.EventChannelTitle, 
      vEventUserName.UserName, 
      vEventDetail.RenderedDescription as 'EventDescription' 
FROM
      Event.vEvent LEFT OUTER JOIN
      vEventUserName ON vEvent.UserNameRowId =   
      vEventUserName.EventUserNameRowId LEFT OUTER JOIN
      vEventCategory ON vEvent.EventCategoryRowId =   
      vEventCategory.EventCategoryRowId LEFT OUTER JOIN
      vEventPublisher ON vEvent.EventPublisherRowId = 
      vEventPublisher.EventPublisherRowId LEFT OUTER JOIN
      vEventLoggingComputer ON vEvent.LoggingComputerRowId = 
      vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN
      vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN
      vEventChannel ON vEvent.EventChannelRowId = 
      vEventChannel.EventChannelRowId LEFT OUTER JOIN
      Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId
WHERE vEventLevel.EventLevelTitle = 'Error'
ORDER BY vEvent.DateTime, vEventLoggingComputer.ComputerName

Alerts

Alerts are stored directly in the OperationsManagerDW database with their respective details and parameters. No aggregation or summarization is done.

Alert.vAlert

Column	Datatype
AlertGuid	uniqueidentifier
AlertProblemGuid	uniqueidentifier
AlertName	nvarchar(256)
AlertDescription	nvarchar(256)
Severity	tinyint
Priority	tinyint
Category	nvarchar(256)
ManagedEntityRowID	int
WorkflowRowId	int
MonitorAlertInd	bit
RaisedDateTime	datetime
SiteName	nvarchar(256)
RepeatCount	int
AlertStringGuid	uniqueidentifier
ParameterHash	uniqueidentifier
DBCreatedDateTime	uniqueidentifier
DWCreatedDateTime	uniqueidentifier
DWLastModifiedTime	uniqueidentifier

Alert.vAlertDetail

Column	Datatype
AlertGuid	uniqueidentifier
Owner	nvarchar(256)
TicketID	nvarchar(256)
CustomField1	nvarchar(256)
CustomField2	nvarchar(256)
CustomField3	nvarchar(256)
CustomField4	nvarchar(256)
CustomField5	nvarchar(256)
CustomField6	nvarchar(256)
CustomField7	nvarchar(256)
CustomField8	nvarchar(256)
CustomField9	nvarchar(256)
CustomField10	nvarchar(256)
DBLastModifiedDateTime	datetime
DBLastModifiedByUserId	nvarchar(256)
DWCreatedDateTime	datetime

Alert.vAlertParameter

Column	Datatype
AlertGuid	uniqueidentifier
ParameterIndex	tinyint
ParameterValue	nvarchar(255)

Query:

SELECT     
   Alert.vAlert.AlertName, 
   Alert.vAlert.AlertDescription, 
   Alert.vAlert.Severity, 
   Alert.vAlert.Priority, 
   Alert.vAlert.Category, 
   Alert.vAlert.RaisedDateTime, 
   Alert.vAlert.RepeatCount, 
   vManagedEntity.FullName, 
   vManagedEntity.Path, 
   vManagedEntity.Name, 
   vManagedEntity.DisplayName, 
   vManagedEntity.ManagedEntityDefaultName
FROM         Alert.vAlertDetail INNER JOIN
   Alert.vAlert ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
   vManagedEntity ON Alert.vAlert.ManagedEntityRowId =    
   vManagedEntity.ManagedEntityRowId
WHERE vManagedEntity.ManagedEntityRowId in (/*List of Managed Entities*/)
AND Alert.vAlert.Severity = 1

Note: The queries above will return values for images which will allow you to visualize an object in a report.

Visualizing the objects shown using an image

Where is it used?

When an object is used in a report the image is queried from the vImage view.

How is it defined in the report?

<Image Name="ObjectImage">
                     <Sizing>Clip</Sizing>
                     <MIMEType>image/png</MIMEType>
                     <ZIndex>7</ZIndex>
                     <Source>Database</Source>
                     <Style>
                       <BorderStyle>
                         <Default>Solid</Default>
                         <Right>None</Right>
                       </BorderStyle>
                       <PaddingLeft>4pt</PaddingLeft>
                       <BorderColor>
                         <Default>DarkGray</Default>
                       </BorderColor>
                       <PaddingTop>2pt</PaddingTop>
                     </Style>
                     <Value>=Fields!Image.Value</Value>
</Image>

Where does the image come from?

The image is taken from a management pack

Appendix C - Data Types and Sample Queries

Operational Data

State

vStateHourlyFull

vStateDailyFull

vStateRaw

Query:

Performance

Perf.vPerfDaily

Perf.vPerfHourly

Perf.vPerfRaw

Query:

Discovery

Objects

Views

Object Properties

Events

Event.vEvent

vEventCategory

vEventChannel

vEventDetail

vEventParameter

vEventUserName

vEventLoggingComputer

Query:

Alerts

Alert.vAlert

Alert.vAlertDetail

Alert.vAlertParameter

Query:

Visualizing the objects shown using an image

Additional resources