Hi, Ernest.
I learned a couple things when I was exploring asset importing. I was
pulling data out of OpenNMS and into RackTables. This included
hostnames, attributes, multiple interfaces, multiple IP addresses, and
category information. I only put it together for a quick
proof-of-concept and it is nowhere near robust enough for actual use,
but I did have my eye on regularly updating RackTables info from
another
authority.
These are some lessons I learned that may provide a head start. The
Export/Import definitely takes some design planning. The main aspects
are 1) You need a unique asset identifier. 2) Use separate logic for
attributes and Tags. 3) Make some great logging for success or fail
conditions.
Straight CSV might not work very well if you want to import multiple
IP
addresses, multiple tags and text-comment fields. In my case the
comment
field had quotes for URLs, so quoting the fields didn't help. I ended
up using a using | to delineate fields. Also, I made two different
files to import.
File 1: asset number|hostname|description|location|comment
field|IP|interfaceid|interface name|HW address
[Lines could be repeated, and if the asset already existed, then
import
just updated with the additional comment/interface/IP information]
File 2: asset number|tag1|tag2|tag3....
For importing data, I found it helpful to create a php include file to
source from my import scripts. This acts as a wrapper for the more
abstract import/update racktables functions but does not rely on web
interface functions or data. I figure this may be more robust than
going
for a direct database import. [See the code pasted at the end of this
email]
Watch out for functions that are finicky and want NULL or an empty
string '' arguments.
Interesting functions
//commitAddObject ($new_name, $new_label, $new_barcode, $new_type_id,
$new_asset_no, $taglist = array())
//commitUpdateObject ($object_id = 0, $new_name = '', $new_label =
'',
$new_barcode = '', $new_type_id = 0, $new_has_problems = 'no',
$new_asset_no = '', $new_comment = '')
// $object_id and $new_type_id is required
//this updates with the values of ALL provided, even if empty or
NULL!
//commitUpdateAttrValue ($object_id = 0, $attr_id = 0, $value = '')
//commitAddPort ($object_id = 0, $port_name, $port_type_id,
$port_label, $port_l2address)
//bindIpToObject ($details['ip'] , $object_id, $details['ifname'],
'regular');
Also, when using the useSelectBlade($query) function for SQL
queries, I
had to do a silly dance before it would allow another query.
$result = useSelectBlade ($query, __FUNCTION__);
$row = $result->fetch (PDO::FETCH_ASSOC);
//do bugfix. Sometimes we can't re-query unless we have fetched all
results, even blank ones
do $result->fetchAll();
while ($result->nextRowSet());
$result->closeCursor();
unset($result);
That should save some hours of head smashing for others who may take
on
this task.
Good Luck,
Gabriel Friedmann
---------------myCLIinit.php---------------------
<?php
//require 'inc/init.php';
//fixContext();
require_once 'inc/config.php';
require_once 'inc/functions.php';
require_once 'inc/database.php';
require_once 'inc/secret.php';
// Now try to connect...
try
{
$dbxlink = new PDO ($pdo_dsn, $db_username, $db_password);
}
catch (PDOException $e)
{
showError ("Database connection failed:\n\n" . $e-
>getMessage(),
__FILE__);
exit (1);
}
$dbxlink->exec ("set names 'utf8'");
$dbver = getDatabaseVersion();
if ($dbver != CODE_VERSION)
{
echo '<p align=justify>This Racktables installation seems to be
' .
'just upgraded to version ' . CODE_VERSION . ', while
the '.
'database version is ' . $dbver . '. This THING WONT
WORK! ';
exit (1);
}
if (!mb_internal_encoding ('UTF-8') or !mb_regex_encoding ('UTF-8'))
{
showError ('Failed setting multibyte string encoding to UTF-8',
__FILE__);
exit (1);
}
//Need to define own function showError() that is usually defined in
interface.php
// It is called when there is a DB query problem.
function showError ($info = '', $location = 'N/A')
{
echo "An error has occured in [${location}]. ";
if (empty ($info))
echo 'No additional information is available.';
else
echo "Additional information: ${info}\n";
}
$configCache = loadConfigCache();
if (!count ($configCache))
{
print "Failed to load configuration from the database. " .
__FILE__ . "\n";
exit (1);
}
?>
-----Original Message-----
From: racktables-users-bounce@xxxxxxxxxxxxx
[mailto:racktables-users-bounce@xxxxxxxxxxxxx] On Behalf Of Denis
Ovsienko
Sent: Wednesday, April 01, 2009 2:54 AM
To: racktables-users@xxxxxxxxxxxxx
Subject: [racktables-users] Re: Help with Object Import
I am going to migrate 400+ hosts from a home grown inventory database
into
RackTables.
I figured why not ask for advice to make sure I am not re-inventing
the
wheel. Does anyone have an example script, or is there a way to use
the
existing RackTables code to import from a flat file? I can export
the
data
into a flat file with the needed fields. Then I'd like to import as
much
information as possible, meaning static data, optional attributes and
tags,
if at all possible.
Ernest, you seem to be the first one.
There are two relatively idependent works here: to export from one
database and to import to another. First you need to stick with the
format of intermediate file, IOW, with the set and ordering of CSV
file
columns. After that you make a backup of your RackTables database and
start debugging your import script, which is a bit more, than the
example code from php.net:
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$cname = $data[0];
$asset_tag = $data[1];
commitAddObject ($cname, '', '', 4, $asset_tag);
}
To add data about optional attributes in the same cycle, you need to
map
the string retrieved from CSV into key value from the dictionary with
own function. Then, if the mapping was successful, the following
pattern
would store the value:
// get the object_id of the last object added
// $object_id = ...
if (($key = os_to_key ($osname)) != NULL)
// set "SW type"
commitUpdateAttrValue ($object_id, 4, $key);
if (($key = hardware_to_key ($hwname) != NULL)
// set "HW type"
commitUpdateAttrValue ($object_id, 2, $key);
... and so on. You'll definitely have to restore from backup and re-
run
the import script several times until the actual result is what you
intended.
--
