[racktables-users] What is on the other end of the cable and other SQL queries
- From: Wawrzek Niewodniczanski <wawrzek.niewodniczanski@xxxxxxxxxx>
- To: "racktables-users@xxxxxxxxxxxxx" <racktables-users@xxxxxxxxxxxxx>
- Date: Fri, 18 Nov 2011 11:14:20 +0000
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
--
Wawrzyniec (Wawrzek) Niewodniczański - 01223 435603 (35603) - wawrzekn
System Administrator - Engineering Services Team (XenServer)
Citrix Systems, Building 101, Cambridge Science Park, CB4 0FY, Cambridge
PhD in Quantum Chemistry, MSc in Molecular Engineering
Other related posts:
