Exporting from RackTables (e.g. for creating an inventory spreadsheet).

mkinventory.pl

#!/usr/bin/env perl

use warnings;
use strict;
# Use modules installed with cpanm -L
use lib 'extlib/lib/perl5';

use DBI;
use POSIX qw(strftime);
use Spreadsheet::WriteExcel;

my $fname = "inv-".strftime("%Y%m%d",localtime).".xls";
my $workbook = Spreadsheet::WriteExcel->new($fname);
my $worksheet = $workbook->add_worksheet();

my $db = "racktables";
my $dbuser = "blah";
my $dbpass = "blah";

my $dbh = DBI->connect("DBI:mysql:database=$db;host=localhost",$dbuser,$dbpass,{'RaiseError' => 1});

# Read sql file to var
open(my $sqlfile, "<", "rt_export.sql") or die "Can't open sql file: $!\n";
local $/ = undef;
my $sql = <$sqlfile>;
close $sqlfile;

my $sth = $dbh->prepare($sql);
$sth->execute();

my $fmt_bold = $workbook->add_format();
$fmt_bold->set_bold();
for (my $i=0; $i<= $#{$sth->{NAME}}; $i++) {
  $worksheet->write(0, $i, $sth->{NAME}->[$i], $fmt_bold);
}

my $row = 1;

RESULT:
while(my @dbrow = $sth->fetchrow_array()) {
  $worksheet->write($row, 0, \@dbrow);
  $row++;
}

$sth->finish();
$dbh->disconnect();

rt_export.sql

SELECT DISTINCT
Object.name AS 'Device Name',
Object.asset_no as 'Asset No',
Object.label as 'Label',
DT.dict_value AS 'Device Type',
(SELECT REPLACE(OS.dict_value, '%GSKIP%', ' ')) AS 'OS',
SN.string_value as 'Serial No',
RR.location_name as 'Location',
RR.name as 'Row',
R.name as 'Rack',

(SELECT CAST(inet_ntoa(IP.ip) AS CHAR)
 FROM IPv4Allocation IP
 WHERE Object.id = IP.object_id AND (IP.name = 'ALOM' OR IP.name = 'ILO' OR IP.name = 'RSC'))
 AS 'OOB Address',
(SELECT group_concat(cast(concat(IF(STRCMP(IP.name, ""),IP.name,'(no name)'), ': ', inet_ntoa(IP.ip)) AS CHAR) SEPARATOR '\r\n')
 FROM IPv4Allocation IP
 WHERE Object.id = IP.object_id
 AND IP.name != 'ILO' AND IP.name != 'ALOM' AND IP.name != 'RSC')
 AS Addresses

FROM Object

LEFT JOIN AttributeValue AS SK ON (SK.object_id = Object.id AND SK.attr_id = 4)
LEFT JOIN Dictionary AS OS ON OS.dict_key = SK.uint_value
LEFT JOIN AttributeValue AS SN ON (SN.object_id = Object.id AND SN.attr_id = 1)
LEFT JOIN RackSpace AS RS ON RS.object_id = Object.id
LEFT JOIN Rack AS R ON RS.rack_id = R.id
LEFT JOIN `Row` AS RR ON R.row_id = RR.id
LEFT JOIN AttributeValue AS HT ON (HT.object_id = Object.id AND HT.attr_id = 2)
LEFT JOIN Dictionary AS HW ON HW.dict_key = HT.uint_value
LEFT JOIN Dictionary AS DT ON Object.objtype_id = DT.dict_key

-- objtype_id 4 = Server
WHERE Object.objtype_id = 4
ORDER BY `Location`, `Row`, `Rack`, `Device Name`

I'd like to add the 'HW Type' value (tag?) for each peace gear listed in the inventory extraction spreadsheet output; however just can't seem to find where that linkage would need to be added (as an outer LEFT JOIN, but from which table, with which key values?) Thanks in advance for any hint in the right direction.

mysql_enable_utf8 => 1
I need to add above option into mysql connection to use utf-8.

Thanks.