2 Replies Latest reply on Jun 8, 2016 11:04 AM by kdavies

    Documentation on Adding Custom Tables to LDMS

    kdavies Apprentice

      I have been searching, with limited success, for documentation on how to add a custom table to the LDMS database that contains permanent information that will persist through LDMS/database upgrades and be available for editing through LDDA bar code web forms and for reporting through LDMS queries. I have found bits and pieces but nothing comprehensive. So far, I have found the following articles:

      Best Known Method - Custom LDDA information permanent

       

      Guide to Importing/Modeling DATE values into LDMS with Data Analytics

      Tip/Trick - Show Extra Patch Related Columns In System Inventory

       

      These articles all touch on the common topic of using an XML file along with the CoreDBUtil utility on the core server to add the data table, but I really want to understand the full topic to make sure that I construct a robust solution that is supported not just with LDMS 9.6 but also LDMS 2016 and beyond.

       

      As a condensed description of the issue I am trying to solve, I am trying to create a table of asset-tracking information including physical device location (e.g. the office number), the first and last name of the person who owns the device (which is not necessarily the person who uses the device), the internal asset tag number assigned to the device, and service tag numbers of any monitors associated or connected to the device. The information will be entered or edited using bar code web forms created through LDDA, will persist throughout the life of the device entry in LDMS, will persist through LDMS upgrades, and the information will be available for inclusion in LDMS queries.

       

      Some questions I have are:

      What is the full schema reference for generating the table? Does the schema allow for indexed tables for efficient searches? Does the schema allow for many-to-one relations ships such as one device having multiple monitors? Does the schema change significantly over time? For example, the "Custom LDDA information permanent" article uses a version 8.7.0.0 schema while the "Show Extra Patch Related Columns in System Inventory" uses version 9.00.2.0.

      Are the custom table changes warranted by LANDesk to survive a LDMS database upgrade? If so, what, if any, restrictions are there regarding in-place upgrades, for example? We depend heavily on this asset information for use with ALM, and if it were to vanish with a core server upgrade, the results would be disastrous. Would a data export followed by data import be required?

        • 1. Re: Documentation on Adding Custom Tables to LDMS
          SupportEmployee

          Hello, the amount of information you need responded to would probably be better handled over a conversation with one of our consultants. Your sales rep can get you in touch with our services department.

          See below for a few quick answers to some of your questions. Beyond that our services guys / partners will be your next level of reference.

           

          What is the full schema reference for generating the table?

               SQL Management Studio can generate a schema diagram but it wont be very useful due to the hundreds of tables in the DB. The important key is the computer table is the parent for anything that must be tied to a device.

          Does the schema allow for indexed tables for efficient searches?

               If you open up datamart.xml you will see that with tables we do add indexes. From there SQL Server does the work on operating in the most efficient way.

          Does the schema allow for many-to-one relations ships such as one device having multiple monitors?

               Yes, some of our tables are one to one, others are many to one. You need to locate a many to one table to incorporate into your template when creating your template. Fileinfoinstance is a many to one where the same computer has many entries to multiple files it has in which it references the fileinfo table as its parent for files.

          Does the schema change significantly over time? For example, the "Custom LDDA information permanent" article uses a version 8.7.0.0 schema while the "Show Extra Patch Related Columns in System Inventory" uses version 9.00.2.0.

               We add between versions and from time to time modify. But overall our database operations are non destructive. Best rule of thumb is to use a unique table name and our upgrade will leave it alone if our schema doesnt include a name with the identical name.

          Are the custom table changes warranted by LANDesk to survive a LDMS database upgrade? If so, what, if any, restrictions are there regarding in-place upgrades, for example? We depend heavily on this asset information for use with ALM, and if it were to vanish with a core server upgrade, the results would be disastrous. Would a data export followed by data import be required?

               Its not necessarily possible for our product management to predict how to make changes to the database that will in no way disrupt what you are doing with it. Our upgrades and database changes are primarily non destructive but from time to time we do need to change columns in our tables. If you are in a mission critical situation where you cannot accept disruption you will need to use a pilot database between upgrades and do schema comparisons and testing. People who have customized their database have gotten along fine but they do have to exercise some caution for those "just in case" possible situations.

           

          Older documents talking about how to expand the schema using coredbutil still apply because the xml formatting and utility in how it works hasnt changed.

           

          Thanks

          Brandon

          1 of 1 people found this helpful
          • 2. Re: Documentation on Adding Custom Tables to LDMS
            kdavies Apprentice

            Thanks for the information. I am sorry I have not had a chance to read your reply earlier. However, if you were modeling data that exists in the unmodeled data table of LDMS, would you manually craft the new table using the procedures described in "LANDesk Management Suite 8.8 - Basic Data Modeling" document (DOC-2538) or use the "Model Attributes" button in DTS in Data Analytics? Both methods look like they do the same thing, so is the DTS method just the more modern more automated GUI-driven version of the XML/CoreDBUtil method in DOC-2538?