OCS Inventory NG Integration

OCS Inventory NG  is an open-source inventory management system. An integration with OCS Inventory NG allows ]po[ to import inventory information as configuration items, allowing helpdesk and other support groups to associate tickets and other information with physical devices, software etc.

Details

The interface code is included in the intranet-confdb package in the folder "ocs_inventory". Core of the integration is the "ocs2pg.perl" PERL script that converts a OCS MySQL database dump into a format suitable for importing into the ]po[ PostgreSQL database.

A number of GUI portlets associated with configuration items will show the OCS Inventory NG information for each configuration item.

The data from OCS inventory are NOT referenced by any other ]po[ object (and should not in the future). This way it is possible to delete the OCS data completely or even to delete the tables.

Status and Download

The interface code is included in the intranet-confdb package which is part of the ]po[ standard distribution, so that you do not have to download any additional sofware.

The OCS Inventory NG integration link is working stable at one ]project-open[ customer at the moment. However, manual configuration is required to install the integration link so please contact us for support unless you have an experienced system administrator in your team.

Manual Import

1. Dump the contents of your MySQL "ocs" database to a .sql file

Login as user root or any other user that has full MySQL access and issue the command:

mysqldump --databases ocs > ocs-dump.mysql.sql

As a result, you should get a ocs-dump.mysql.sql  file with around 100kB size.

2. Convert the MySQL database dump into a PostgreSQL database dump

]project-open[ provides a tool capable of converting a MySQL dump into PostgreSQL format.
Please login as a user with appropriate permissions and issue the command:

perl /web/projop/packages/intranet-confdb/ocs_inventory/ocs2pg.perl ocs-dump.mysql.sql ocs-dump.pg.sql

Please note that there was an issue in the ocs2pg.perl script when processing data from OCS Inventory 2.x. This bug has been fixed in ]po[ V4.1.0.0.0 on 2014-07-15.

3. Create the PostgreSQL "ocs" database

As user "projop" please issue the commands (on the shell level):

createdb ocs
createlang plpgsql ocs

4. Import the OCS data into a new PostgreSQL database

You can manually import the OCS database dump into PostgreSQL by executing:

psql -f ocs-dump.pg.sql

Please examine the output for errors:

• Error: database "ocs" already exists:
  This error is OK and actually necessary. We have created the "ocs" database manually before in order to add the "plpgsql" language.
  
  
• Error: table "..." does not exist and Error: sequence "..." does not exist:
  These errors are OK when you execute the ocs-dump.pg.sql  for the very first time.
  
  
• \connect: could not translate host name "@saved_cs_client" to address:
  This is because of a MySQL version mismatch. In this case please manually edit the ocs-dump.pg.sql and edit the line "\c ocs ... ;" (line 22 approximately) and remove the commented text between the "\c ocs" and the last ";".
  

At this point all OCS data should be available inside the PostgreSQL "ocs" database. You can check this for example using the following commands:

su - projop                   (make sure you are logged in as user "projop")
psql ocs                      (start the psql client with database "ocs")
select * from hardware;       (SQL command to show a table)

There should be several lines of output resembling hardware items from OCS inventory.

5. Create ]project-open[ Objects from OCS objects

Please execute the script "import.ocs.sql" PL/SQL script that creates ]project-open[ configuration items from OCS hardware items:

psql -f import.ocs.sql

Now you should find OCS hardware objects in the ]project-open[ configuration database.