Integrating Monitoring with Excel

  • Article

This sample shows how to use Microsoft Office Excel to connect to a monitoring database and to display basic operational data on a chart. You will learn about one of the most useful views that is exposed through the AppFabric monitoring database.This sample will work with any application. We recommend the Common AppFabric Sample Application, which was created for use with AppFabric samples. To find this application, navigate to the <samples>\SampleApplication\OrderApplication folder, where <samples> is the path under which you have installed the AppFabric samples.

Note

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Prerequisites

  • Microsoft Office Excel 2007 or above

  • At least one WCF or WF service hosted in AppFabric.

    Important

    The service must have monitoring enabled and configured at the Health Monitoring level. This is because the sample uses the aggregate operational events that are captured at this level.

Sample Files

This sample comes with only one Excel file. It contains all of the information needed to run the sample.

Setting Up and Running This Sample

  1. Ensure that your service is running and that Health Monitoring is configured and is working properly.

  2. Open the Excel file.

  3. Navigate to Data->Connections and select Query for monitoring.

  4. Click Properties.

  5. Navigate to the Definition tab and make sure the connection string points to the correct monitoring database.

  6. While on the Definition tab, modify the command text (shown below) so that your desired service operation is selected:

    SELECT TOP 30000 ASWcfEvents.Id,  
     CAST(ASWcfEvents.TimeCreated as datetime) as 'TimeCreated' , 
 ASWcfEvents.AverageDuration, ASWcfEvents.OperationName,
 ASWcfEvents.AggregateCount, ASWcfEvents.EventTypeId
FROM Beta2Monitoring.dbo.ASWcfEvents ASWcfEvents
WHERE (ASWcfEvents.OperationName='MyOperationName') AND (ASWcfEvents.EventTypeId=364)
ORDER BY TimeCreated DESC

  7. Cllick OK and close the Connections dialog box.

  8. On the Data ribbon, click Refresh All to refresh the table and charts. This causes the report to fetch the latest data from the monitoring database.

Understanding This Sample

For a given operation this sample goes to the monitoring database and retrieves the latest events that represent its call history. The raw data is dumped into a table, and the two charts display the trends for number of calls and average call duration over time.

Removing This Sample

To remove the sample, simply delete the file.