It is completely wrong to get all the information you need by means of SQL.All the necessary queries and functions already persist in RackTables and you can make them work for you. Note the wiki for sample:
http://sourceforge.net/apps/mediawiki/racktables/index.php?title=RackTablesDevelGuide#Adding_a_custom_report Also, if you want a stand-alone report, you could use this template: <?php require '/path/to/your/installation/wwwroot/inc.init.php'; $port_id = 12; $portinfo = getPortInfo ($port_id); if ($portinfo['linked'])echo "Remote side of port#$port_id is $portinfo['remote_object_name'] $portinfo['remote_name']}\n";
else echo "Port port#$port_id is not linked\n"; ?> Then call this script with cli php interpreter. 18.11.2011 15:14, Wawrzek Niewodniczanski пишет:
Hi, We've been adding more and more information to our Racktables, and now we (and our 'internal customers' too) expect to get some nicely formatted output from it. E.g. recently I'm working on program helping with OS installation over PXE Boot and use some information from Racktables: hostname, all MAC addresses, PDU machine is connect to. In the future we would like to have even more info (CPU type, Memory, HDD) so for the test I added also CPU type. I would like also ask how (and if) you do hardware reporting. My query (you can see it below) is already quite long . Next step is to add information about what is on the another end of serial cable (Would I need another union?), next more info about Hardware information I already mentioned. Please find my query. BTW I'm happy to add to this us well some more queries, but I think I need RW access to it :) SELECT * FROM ( (SELECT ro.name AS 'Name', p.name AS 'NIC', p.l2address AS 'MAC Address', \ ro1.name AS 'PDU', p11.name AS 'port', \ d.dict_value AS 'CPU Model' \ FROM RackObject AS ro \ /* NIC */ JOIN Port AS p ON ro.id=p.object_id \ /* CPU */ JOIN AttributeValue AS av ON av.object_id=ro.id \ JOIN Attribute AS a ON a.id=av.attr_id \ JOIN AttributeMap AS am ON a.id=am.attr_id AND am.objtype_id=ro.objtype_id \ JOIN Dictionary AS d ON d.chapter_id=am.chapter_id AND d.dict_key=av.uint_v\ /* PDU */ JOIN Port AS p1 ON ro.id=p1.object_id \ JOIN Link AS l1 ON p1.id=l1.porta \ JOIN Port AS p11 ON l1.portb=p11.id \ JOIN RackObject AS ro1 ON p11.object_id=ro1.id \ AND ro.name REGEXP ".t[[:digit:]]{2}" \ AND p.l2address!='NULL' \ AND a.name='CPU Model' \ AND p1.type=16 ) \ UNION \ (SELECT ro.name AS 'Name', p.name AS 'NIC', p.l2address AS 'MAC Address', \ ro1.name AS 'PDU', p11.name AS 'port', \ d.dict_value AS 'CPU Model' \ FROM RackObject AS ro \ /* NIC */ JOIN Port AS p ON ro.id=p.object_id \ /* CPU */ JOIN AttributeValue AS av ON av.object_id=ro.id \ JOIN Attribute AS a ON a.id=av.attr_id \ JOIN AttributeMap AS am ON a.id=am.attr_id AND am.objtype_id=ro.objtype_id \ JOIN Dictionary AS d ON d.chapter_id=am.chapter_id AND d.dict_key=av.uint_v\ /* PDU */ JOIN Port AS p1 ON ro.id=p1.object_id \ JOIN Link AS l1 ON p1.id=l1.portb \ JOIN Port AS p11 ON l1.porta=p11.id \ JOIN RackObject AS ro1 ON p11.object_id=ro1.id \ AND ro.name REGEXP ".t[[:digit:]]{2}" \ AND p.l2address!='NULL' \ AND a.name='CPU Model' \ AND p1.type=16 \ ) ORDER BY name, NIC )pdu_union\ INTO OUTFILE 'machines.csv' FIELDS TERMINATED BY ','; And this is initial version, failing because of memory usage: |SELECT ro.name AS ||'Name'||, p.name AS ||'NIC'||, p.l2address AS ||'MAC Address'||, \| |||ro2.name AS ||'PDU'||, p2.name AS ||'port'||,\| |||ro3.name AS ||'Serial'||, s2.name AS ||'port'||,\| |||d.dict_value AS ||'CPU Model'||\| |||FROM RackObject AS ro \| |||JOIN Port AS p ON ro.id=p.object_id \| |||JOIN AttributeValue AS av ON av.object_id=ro.id \| |||JOIN Attribute AS a ON a.id=av.attr_id \| |||JOIN AttributeMap AS am ON a.id=am.attr_id AND am.objtype_id=ro.objtype_id \| |||JOIN Dictionary AS d ON d.chapter_id=am.chapter_id AND d.dict_key=av.uint_value \| |||JOIN Port AS p1 ON ro.id=p1.object_id \| |||JOIN Link AS l1 ON (p1.id=l1.porta OR p1.id=l1.portb) \| |||JOIN Port AS p2 ON (p2.id=l1.porta OR p2.id=l1.portb) \| |||JOIN RackObject AS ro2 ON p2.object_id=ro2.id \| |||LEFT JOIN Port AS s1 ON ro.id=s1.object_id \| |||JOIN Link AS l2 ON (s1.id=l2.porta OR s1.id=l2.portb) \| |||JOIN Port AS s2 ON (s2.id=l2.porta OR s2.id=l2.portb) \| |||JOIN RackObject AS ro3 ON s2.object_id=ro3.id \| |||WHERE (ro.name LIKE ||'st__'| |OR ro.name LIKE ||'dt__'||) \| |||/*AND p.name='eth0' \*/| |||AND p.l2address!=||'NULL'| |\| |||AND a.name=||'CPU Model'| |\| |||AND p1.type=||16| |\| |||AND p1.id!=p2.id \| |||AND (s1.type=||29| |OR s1.type=||681||)\| |||AND s1.id!=s2.id \| |||ORDER BY ro.name, p.name;| Thanks, Wawrzek
-- Best regards, Alexey