Configuring MOM/SCOM query

  • 7004880
  • 18-Nov-2009
  • 26-Jun-2012

Environment

Novell Business Service Manager 4.0
Novell Business Service Manager 4.5
Microsoft MOM 2005
Microsoft SCOM 2007

Situation

Purpose
 
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.

Resolution

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
 

Additional Information

Troubleshooting Steps
 
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. 

Feedback service temporarily unavailable. For content questions or problems, please contact Support.