Monitoring Database Views and Tables

  • Article

The views and staging table in the Windows Server AppFabric monitoring database are defined with the columns, and associate types, that they implement. These are described as generic types that are replaced by an implementation specific type for the database on which these views are implemented. The requirement is that these types can be converted to an equivalent .NET type using the ADO .NET provider for the database. The staging table and the views are installed in the default schema for the database.

Note

Queries or insert statements within AppFabric do not prefix with a schema. Rather, they default to the schema for the logged in user

Database Types and Views

These are types and views used in the monitoring database to store data used by the AppFabric Monitoring tools.

Types Used

Type Description

Int

Represents a 32-bit signed integer

Char

Represents a Unicode character

Byte

Represents an 8-bit unsigned integer

Int64

Represents a 64-bit signed integer

Datetime

Represents an instant in time, typically expressed as a date and time of day

Boolean

Represents a Boolean value

View Name ASEvents

This view is created on all the analytic tracing events that are emitted when a WCF service is invoked.

Column Name Type isNullable Description

Id

Int64

N

The unique ID for this event entry in the view

EventTypeId

Int

N

The ID of the event emitted from the WCF and WF service.

EventType

Char - minimum size 41

Y

Name of the event type emitted

EventVersion

Int

N

Version of the event emitted

EventSourceId

Int

N

The event source for this event. This refers to the ID in the EventSources view.

ProcessId

Int

N

Process ID of the process that emitted the event.

TraceLevelId

Byte

N

The ID for the trace. Values correspond to the string stored in Trace Level:0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”

TraceLevel

Char - minimum size 11

Y

Store strings corresponding to the TraceLevelId as specified above.

E2EActivityId

Char - minimum size 36

Y

The end-to-end activity ID for this event

TimeCreated

Datetime

N

Timestamp (UTC) of when the event was created

View: ASEventSources

This view stores metadata for the events to identify the source of events such a service name, site and virtualpath.

Column Name Type isNullable Description

Id

Int64

N

The unique ID for this entry in the view

Name

Char – minimum size 256

Y

The service name

Computer

Char – minimum size 450

N

The name of the computer from which the event is emitted

Site

Char – minimum size 256

Y

The site name where the application in installed

VirtualPath

Char – minimum size 256

Y

The virtual path for the application or service

ApplicationVirtualPath

Char – minimum size 256

Y

The application virtual path

ServiceVirtualPath

Char – minimum size 256

Y

The service virtual path

View: ASWcfEvents

This view is created on all the analytic tracing events that are emitted when a WCF service is invoked.

  Type isNullable Description

Id

Int64

N

The unique ID for this entry in the view

EventTypeId

int

N

The ID of the event emitted from the WCF and WF service. The event ID matches the ID stored in the ETW manifest for the events

EventType

Char –minimum size 41

Y

Name of the event type emitted

EventVersion

int

N

Version of the event emitted

EventSourceId

int

N

The event source for this event. This refers to the ID in the EventSources view.

ProcessId

int

N

Process ID of the process that emitted the event.

TraceLevelId

Byte

N

The ID for the trace. Values correspond to the string stored in Trace Level:0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”

TraceLevel

Char – minimum size 11

Y

Store strings corresponding to the TracelevelId as specified above.

E2EActivityId

Char – minimum size 36

Y

The end-to-end activity ID for this event

TimeCreated

Datetime

N

Timestamp (UTC) of when the event was created

CorrelationId

Char - minimum size 36

Y

CorrelationId of the event used to correlate Send/Receives.

ServiceTypeName

Char – minimum size 450

Y

The typename for this service

InspectorTypeName

Char – minimum size 450

Y

Typename of the parameter or message inspector

ErrorHandlerType

Char – minimum size 450

Y

Typename for the error handler

Handled

int

Y

Indication if error is handled

ExceptionMessage

Char – size unlimited

Y

The exception message emitted from WCF error-related events

ExceptionTypeName

Char – minimum size 450

Y

The type name for the exception

ThrottleProperty

Char – minimum size 450

Y

Throttle property name (MaxConcurrentCalls, MaxConcurrentSessions, MaxConcurrentInstances)

ThrottleCapacity

int

Y

Capacity

Uri

Char – minimum size 450

Y

The endpoint URI for the event

Action

Char – minimum size 450

Y

The SOAP action

OperationName

Char – minimum size 450

Y

Name of the operation that was invoked

CallerInfo

Char – minimum size 450

Y

Address of the client/proxy that called the service

ContractName

Char – minimum size 450

Y

Contract Name for the service

Destination

Char – minimum size 450

Y

The destination where the message is sent

Duration

int

Y

The time taken to complete the operation

InstanceKey

Char – minimum size 36

Y

The key to the workflow instance

Values

Char – minimum size 450

Y

The values used to correlate to the workflow instance

ParentScope

Char – minimum size 450

Y

The WF correlation scope

AggregateCount

Int

Y

Aggregate of number of calls completed. This field is populated when an event representing aggregate of WCF completed calls is emitted.

AverageDuration

Int

Y

Average duration of the calls. This field is populated with the aggregate event for WCF completed calls.

MaxDuration

Int

Y

The maximum call duration observed within the period the aggregated event was emitted.

View: ASWfEvents

This view is created on the tracking events emitted for WF instances.

Column Name Type isNullable Description

Id

Int

N

The unique ID for this entry

EventTypeId

Int

N

The ID of the event emitted from the WCF and WF service.

EventType

Char – minimum size 41

Y

Name of the event type emitted

EventVersion

int

N

Version of the event emitted

EventSourceId

int

N

The event source for this event. This refers to the ID in the EventSources view.

ProcessId

int

N

Process ID of the process that emitted the event

WorkflowInstanceId

Char – minimum size 36

Y

The instance ID for the workflow

TrackingProfileId

Int

Y

The ID of the tracking profile. Refers to the ID in the WfTrackingProfiles view

E2EActivityId

Char – minimum size 36

Y

The end-to-end activity ID for this event

TraceLevelId

Byte

N

The ID for the trace. Values correspond to the string stored in Trace Level:0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”

TraceLevel

Char – minimum size 11

Y

Store strings corresponding to the TracelevelId as specified above

RecordNumber

Int64

Y

A sequence number that identifies the order in which tracking events are emitted for a workflow instance

AnnotationSetId

Int

Y

The key to the annotation set for this workflow event.

TimeCreated

Datetime

N

Timestamp (UTC) of when the event was created

ActivityName

Char – minimum size 450

Y

Name of the activity in the workflow

ActivityId

Char – minimum size 450

Y

The activity ID for the activity in the workflow

ActivityInstanceId

Char – minimum size 450

Y

The activity instance ID for the activity in executing workflow instance

ActivityRootId

Char – minimum size 450

Y

The ID/name of the root activity for the workflow

ActivityTypeName

Char – minimum size 450

Y

Type name of the activity

CustomRecordName

Char – minimum size 450

Y

Name of the custom tracking record

SourceName

Char – minimum size 450

Y

The source activity name that resulted in workflow unhandled exception

SourceId

Char – minimum size 450

Y

The source activity ID that resulted in workflow unhandled exception

SourceInstanceId

Char – minimum size 450

Y

The source activity instance ID that resulted in workflow unhandled exception

SourceTypeName

Char – minimum size 450

Y

The source activity type name that resulted in workflow unhandled exception

OwnerType

Char – minimum size 450

Y

Owner activity type name for the bookmark resumption event

State

Char – minimum size 450

Y

The state of workflow instance or activity instance

ChildActivityId

Char – minimum size 450

Y

Child activity ID emitted with ActivityScheduled of CancelRequested tracking record

ChlidInstanceId

Char – minimum size 450

Y

Child activity instance ID emitted with ActivityScheduled of CancelRequested tracking record

ChildTypeName

Char – minimum size 450

Y

Child activity type name emitted with ActivityScheduled of CancelRequested tracking record

ChildActivityName

Char – minimum size 450

Y

Child activity name emitted with ActivityScheduled of CancelRequested tracking record

FaultSrcName

Char – minimum size 450

Y

Activity Name of the activity where the fault propagation record was emitted

FaultSrcId

Char – minimum size 450

Y

Activity ID of the activity where the fault propagation record was emitted

FaultSrcInstanceId

Char – minimum size 450

Y

Activity Instance ID of the activity where the fault propagation record was emitted

HandlerId

Char – minimum size 450

Y

Fault handler activity ID

HandlerInstanceId

Char – minimum size 450

Y

Fault handler activity instance ID

FaultHandler

Char – minimum size 450

Y

Fault handler activity name

FaultSourceType

Char – minimum size 450

Y

Fault source activity type

FaultHandlerType

Char – minimum size 450

Y

Fault handler activity type

Fault

Char – size unlimited

Y

Fault message

IsFaultSource

Char – minimum size 450

Y

Indicates if the event is emitted from the fault source

SubInstanceId

Char – minimum size 450

Y

The instance ID for the bookmark scope

OwnerActivityId

Char – minimum size 450

Y

The ID for the bookmark scope

OwnerInstanceId

Char – minimum size 450

Y

Owner activity instance ID for the bookmark resumption event

OwnerActivityName

Char – minimum size 450

Y

Owner activity name for the bookmark resumption event

Exception

Char – size unlimited

Y

The exception message associated with workflow unhandled exception event

Reason

Char – size unlimited

Y

The reason the workflow instance aborted, suspended or terminated

View: ASWfInstances

This view is created on the active WF instances. There is one row per WF instance.

Column Name Type isNullable Description

id

Int

N

The unique ID for this entry

WorkflowInstanceId

Char – minimum size 36

N

Workflow instance ID

LastEventSourceId

Int

N

The source ID for the last event that was emitted for the workflow instance

LastEventStatus

Datetime

N

The status of the workflow instance extracted from the last event

StartTime

Datetime

N

Timestamp (UTC) of when the workflow instance started.

LastModifiedTime

Datetime

N

Timestamp (UTC) for the last event emitted for the workflow instance

CurrentDuration

Int

N

Duration for the running workflow (LastModifiedTime – StartTime)

ExceptionCount

Int

N

The number of exceptions encountered by the workflow instance

LastAbortedTime

Datetime

Y

Timestamp(UTC) for the last aborted event for the workflow instance

View: ASWfEventProperties

Column Name Type isNullable Description

EventId

Int64

N

The unique ID for this entry

Name

Char – minimum size 128

N

The name of the event property

WfDataSource

Char – minimum size 8

Y

The source of the property.(Argument, Variable or custom data)

Type

Char – minimum size 128

Y

The type of the property

Value

Char – minimum size 128

Y

The value of the property

ValueBlob

Char – size unlimited

Y

The value of property in case the type is a complex type

TimeCreated

Datetime

N

Timestamp when this property was emitted

View: ASWfEventAnnotations

This view is created on the annotations that get emitted with WF tracking events from the WF services.

Column Name Type isNullable Description

Id

Int

N

The unique identifier for this event row

AnnotationSetId

Int

N

The key to find the WF event that emitted the annotation

Name

Char – minimum size 128

N

Name of the annotation

Value

Char – minimum size 450

N

Value of the annotation

View: ASWfPropertyNames

This view is created on the tracked property names that are extracted from WF services.

Column Name Type isNullable Description

EventSourceId

Int

N

The event source for this event. This refers to the ID in the EventSources view

Name

Char – minimum size 128

N

Name of the tracked property

Type

Char – minimum size 128

N

Type of the tracked property

View: ASWfTrackingProfiles

This view is created on all the tracking profiles used by the WF services to emit WF tracking events.

Column Name Type isNullable Description

Id

Int

N

The unique ID for the tracking profile

Name

Char – minimum size 450

Y

Name of the tracking profile

View: ASTransferEvents

This view is created on the transfer events that get emitted whenever the end to end activity ID changes.

Column Name Type isNullable Description

Id

Int64

N

The unique ID for this entry in the view

EventTypeId

int

N

The ID of the event emitted from the WCF and WF service. The event ID matches the ID stored in the ETW manifest for the events

EventType

Char –minimum size 41

Y

Name of the event type emitted

EventVersion

int

N

Version of the event emitted

EventSourceId

int

N

The event source for this event. This refers to the ID in the EventSources view.

ProcessId

int

N

Process ID of the process that emitted the event.

TraceLevelId

Byte

N

The ID for the trace. Values correspond to the string stored in Trace Level:0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”

TraceLevel

Char – minimum size 11

Y

Store strings corresponding to the TracelevelId as specified above.

E2EActivityId

Char – minimum size 36

Y

The source end-to-end activity ID for this event

ToE2EActivityId

Char – minimum size 36

Y

The new end-to-end activity ID that the transfer event transfers to

TimeCreated

Datetime

N

Timestamp (UTC) of when the event was created

View: ASDBVersion

This view can be used to maintain versioning information for monitoring database.

Column Name Type isNullable Description

DBIdentity

Char – minimum size 36

N

Unique identifier for the monitoring database

MajorVersion

int

N

Major version of the Monitoring Database

MinorVersion

int

N

Minor version of the Monitoring Database

BuildVersion

int

N

Build version of the Monitoring Database

RevisionVersion

int

N

Revision version of the Monitoring Database

Database Tables

The following are table definitions from the AppFabric monitoring database.

ASStagingTable

The Event Collection service stores event data to this table.

Column Name Type isNullable Description

Id

Int64 (bigint)

N

The unique ID for this entry

ArchiveID

Int64

N

The archive id for the events. For events stored by event collector this value is 0

EventTypeId

Int

N

The id of the event emitted from the WCF/WF service

E2EActivitd

Char – minimum size 36

Y

The end to end activity ID for this event

Computer

Char – minimum size 36

N

The computer name where the event was emitted from

EventSource

Char – minimum size 1024

Y

The source of the event. Contains the web site/virtual path. It's format is defined as 'Web Site Name Application Virtual Path|Service Virtual Path|ServiceName' Example: 'Default Web Site/CalculatorApplication|/CalculatorService.svc|CalculatorService'

ProcessId

Int

N

Process Id of the process that emitted the event.

TraceLevelId

Small Int (tiny Int)

N

The Id for the trace. Values correspond to the string stored in Trace Level0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”

TimeCreated

Datetime

N

Timestamp (UTC) of when the event was created

Data1Str

Char – minimum size 450

Y

Column for payload data from events

Data2Str

Char – minimum size 450

Y

Column for payload data from events

Data3Str

Char – minimum size 450

Y

Column for payload data from events

Data4Str

Char – minimum size 450

Y

Column for payload data from events

Data5Str

Char – minimum size 450

Y

Column for payload data from events

Data6Str

Char – minimum size 450

Y

Column for payload data from events

Data7Str

Char – minimum size 450

Y

Column for payload data from events

Data8Str

Char – minimum size 450

Y

Column for payload data from events

Data9Str

Char – minimum size 450

Y

Column for payload data from events

Data1MaxStr

Char – size unlimited

Y

Column for payload data from events

Data1Int

Int

Y

Column for payload data from events

Data2Int

Int

Y

Column for payload data from events

Data3Int

Int

Y

Column for payload data from events

Data1BigInt

Long (bigint)

Y

Column for payload data from events

Data1UniqueId

Char – minimum size 36

Y

Column for payload data that represents unique identifier such as WF instance Id

CustomAnnotations

Char – size unlimited

Y

Custom annotations defined on WF tracking event

CustomProperties

Char – size unlimited

Y

Variables or custom data emitted with the WF tracking event

CustomArguments

Char – size unlimited

Y

Arguments emitted with the WF tracking event