1 2 Previous Next 16 Replies Latest reply on Nov 29, 2019 9:07 AM by Bernard Stern

    CMDB syncmapping extension for SQL Server Database

    Bernard Stern
      Share This:

      We need to be able to map SQL Server Database DB types directly to the BAI.

      The reason is, the Software Instance 'Microsoft SQL Server' containing the SQL Server Database does not contain the DB instance.

      An Oracle Database Server SI does contain the DB instance name. That Oracle DB Server SI is related to the BAI, so this information can be used for monitoring purposes.

      We need to do the same for MS SQL DBs. Since the instance is not available on the SI level, the idea is to create a new relation between the SQL Server Database and the BAI.

       

      I have looked a some syncmapping examples. The first question is, where should I start? Should I start from the BAI or from the Database?

       

      Start with this here

       

        mapping from Database.database_node as contained_node

          traverse Detail:Detail:ElementWithDetail:BusinessApplicationInstance as bai where type = 'SQL Server Database'

            application -> BMC_Application;

          end traverse;

        end mapping;

       

      or with that

       

        mapping from BAI_Application.bai as bai

          traverse ElementWithDetail:Detail:Detail:Database as database_ci

            ??

          end traverse;

        end mapping;

       

      Sorry if this sound trivial, I'm quite new to syncmapping patterns! I've done a lot of regular patterns but syncmapping is terra incognita.

       

      Thanks for any hint.

        • 1. Re: CMDB syncmapping extension for SQL Server Database
          Andrew Waters

          Your starting point is not very clear.

           

          You already have a relationship in Discovery between the BAI and SQL Database? If not you are picking one / all? What do you mean by create a relationship between? The standard mapping will create a relationship in the CMDB is one exists (docs).

          • 2. Re: CMDB syncmapping extension for SQL Server Database
            Marie Johnson

            Yes databases are different when you look at instances for mongo, oracle and ms sql or any others.  I’d suggest referring to the configipedia Microsoft SQL Server - Configipedia - BMC Documentation

             

            the relationships should be done already.

            • 3. Re: CMDB syncmapping extension for SQL Server Database
              Bernard Stern

              We want to see the SQL Server Database DB (as discovered with Discovery and mapped to a BAI using CAM) directly related to the Application in the CMDB. In the CMDB Application, we currently see the components discovered as Software Instances, but not the components discovered as Databases. We see the Microsoft SQL Server SI, but since its identification is i01, which is shared by most other MS SQL Server SIs, it cannot be used for impact management. The Oracle Database Server, on the other hand, is clearly identified by its instance. This can be used for impact management.

              When I look in Discovery, I see the SQL Server Database bearing names that can be used for impact management. Thus the idea to also map the SQL Server Database to the BAI, with the understanding, obviously erroneous, that the CMDBsync will create a relation between the Application and the Database.

              With these observations, I came to the conclusion that I need to write a custom syncmapping extension to create the relation between the Application and the Database in the CMDB. I hope it's clearer now.

              1 of 1 people found this helpful
              • 4. Re: CMDB syncmapping extension for SQL Server Database
                Lisa Keeler

                Yes, if you want to have the Databases directly related to the Application in the CMDB, then you would need to write a syncmapping extension to create that relationship.

                 

                We have a sample syncmapping extension that creates a direct relationship from BMC_Application to BMC_ComputerSystem.

                 

                tpl 1.11 module BAI_Application_CS_Link;

                 

                from CMDB.Host_ComputerSystem import Host_ComputerSystem 2.0;

                from CMDB.BAI_Application import BAI_Application 2.1;

                 

                syncmapping BAI_Application_CS_Link 1.0

                    """

                    Extension to create a denormalized relationship from BMC_Application to BMC_ComputerSystem.

                    """

                  overview

                        tags CMDB, Extension;

                    end overview;

                 

                    mapping from BAI_Application.bai as bai

                    end mapping;

                 

                    body

                        application    := BAI_Application.application;

                        computersystem := Host_ComputerSystem.computersystem;

                 

                        sync.rel.BMC_Dependency(Source      := computersystem,

                                                Destination := application,

                                                Name        := "APPLICATIONSYSTEMCOMPUTER");

                    end body;

                end syncmapping;

                1 of 1 people found this helpful
                • 5. Re: CMDB syncmapping extension for SQL Server Database
                  Bernard Stern

                  Thanks Lisa. I'm getting an error I don't undersand. Line 25 is outlined in yellow. I am understanging the database_ci comes from this portion of the original CMDB.Database module:

                   

                  mapping from SoftwareInstance_SoftwareServer.softwareinstance

                           from SoftwareInstance_ApplicationSystem.softwareinstance

                           from SoftwareCluster_Cluster.softwarecluster

                           as softwareinstance

                           traverse ElementWithDetail:Detail:Detail:Database as database_node

                              database_ci -> BMC_DataBase;

                              // No deeper mapping, just the logical dbs, no tables/schemas

                           end traverse;

                   

                   

                  Mapping error in extension 'CMDB.BAI_Application_Database_Link.BAI_Application_Database_Link': CMDB.BAI_Application_Database_Link.BAI_Application_Database_Link:25: CMDB.Database.Database.database_ci not found

                   

                   

                  tpl 1.12 module CMDB.BAI_Application_Database_Link;

                   

                  from CMDB.Host_ComputerSystem import Host_ComputerSystem 2.0;

                  from CMDB.BAI_Application import BAI_Application 2.1;

                  from CMDB.Database        import Database 2.0;

                   

                  syncmapping BAI_Application_Database_Link 1.0

                    """

                    Extension to create a denormalized relationship from BMC_Application to BMC_DataBase.

                    """

                   

                    overview

                      tags CMDB, Extension;

                    end overview;

                   

                    mapping from BAI_Application.bai as bai

                    end mapping;

                   

                    body

                      application := BAI_Application.application;

                      database := Database.database_ci;

                      computersystem := Host_ComputerSystem.computersystem;

                      sync.rel.BMC_Component(Source      := application,

                                             Destination := database,

                                             Impacted    := application,

                                             Name        := "APPLICATIONSYSTEMHIERARCHY");

                    end body;

                   

                  end syncmapping;

                  1 of 1 people found this helpful
                  • 6. Re: CMDB syncmapping extension for SQL Server Database
                    Lisa Keeler

                    The database_ci does come from the base Database pattern, yes.

                    But, the database_ci is set inside a traversal.

                     

                    So, I think you need something like this (with traverse):

                     

                    ==============

                     

                    tpl 1.12 module CMDB.BAI_Application_Database_Link;

                     

                    from CMDB.Host_ComputerSystem import Host_ComputerSystem 2.0;

                    from CMDB.BAI_Application import BAI_Application 2.1;

                    from CMDB.Database        import Database 2.0;

                     

                    syncmapping BAI_Application_Database_Link 1.0

                      """

                      Extension to create a denormalized relationship from BMC_Application to BMC_DataBase.

                      """

                      overview

                        tags CMDB, Extension;

                      end overview;

                     

                      mapping from BAI_Application.bai as bai

                         traverse ElementWithDetail:Detail:Detail:Database as database_node

                         end traverse;

                      end mapping;

                     

                      body

                        application := BAI_Application.application;

                        computersystem := Host_ComputerSystem.computersystem;

                     

                        for db in database_node do

                            database := Database.database_ci;

                            sync.rel.BMC_Component(Source      := application,

                                               Destination := database,

                                               Impacted    := application,

                                               Name        := "APPLICATIONSYSTEMHIERARCHY");

                     

                        end for;

                      end body;

                    end syncmapping;

                     

                    ============

                     

                    I tested it and it worked for me.

                    1 of 1 people found this helpful
                    • 7. Re: CMDB syncmapping extension for SQL Server Database
                      Brice-Emmanuel Loiseaux

                      How do you ensure that the database_ci corresponds to the db node you iter on?

                       

                      I would get the BMC_Database CI by using its key:

                       

                      database = sync.shared.BMC_Database(key := db.key);

                      if database then

                        // create relationship

                      1 of 1 people found this helpful
                      • 8. Re: CMDB syncmapping extension for SQL Server Database
                        Lisa Keeler

                        Thanks, Brice.

                         

                        I wondered about that, and just didn't think about retrieving based on the key.

                         

                        Revised TPL:

                         

                        tpl 1.12 module CMDB.BAI_Application_Database_Link;

                         

                        from CMDB.Host_ComputerSystem import Host_ComputerSystem 2.0;

                        from CMDB.BAI_Application import BAI_Application 2.1;

                        from CMDB.Database        import Database 2.0;

                         

                        syncmapping BAI_Application_Database_Link 1.0

                          """

                          Extension to create a denormalized relationship from BMC_Application to BMC_DataBase.

                          """

                          overview

                            tags CMDB, Extension;

                          end overview;

                         

                          mapping from BAI_Application.bai as bai

                             traverse ElementWithDetail:Detail:Detail:Database as database_node

                             end traverse;

                          end mapping;

                         

                          body

                            application := BAI_Application.application;

                            computersystem := Host_ComputerSystem.computersystem;

                         

                            for db in database_node do

                                database = sync.shared.BMC_Database(key := db.key);

                                if database then

                                // create relationship

                                sync.rel.BMC_Component(Source      := application,

                                                   Destination := database,

                                                   Impacted    := application,

                                                   Name        := "APPLICATIONSYSTEMHIERARCHY");

                         

                            end for;

                          end body;

                        end syncmapping;

                        2 of 2 people found this helpful
                        • 9. Re: CMDB syncmapping extension for SQL Server Database
                          Duncan Grisby
                          I would get the BMC_Database CI by using its key:

                           

                          database = sync.shared.BMC_Database(key := db.key);

                          if database then

                            // create relationship

                          No, that is wrong. You are trying to make a cross-reference to a CI made by another mapping. You don't want to assert the existence of a BMC_DataBase CI with no state. You need

                           

                            database := sync.crossref.BMC_DataBase(key := db.key);

                           

                          And there is no point in testing the result of that with if, because at the stage the mapping executes, it will always be present.

                           

                          But actually, all of this is misguided. I you have a CAM BAI and you want a Database node to be directly related to the BAI node, the correct thing to do is to use a pattern to create that SoftwareContainment relationship in the Discovery model. Then the standard out-of-box CMDB mappings will synchronize it.

                          4 of 4 people found this helpful
                          • 10. Re: CMDB syncmapping extension for SQL Server Database
                            Bernard Stern

                            Thanks Lisa, with a couple minor typo fixes, I was able to make it pass through the successful upload. Unfortunately, there is no effect at all in the CMDB. Given Duncan's input, I more or less understand why.

                            • 11. Re: CMDB syncmapping extension for SQL Server Database
                              Bernard Stern

                              Thanks Duncan. If I understand you correctly, I will write a normal TPL pattern, trigger on a created and verified Database, which I can limit on MS SQL Databases, as we don't need that addition relation for databases (like Oracle DB Server SI) that we can already identify clearly via the software instance. I'll try to follow this path and post the solution if I manage to get it to work properly.

                              • 12. Re: CMDB syncmapping extension for SQL Server Database
                                Bernard Stern

                                I have tried different approaches. The direct approach I tried does not work.

                                 

                                  triggers

                                    on sql_db := Database created, confirmed where type = 'SQL Server Database'

                                                                            and instance exists

                                                                            and name exists;

                                  end triggers;

                                 

                                  body

                                    log.info ("entering BAI MSSQL Containment pattern triggered by SQL Sever Database %sql_db.name%...");

                                 

                                    bais := search (in sql_db traverse ContainedSoftware:SoftwareContainment:SoftwareContainer:BusinessApplicationInstance);

                                    if not bais then

                                      log.info ("No BAI found for MS SQL DB %sql_db.name%, stop pattern run");

                                      stop;

                                    end if;

                                 

                                    for bai in bais do

                                      log.info ("creating a containment for database %sql_db.name% in BAI %bai.name%");

                                      // model.addContainment(bai, sql_db);

                                    end for;    

                                  end body;

                                 

                                This gets me the following log output:

                                 

                                E01-140205376395008: 2019-11-28 13:53:47,641: engine.pattern.ZKB.BAI_MSSQL_Containment.BAI_MSSQL_Containment: INFO: No BAI found for MS SQL DB SQL Server Database immopac_zkb_data, stop pattern run

                                 

                                The search probably is exactly what I am trying to create in the model.addContainment statement...

                                 

                                I also tried an indirect way using the SI in-between the BAI and the DB. The probleme here it works too well, it creates a relation between ALL BAIs and all DBs.

                                 

                                Perhaps I am misunderstanding what the effect of my CAM model is:

                                 

                                I thought this would create a relation between that database and the BAI I am modelling. The thing I cannot do is write a dedicated pattern for this BAI referencing that 'immopac' DB, as we are going to use that type of modelling for all SQL DBs.

                                • 13. Re: CMDB syncmapping extension for SQL Server Database
                                  Andrew Waters

                                  If you add Database using CAM you need to go through the FunctioncalComponent, i.e.

                                  search(in sql_db traverse ContainedFunctionality:FunctionalContainment:FunctionalContainer:FunctionalComponent

                                                             traverse ContainedFunctionality:FunctionalContainment:FunctionalContainer:BusinessApplicationInstance);

                                  3 of 3 people found this helpful
                                  • 14. Re: CMDB syncmapping extension for SQL Server Database
                                    Bernard Stern

                                    Thanks Andrew, this part is now working properly. For the CAM models without the database functional component, I am seeing this in the logs:

                                     

                                    E03-140336196470528: 2019-11-29 13:17:23,461: engine.pattern.ZKB.BAI_MSSQL_Containment.BAI_MSSQL_Containment: INFO: entering BAI MSSQL Containment pattern triggered by SQL Sever Database SQL Server Database CMISTAR...

                                    E03-140336196470528: 2019-11-29 13:17:23,462: engine.pattern.ZKB.BAI_MSSQL_Containment.BAI_MSSQL_Containment: INFO: No BAI found for MS SQL DB SQL Server Database CMISTAR, stop pattern run

                                     

                                    For the test CAM model featuring the database functional component, the logs now say:

                                     

                                    E01-140205800605440: 2019-11-29 12:55:33,008: engine.pattern.ZKB.BAI_MSSQL_Containment.BAI_MSSQL_Containment: INFO: entering BAI MSSQL Containment pattern triggered by SQL Sever Database SQL Server Database immopac_zkb_data...

                                    E01-140205800605440: 2019-11-29 12:55:33,010: engine.pattern.ZKB.BAI_MSSQL_Containment.BAI_MSSQL_Containment: INFO: creating a containment for database SQL Server Database immopac_zkb_data in BAI IMMOPAC

                                     

                                    What seems not to be working so far is the actual creation of the relation. According to the documentation, it _should_ create a relation between the BAI and the DB, but it does not so far. I have scanned the IP hosting that DB a few times, but there is still no relation in the CMDB and I also can't see any logs telling me the relation is created.

                                     

                                      body

                                        log.info ("entering BAI MSSQL Containment pattern triggered by SQL Sever Database %sql_db.name%...");

                                     

                                        bais := search(in sql_db traverse ContainedFunctionality:FunctionalContainment:FunctionalContainer:FunctionalComponent

                                                                 traverse ContainedFunctionality:FunctionalContainment:FunctionalContainer:BusinessApplicationInstance);

                                        if not bais then

                                          log.info ("No BAI found for MS SQL DB %sql_db.name%, stop pattern run");

                                          stop;

                                        end if;

                                     

                                        for bai in bais do

                                          log.info ("creating a containment for database %sql_db.name% in BAI %bai.name%");

                                          model.addContainment(bai, sql_db);

                                        end for;    

                                      end body;

                                     

                                    The addContainment is correct, right? The container is the BAI and the containee is tthe DB, as far as I understand.

                                    1 2 Previous Next