Configuring MOM/SCOM query

 

Explains how to set up the SQL query in the configuration file used by the MOM/SCOM adapter.  The query retrieves alarm data from the MOM or SCOM system. Troubleshooting steps are included.

The MOM configuration file is an XML file that can be edited by any text editor. It's divided into two sections, MOM2005 for defining the connection to a MOM 2005 server and MOM2007 for a connection to the SCOM 2007 server. Make all changes to the section that corresponds to the correct server.

 

Here are the properties relevant to building the SQL query:

 

sqlStatement

 

Basic SELECT statement, including the tables that data comes from. The order and names of the data returned by the select statement can't be changed, but the tables and default values can.

 

sqlStatementWhere

 

WHERE clause for the initial query. If the SELECT statement isn't using standard join syntax, include join conditions here as well. The default statement selects alarms with a resolution state of zero.

 

sqlStatementWhereUpdate

 

First part of the WHERE clause for retrieving alarm updates. As before, if the SELECT isn't using standard join syntax, include join conditions here.

 

sqlStatementWhereAppender

 

Second part of the WHERE clause for retrieving updates. This part contains the date condition that selects only alarms that have been updated since the last query. The time of the last query will be appended to this.

 

sqlStatementOrderBy

 

ORDER BY clause for sorting the results of the query.

 

The server builds the initial query, run when the adapter starts, by concatenating the following properties: 

 

sqlStatement

sqlStatementWhere

sqlStatementOrderBy

 

The server builds the alarm update query, run periodically to retrieve updates to alarms, by concatenating the following properties: 

 

sqlStatement

sqlStatementWhereUpdate

sqlStatementAppender

<time of last query>

sqlStatementOrderBy

 

 

If the adapter isn't retrieving the right alarms, log the generated SQL query and test it in any database query tool.  For this, do the following:

 

1.  Log into the Business Service Management client as admin.

2.  Right click on Administration/Server.

3.  Select Logging from the menu, and Display Categories from the submenu.

4.  Double click Logging categories to list the categories.

5.  Right click Integration.msmom.<adapter name> and select DEBUG from the menu, where <adapter name> is the name you gave to your MOM/SCOM adapter.  If the category Integration.msmom.<adapter name>.DefaultDataSource exists, set it to debug as well. 

6.  Restart your adapter.

7.  After your adapter has started up, the generated SQL statements will be in your formula.trc file. 

 

Cut and paste these SQL statements into any database query tool and test them against the MOM database.  Typical problems include syntax errors or joins without conditions which generate excessive amounts of data.