Use this Knowledge Script to run SQL statements or stored procedures. You can enter the SQL Server statements to be executed when you run this script, or you can load the statements from a script file and specify a full path to the file you want to use.
You can set maximum and minimum thresholds to be compared against the SQL Server statement’s primary output value--either the number of rows returned, or the value found in the column you specified. This script raises an event if the output value exceeds the maximum threshold or falls below the minimum threshold you set.
When constructing a SQL statement for use with this script, include the database name in the syntax.
For example:
select categoryid from Northwind.dbo.categories
This script might encounter problems if you employ the USE command to qualify queries.
For example:
use mydatabase select * from mytable.
Instead, specify select * from mydatabase.dbo.mytable to fully qualify queries.
AppManager does not provide a syntax-checking mechanism. Syntax checking is performed by the SQL Server on the monitored server when the job is run. If an error is detected, the SQL Server passes an error to the Knowledge Script.
SQL Server instance
The default interval for this script is Every 5 minutes.
Set the following parameters as needed:
Description |
How to Set It |
---|---|
General Settings |
|
Job Failure Notification |
|
Raise event if job fails unexpectedly? |
Select Yes to raise an event if the SQLServer_RunSql job fails unexpectedly. The default is Yes. |
Event severity when job fails unexpectedly |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5. |
Raise event if SQL Server login fails? |
Select Yes to raise an event if login to SQL Sever fails. The default is Yes. |
Event severity when SQL Server login fails |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the login to SQL server fails. The default is 15. |
Additional Settings |
|
Event Details |
|
Event detail format |
Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table. |
Authentication |
Select the authentication method that you want to use to access SQL Server. You can either select Windows Authentication or SQL Server Authentication. The default is Windows Authentication. |
User name |
Specify the Windows or SQL Server user name that you want to use to access SQL Server. You can specify multiple users separated by a comma. The default is none. For more information on specifying user name, see Specifying the User Name in the Knowledge Script in the Management Guide. For more information on specifying user name, see Section 2.7.3, Specifying the User Name in the Knowledge Script. |
Monitoring |
|
Timeout |
Specify the number of seconds to wait while trying to establish a SQL connection before terminating the attempt and raising a timeout exception. The default is 30 seconds. |
Number of retries |
Specify the number of times the script should retry establishing a SQL connection before terminating the attempt. The default is 0 (no retry attempts). |
SQL query or command source |
Select one of the following options:.
|
SQL query or command statement |
If you have selected the Parameter option in SQL query or command source, enter a single-line statement here. IMPORTANT:This parameter does not accept multi-line statements. HINT:Unless you are entering very simple statements, you might find that typing them into this field is error-prone. To avoid errors, you can copy and paste statements, or use the Full path to SQL query or command file parameter. |
Full path to SQL query or command file |
If you have selected the File option in SQL query or command source, enter the complete file path. For example, F:\NetIQ Corporation\Sample.txt, or \\<computer>\<dir>\Sample.txt. NOTE:The T-SQL statements in the file must be separated by semi-colons. For more information, see Loading SQL Server Statements From a Script File. You cannot use a UNC path if the NetIQ AppManager Client Resource Monitor service is running as a system account. |
Specify a description for the SQL query |
Specify a description for the SQL query that you want to display in the event detail message. By default, the description is Detail result of query. |
Save query results to a file? |
Select Yes to save the query results to a file. By default, query results are not saved to a file. |
Full path to results file |
If you are saving query results to a file, enter the complete file path and filename. For example, F:\NetIQ Corporation\QueryResult, or \\<computer>\<dir>\QueryResult. You cannot use a UNC path if the NetIQ Corporation AppManager Client Resource Monitor service is running as a system account. |
Append to results file? |
Select Yes to append the results of the query to the existing contents of the results file. If disabled, the results file is overwritten each time. By default, the contents of the results file are appended each time a query or command is run. |
Number of rows to write to results file |
Specify the number of rows to write to the result file. The default is 10. |
Include query results in event details? |
Select Yes to include the results of your query or command in the event detail message. By default, query results are included. |
Number of rows to display in event details |
Specify the number of rows to display in the event detail message. The default is 10 rows. NOTE:You can enter 0 to indicate all rows. However, the event detail message has a 32-KB limit. |
Include query results in data details? |
Select Yes to include the results of your query in the data detail message. By default, query results are included. |
Number of results to display in data details |
Specify the number of rows to display in the data detail message. The default is 10 rows. NOTE:Enter 0 to indicate all rows. However, the event detail message has a 32-KB limit. |
Event notification |
|
Threshold type |
Select whether you want to perform threshold checking on one of the following:
|
Column number |
If the threshold is comparing values found by column number, indicate the column number to use as the primary output value. The column you specify must contain numeric data. A value of 0 returns the first column from the SQL statements. Any other positive value returns the value for the first row of data from the specified column. If the specified column is not a numeric field, an error is raised and the Knowledge Script returns a -1 result. If you do not select by column number, you can select by column name. The default is 0. NOTE:You should execute your query in SQL Query Analyzer to ensure that the proper rows and number of columns are returned. The leftmost column is column zero. Also note that if a query is doing a “Select * ...“ and if the table schema changes, the column numbering might change. |
Named Column |
If the threshold is comparing values found by column name, indicate the column name to use as the primary output value. The column you specify must contain numeric data. The value for the first row of data in the specified column is returned. If the specified column is not a numeric field, an error is raised and the Knowledge Script fails. |
Raise event if maximum threshold is exceeded? |
Select Yes to raise an event when the T-SQL statements run by this script return more rows than the threshold, or if the value in the specified column exceeds the threshold you set. The default is Yes. |
Event severity when maximum threshold exceeded |
Set the severity level, from 1 to 40, to indicate the importance of an event in which the maximum threshold is exceeded. The default is 5. |
Threshold -- Maximum |
Specify the maximum number of rows that can returned by the query or for the value found in the column. If the number of rows exceeds the threshold value, an event is raised. The default is 100. |
Raise event if minimum threshold is not met? |
Select Yes to raise an event when the T-SQL statements run by this script return fewer rows than the threshold, or if the value in the specified column falls below the threshold value you set. The default is Yes. |
Event severity when minimum threshold not met |
Set the severity level, from 1 to 40, to indicate the importance of an event in which the minimum threshold is not met. The default is 5. |
Threshold -- Minimum |
Specify the minimum number of rows that can be returned by the query or for the value found in the column. If the number of rows falls below the threshold value, an event is raised. The default is 0. |
Event detail options |
|
Raise event for SQL Server informational messages? |
Select Yes to raise an event when your query generates a Microsoft SQL Server informational message. The text of the message is returned in the event detail message. This option is helpful when SQL Server returns the query result as an informational message rather than as data. For example, when using the dbcc checktable command. By default, this script does not raise an event under these conditions. |
Event severity for SQL Server informational messages |
Set the severity level, from 1 to 40, to indicate the importance of an event in which a SQL Server information message is generated. The default is 35. |
Raise event if no rows are returned? |
Select Yes to raise an event if no rows are returned. The default is unselected. |
Data Collection |
|
Collect data for number of rows returned? |
Select Yes to collect the number of rows returned by your query. If this parameter is set to Yes and the value returned is -1 or any negative number, an error occurred while attempting to collect data. By default, this script does not collect data. |
Custom data stream legend |
Specify a custom legend for the data stream returned by this Knowledge Script. |
Because these SQL Server statements are passed through the ODBC driver, you do not need to enter the go statements normally required in the command line tool, osql. Example of a simple SQL Server script:
SELECT COUNT(*) FROM QBD.dbo.Event WHERE ParentEventId IS NOT NULL AND Status = 0; SELECT EventID, JobID, MachineName, KPName, Severity, Occurrence, ModificationTime, EventMsg FROM QDB.dbo.Event WHERE ParentEventId IS NOT NULL AND Status = 0