Blog Entry

(View Disclaimer)

Almost any application that uses a database as its data store can be integrated into NOC through a custom BDI adapter. With strong knowledge about your schema, and the power of SQL, you have the power to integrate your database-backed application into the NOC product.

A SQL database contains all kinds of information about its Schema. For the sake of this discussion, lets restrict our view to tables and columns. This data can be found in the sys.databases, sys.tables, and sys.columns , respectively.

Lets start by building our basic hierarchy. Create a new BDI definition with the name SQL DB. Create new elements under the root element to match the following hierarchy:

SQL  DB
    [Database Element] Database
        [Group] Tables
            [Database Element] Table
                [Group] Columns
                    [Database Element] Column

Databases:

Starting with our “Database” Element, lets setup the query so we can retrieve the basic information about our database.

On the Query - Editor page, set the following values
This Query uses the following Database Tables:
sys.databases
Where:
sys.databases.name = '${query.integration.dbDatabase}'

Now lets define the basic properties we want to retrieve : the Element Name, and an id
For the elementName property, set “Property Specified by Column Name” to :
sys.databases.name
Create a new property for the id : give it Property ID :
database_id
Mark the “Key Property” checkbox and set “Property Specified by Column Name” to :
sys.databases.database_id

Clicking the play button to execute the query now will yield no results, because this query uses a macro to get the database name from the adapter properties. Lets turn on Macro Expressions now:

Select the SQL DB root element, and open the Properties page. Mark the checkbox beside “Use Macro Expressions” and set the value in the text box to true.

Returning to the Databases element, we can now see the record for our Database in the Query Results.

Now lets Configure the “Table” Element. On the Query - Editor page, set the following values
This Query uses the following Database Tables:
sys.tables
Where:
1=1

The expression “1=1” will return true for all rows, so this expression is really asking for all rows in the sys.tables. Any tautology can be used in the Where clause in this case.

Now lets define the basic properties we want to retrieve : the Element Name, and an id
For the elementName property, set “Property Specified by Column Name” to :
sys.tables.name
Create a new property for the id : give it Property ID :
table_id
Mark the “Key Property” checkbox and set “Property Specified by Column Name” to :
sys.tables.object_id
Tables are linked to their parent database via the database_id property. Set the “Property Specified by Column Name” to the subquery:
(SELECT sys.databases.database_id FROM sys.databases WHERE sys.databases.name = '${query.integration.dbDatabase}')

Columns:

Now lets Configure the “Column” Element. On the Query - Editor page, set the following values
This Query uses the following Database Tables:
sys.columns
Where:
sys.columns.object_id in (SELECT object_id FROM sys.tables)

The Where expression here is restricting the returned records to the columns that are in the tables we have previously retrieved, so we don't have orphaned columns that appear in our adapter.

Now lets define the basic properties we want to retrieve : the Element Name, and an id
For the elementName property, set “Property Specified by Column Name” to :
sys.columns.name
Create a new property for the id : give it Property ID :
column_id
Mark the “Key Property” checkbox and set “Property Specified by Column Name” to :
sys.columns.column_id
Columns are linked to their parent database via the database_id property. Set the “Property Specified by Column Name” to the same subquery that was used for the tables:
(SELECT sys.databases.database_id FROM sys.databases WHERE sys.databases.name = '${query.integration.dbDatabase}')
Columns are also linked to their parent table via the table_id property. Set the “Property Specified by Column Name” to:
sys.columns.object_id

Instantiating the adapter against sql's master database should give you something like :

You now have the basics of a MS SQL adapter that mimics the basic view of the Microsoft SQL Management Studio. Expand the Database elements with additional properties, maybe some additional elements to represent key constraints, maybe alarms when the schema changes, or when the database goes offline. You know longer need to ask “What can I do with the adapters I have in Novell Operations Center?” and you can start to ask “How can I integrate my other applications into Novell Operations Center?”

Disclaimer: As with everything else at Cool Solutions, this content is definitely not supported by Novell (so don't even think of calling Support if you try something and it blows up).

It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test, test, test before you do anything drastic with it.