Import Excel with MDR Configurator
Tutorial - MDR Setup
MDR stands for Management Data Repository. It is used to connect and import different data source into OneCMDB. Each data source is organized in its own unique way with tables and columns. Therefore the MDR configuration is responsible to transformation the external data source to the model defined in OneCMDB. Often information is stored in different places and with different formats. The MDR concept in OneCMDB makes it possible to combine all of this information into one model. The supported data sources are excel, csv and sql databases, access through jdbc.
This tutorial will go through how to connect and import an excel sheet that contains applications and hardware server information. The model used in this tutorial is the Basic Model that is provided with OneCMDB. The tasks that need to be performed when connecting and importing external data sources are the following:
- Create a new MDR and a configuration.
- Configure the MDR
- Select the data source type and location.
- Map columns in the data source to templates/attribute defined in the the OneCMDB model.
- Optionally add additional data.
- Resolve relations between templates, this process is automated since the model dictates how all template references each other.
- Per template select which attributes that should be considered as natrual keys. The natural keys are used to identify an instance, used to prevent duplication of instances.
- Execute the MDR configuration.
- Open the execution result and commit changes to OneCMDB. Each MDR configuration keeps an history of previously committed data. In this way changes in the data is detected, as well as deletion of removed data.
Excel data source
The example Excel file used in this tutorial has two sheets, Servers and Applications.
The demo Excel-file can be found here.
Since we will populate the Basic Model with this tutorial lets examine it. The model contains the following templates structure.
To get a better overview of the model, use the “Model Overview” widget. It's an applet that graphically displays all templates/attributes and the relations with each other. To view the references for a specific template just click on it in the left tree graph and the references will be visible in the right panel. The template structure and/or template attributes can easily be modified by using the “Model Editor” found under Start->Design->Model Editor. If the Basic Model doesn't fit your needs one can create custom designed model.
Import Basic Model
Create MDR and Configuration
Enter the name of the MDR and the name for this particular configuration, and press “Create”
The MDR Name groups many configurations together. Typically the same data source have the same MDR name, and different transforms from that data source will have its own configuration.
The MDR configurations are stored on disk in the repository as xml-files. They can be found under the directory repository/MDR. Each MDR have it's own directory, the MDR Name, in this example it will be /Tutorial. Each configuration is then stored under conf/Servers/source.xml, transform.xml. Note that a new feature we call OneCMDB Quick help popups at the bottom/right of the desktop. This will help you understand what you are doing. Once you know your way around this can be disabled from the Start menu, Start->Quick Help->disable.
Configure the MDR
Once the MDR has been created, the configuration window popups. This configuration can later be open by selecting the “Configure” button in the “MDR Overview” (marked orange in image).
Define the Data Source
- Press the “Upload” button to upload the excel file to onecmdb server repository. Or if that already has been uploaded one can select that file with the “Select”.
- Enter the sheet name.
- Enter the row that contains header row.
- Enter the first row where data begins.
Preview the Data Source
Map columns to templates and attributes
Here the table is transposed, meaning that each column now is a row. Each row is identified by the column header name.
To map a column first select in which CMDB Template that should contain values from this column. Click on the CMDB Template Cell, and then click on the small icon on the right side of the cell.
To populate more than one attribute in a template select that template again.
For each row in the data source a new instance of the selected template will be created. This can however be controlled by the usage of natural keys, see below. Also if more than one template is used relations between the instances can be controlled in the “Reference”-tab. The system will automatically find all references for you, but one can remove the ones that are not valid in this transform.
Add additional data
Now select the next tab, Additional Data.
Here one can populate the CMDB with additional fixed data not stored in the data source.
Here we add a Company template and select that attribute Name.
This can in the next tab, References, be connected to instances that contains references to a Company.
In this tab we select which references that should be used. Data in each row in the data source will be considered to relate to each other, else is should not be on the same row.
The first time one selects this tab, the system will automatically populate all references between the templates used. If one changes the mapping or adding additional data, one has to push the “Update” button to resolve new references again.
The following references exists between all templates.
The Company template we added in the additional tag, is referenced a lot. This was not the intention. The gaol was to make the PlatformService used by a Company. The Maintenance was intended for the hardware server and the OS.
So we can remove the unwanted references by selecting the check-box at the left and then click on “Remove”.
So the final result look like this:
Determine Natural Keys
The natural keys controls how many instances of a specific template that should be created. It specifies which attributes are regarded as keys.
If another instance have the same set of keys it will be re-used.
To illustrate this, click on the “Calc Instances” and the result will look like this.
We see that it will generate 24 server instances, but we only had 12 row in the excel sheet! This is because each time the transform tries to find a instance it creates a new since we have not set any natural key. The reason it's more than 12 is because the Server is referenced by another instance, and since we don't reuse anything it create a new instances.
The PlatformService instances will be identified with the Name attribute, and that only has 5 different values in the excel sheet. Another example is the OS, here we have selected the InstalledOn, which is a reference to a Server. Since in the input data we do not have anything to identify the OS by else than it is installed on a specific Server.
Now we are ready with the configuration, press the “Save” button and close this window. (If you try to close the window or press the finish button and you have done modifications, you will get an question to save or not)
Execute the MDR
The execution of the MDR is performed by an external script. This is because sometimes we need to run an application to retrieve information, like NMAP.
Open the execution by selecting the MDR configuration and click on execute.
Then press start in the window and the transformation of the excel-file starts.
Look for “Execution Completed” when the transformation is ready.
Then close the window.
Open Execution Result and Commit
The select “Commit to CMDB” to actually update the database with this data.
When the commit has finished the status in the MDR window will now show “COMMITTED”.
One can also see how many objects have been add/deleted and modified. If you upload a new modified version of the excel-file and run the execute again, all changes will be detected including removal of data.
Add Applications to the Servers
In the excel file we had another sheet specifying all applications that are running on each server. To import that we follow the same procedure as when importing the servers,
- Create a new MDR configuration, use the same MDR name, Turorial. And Applications as the configuration name.
- Select the same excel-fil, by using the “Browse”-button in the “Data Source” tab.
- Specify Sheet name “Applications”, Header Row (3) and Data Start Row (4)
Then select the “References” tab, this will however be empty. This is because there are no reference between an “ServerApplication” and “Server”. The “ServerApplication” has a reference to a OS and the OS has a reference to the Server. So then we need to use the “Additional Data” and add the OS template.
We use the Family attribute and an empty value. Now go back to the “References” tab and click on the “Update”-button. Now the reference between ServerApplication → OS → Server have been established.
Next we define the Natural Keys.
Since we already have imported the Server and OS previously we see that we have found these in the CMDB by using the Natural Key.
Now select the Finish-tab and click “Save” and close the window.
Execute the transform and open/commit the data.
Now we have populated OneCMDB with our servers and applications.
The result can now be view with “CMDB View”.