Use this Knowledge Script to query a Microsoft SQL Server using Open Database Connectivity (ODBC) and a Data Source Name (DSN).
This script generates the following data streams:
Response time
Overall response time. The information returned by this data stream is also saved with the data point, and can be viewed by double-clicking the data point in the Graph Pane or Chart Console.
Response-time Breakdown. If enabled as separate parameters, up to three response-time breakdown data streams. These are individual data points for the different parts of the Knowledge Script transaction that are timed. See Setting Parameter Values below for more information.
Availability--Returns one of two values:
1 or 100 = transaction was successful
0 = transaction was not successful.
The Availability data point is an indication of whether the transaction succeeded or failed.
This script raises an event whenever one of the following occurs:
A threshold that you have specified as an event parameter is exceeded.
The SQL-RT engine can’t be initialized. An initialization error is raised, but neither an Availability nor a Response Time data stream is generated.
The job transaction doesn’t complete successfully. A transaction error is raised. Only an Availability data stream is generated, with a value of 0.
You can select where some of the possible events are displayed in the Operator Console TreeView or Control Center Console Server view. This event proxying feature is useful in Control Center Service Map views. It is not supported for jobs that are started in the Operator Web Console.
The SQL response time ODBC client
The default interval for this Knowledge Script is Every 15 minutes.
ODBC Knowledge Scripts do not support Windows authentication (integrated security), but DSN does. Therefore, the job may not use the type of authentication you wanted it to use. For example, if you set an invalid SQL username and/or password in the ODBCDSNQuery Knowledge Script, the job may run successfully if the DSN is set to use Windows authentication.
In such a case, if the value supplied for the Run As Username parameter has appropriate privileges on the SQL Server database, the ODBC driver ignores the specific logon username and password you supplied. The logon then succeeds in this case because the Windows authentication in the DSN takes precedence over the authentication method set in the Knowledge Script.
Depending on what security model was set when you defined the DSN, you should only use that security method when running this Knowledge Script.
When using a DSN defined with SQL authentication, use the SQL Logon Username and Password fields only.
When using a DSN configured to use Windows authentication, leave the SQL Logon Username and Password fields blank, and specify the valid Windows account for the Run As Knowledge Script parameters.
Set the following parameters as needed:
|
Description |
How to Set It |
|---|---|
|
Availability |
|
|
Collect data for availability? |
Select Yes to collect data for graphs and reports. If enabled, data collection returns:
The default is yes. |
|
Data stream format |
Select the data stream format for the Availability data stream. You can use a 0 (“not available”) or 100 (“available”) format. The default value is 0-100. |
|
Raise event if transaction fails? |
Select Yes to raise an event when the server cannot be contacted. The default is Yes. |
|
Event severity when transaction fails |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the server cannot be contacted. The default is 5. |
|
Response Time |
|
|
Collect data for response time? |
Select Yes to collect response time data for graphs and reports. The default is Yes. If you enable data collection, you also have the option to see a breakdown in the response times for the component parts of the query, such as the time taken to connect to the SQL Server. |
|
Threshold -- Maximum response time (seconds) |
Specify the maximum response time in seconds. When response time exceeds this value, an event is raised. The default is 15 seconds. |
|
Raise event if threshold is exceeded? |
Select Yes to raise an event when the threshold is exceeded. The event message contains a breakdown of the total response time. The default is Yes. |
|
Event severity when threshold is exceeded |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which response time exceeds the threshold. The default is 15. |
|
Response Time Breakdown |
|
|
Collect data for connecting to SQL Server? |
Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to establish a connection to the SQL Server. The default is unselected. |
|
Collect data for executing SQL statement? |
Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to execute the SQL statement. The default is unselected. |
|
Collect data for fetching data? |
Select Yes to collect response-time data showing how much of the overall response time could be attributed to the time taken to perform a fetch of the query data. The default is unselected. |
|
Target computer |
Specify the computer name and instance of the SQL Server. This is an optional field, and is used to enable retrieval of data streams by AppManager Analysis Center v2.0 and later. If specified, it is also used in place of the DSN in the data stream legend. If you set the Event on parameter, the Target computer parameter lets you select the server where the event will appear in your console. Specify the name of the server and instance, or click Browse [...] to select from a list of available servers. The server you select must already be in the TreeView. |
|
Data source (DSN) |
Provide the system DSN on which the Knowledge Script will be run. Required. |
|
SQL Statement |
Provide a SQL statement (128-character maximum) that is compatible with the provider. The format is execute stored_procedure. |
|
Event on |
Select the TreeView location where events should be displayed. Select either:
Notes This setting does not apply to events related to the Knowledge Script itself, such as Knowledge Script failure or initialization problems. Such events are always displayed on the computer where the job ran. You must select Agent when starting jobs in the Operator Web Console. If you select Server, no events are generated. If you select Both, an event is only shown on the agent. |
|
SQL Logon |
|
|
Username |
Set this value appropriately based upon your DSN settings. |
|
Password |
Set this value appropriately based upon your DSN settings. |
|
Run As |
|
|
Username |
Provide the username associated with a specific user who has the required permissions to run this application. Required. Interactive User is a possible value. Leave the Password and Domain parameters blank if you specify "Interactive User". Interactive User requires a user to be physically logged into the computer for the test to run. You might want to do this in environments where a firewall is preventing access to an Active Directory domain controller, or where the test computer is part of a workgroup and not part of a domain. With this feature, the user is not validated, so the test can proceed despite the lack of access to the domain. |
|
Password |
Provide the password associated with this user that is required to log on to the network and run the application. |
|
Domain |
Provide the domain associated with this user that is the domain name you are logging onto. Required. |
|
Administrators group on managed client |
Provide the name of the Administrators Group on the managed client. Typically, this name is “Administrators”, except on some foreign language operating systems. The default is “Administrators”. |
|
Connection timeout |
Specify the number of seconds to wait while executing a command before terminating the attempt and generating an error. The default is 15 seconds. |