Importing Other Fields into Novell Service Desk Using AMIE Imports

  • 7012306
  • 13-Aug-2010
  • 29-May-2013

Environment

Novell Service Desk

Situation

This article explains how to import other fields from an asset database into Novell Service Desk.

The pre-requisite is that the SQL query required to ascertain the field for the unique identifier (ASSETID) is already established. Also a basic understanding of the layout of an XML file might be beneficial.

Resolution

Importing Other Fields Using AMIE Imports

The XML templates used in AMIE imports are customizable to allow other fields in the external asset database to be pulled into Novell Service Desk. This article explains how to do this with an example scenario.

This example is done using an Express Software Manager/Express Metrix asset management system on SQL Server database. The field in question is a custom field inside the asset management database. This has been set to store the item’s department. However, this approach can be used for other asset management systems on other database formats.

First the SQL query that pulls the piece of information is required. Such queries may be obtained by examining the database for the asset management system (without making any changes to it), or by contacting the makers of the software.

It may also be beneficial to have a basic understanding of the layout of XML files since we will be reading such files to ascertain how to create the queries needed and ultimately we are editing an XML file.

 

Obtaining what is needed from Novell Service Desk

The SQL query needs to be able to be filtered for one row of information based on the unique identifier. This is found at the start of the XML template.

To obtain the XML Template, do the following:

1. Login as Admin

2. Setup > CMDB Import > Customize

3. With the Operation set to Export Configuration, select the appropriate type of asset management server from the list

4. Click Export

Examining the start of the XML template we see the following...

<IMPORT_DEFN SERVER=”Express Software Manager” VERSION=”9.0” AUTHOR=”Novell Service Desk Software Inc.”>

  <ITEM CATEGORY=”Hardware”>

    <ASSET_ID_LIST PARAM_NAME=”ASSETID”>

      <SELECT>SELECT machineHardware.PROPMACHINEID</SELECT>

      <FROM>FROM com.Novell Service Desk.assetimport.MACHINEHARDWARE machineHardware</FROM>

 

At first glance, it might seem like the unique identifier is the field PROPMACHINEID in the table com.Novell Service Desk.assetimport.MACHINEHARDWARE.

However, this is the how these fields and tables are referenced by Novell Service Desk in the XML. To understand where these values have originated, the AMIE generated schema debug files, need to be checked. For versions, prior to 6.1, Novell Service Desk needs to be configured to output the debug logging entries for AMIE imports for these files to be generated. To enable the debug logging, the following lines of the logging.properties file inside the WebApps/Novell Service Desk/WEB-INF/Novell Service Desk.woa/Contents/Resources folder need to be changed from...

### Asset Management logging

log4j.logger.com.Novell Service Desk.assetmgmt=warn

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb=warn

 

log4j.logger.middlegen=warn

log4j.logger.org.apache.velocity=warn

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb.MiddlegenDriver=warn

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb.MiddlegenEngine=error

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb.MiddlegetPopulate=error

log4j.logger.net.sf.hibernate.tool.hbm2java=error

 

...to...

 

### Asset Management logging

log4j.logger.com.Novell Service Desk.assetmgmt=debug

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb=debug

 

log4j.logger.middlegen=debug

log4j.logger.org.apache.velocity=debug

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb.MiddlegenDriver=debug

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb.MiddlegenEngine=debug

log4j.logger.com.Novell Service Desk.assetmgmt.dynamicdb.MiddlegetPopulate=debug

log4j.logger.net.sf.hibernate.tool.hbm2java=debug

 

Whilst the above debug settings are not required to see the schema files for 6.1 and above, these debug entries can help provide extra insight in the log files, when testing imports, if queries don’t work at first. As such, these are probably a good idea to configure in any case.

After this, an import attempt needs to be made. (Selecting not “Auto Create New Items” will make sure no items are promoted from the AMIE Snapshot too early, if we are not ready to do this.)

This will generate a number of files inside /Novell Service Desk/AssetImport/. This may be C:\Novell Service Desk\AssetImport\ on Windows machine or similar. Underneath this folder, will be a numbered folder, which is the unique identifier of the configuration, then under that be /com/Novell Service Desk/assetimport/. These are the debug schema files for asset imports.

From version 6.1, these files are also included when the logs are exported in Novell Service Desk by selecting Reports > Log > Export as an Administrator user.

Note that the files will be cleared if the application is restarted, until an import is attempted again.

 

Converting SQL queries to XML template Queries

Inside the asset import debug file folder, we are only interested in files ending with .hbm.xml. If we open the MACHINEHARDWARE.hbm.xml the following needs to be noted:

<class name=”com.Novell Service Desk.assetimport.MACHINEHARDWARE” table=”machinehardware” schema=”dbo” lazy=”true”>

On this line it shows the name of the table that is required in the Novell Service Desk XML code (name) followed by the actual table and schema in the SQL Server database.

Following this we have id tags for all the fields in the database:

<id name=”PROPMACHINEID” type=”java.lang.Integer” column=”`machineid`”>

These show the name of the field to be used in the XML code followed the Java identifier of it’s type and the actual column name in the SQL server database.

This not only shows us how we need to create the queries in the Novell Service Desk XML code format but also ensures that we are looking at the correct unique identifier field. From this we can see the identifier field in the asset database is machinehardware.machineid (machineid in the machinehardware table).

When viewing the other queries in the XML, notice that they are all based on this replacing the content of this field with :ASSETID. As shown:

    <QUERY NAME=”computer”>SELECT machineHardware FROM com.Novell Service Desk.assetimport.MACHINEHARDWARE machineHardware WHERE machineHardware.PROPMACHINEID = :ASSETID</QUERY>

    <QUERY NAME=”network”>SELECT machine FROM com.Novell Service Desk.assetimport.MACHINE machine WHERE machine.PROPMACHINEID = :ASSETID</QUERY>

    <QUERY NAME=”owner”>SELECT user FROM com.Novell Service Desk.assetimport.USER user, com.Novell Service Desk.assetimport.MACHINEHARDWARE machineHardware WHERE machineHardware.PROPMACHINEID = :ASSETID and machineHardware.PROPCLASTUSER = user.PROPUSERID</QUERY>

 

What to note from these queries is that this id field is referenced as machines.machineid (machineid in the machines table) in the network query so we don’t just have to base our queries on the machinehardware table.

Initially one might think that to create the queries in the XML form, the tables are prefixed with com.Novell Service Desk.assetimport., capitalized, appended with an alias with field names capitalized and prefixed with PROP, but it’s not always that simple...

Something to note here is that in the debug file for MACHINE there is this class line:

<class name=”com.Novell Service Desk.assetimport.MACHINE” table=”machines” schema=”dbo” lazy=”true”>

Note that Novell Service Desk has not just prefixed the table name with com.Novell Service Desk.assetimport. but it has removed the s from the end of it’s name.

Another thing that be seen is underneath this class line is this:

   <composite-id name=”comp_id” class=”com.Novell Service Desk.assetimport.MachinePK”>^M

        <key-property name=”PROPNAME” column=”`name`” type=”java.lang.String” length=”128”>

        

The field named “name” is in sub tag called comp_id. This means that to refer to this field in Novell Service Desk XML, it needs to reference it as comp_id.PROPNAME. This becomes more relevant later in this tutorial.

One other illustration of where the field name can change is if we look at the last query mentioned above, it includes the field “machineHardware.PROPCLASTUSER”, but the debug file shows this as follows:

<property name=”PROPCLASTUSER” type=”java.lang.Integer” column=”`c_lastuser`” not-null=”true” length=”10” />

Note that the actual field in the SQL Server database contains an underscore.

The above examples show why the debug files are quite crucial to creating custom queries so these files do need to be referred to during the creation of custom queries such as this.

The actual name of the query language used in the XML file is Hibernate Query Language (HQL). There are sites on the internet that help with the syntax involved with this language, which may become more of a factor when dealing with more complex queries. A good place to start is here:

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html

Example

Moving back to the example for this article, the following query will provide the custom field numbered 1015 for all the items in the table. This query is specific to the Express Software Manager and the resulting field could store whatever custom value they have configured it to display. In this example, it is storing the department:

select machineattributes.stringvalue

from machineattributes, machines

where machines.machineid=machineattributes.machineid and

machineattributes.attribute = 1015

Now we can see reference to the machineid field in the machines and machinesattribute tables here. If we look at the other queries in the original template XML, we can see that the machines.machineid is compared with :ASSETID, as well as the machinehardware.machineid field. This tells us that all these machineid fields refer to the same unique identifier so if we convert this example query to provide us this custom field for just one item, we would simply add an extra where argument, like thus:

select machineattributes.stringvalue

from machineattributes, machines

where machines.machineid=machineattributes.machineid and

machineattributes.attribute = 1015 and

machineattributes.machineid = 1

This is looking at the department (custom field 1015) for the item identified in the database as number 1, if the department has been entered for this item. 

Therefore to convert this query, we know that the first thing we need to do is to ensure it includes a reference to the unique identifier as the other queries in the Novell Service Desk XML do. As such, we would change the last line to read:

machineattributes.machineid = :ASSETID

Now we need to convert the table names. If we look at the debug files, we will see there is no MACHINEATTRIBUTES file, but there is a MACHINEATTRIBUTE(.hbm.xml) file, which is close enough so examining that file will tell us that we need to change that table name to com.Novell Service Desk.assetimport.MACHINEATTRIBUTE, as indicated in this class line here:

<class name=”com.Novell Service Desk.assetimport.MACHINEATTRIBUTE” table=”machineattributes” schema=”dbo” lazy=”true”>

We already know from looking at the other query results and/or from referring to the MACHINE(.hbm.xml) file, that we are replacing machines with com.Novell Service Desk.assetimport.MACHINE so this changes from line to read as follows:

from com.Novell Service Desk.assetimport.MACHINEATTRIBUTE, com.Novell Service Desk.assetimport.MACHINE

Note that in the other queries an alias is added after the table names so the rest of the query then refers to the tables via the alias instead of this full XML version, so we shall do that here also, which makes the from line read:

from com.Novell Service Desk.assetimport.MACHINEATTRIBUTE machineattribute, com.Novell Service Desk.assetimport.MACHINE machine

The aliases have been kept in line with the table names, in that they don’t have the extra s on the end too. In doing this, we need to update the rest of the SQL query to refer to the new aliases in place so the query, at this point, becomes: 

select machineattribute.stringvalue

from com.Novell Service Desk.assetimport.MACHINEATTRIBUTE machineattribute, com.Novell Service Desk.assetimport.MACHINE machine

where machine.machineid=machineattribute.machineid and

machineattribute.attribute = 1015 and

machineattribute.machineid = :ASSETID

The next thing to do is to replace the specific field being referenced. Currently the query is looking at the stringvalue in the machineattributes table but the actual fields are referenced in the FIELD lines further down in the XML template, so we shall change the first line to read:

select machineattribute

The last part is to convert the remaining field names over. The field names now referenced in this query, followed by the relevant lines in the debug files are as follows:

machine.machineid

    <property name=”PROPMACHINEID” type=”java.lang.Integer” column=”`machineid`” not-null=”true” unique=”true” length=”10” />

This tells us that we can just put PROPMACHINEID in place of machineid

machineattribute.attribute

machineattribute.machineid

    <composite-id name=”comp_id” class=”com.Novell Service Desk.assetimport.MachineattributePK”>

        <key-property name=”PROPMACHINEID” column=”`machineid`” type=”java.lang.Integer” length=”10”><meta attribute=”use-in-equals”>true</meta></key-property>

        <key-property name=”PROPATTRIBUTE” column=”`attribute`” type=”java.lang.Integer” length=”10”><meta attribute=”use-in-equals”>true</meta></key-property>

This tells us that the fields are inside the comp_id names, as mentioned previously in this article so they are referred to as comp_id.PROPATTRIBUTE and comp_id.PROPMACHINEID

Finally, we can tidy up the select statement so it looks like the others with upper case terms like SELECT and then add it as a query with a name. In this case we will call it the orgunit as that relates to the department. This gives us the following line to add to the list of queries in the template XML:

<QUERY NAME=”orgunit”>SELECT machineattribute FROM com.Novell Service Desk.assetimport.MACHINEATTRIBUTE machineattribute, com.Novell Service Desk.assetimport.MACHINE machine WHERE machine.PROPMACHINEID=machineattribute.comp_id.PROPMACHINEID and machineattribute.comp_id.PROPATTRIBUTE = 1015 and machine.PROPMACHINEID = :ASSETID</QUERY>

In this example we had configured custom field 14 on the hardware category inside Novell Service Desk to show the Department/OrgUnit so we can copy one of the other ITEM PROPERTY lines already there and modify it accordingly.

For example, the first line shows the following:

    <ITEM_PROPERTY FIELD=”field1” VALUE_PREFIX=”” VALUE=”computer.PROPCPROCESSOR” VALUE_SUFFIX=”MHz” TYPE=”LOOKUP” />

We know the field in Novell Service Desk is field14 so we can update that. There is no prefix or suffix we need to add to the value so the prefix can be left as is, with the Mhz value removed from the suffix. The type is a lookup as the data is being lookup on the database as opposed to some fixed static value.

This leaves the VALUE field which is comprised of the query name and the field in query so this gives us the following line:

    <ITEM_PROPERTY FIELD=”field14” VALUE_PREFIX=”” VALUE=”orgunit.PROPSTRINGVALUE” TYPE=”LOOKUP” />

Adding this line in the list of property fields and before the </ITEM> line, will complete this work.

These are all the changes needed to ensure this value is imported into Novell Service Desk.

However, in the case of the orgunit/department, we could go one step further and actually assign ownership of the item to the department too/instead.

Due to the setup of Express Software Manager the XML template only has the line for allocating ownership to a customer. Other templates for other asset management systems include the line for also assigning Org Unit so we can take such a line as the basis for adding this to our XML template.

The end result would be adding this line between the <OWNERSHIP> and </OWNERSHIP> lines:

      <ORG_UNIT FIELD=”displayString” VALUE=”orgunit.PROPSTRINGVALUE” TYPE=”LOOKUP” />

Using the file with this in as well or instead will now make the ownership apply if an Org Unit with the same name has been entered in Novell Service Desk. Note that in the case of departments, the value it will be looking for is Company - Dept, so that is what needs to be stored in this field in the asset database for this to work.

 

Updating the XML in Novell Service Desk

The following steps need to be followed to update or upload a custom XML template in Novell Service Desk:

1. Login as Admin

2. Setup > CMDB Import > Customize

3. Click Upload/Edit Configuration

4. Click New if this is a new configuration or select the current Configuration in the Type popup if a custom one is being edited and then click Edit

6. Click “Choose File” and upload the new configuration file containing the XML template

7. Click Save

On the Setup > CMDB Import > [Server name] screen, if the type in use is the default one supplied by Novell Service Desk, this needs to be updated to the custom one created. This would probably be only true the first time round, if an import was attempted first using the default template. Do this update by editing this screen, NOT by deleting the configuration.

Running an import now with the updated configuration should update the item numbers for the existing imported items.

The original configuration should NEVER be deleted unless the Asset Management server has been decommissioned really. Doing the above maintains all the links from the configuration to the snapshot to the item, so the updates simply flow through.

Doing an item import should now update the new field(s) accordingly. 

Additional Information

Formerly known as 1001036