17. 05. 2024
Development, DevOps, NetEye
14. 05. 2014
MarinovMihail
Asset Management, Service Management
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
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.
If you’re interested in getting more information about the integration please feel free to contact me through the blog.
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
Latest posts by MarinovMihail
18. 06. 2019
NetEye, Unified Monitoring
Go pprof – How to Understand Where There is Memory Retention
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?
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/
i want to dynamically automate the synchronization process between OCS to OTRS CMDB so can u tell me plz how to to that ?
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/
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.
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/
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
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/
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})
This is just an example.
Feel free to adapt it to your needs.
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
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/
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!
dear Mihail Marinov,
can you tell me in wich folder of otrs do i past the file that permit automatic link.
Dear Mateus Domingos,
put the script in /opt/otrs/bin/
Regards
Amazing, i’ll try it!!
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.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
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!
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.
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
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
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
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
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.
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
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
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,
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.
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.
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
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.
Hi Garcia,
couldn’t the column ‘IPADDR’ of the ‘hardware’ table be helpful for you?
Regards
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?
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
Thank you for your fast response. Works like a charm.
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!
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
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?
Hi dw,
this script works with OTRS 3.3.x
It should be ported to OTRS 5. Just use the Object Manager
Regards
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
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?
Hi,
it sounds as an encoding error. Check first if the exported file is a correct UTF8 one.
Regards