Extending Discovery to Discover Databases and Database Tables

[richardbrounste]

Database server instances and their respective databases are a large part of any data center.  I’m often asked how ServiceNow Discovery can discover and model the database structures such as instances, DB schemas, and even tables in Microsoft SQL Server or Oracle Database and other DBMSs.  Inevitably, users want to manage more than just the DB instances in the CMDB.  The database schemas are also important as the specific DB name reveals insight into the kind of data that is stored in the database.  A user may even want to collect the individual tables associated with each database.

In this article, I will describe the steps you must go through to discover the full architecture of a database down to the name of the table and store this data in the CMDB.  This will involve creating a custom class and extending a Discovery Pattern.  These concepts can be applied to any software that has different levels of complexity in the data (application servers, web servers, messaging servers, etc.) so use these instructions and the below documented example as a template for other applications.  For my specific example, I will focus on Microsoft SQL Server and describe how to modify the MS SQL DB on Windows Discovery Pattern.  For the example I document, I am using a ServiceNow Paris instance.

I don’t go into the details on how to use ServiceNow Discovery.  You can review Discovery here: https://docs.servicenow.com/bundle/paris-it-operations-management/page/product/discovery/reference/r...

Here is a simplified of the architecture of Microsoft SQL Server database instances with a breakdown to the SQL Server databases and the database tables.

Here is how this can be represented in ServiceNow in the Dependency Viewer:

Our goal will be to teach ServiceNow Discovery to collect information on the SQL Server Instance, SQL Server databases and the associated tables and relate them together in the CMDB. In the CMDB, we will need to populate the Configuration Item Types in this below table.  Currently, the Out-Of-The-Box discovery pattern MSSql DB On Windows already discovers the MSFT SQL Instance and the related MS SQL Database.  In this article, I will go over the details for how the pattern can populate these CIs and create the relationship and then go over how to extend it to populate the MS SQL tables with the correct relationships.

STEP 1: Create the Configuration Item Class in the CI Class Model

The MSFT SQL Instance and MS SQL Database classes already exist and have their dependencies properly defined.  It is important to understand some concepts for how classes need to be defined so that a relationship can be created by a Discovery pattern.  First, let us look at the definition of the MSFT SQL Instance and its dependent relationships:

Like all software applications, it has a Dependent Relationship to CI Hardware.  This means that you cannot create an instance of MSFT SQL Instance unless you are also creating a “Runs on” relationship to a CI of Hardware class with the MSFT SQL Instance as the parent.

Now look at the definition for the MS SQL DataBase:

The MS SQL DataBase is a child class to the MSFT SQL Instance with a Contains relationship.  It is not allowed to create an instance of the MS SQL DataBase unless it is created as a child to an MSFT SQL Instance with a Contains relationship.  Now we want to create the new class to store the MSFT Database tables with a dependency on the MS SQL DataBase.

Extend the Database Instance class to create the new class MS SQL tables.  Note the internal name (always with a ‘u_’ at the beginning: u_cmdb_ci_ms_sql_tables

We need to create additional attributes to store the database name and instance name:

• Database Name (u_database_name)
• Instance Name (u_instance_name)

The database name will be important later in the pattern when we create the relationship.  This is explained later in this article.

For the Identification Rule, use a combination of the Database Name (u_database_name) and Name attributes.

Now we need to define the Dependent relationship.  It is important to override the default Dependent Relationship.  Make sure that the MS SQL DataBase Contains the MS SQL tables with the MS SQL DataBase as the parent.  It must look like the below image.  In the Metadata editor, you will see the rule reflected as well under Containment Rules.

If you go into the Metadata Editor, you will see a structure like the one below under Containment Rules.  It is not necessary to make any changes in the Metadata Editor.  I’m showing this screenshot for informational purposes.

STEP 2: Enter the Applicative Credentials as necessary

Go to Discovery -> Credentials and create an applicative credential.

Now, when a command is run using the “Parse Command Output” step that specifies MS SQL tables. In the command step, you can put in $$username$$ and $$password$$ and it will substitute the User name and Password from the credential that is associated with that class.

STEP 3: Build the Discovery Pattern

Since an existing discovery pattern already exists that discovers SQL Server and associated databases, we will simply add to it.  We can create an Extension Section on the MSSql DB on Windows Discovery Pattern.  The advantage of an Extension Section is that the pattern sections are stored separately from the original discovery pattern.  That means that any updates made to the original pattern will not be updated by any updates or patches made to the instance.

Create the Extension Section and call it Get DB Tables.  Then edit the section.

We are going to want to go into Debug Mode as we make this pattern work.  Before we can do that, we need to update the pattern so that it knows that we are going to be populating our new class, MS SQL Tables.  In order to do this, we need to add the MS SQL Tables CI type to the pattern by clicking on the 3 horizontal line button on the right-hand side under CI attributes and adding the CI Type to the tabs on the right-hand side.  You will then see this class in the tabs on the far right side of the pattern.

1. In the pattern, Step 1 will run the command to get all the Database tables.  Fill out the step with the details below.  Make sure to run the command to ensure that the tables are returned and parsed correctly.

1. 1. Name the step: Get All Database Tables
   2. Operation is Parse Command Output
   3. Set Command details to return the list of tables.  Here is a command that uses sqlcmd that will return the tables (note the $$username$$ and $$password$$):

"sqlcmd -h-1 -U $$username$$ -P $$password$$ -Stcp:" + $computer_system.primaryHostname + "," + $tcp_port + " -Q \"SET NOCOUNT ON DECLARE @AllTables table (CompleteTableName nvarchar(4000)) INSERT INTO @AllTables (CompleteTableName) EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id' SELECT * FROM @AllTables ORDER BY 1;\""

1. 1. Turn on Advanced Details and set Execution to Default (Remote) with CI Type MS SQL Tables so that it will substitute the credential for the $$username$$ and $$password$$.
   2. Define Parsing should be Delimited Text
   3. (optional) Exclude Lines that contain tempdb
   4. The Variable will be the internal table name for the MS SQL Tables class that you defined.  The columns within the table that you specify will be the internal attribute names in the table.  In this example, it is: u_cmdb_ci_ms_sql_tables with the fields u_database_name, and name.  In my example, I also defined the u_instance_name but that is not necessary.
   5. The one delimiter is the period ‘.’
   6. Set Positions to 1,2,4

1. Step 2 will set the relationship between the tables and the database schema within the MS SQL Server Instance.  We are going to have it create the relationship using a technique called “Field Matching”.  This is where it is important to capture the exact database name in the field u_database_name.  It will match the field database in the MS SQL DataBase table.  Now we can create the step to build the relationship.

1. 1. Set the Operation to Create Relation/Reference
   2. Set the Parent Table to $cmdb_ci_db_mssql_database
   3. Set the Child Table to the CI type for the SQL Tables: $u_cmdb_ci_ms_sql_tables
   4. Set the Result to anything like $db_table
   5. Set the Relation Type to: Contains::Contained by
   6. Set the Creation Criteria to Field matching
   7. Set the Parent Table Field to database
   8. Set the Child Table Field to u_database_name
   9. Set the Unmatched Values to Remove (this means if the u_database_name field does not find a matching value in the database field, it will just remove that table value).

That’s it!  Make sure to save the entire pattern.  You will notice that you don’t need to publish the pattern.  That’s because it is only the extension that is being saved and it is saved separately from the pattern.

Now, when you run the Discovery against the Server that is running the Microsoft SQL Server Database, it will create the database instance and related database tables.