SQL commands, variable expressions, and helper buttons in tools, automations, and transient event lists

You can use a number of SQL commands, variable expressions, and helper buttons to retrieve information from a running event list, the current event, or the operating system environment. You can use these expressions when creating a tool, trigger, or SQL procedure, or in parameters passed to a transient event list.

The following table lists the SQL commands, variable expressions, and helper buttons.

Table 1. SQL commands, variable expressions, and helper buttons in tools, triggers, procedures, and the transient event list
Command/variable expression Button Usage
select_command

insert_command

update_command

delete_command

use_command

service_command
This figure shows the SQL Commands helper button.
 Click this button to select an SQL command from the pop-up menu. Based on the command that you select, complete the resulting window as follows:
  • Select: Select the database and table on which to run the SELECT command. Then, choose the table columns to select.
  • Insert: Select the database and table on which to run the INSERT command. Then, select the table columns in which to insert values. For each selected column, enter the value to insert. For insert statements, you must include the primary key. Primary keys are indicated with an asterisk (*).
  • Update: Select the database and table on which to run the command. Then, select the table columns to update. For each selected column, enter the new value. For update statements, you must exclude the primary key. Primary keys are indicated with an asterisk (*).
    Note: For inserts and updates to the alerts.status table, any existing conversions appear in the drop-down lists.
  • Delete: Select the table to delete.
  • Use: Select the database to use.
  • Service: Select a service name and a value. Values can be Good, Marginal, or Bad.
column_name

@column_name
This figure shows the Columns helper button.
 Click this button to select a table column name to add to the command. The column name is substituted for the corresponding event list row value when the tool runs.

When prefaced with the @ symbol, the column name is substituted with the corresponding event list row value during execution. This can be used in an SQL query or restriction filter, such as: RemoteNodeAlias = '@LocalNodeAlias'
conversion_name
This figure shows the Conversions helper button.
 Click this button to select from a list of available conversions.
N/A
This figure shows the SQL Keywords helper button.
 Click this button to bring up a list of keywords that complete the entered SQL.
N/A
This figure shows the Check SQL Syntax helper button.
 Click this button to check the validity of the entered SQL syntax.
%internal_value
This figure shows the Internal Values helper button.
 Click this button to select from a list of internal values known to the current instance of the event list. For example, to run the transient event list and specify the ObjectServer to connect to using the -server command-line option, specify:
-server "%server"
The following internal values are available for tools and as a parameter to the transient event list:
  • %display: The current display running the application (UNIX only).
  • %password: The password of the user running the application.
  • %encrypted_password: The encrypted password of the user running the application (UNIX only). In FIPS 140–2 mode, the password is passed as plain text when used in tools, but is hidden when specified as a parameter from the command line.
  • %server: The name of the ObjectServer to which the tool is currently connected.
  • %desktopserver: The name of the desktop ObjectServer to which the tool is currently connected.
  • %uid: The ObjectServer user identifier of the user running the application.
  • %username: The ObjectServer user name of the user running the application.
The following internal value is available for procedures and triggers:
  • %user: Used to specify user variables and access information about connected users.
The following internal value is available for triggers only:
  • %trigger: Used to specify trigger variables and access information about the current and previous executions of triggers.
The following internal value is additionally available for signal triggers only:
  • %signal: Used to specify signal variables and access information about signals raised.
$prompt.
prompt_name
This figure shows the Prompts helper button.
 Click this button to select the name of the prompt to use when querying the user. For example, to run the transient event list and prompt the user to enter their password using the Password prompt, specify: -password $prompt.Password

You can use prompts in tools and as a parameter to the transient event list.
$selected_rows.
column_name		 N/A List of values of column_name for all selected alerts. For example:

update alerts.status set TaskList = 0 where Serial in ($selected_rows.serial)

Do not use this syntax if you select the Execute for each selected row check box. Instead, select the check box if the change is different for each alert.
$(environment_
variable)		 N/A Indicates an environment variable. For example, when you run a transient event list, you can specify the filter file by using the -elf command-line option, such as:

-elf "$(NCHOME)/omnibus/ini/tool.elf

To run the tool on Windows, enclose the environment variable, such as $(NCHOME), in double quotation marks. If there is a space in the path name, it will not be interpreted correctly.
Tip:
  • When typing SQL commands within the Tivoli Netcool/OMNIbus SQL editor panels, you can type one or more characters and then press Ctrl+F1 to obtain a dialog box with a list of keywords that might match your entry. Select the required keyword and click OK to complete your entry. If only one keyword matches your typed characters, the keyword is automatically completed for you. If you press Ctrl+F1 after typing a database-related keyword, the dialog box provides a list of possible ObjectServer databases from which you can select. If you press Ctrl+F1 after typing a database name followed by a dot (for example: alerts.), you can press Ctrl+F1 again to view and select from a list of tables in the database.
  • You can click the To Clipboard button to copy the command in a text format to the clipboard.