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`

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

Thanks.