[racktables-users] SQL to trace from server to switch.
- From: magnus michaelson <magnusmichaelson@xxxxxxxxx>
- To: racktables-users <racktables-users@xxxxxxxxxxxxx>
- Date: Thu, 20 Mar 2014 02:08:57 +0000
Hello,
In the datacenter where I am working we needed to see which switch and
interface each server connected too. I have written an SQL query to do this.
We use an arrangement where the server cable and the switch cable have the
same name. This, combined with various INNER JOINs was enough to get some
results, but not all.
The difficulty was that in the Link table there was no way of knowing
whether the server was on porta or portb, the same applied to the switch.
These two possible orientations on the two cables means there are four
different configurations this query would need to test.
I did this by adding only INNER JOINing the switch and interface, then
moving the rest of the trace to four subqueries in the WHERE section that
checked each of the four possible configurations. If one of them does
result in the server id then you have the right switch and interface.
Here is a link to the formatted SQL-
http://www.magnusmichaelson.com/racktables/racktables_server_switch_trace.sql.txt
I will also paste it below for safe keeping.
Kind regards,
Magnus Michaelson.
SELECT
a.name AS switch,
b.name AS interface
FROM
Object AS a
INNER JOIN
Port AS b
ON a.id = b.object_id
WHERE
a.objtype_id=8
AND
(
# switch port on porta
# server port on porta
b.id=
(
SELECT
b.id
FROM
Port AS b
INNER JOIN
Link AS c ON b.id = c.porta
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.porta = e.id
INNER JOIN
Object AS f ON e.object_id = f.id
WHERE
f.id= 3702
AND
c.porta != d.porta
)
OR
# switch on portb
# server on porta
b.id =
(
SELECT
b.id
FROM
Port AS b
INNER JOIN
Link AS c ON b.id = c.portb
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.porta = e.id
INNER JOIN
Object AS f ON e.object_id = f.id
WHERE
f.id= 3702
AND
c.porta != d.porta
)
OR
# switch on porta
# server on portb
b.id =
(
SELECT
b.id
FROM
Port AS b
INNER JOIN
Link AS c ON b.id = c.porta
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.portb = e.id
INNER JOIN
Object AS f ON e.object_id = f.id
WHERE
f.id= 3702
AND
c.porta != d.porta
)
OR
# switch on portb
# server on portb
b.id =
(
SELECT
b.id
FROM
Port AS b
INNER JOIN
Link AS c ON b.id = c.portb
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.portb = e.id
INNER JOIN
Object AS f ON e.object_id = f.id
WHERE
f.id= 3702
AND
c.porta != d.porta
)
);
# KEY
# a - switch
# b - switch port
# c - switch cable
# d - server cable
# e - server port
# f - server
# switch cable and server cable must have the same name
Other related posts:
