Updating SQL ODBC databases with RUNSQL.bat

One of the most useful actions that can be configured is to update a relational database table with received messages. This is accomplished using the RUNSQL.bat action program.

Example

You might want to selectively update a relational database table with any message that meets a particular criterion or pattern. (If multiple criteria exist, the RUNSQL.bat program can be configured multiple times in the list of actions, each with the same arguments, but matching different specific patterns.) 

Related topic

BMC-Defender-Server-automation

With small modifications, you can also perform database updates, or other more sophisticated SQL operations.

The RUNSQL.bat program is one of the more difficult actions to configure but is still quite simple. The steps to configure this action program are listed below:

  1. The Administrator creates a System DSN, using the standard Windows > Control Panel > Administrative Tools > ODBC tools interface. The DSN must be a System DSN, and the name is arbitrary. An existing system DSN can be used.
  2. The Administrator edits the BMC Defender Server/system/RunSQL.cnf file to provide the name of the ODBC data source as preceding, and any username and password required to access the database. (See notes as follows.)
  3. The Administrator creates a database table called MessageData in the target relational database. This table contains four columns, Time, Address, Facility, Severity, and Message.  (See further notes as follows.)
  4. The Administrator adds the RUNSQL.bat action to the list of BMC Defender Server actions, specifying the runsql.cnf file as the single and only argument to the program. (The other data is passed using environmental variables to the batch file.)

The RunSQLcnf file is a simple configuration file, similar to the various other configuration files within the BMC Defender Server. However, the file resides in the system directory, hence can be specified as an argument to the RUNSQL.bat program without a full pathname. (The .cnf suffix is required.)

This file must be created or adjusted by the administrator, such as using notepad.exe or some other text file editor. A template for this file is provided in the basic BMC Defender Server, in the BMC Defender Server/system directory. The file contains one mandatory field (the dsnname field) and two optional fields (the username and password fields, if so required.)

An instance of this configuration file is provided as follows. This file contains the following fields.

dsnname   BMCDefender Server-database
username  none
password  none

In the preceding, for example, the ODBC Data Source Name, created using the Windows Control Panel in step #1, is BMC Defender Server-database.

This value was identified when the System DSN was created. In this case, the username and password are both none (indicating that a login is not required for the database that the BMC Defender Server-database DSN represents.) The ODBC data source BMC Defender Server-database is maintained by the Windows system, and contains the actual location of the database, as well as any other parameters that are required to access the database, such as logins, and network locations.

The MessageData table, that is required and updated by the RUNSQL command, is created as follows.

Note

This is preprogrammed into the RUNSQL.bat action program, and can easily be tailored and modified by a user to change the table name, column types, column widths, and other information.

create
table MessageData
(
   MessageTime                     varchar(20),
   MessageAddress                  varchar(16),
   MessageFacility                 varchar(12),
   MessageSeverity                 varchar(12),
   MessageData                     varchar(128)
)

The preceding format is somewhat arbitrary. More efficient types of tables can be created, but for most applications (of perhaps 1 million messages or less) the preceding table definition should be adequate to contain the pertinent messages matched by the Action facility.

Note

The message is truncated to 128 characters automatically. 

Finally,

Note

RUNSQL.bat action program, although quite simple, can be expanded upon to include multiple DSN files (each with a different configuration file argument) and performing various types of updates, such as updating counter values, creating table joins, and deleting old data on the system.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*