Monitoring Database Event Mappings

Article
09/12/2012

This topic contains the mapping of the fields from the staging table in the monitoring database into the event views for each of the event types emitted by the Windows Workflow Foundation (WF) runtime. This information is needed when configuring a non-SQL monitoring database for use with Microsoft AppFabric 1.1 for Windows Server. When using SQL Server, all the views and the staging table are created by the AppFabric configuration process. In a non-SQL database implementation, however, you will need to manually create these views and the staging table. When an event occurs in a WCF or WF service managed by AppFabric, the Event Collection service writes a row corresponding to that event to the ASStagingTable table in the monitoring database. That data needs to be mapped into the corresponding view so it can be displayed using the AppFabric Dashboard. This mapping can occur using whatever mechanism you choose to get the data from the staging table into any tables that support the monitoring views. For instance, you could use a database trigger or job.

This document only contains a few events and view mappings. For a complete set of events and their associated mappings, download the AppFabric Database Event Mappings. For more information on this mapping process, see How To: Use a Non-SQL Server Monitoring Database and Monitoring Database Views and Tables.

Common Mapping for All Events

ASStagingTable Table

Every event will populate the following fields in the ASStagingTable table.

ASStagingTable Columns	Event Data
EventTypeId	ID of the emitted event
E2EActivityId	End-to-end activity ID emitted with this event
Computer	Machine name where the event is emitted
EventSource	Virtual path for this event
ProcessId	Process ID
TraceLevelId	Trace level for the emitted event
TimeCreated	Time created for this event

ASEventSources View

Almost all events will populate the ASEventSources view (the exception is event ID 499).

Column Name	Source of the data
ID	ID for the event source. This is used by other views to get the details for the event source.
Name	Name of the service extracted from the EventSource column in the ASStagingTable
Computer	Mapped to Computer in the ASStagingTable
Site	Name of the site extracted from the EventSource column in the ASStagingTable
VirtualPath	Name of the full VirtualPath extracted from the EventSource column in the ASStagingTable
ApplicationVirtualPath	Name of the Application Virtual Path extracted from the EventSource column in the ASStagingTable
ServiceVirtualPath	Name of the Service Virtual Path extracted from the EventSource column in the ASStagingTable

ASEvents View

Almost all events will populate the ASEvents view (the exception is event ID 499).

Column Name	Source of data
EventTypeId	EventTypeId mapped to ASStagingTable.EventTypeId
EventName	Name of the event
EventVersion	Version of the event. Currently 0.
EventSourceID	Mapped to ID for the event source in the ASEventSources views
ProcessId	Mapped to ASStagingTable.ProcessId
TraceLevelId	Mapped to ASStagingTable.TraceLevelID
TraceLevel	String representation of the TraceLevelId
E2EActivityId	Mapped to ASStagingTable.E2EActivityId
TimeCreated	Mapped to ASStagingTable.TimeCreated

Events

Here are some of the event mappings to implement a monitoring database.

Event ID 100

In addition to the common columns, the Event Collection service populates the following columns in the ASStagingTable table for Event ID 100.

Event Data	ASStagingTable Columns
TypeName	Data1UniqueId
RecordNumber	Data1BigInt
ActivityRootId	Data3Str
State	Data2Str
Tracking Profile Name	Data1Str
Annotations	CustomAnnotations

The data from event 100 populates the following views: ASEventSources, ASWfEvents, ASEvents, ASWfTrackingProfiles, ASWfEventAnnotations.The mapping of data from ASStagingTable to the common views ASEventSources and ASEvents is specified in the common mappings.

View: ASWfEventAnnotations

This view is updated with the annotations that are emitted with WF tracking events from the WF services.

Column Name	Populated Data
Id	Unique identifier for this event row
AnnotationSetId	Key to find the WF event that emitted the annotation
Name	Name of the annotation obtained from ASStagingTable.CustomAnnotations
Value	Value of the annotation obtained from ASStagingTable.CustomAnnotations

Extracting Annotations Name and Value from ASStagingTable.CustomAnnotations

Annotations are stored as follows. The annotations are emitted with this event. The values of annotations are stored in this format.

 
< items >
< item  name = "annotationName" type="System.String">annotationValue</item>
</items >

If no annotations are specified, then the string contains </items>. The name and value are extracted by parsing the value stored in the ASStagingTable.CustomAnnotations column.

View ASWfTrackingProfiles

Column Name	Populated Data
Id	Unique ID for the tracking profile
Name	Mapped to ASStagingTable-> Column ASStagingTable.Data1Str

View ASWfEvents

Only columns populated for this event in this view are specified. All other columns have a NULL value. The value specifically populated by the event is specified in bold. This view is created on the tracking events emitted for WF instances.

Column Name	Populated Data
Id	Generated unique ID
EventTypeId	100 (Mapped To: ASStagingTable-> Column EventTypeId)
EventType	“WorkflowInstanceRecord”
EventVersion	0
EventSourceId	ID in ASEventSources view
ProcessId	Mapped to ASStagingTable-> Column: ASStagingTable.ProcessId
WorkflowInstanceId	Mapped to ASStagingTable-> Column: ASStagingTable.Data1UniqueId
TrackingProfileId	Unique ID of tracking profile mapped to the ID column in the ASWfTrackingProfiles view. Both this and the ASWfTrackingProfiles are updated in a single transaction.
E2EActivityId	Mapped to ASStagingTable-> Column: ASStagingTable.E2EActivityId
TraceLevelId	Mapped to ASStagingTable-> Column: ASStagingTable.TraceLevelId
TraceLevel	Dependent on TraceLevelId: 0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”
RecordNumber	Mapped to ASStagingTable-> Column: ASStagingTable.Data1BigInt
AnnotationSetId	Unique ID of Annotation Set mapped to the AnnotationSetId column in the ASWfEventAnnotations view. Both this and the ASWfEventAnnotations are updated in a single transaction.
TimeCreated	Mapped to ASStagingTable-> Column: ASStagingTable.TimeCreated
ActivityRootId	Mapped to ASStagingTable-> Column: ASStagingTable.Data3Str
State	Mapped to ASStagingTable-> Column: ASStagingTable.Data2Str

View: ASWfInstances

This view is updated with the instance state for the active WF. This view is updated after the ASWfEvents view but within the same transaction. When an event is added to the ASWfEvents view, this view needs to be updated simultaneously with the values inserted in the ASWfEvents view, and therefore in the same transaction. There is only one row per workflow instance. With each event that gets added to the WFEvents table the row for the workflow instance is modified. A row is added/updated in this table only under certain conditions. If state = “Deleted” or “Unloaded” (ASWfEvents.State) for an event with ID 100 then a row is not added in this table.

Column Name	Populated Data
Id	Unique ID for this entry
WorkflowInstanceId	Mapped to ASStagingTable-> Column: ASStagingTable.Data1UniqueId Added only once for a unique instance.
LastEventSourceId	The last eventSourceID that modified this row. Mapped to ASWfEvents.EventSourceId.
LastEventStatus	Mapped to ASWfEvents view-> Column: ASWfEvents.State. Inserted/updated only if state is not equal to “Deleted” or “Unloaded”. If state == “Deleted” or “Unloaded” then a row is not added in this table.
StartTime	Mapped to ASWfEvents-> Column: ASWfEvents.TimeCreated. This is the TimeCreated for the event with the lowest RecordNumber for this workflow instance.
LastModifiedTime	Mapped to ASWfEvents -> Column: ASWfEvents.TimeCreated. Updated when state changes. Also note that to update this column the RecordNumber of the WorkflowRecord is greater the previous RecordNumber that updated this value. (Possible implementation is to maintain a temporary table that maps the instance ID in this table to the RecordNumber of the Workflow Event Record from ASWfEvents that modified it. When a new event tries to update this row for this instance, check if the RecordNumber is greater than the last modified RecordNumber.)
CurrentDuration	Duration for the running workflow (LastModifiedTime – StartTime)
ExceptionCount	Number of exceptions encountered by the WF. Not updated by Event ID 100.
LastAbortedTime	Not updated by Event ID 100

For more information about this event, see 100 - WorkflowInstanceRecord (https://go.microsoft.com/fwlink/?LinkId=187224).

Event ID 103

In addition to the common columns, the Event Collection service populates the following columns in the ASStagingTable table for Event ID 103.

Event Data	ASStagingTable Columns
workflowinstanceid	Data1UniqueId
RecordNumber	Data1BigInt
Tracking Profile Name	Data1Str
State	Data2Str
ActivityName	Data3Str
ActivityId	Data4Str
ActivityInstanceId	Data5Str
ActivityTypeName	Data6Str
Annotations	CustomAnnotations
Variables	CustomProperties
Arguments	CustomArguments

The data from event 103 populates the following views: ASEventSources, ASWfEvents, ASEvents, ASWfTrackingProfiles, ASWfEventAnnotations, ASWfEventProperties, ASWfPropertyNames. The mapping of data from ASStagingTable to the common views ASEventSources and ASEvents is specified in the common mappings.

View: ASWfEventAnnotations

This view is updated with the annotations that are emitted with WF tracking events from the WF services.

Column Name	Populated Data
Id	Unique identifier for this event row
AnnotationSetId	Key to find the WF event that emitted the annotation
Name	Name of the annotation obtained from ASStagingTable.CustomAnnotations
Value	Value of the annotation obtained from ASStagingTable.CustomAnnotations

Extracting Annotations Name and Value from ASStagingTable.CustomAnnotations

The annotations are emitted with this event. The values of the annotations are stored in the following format. If no annotations are specified then the string contains </items>. The name and value are extracted by parsing the value stored in the ASStagingTable.CustomAnnotations column.

 
< items >
< item  name = "annotationName" type="System.String">annotationValue</item>
</items >

View: ASWfPropertyNames

This view is created on the tracked property names that are extracted from WF services. The name could be a variable name, an argument name, or a name within customtrackingrecord.

Column Name	Populated Data
EventSourceId	Event source for this event. This refers to the ID in the EventSources view.
Name	Name of the tracked property
Type	Type of the tracked property

Extracting Name and Type from ASStagingTable.CustomProperties or ASStagingTable CustomArguments

Workflow variables are stored in ASStagingTable.CustomProperties. For event 103 the format of variables is as follows. If no variables are specified then the string contains </items>. Parse the XML string to get the name and type of the variable.

< items >
< item  name = "variableName" type="System.String">variableValue</item>
</items >

Arguments are stored in ASStagingTable.CustomArguments. For event 103 the format of arguments is as follows. Parse the XML string to get the name and type of the argument. If no arguments are specified then the string contains </items>.

< items >
< item  name = "argumentName" type="System.String">argumentValue</item>
</items >

View: ASWfEventProperties

This view has one row per variable/argument/custom property that is extracted from the WF event.

Column Name	Populated Data
EventId	Maps to the Event ID in the ASWfEvents view -> ASWfEvents.Id refers to the WF event that emitted this data.
Name	Name extracted from parsing the XML from ASStagingTable.CustomArguments or ASStagingTable.CustomProperties
WfDataSource	Source of the property (argument, Variable, or custom data).If the data is populated from ASStagingTable.CustomArguments the value is “Argument”. If the data is populated from ASStagingTable.CustomProperties the value is “Variable”.
Type	Type of the property extracted from the CustomArguments of CustomProperties xml
Value	Value of the property extracted from the CustomArguments of CustomProperties xml when type is a simple type
ValueBlob	Value of property extracted from the CustomArguments of CustomProperties xml in case the type is a complex type
TimeCreated	Timestamp when this property was emitted mapped to ASWfEvents.TimeCreated

View ASWfTrackingProfiles

Column Name	Populated Data
Id	Unique ID for the tracking profile
Name	Mapped to ASStagingTable-> Column ASStagingTable.Data1Str

View ASWfEvents

Column Name	Populated Data
Id	Generated unique ID
EventTypeId	103 (Mapped To: ASStagingTable-> Column EventTypeId)
EventType	“ActivityStateRecord”
EventVersion	0
EventSourceId	ID in ASEventSources view
ProcessId	Mapped to ASStagingTable-> Column: ASStagingTable.ProcessId
WorkflowInstanceId	Mapped to ASStagingTable-> Column: ASStagingTable.Data1UniqueId
TrackingProfileId	Unique ID of tracking profile mapped to the ID column in the ASWfTrackingProfiles view. Both this and the ASWfTrackingProfiles are updated in a single transaction.
E2EActivityId	Mapped to ASStagingTable-> Column: ASStagingTable.E2EActivityId
TraceLevelId	Mapped to ASStagingTable-> Column: ASStagingTable.TraceLevelId
TraceLevel	Dependent on TraceLevelId: 0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”
RecordNumber	Mapped to ASStagingTable-> Column: ASStagingTable.Data1BigInt
AnnotationSetId	Unique ID of Annotation Set mapped to the AnnotationSetId column in the ASWfEventAnnotations view. Both this and the ASWfEventAnnotations are updated in a single transaction.
TimeCreated	Mapped to ASStagingTable-> Column: ASStagingTable.TimeCreated
ActivityName	Mapped to ASStagingTable-> Column: ASStagingTable.Data3Str
ActivityId	Mapped to ASStagingTable-> Column: ASStagingTable.Data4Str
ActivityInstanceId	Mapped to ASStagingTable-> Column: ASStagingTable.Data5Str
ActivityTypeName	Mapped to ASStagingTable-> Column: ASStagingTable.Data6Str
State	Mapped to ASStagingTable-> Column: ASStagingTable.Data2Str

View: ASWfInstances

This view is updated with the instance state for the active WF. This view is updated after the ASWfEvents view but within the same transaction. When an event is added to the ASWfEvents view this view needs to be updated simultaneously with the values inserted in the ASWfEvents view, and therefore in the same transaction. There is only one row per workflow instance. With each event that gets added to the WFEvents table the row for the workflow instance is modified.

Column Name	Populated Data
Id	Unique ID for this entry
WorkflowInstanceId	Mapped to ASStagingTable-> Column: ASStagingTable.Data1UniqueId. Added only once for a unique instance.
LastEventSourceId	The last eventSourceID that modified this row. Mapped to ASWfEvents.EventSourceId.
LastEventStatus	Mapped to the ASWfEvents view-> Column: ASWfEvents.State. Inserted/updated only if state is not equal to “Deleted” or “Unloaded”. If state == “Deleted” or “Unloaded” then a row is not added in this table.In the case of event 101 the value is “UnhandledException”.
StartTime	Mapped to ASWfEvents-> Column: ASWfEvents.TimeCreated. This is the TimeCreated for the event with the lowest RecordNumber for this workflow instance represented by ASWfEvents.WorkflowInstanceId.
LastModifiedTime	Mapped to ASWfEvents -> Column: ASWfEvents.TimeCreated. Updated when state changes. Also note that to update this column the RecordNumber of the WorkflowRecord is greater the previous RecordNumber that updated this value. (Possible implementation is to maintain a temporary table that maps the instance ID in this table to the RecordNumber of the Workflow Event Record from ASWfEvents that modified it. When a new event tries to update this row for this instance, check if the RecordNumber is greater than the last modified RecordNumber.)
CurrentDuration	Duration for the running workflow (LastModifiedTime – StartTime)
ExceptionCount	Number of exceptions encountered by the WF. Count of Events 101 encountered by this WF instance represented by instance ID ASWfEvents. WorkflowInstanceId.
LastAbortedTime	Latest time from the ASWfEvents view for the event with ID 101 or 102. For the workflow instance represented by ASWfEvents.WorkflowInstanceId.

For more information about this event, see 103 - ActivityStateRecord (https://go.microsoft.com/fwlink/?LinkId=187226).

Event ID 214

In addition to the common columns, the Event Collection service populates the following columns in the ASStagingTable table for Event ID 214.

Event Data	ASStagingTable Columns
MethodName	Data1Str
Duration	Data1Int

The data from event 214 populates the following views: ASEventSources, ASWcfEvents, ASEvents. The mapping of data from ASStagingTable to the common views ASEventSources and ASEvents is specified in the common mappings.

View ASWcfEvents

Only columns populated for this event in this view are specified. All other columns have a NULL value. The value specifically populated by the event is specified in bold.

ColumnName	Populated Data
Id	Generated unique ID
EventTypeId	214 (Mapped To: ASStagingTable-> Column EventTypeId)
EventType	“OperationCompleted”
EventVersion	0
EventSourceId	ID in the ASEventSources view
ProcessId	Mapped to ASStagingTable-> Column: ProcessId
TraceLevelId	Mapped to ASStagingTable-> Column: TraceLevelId
TraceLevel	Dependent on TraceLevelId: 0:”LogAlways”1:”Critical”2:”Error”3:”Warning”4:”Information”5:”Verbose”
E2EActivityId	Mapped to ASStagingTable-> Column: ASStagingTable.E2EActivityId
TimeCreated	Mapped to ASStagingTable-> Column: ASStagingTable.TimeCreated
OperationName	Mapped to ASStagingTable-> Column: ASStagingTable.Data1Str
Duration	Mapped to ASStagingTable-> Column: ASStagingTable.Data1Int

For more information about this event, see 214 - OperationCompleted (https://go.microsoft.com/fwlink/?LinkId=187225).

Event ID 219