29. 03. 2019 Mirko Morandini EriZone & OTRS

Importing and Exporting Data

Martin Abegglen, CC BY-SA

A service management system collects an enormous amount of ticketing data, which often needs to be used for statistical evaluation, accounting purposes, monitoring the involvement of 3rd party companies, etc.  EriZone provides several different means to extract this data: On the web interface, you can save search results as CSV or Excel files, and then run user-defined reports using the statistics module. On the command line, you can extract data directly from the DB, or you can extend the underlying OTRS API to create custom events or generic agents that are able to extract data periodically or immediately as it is created (e.g. each time a ticket is created or each time a note is added to the ticket).

Often, you also need to import data into your system: tickets created or managed in other systems, customer data from HR or ERP systems, contracts, and information about hardware and software assets. Writing data directly to the database should be avoided in most cases. Various scripts and commands available on the “otrs.Console” allow for importing services, queues, tickets (https://www.neteye-blog.com/2017/07/importing-tickets-in-erizone/) and other data, but to execute them you need access to the console, and the format of the input data is hard-coded. Thus, as consultants we typically use them in scheduled scripts and during the initial setup of a system.

Moreover, you can use the Generic Interface API for SOAP and REST calls. The standard implementation includes operations for ticket creation, ticket search and data retrieval, and ticket modification.

In this blog post I would like to highlight the Import-Export module of OTRS/EriZone. This module consists of a Perl interface to define modules for importing and exporting the desired objects, and a complete web interface. Once the import/export modules are available (EriZone is delivered with modules for CMDB items import/export and category import/export), you can define import/export templates comfortably on the admin web interface. These templates define the file format and data content; you can even define different templates for the same import/export module.

Let’s look at an example:  Suppose you need a list of all categories in EriZone together with their destination ticket type as shown here: 

CATEGORY NAMEDESTINATION TYPE
Request for InformationService Request
MalfunctionIncident

You just need to define the column mapping for your category template, with these two columns: 

Now you can already export and import data based on this form and the format defined in the template wizard (e.g. comma-separated with title row). Of course, for importing you need to respect the mandatory data requirements – for importing categories the two columns in the example are not enough. Moreover, these templates can even be called from otrs.Console on the command line. For example, they are used by the GLPI asset import scripts available in EriZone.

Import/export Perl modules could also be programmed for any object in the ticketing system. We recently developed one for customer users which can now be imported from CSV files directly from the web interface*. We are also planning to migrate the existing ticket import/export scripts to this mechanism, to allow for flexible ticket imports and exports with user-defined file formats.

(*) custom implementation – not yet included in the standard product.

Mirko Morandini

Mirko Morandini

Mirko Morandini, PhD, is part of the EriZone team since 2015. As a consultant, he guided the implementation of EriZone in various projects in the DACH area and in Italy.

Author

Mirko Morandini

Mirko Morandini, PhD, is part of the EriZone team since 2015. As a consultant, he guided the implementation of EriZone in various projects in the DACH area and in Italy.

Leave a Reply

Your email address will not be published. Required fields are marked *

Archive