Querying the SQL Monitoring Database Using Windows PowerShell

  • Article

This sample shows how to use SQL scripting to create a typical Windows PowerShell cmdlet that retrieves monitoring data. It also helps you understand the SQL views that Windows Server AppFabric provides. This sample will work with any application. We recommend the Common Windows Server 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

Users should have some knowledge of Windows PowerShell scripting and SQL commands. The sample assumes the following:

  • Windows PowerShell 2.0 is installed

  • SQL Server 2008 Express is installed

  • AppFabric is installed

Sample Location and Files

  • Scripts\ScriptCmdlets.ps1

  • Readme.mhtml

Setting Up and Running This Sample

To run this script:

  1. Open the Windows PowerShell console with administrative privileges.

  2. Navigate to the folder containing the sample.

  3. Navigate to the Scripts subdirectory of the SQLMonitoringQueryCmdlets samples folder.

  4. Execute the following commands:

    Set-ExecutionPolicy Unrestricted
Import-Module ‘.\ScriptCmdlets.ps1’

Understanding This Sample

The Monitoring Query sample exposes the following cmdlets:

  • Get-ASAppTrackedPropertyName

  • Get-ASAppTrackedInstance

  • Get-ASAppTrackedWcfEvent

  • Get-ASAppTrackedWfEvent

Get-ASAppTrackedPropertyName

This cmdlet queries the monitoring database for the tracked property names that are available for a particular service. For a given workflow service, the tracking framework captures properties on certain events such as environment variables and user tracked variables. This cmdlet returns the names of all variables that are available for a given workflow service.

Syntax

Get-ASAppTrackedPropertyName [-Database<String>] [-MachineName<String>] [-Server <String>] [-SiteName <String>] [-VirtualPath <String>]

Parameters

Database - Mandatory parameter. A string that indicates which database to query.

MachineName - Optional parameter. A string that is used to query for only the monitoring data that was generated on the specified machine.

Server - Mandatory parameter. A string that indicates which SQL server to query.

SiteName - Optional parameter. A string that is used to query the property names of instances for the specified site.

VirtualPath -Optional parameter. A string that is used to query the property names of instances of services located at or under the specified virtual path.

Piping

The Get-ASAppTrackedPropertyName cmdlet can be piped from the Get-ASAppService cmdlet. Get-ASAppService returns an ApplicationObject that can be used as input for Get-ASAppTrackedPropertyName.

Examples

Get-ASAppTrackedPropertyName –SiteName “Default Web Site” –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppService –SiteName “Default Web Site” | Get-ASAppTrackedPropertyName –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedInstance**

This cmdlet allows users to query for data about service instances that have been tracked. Users can specify criteria that must be matched against the tracked instances in order for them to be returned. This cmdlet works against any operational scopes: Machine, Site, Application, Service, and Virtual Path.

Syntax

Get-ASAppTrackedInstance [-Count <Switch>] [-Database<String>] [-MachineName<String>] [-MaxResults <int>] [-ModifiedSince <DateTime>] [-Server <String>]  [-SiteName <String>] [-VirtualPath <String>]

Parameters

Count - Optional parameter. A flag that when specified, causes the cmdlet to return a count of instances that match the specified criteria.

Database -Mandatory parameter. A string that indicates which database to query.

MachineName - Optional parameter. A string that is used to query for only the monitoring data that was generated on the specified machine.

MaxResults - Optional parameter. An integer that is used to specify the maximum number of results displayed. The default is 50.

ModifiedSince - Optional parameter. A date time that is used to query for only the instances that were modified after this date time.

Server - Mandatory parameter. A string that indicates which SQL server to query.

SiteName - Optional parameter. A string that is used to query for only the instances for the specified site.

VirtualPath - Optional parameter. A string that is used to query for only the instances located at or under the specified virtual path.

Piping

The Get-ASAppTrackedInstance cmdlet can be piped from the Get-ASAppService cmdlet. Get-ASAppService returns an ApplicationObject that can be used as input for Get-ASAppTrackedInstance.

Examples

Get-ASAppTrackedInstance –SiteName “Default Web Site” –Count –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedInstance –MaxResults 10  –ModifiedSince 2-20-2010 –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppService –SiteName “Default Web Site” | Get-ASAppTrackedInstance –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedWcfEvent**

This cmdlet queries the monitoring database for WCF event data. This cmdlet works against any operational scopes: Machine, Site, Application, Service, and Virtual Path.

Syntax

Get-ASAppTrackedWcfEvent [-Count <Switch>] [-Database<String>] [-EmitTimeFrom <DateTime>] [-EmitTimeTo <DateTime>] [-MachineName<String>] [-MaxResults <int>]  [-Server <String>]  [-SiteName <String>] [-VirtualPath <String>]

Parameters

Count -Optional parameter. A flag that when specified, causes the cmdlet to return a count of WCF events that match the specified criteria.

Database - Mandatory parameter. A string that indicates which database to query.

EmitTimeFrom - Optional parameter. A date time that is used to query for only the WCF events that were emitted after this date time.

EmitTimeTo - Optional parameter. A date time that is used to query for only the WCF events that were emitted before this date time.

MachineName - Optional parameter. A string that is used to query for only the WCF events that were generated on the specified machine.

MaxResults - Optional parameter. An integer that is used to specify the maximum number of results displayed. The default is 50.

Server - Mandatory parameter. A string that indicates which SQL server to query.

SiteName - Optional parameter. A string that is used to query for only the WCF events that were generated on the specified site.

VirtualPath - Optional parameter. A string that is used to query for only the WCF events that were generated at or under the specified virtual path.

Piping

The Get-ASAppTrackedWcfEvent cmdlet can be piped from the Get-ASAppService cmdlet. Get-ASAppService returns an ApplicationObject that can be used as input for Get-ASAppTrackedWcfEvent.

Examples

Get-ASAppTrackedWcfEvent –SiteName “Default Web Site” –Count  –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedWcfEvent  –MaxResults 10 –EmitTimeFrom 2-20-2010 –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedWcfEvent –EmitTimeFrom 2-18-2010 –EmitTimeTo 2-20-2010 –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppService –SiteName “Default Web Site” | Get-ASAppTrackedWcfEvent –MaxResult 5 –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedWfEvent**

This cmdlet queries the monitoring database for WF event data. This cmdlet works against any operational scopes: Machine, Site, Application, Service, and Virtual Path.

Syntax

Get-ASAppTrackedWfEvent [-Count <Switch>] [-Database<String>] [-EmitTimeFrom <DateTime>] [-EmitTimeTo <DateTime>] [-MachineName<String>] [-MaxResults <int>] [-Server <String>] [-SiteName <String>] [-VirtualPath <String>]

Parameters

Count - Optional parameter. A flag that when specified, causes the cmdlet to return a count of WF events that match the specified criteria.

Database - Mandatory parameter. A string that indicates which database to query.

EmitTimeFrom - Optional parameter. A date time that is used to query for only the WF events that were emitted after this date time.

EmitTimeTo - Optional parameter. A date time that is used to query for only the WF events that were emitted before this date time.

MachineName - Optional parameter. A string that is used to query for only the WF events that were emitted on the specified computer.

MaxResults - Optional parameter. An integer that is used to specify the maximum number of results displayed. The default is 50.

Server - Mandatory parameter. A string that indicates which SQL server to query.

SiteName - Optional parameter. A string that is used to query for only the WF events that were generated on the specified site.

VirtualPath - Optional parameter. A string that is used to query for only the WF events that were generated at or under the specified virtual path.

Piping

The Get-ASAppTrackedWfEvent cmdlet can be piped from the Get-ASAppService cmdlet. Get-ASAppService returns an ApplicationObject that can be used as input for Get-ASAppTrackedWfEvent.

Example

Get-ASAppTrackedWfEvent –SiteName “Default Web Site” –Count –EmitTimeFrom 2-20-2010 –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedWfEvent –VirtualPath “/calculator/service.xamlx” –MaxResults 10 –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedWfEvent –EmitTimeFrom 2-18-2010 –EmitTimeTo 2-20-2010 –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppTrackedWfEvent –Count –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Get-ASAppService –SiteName “Default Web Site” | Get-ASAppTrackedWfEvent –Count –Server localhost\SQLExpress –Database ApplicationServerMonitoringDatabase

Removing This Sample

To remove this sample, delete its files and revert your execution policy to its previous level or to the default for Windows PowerShell with the following command:

Set-ExecutionPolicy Default