Querying the SQL Monitoring Database Using Windows PowerShell
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 Microsoft AppFabric 1.1 for Windows Server provides. 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
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:
Open the Windows PowerShell console with administrative privileges.
Navigate to the folder containing the sample.
Navigate to the Scripts subdirectory of the SQLMonitoringQueryCmdlets samples folder.
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
2012-09-12