Skip to content

Instantly share code, notes, and snippets.

@jamesog
Last active September 2, 2019 22:45
Show Gist options
  • Save jamesog/5600224 to your computer and use it in GitHub Desktop.
Save jamesog/5600224 to your computer and use it in GitHub Desktop.
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);
$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();
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`
@patrickthoreson
Copy link

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.

@khapota
Copy link

khapota commented Dec 4, 2017

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

Thanks.

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