Export (0) Print
Expand All

Appendix C - Data Types and Sample Queries

Updated: September 30, 2008

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:

  1. First, retrieve the ManagedEntityRowID for the group:
    select * from vmanagedentity
    where fullname like 'Microsoft.SystemCenter.AllComputersGroup'

  2. Note the ManagedEntityRowID.

  3. 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>
    
  4. 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 &gt;= @StartDate)

AND (vStateHourlyFull.DateTime &lt; @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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft