14. 05. 2014 MarinovMihail Asset Management, EriZone & OTRS

CMDB integration between OTRS and OCS Inventory

OTRS is a configuration management system (CSM), ITIL© v3 compliant that has its own CMDB in order to manage configuration Items (CI) involved in ITIL processes. It implements the Service Asset & Configuration Management process to ensure that the assets required to deliver services are properly controlled, and that reliable information about those assets is available where it is needed. The most important point now is how to populate this CMDB and then how to ensure that gaps with the real world are detected and notified.

Well, an automatic tool for CI information collection is required. A good solution could be OCS Inventory NG.  It  is  an open source software that enables users to automatically discover the IT assets. OCS agents are installed on networked machines in order to collect information about the hardware and software and send them to an OCS server.

The idea is first to get all necessary data collected by OCS and to populate initially the OTRS CMDB and then to use up-to-date OCS  data to notify uncontrolled gaps between CMDB and real assets.

The initial data could be retrieved in csv format from the OCS database and uploaded through the OTRS Import/Export module. Further, in a regular time intervals, the same data could be extracted from OCS in csv format  and compared with those exported in csv from OTRS. All difference could be notified as unauthorized changes by sending an email to OTRS.

Here is a simple example for computer and installed software data upload.

1. Export computers from OCS:

2. Import computers in OTRS

Import Computer OTRS

3. OTRS is initially populated and links to the components of OCS are provided.

4. Do the same for the software. Then you can export from OCS and create links between software and computers in OTRS using a simple script.

configuration item OTRS

If you’re interested in getting more information about the integration please feel free to contact me through the blog.

 

MarinovMihail

MarinovMihail

Developer at Würth Phoenix
“Hi guys! I’m Mihail and since the university years I has been fascinated by distributed systems and measurements on them. Now when I join the Neteye project I get the possibility to continue with this passion and this is great. My free time is completely dedicated to my wife and my daughters, I simply love them.”

Author

MarinovMihail

“Hi guys! I’m Mihail and since the university years I has been fascinated by distributed systems and measurements on them. Now when I join the Neteye project I get the possibility to continue with this passion and this is great. My free time is completely dedicated to my wife and my daughters, I simply love them.”

43 Replies to “CMDB integration between OTRS and OCS Inventory”

  1. joao vitorino says:

    Your article helped me a lot to do a integration between OTRS and OCS in my company.
    Now I’m trying automatically create link between CI on OTRS.
    i.e: Link a computer to a hardware.
    Do you know how to do that?

  2. Mihail Marinov says:

    You can create a simple perl script using the OTRS API
    In the following a sample for SW-HW link creation (snippet)

    open FILE, “name of file” or die $!;
    while (,FILE>) {
    my @FIELDS = split “,”, $_;
    my $HW = $CommonObject{ConfigItemObject}->ConfigItemSearchExtended(
    Name => $FIELDS[1],
    );
    my $SW = $CommonObject{ConfigItemObject}->ConfigItemSearchExtended(
    Name => $FIELDS[2],
    );
    …..
    my $LinkResult = $CommonObject{LinkObject}->LinkAdd(
    SourceObject => ‘ITSMConfigItem’,
    SourceKey => @{$SW}[0],
    TargetObject => ‘ITSMConfigItem’,
    TargetKey => @{$HW}[0],
    Type => ‘RelevantTo’,
    State => ‘Valid’,
    UserID => 1,
    );
    ……
    }
    close FILE;

    For further information about our solutions feel free to contact us through our web site: http://www.wuerth-phoenix.com/en/solutions/system-management/erizone-otrs/

  3. i want to dynamically automate the synchronization process between OCS to OTRS CMDB so can u tell me plz how to to that ?

  4. Mihail Marinov says:

    It can be done by a simple script executed via ctontab
    steps
    1. execute the query to retrieve the OCS data in FILENAME_OCS
    2. run /opt/otrs/bin/otrs.ImportExport.pl -n TEMPLATENUMBER -a import -i FILENAME_OCS in order to load data
    If you want to check the differences
    run /opt/otrs/bin/otrs.ImportExport.pl -n TEMPLATENUMBER -a export -o FILENAME_OTRS to export data from OTRS
    diff FILENAME_OCS FILENAME_OTRS to get the differences
    evantually for the differences send an email to OTRS

    For further information about our solutions feel free to contact us through our web site: http://www.wuerth-phoenix.com/en/solutions/system-management/erizone-otrs/

  5. Arthur says:

    Hi.. Please i’m new to otrs and ocs and i need to do this integration.
    Can you send me some more detailed information on how to do this?

    Thanks.

    1. Mihail Marinov says:

      A good documentation regarding OTRS is available at http://otrs.github.io/doc/
      There you can find the description of the Import/Export mechanism.
      This post describes an idea how to use this mechanisme to import data from OCS (http://wiki.ocsinventory-ng.org/index.php/Documentation:Main)

      For further information about our solutions feel free to contact us through our web site: http://www.wuerth-phoenix.com/en/solutions/system-management/erizone-otrs/

  6. Matthew says:

    Hi, please describe little bit more how to create links between software and computers in OTRS using a simple script and how to use OTRS API to create this integration.
    Thanks
    Matt

    1. MarinovMihail says:

      You can find a simple script here: http://www.neteye-blog.com/wp-content/uploads/2014/11/LinkCIs.zip
      This is just an example.

      For further information about our solutions feel free to contact us through our web site: http://www.wuerth-phoenix.com/en/solutions/system-management/erizone-otrs/

  7. Matt says:

    Thanks, but below condition is not working, generally veribles @{$HW} and @{$SW} are empty.

    if( $HW && ref $HW eq ‘ARRAY’ && @{$HW} && $SW && ref $SW eq ‘ARRAY’ && @{$SW})

    1. Mihail Marinov says:

      This is just an example.
      Feel free to adapt it to your needs.

  8. Arnaud says:

    Hey,

    I’m trying as well to integrate it into OTRS, but encountered problems… I’m wondering by wwhere I have to start. Is it possible to help e going through that ? Thanks a lot.
    Arnaud

  9. Mihail Marinov says:

    For more information about OTRS integration and our solutions feel free to contact us through our web site: http://www.wuerth-phoenix.com/en/solutions/system-management/erizone-otrs/

  10. Arnaud says:

    Hey,
    Finally I manage to do a scrit that import automatically the computer and network in the CMDB. And I succeed to do the perl script that automatticaly link CI between them (for computer and software). I was wondering if for linking computer and network what I have really to change ? Can you indicate me the way ? Thanks a lot!

  11. Mateus Domingos says:

    dear Mihail Marinov,

    can you tell me in wich folder of otrs do i past the file that permit automatic link.

    1. Mihail Marinov says:

      Dear Mateus Domingos,

      put the script in /opt/otrs/bin/

      Regards

  12. Miguel says:

    Amazing, i’ll try it!!

  13. Todd Butt says:

    When I go to import into the OTRS I will get the error Can’t Import Entity “X”: Identifier Field is empty. Or I will receive one of these errors Need Name!, or Can’t Import entity X: Error when adding the New config item version. Any Idea what I’m over looking when I go to import a .CSV file

    1. Mihail Marinov says:

      1.check if the fields in you ci definition and in the import template are the same
      2.make sure your import file contains the fields as expected

  14. Juan says:

    Hello, when I try to edit the mapping information I do not see the options “OcsType” and “SCO-Link”. Where should I create these fields? Greetings!

    1. mihail marinov says:

      Hi,
      it depends on how your ci is defined in the Config Item Management Area
      In this example the ci definition is like this:

      [
      {
      Key => ‘ID’,
      Name => ‘ID’,
      Searchable => 1,
      Input => {
      Type => ‘Text’,
      Size => 20,
      MaxLength => 20,
      },
      },
      {
      Key => ‘Vendor’,
      Name => ‘Vendor’,
      Searchable => 1,
      Input => {
      Type => ‘Text’,
      Size => 50,
      MaxLength => 50,
      },
      },
      {
      Key => ‘Model’,
      Name => ‘Model’,
      Searchable => 1,
      Input => {
      Type => ‘Text’,
      Size => 50,
      MaxLength => 50,
      },
      },
      {
      Key => ‘OcsType’,
      Name => ‘OcsType’,
      Searchable => 1,
      Input => {
      Type => ‘Text’,
      },
      },
      {
      Key => ‘Owner’,
      Name => ‘Owner’,
      Searchable => 1,
      Input => {
      Type => ‘Customer’,
      },
      },
      {
      Key => ‘SerialNumber’,
      Name => ‘Serial Number’,
      Searchable => 1,
      Input => {
      Type => ‘Text’,
      Size => 50,
      MaxLength => 100,
      },
      },
      {
      Key => ‘OperatingSystem’,
      Name => ‘Operating System’,
      Input => {
      Type => ‘Text’,
      Size => 50,
      MaxLength => 100,
      },
      },
      {
      Key => ‘OCS-Link’,
      Name => ‘OCS-Link’,
      Searchable => 1,
      Input => {
      Type => ‘Text’,
      Size => 50,
      MaxLength => 100,
      },
      },
      ];
      Regards.

  15. Guillermo says:

    Hi mihail, i am trying to get this script work but i dont know where to put it or the extension of the same.

    select h.ID as ID, h.NAME as Name,b.SMANUFACTER as MANUFACTER,b.SMODEL as MODEL, b.TYPE as OCSTYPE, h.USERID as OWNER, b.SSN as SERIALNUMBER, h.OSNAME as OPERATINGSYSTEM,
    “Production” AS DEPLOYMENTSTATE,
    “Operational” AS INCIDENTSTATE,
    CONCAT(“http://192.168.1.120/ocsreports/index.php?function=compute&head=l&systemid=”,h.ID) as OCSLINK
    from hardware h JOIN bios b ON h.ID=b.HARDWARE_ID
    INTO OUTFILE ‘/tmp/computers.csv
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\N’;

    Thanks

  16. mihail marinov says:

    Hi Guillermo,
    You should run/schedule the query on the OCS server.
    mysql ocsweb -u USER -pPW < YOUR SCRIPT.sql
    Afterward the output file /tmp/computers.csv should be imported in OTRS
    Regards

  17. Joan Frigola says:

    Hi all,
    In my case the SQL sentences has been execute in SQL menu at https://server_name/phpmyadmin (where server_name is my OCSInventory server)

    SELECT “” AS NUMBER, A.TAG AS NAME, “Production” AS DEPLOYMENTSTATE, “Operational” AS INCIDENTSTATE, B.`SSN` AS SERIAL_NUMBER, “” AS NUM_FAC, “” AS DATA_FAC, “” AS PROVEIDOR, H.`ID` AS OCS_ID, CONCAT( “https://server_name/ocsreports/index.php?function=computer&head=1&systemid=”, H.`ID` ) AS OCSLINK, H.`NAME` AS OCS_COMPUTER, H.`USERID` AS USER_ID, B.`SMANUFACTURER` AS FABRICANT, B.`SMODEL` AS MODEL, B.`TYPE` AS TIPUS
    FROM `hardware` H
    LEFT JOIN `bios` B ON H.ID = B.HARDWARE_ID
    JOIN accountinfo A ON H.ID = A.HARDWARE_ID

    For you information: A.TAG = accountinfo.TAG = is a manual fieldname we use in OCS Inventory

  18. Spencer Hazell says:

    As per the tutorial I have 3 csv files. How do I link the computer to the software. I guess this is something to do with the Link_Relation and ConfigItem tables but I dont know how get the linked items set by scripting. Please help

  19. mihail marinov says:

    Hi Spencer Hazell,

    the third file, namely computers_sw.csv, should be used as an input to the script you can find here http://www.neteye-blog.com/wp-content/uploads/2014/11/LinkCIs.zip

    This is just an example.

    Regards.

  20. Cyrille says:

    Hello,

    I did some tests here, and have discovered that computers ID’s in OCS change from time to time. So, automatic update of the OTRS CMDB seems not possible.

    Did you already experience this behaviour?

    Best regards,

    Cyrille

  21. mihail marinov says:

    Hi,

    for recent versions of OCS you can try using UUID. The DeviceID will still change when, for example, the name of the device is changing.

    for computers.csv:

    select h.UUID as ID, h.NAME as Name,b.SMANUFACTURER as MANUFACTURER,b.SMODEL as MODEL, b.TYPE as OCSTYPE, h.USERID as OWNER, b.SSN as SERIALNUMBER, h.OSNAME as OPERATINGSYSTEM,
    “Production” AS DEPLOYMENTSTATE,
    “Operational” AS INCIDENTSTATE,
    CONCAT(“http://YourOSCServer/ocsreports/index.php?function=computer&head=1&systemid=”,h.ID) as OCSLINK
    from hardware h JOIN bios b ON h.ID=b.HARDWARE_ID
    INTO OUTFILE ‘/tmp/computers.csv’
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\n’;

    for software.csv:

    select s.PUBLISHER as VENDOR, s.NAME as NAME, “Production” AS DEPLOYMENTSTATE, “Operational” AS INCIDENTSTATE
    from softwares s JOIN dico_soft d ON s.NAME=d.EXTRACTED where s.PUBLISHER != “”
    group by s.NAME
    INTO OUTFILE ‘/tmp/software.csv’
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\n’;

    and for computers_sw.csv:

    select h.UUID,h.NAME,s.NAME from hardware h JOIN softwares s ON h.ID = s. HARDWARE_ID JOIN dico_soft d ON s.NAME=d.EXTRACTED
    where s.PUBLISHER != “” GROUP BY CONCAT(s.NAME,h.NAME)
    INTO OUTFILE ‘/tmp/computers_sw.csv’
    FIELDS TERMINATED BY ‘,’
    LINES TERMINATED BY ‘\n’;

    Regards

    1. Darrell says:

      Hi,

      This has been so helpful! Thank you so much for sharing and helping.

      I do have a question though, how do you link the computers and the software with the computers_sw.csv file in OTRS?

      Regards,

      1. Hi Darrell,

        the output file,computers_sw.csv, should be used as an input to the script you can find here http://www.neteye-blog.com/wp-content/uploads/2014/11/LinkCIs.zip

        This is just an example.

        Regards.

    2. jgarcia says:

      Hi!

      Thanks for help us with the ocs-otrs integration!

      I understand the whole procedure, but I can not do it.

      I dont know how to do the mysql queries to extract the csv.

      I have the ocs server in linux and I tried to make the query through mysql installed in the server but I appear syntax errors.

      any ideas? Thanks in advance. Greetings.

      1. mihailmarinov says:

        Hi Garcia,

        what kind of syntax errors?
        Pay attention to the quotation marks, please. They must be straight and not curly
        wrong:
        “Production”
        ‘/tmp/software.csv’

        right:
        “Production”
        ‘tmp/software.csv’

        Regards

        1. jgarcia says:

          Hi mihailmarinov!

          Thank you very much. I’ve managed to do it without problems.

          I have only one question. I would like to draw from OCS Inventory the computers IP address.. How could it? I cant find the right tables for the query.

          Thanks in advance. Greetings.

          1. mihailmarinov says:

            Hi Garcia,

            couldn’t the column ‘IPADDR’ of the ‘hardware’ table be helpful for you?

            Regards

  22. Sebastian says:

    Hello,

    thank you for this very helpful approach. Unfortunately there is no otrs.ImportExport.pl in OTRS ITSM 5 for automation anymore. Did you already implemented an automation for the latest OTRS Version?

  23. mihail marinov says:

    Hi Sebastian,

    use the OTRS5 Console, please.

    as otrs user
    /opt/otrs/bin/otrs.Console.pl Admin::ITSM::ImportExport::Import –template-number TEMPLATENUMBER FILENAME_OCS
    /opt/otrs/bin/otrs.Console.pl Admin::ITSM::ImportExport::Export –template-number TEMPLATENUMBER FILENAME_OTRS

    as root
    su -c “/opt/otrs/bin/otrs.Console.pl Admin::ITSM::ImportExport::Import –template-number TEMPLATENUMBER FILENAME_OCS” -s /bin/bash otrs
    su -c “/opt/otrs/bin/otrs.Console.pl Admin::ITSM::ImportExport::Export –template-number TEMPLATENUMBER FILENAME_OTRS” -s /bin/bash otrs

    Regards

    1. Sebastian says:

      Thank you for your fast response. Works like a charm.

    2. jgarcia says:

      Hi again mihail marinov!

      With your comments and help I managed to make the whole procedure like a charm.

      I just have a question:

      When I import the OCS csv in OTRS all computers are duplicated.

      What I want is that only change the NEW information (if there are changes) from each computer and are not affected Ticket-Computer links.

      I do not know if I explain, sorry for my english and thanks again!

      1. mihailmarinov says:

        Hi Garcia,

        When you created the import template in Import/Export Management, had you specified the identifier?
        See http://www.neteye-blog.com/wp-content/uploads/2014/05/pic1.jpg : ID -> the box IDENTIFIER is checked.

        Regards

  24. dw says:

    Hi,

    nice tutorial and import is working but if i run perl script following message is displayed:

    $Kernel::OM is not defined, please initialize your object manager at /opt/otrs-5.0.8/Kernel/System/Log.pm line 67
    Kernel::System::Log::new(‘Kernel::System::Log’, ‘LogPrefix’, ‘OTRS-OCS’, ‘ConfigObject’, ‘Kernel::Config=HASH(0x79a5f8)’) called at ./LinkCIs.pl line 49

    Has anything changed in syntax?

    1. mihailmarinov says:

      Hi dw,

      this script works with OTRS 3.3.x
      It should be ported to OTRS 5. Just use the Object Manager

      Regards

    2. mihailmarinov says:

      Hi dw,

      you can find the script ported to OTRS5 here:
      http://www.neteye-blog.com/wp-content/uploads/2016/09/otrs.LinkCIs_OTRS5.zip

      This is just a sample. Feel free to create your own console command.

      Regards

  25. Emerson Ramos says:

    Hi,

    My software export was generate empty. I can export using this script:

    select s.ID as ID, s.NAME as NAME, s.PUBLISHER as VENDOR, s.VERSION as VERSION, s.LANGUAGE as LANGUAGE, “Production” AS DEPLOYMENTSTATE, “Operational” AS INCIDENTSTATE
    from softwares s
    group by s.NAME
    INTO OUTFILE ‘/tmp/software.csv’
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\n’;

    But, when I import on OTRS 5, I received a Internal Server Error and this log:

    [:error] [pid 62546] Malformed UTF-8 character (fatal) at /opt/otrs//Kernel/System/DB/mysql.pm line 113.\n

    Can you help me?

    1. mihailmarinov says:

      Hi,

      it sounds as an encoding error. Check first if the exported file is a correct UTF8 one.

      Regards

Leave a Reply

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

Archive