Here at the HP Business Service Management (BSM) Research and Development department, we’re constantly working on ways to make your work-life easier. Here are some useful tips on how to create a custom database monitor and pass results of SQL queries onto another monitor via temporary files. Let’s get started…
Two Database monitors (e.g. one Custom Database and one Database Query) and a Composite monitor are used. 1. The first database monitor (Custom Database) is set to execute a stored procedure against the first server, and to put the results into an SQL query for the second request. The resulting SQL query is written to a temporary file by the first monitor. 2. The second database monitor (Database Query or Custom Database in case further processing is required) is set to get the complete SQL query string from the temporary file and then use that query string to execute a stored procedure on another server. 3. The Composite monitor is set to control the above monitors (their run order and possibly delay between runs).
Enhancements for initial concept
This solution does not take into account when more than one configuration of monitors is required. In this case each monitor's configuration should use a dedicated temporary file. In order to provide this interconnection, each temporary file should contain a token string that could be looked up by both of the monitors that are going to use the file.
Upon template deployment the following monitors configuration is created.
1. Composite monitor
2. Two Custom Database monitors with description field containing token string unique for this specific configuration
Monitor run order and data flow
1. Composite monitor runs the first database monitor (Custom Database). The Custom Database monitor executes the first SQL query.
2. The result of the SQL query is stored to a sequentially numbered temporary file containing a token string.
3. Composite monitor runs the second database monitor (Custom Database). The Custom Database monitor executes the SQL query taken from the temporary file.