#!/usr/bin/perl

use DBI;

$db="racktables";
$host="localhost";
$user="racktables";
$passwd="racktables";


$connectinfo="dbi:mysql:$db;$host";
$filename="racktables-rancid-devices.txt";
$dbh = DBI->connect($connectinfo,$user,$passwd);

$query = "select inet_ntoa(IPv4Allocation.ip), RackObject.name from RackObject JOIN AttributeValue JOIN IPv4Allocation ON RackObject.id=AttributeValue.object_id AND AttributeValue.object_id=IPv4Allocation.object_id WHERE AttributeValue.attr_id=10003 AND AttributeValue.uint_value=50030 group by RackObject.name";
open FILE, ">", "$filename" or die $!;

$sth=$dbh->prepare($query);
$sth->execute();
$sth->bind_columns(\$IP, \$Name);
while($sth->fetch()) {
	if ($IP)
		{
		$query2="select Dictionary.dict_value from Dictionary JOIN AttributeValue JOIN RackObject JOIN IPv4Allocation ON Dictionary.dict_key = AttributeValue.uint_value AND AttributeValue.object_id=RackObject.id AND AttributeValue.object_id=IPv4Allocation.object_id WHERE AttributeValue.attr_id='2' AND RackObject.name='$Name'";
		$sth2=$dbh->prepare($query2);
		$sth2->execute();
		$Device = $sth2->fetchrow_array();
		if ($Device =~ m/^\[\[HP/)
			{
					
			print FILE "# $Name - $IP \n$Name:hp:up\n";
			}
		elsif ($Device =~ m/^\[\[Juniper/)
			{
			print FILE "# $Name - $IP \n$Name:juniper:up\n";
			}
		else
			{
			print FILE "# $Name - $IP \n$Name:cisco:up\n";
			}
		$sth2->finish();
		}
}

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