Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
Exporting from RackTables (e.g. for creating an inventory spreadsheet).
#!/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);
my $fmt_bold = $workbook->add_format();
for (my $i=0; $i<= $#{$sth->{NAME}}; $i++) {
$worksheet->write(0, $i, $sth->{NAME}->[$i], $fmt_bold);
my $row = 1;
while(my @dbrow = $sth->fetchrow_array()) {
$worksheet->write($row, 0, \@dbrow);
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', as 'Row', as 'Rack',
(SELECT CAST(inet_ntoa(IP.ip) AS CHAR)
FROM IPv4Allocation IP
WHERE = IP.object_id AND ( = 'ALOM' OR = 'ILO' OR = 'RSC'))
AS 'OOB Address',
(SELECT group_concat(cast(concat(IF(STRCMP(, ""),,'(no name)'), ': ', inet_ntoa(IP.ip)) AS CHAR) SEPARATOR '\r\n')
FROM IPv4Allocation IP
WHERE = IP.object_id
AND != 'ILO' AND != 'ALOM' AND != 'RSC')
AS Addresses
FROM Object
LEFT JOIN AttributeValue AS SK ON (SK.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 = AND SN.attr_id = 1)
LEFT JOIN RackSpace AS RS ON RS.object_id =
LEFT JOIN Rack AS R ON RS.rack_id =
LEFT JOIN `Row` AS RR ON R.row_id =
LEFT JOIN AttributeValue AS HT ON (HT.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`

This comment has been minimized.

Copy link

@patrickthoreson patrickthoreson commented Nov 4, 2017

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.


This comment has been minimized.

Copy link

@khapota khapota commented Dec 4, 2017

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


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment