[racktables-users] Re: SQL to trace from server to switch.
- From: Olivier Contant <Olivier.Contant@xxxxxxxxxxxxxxxxx>
- To: "racktables-users@xxxxxxxxxxxxx" <racktables-users@xxxxxxxxxxxxx>
- Date: Thu, 20 Mar 2014 14:55:05 +0000
Hi,
I would like to know more about this, because when i configured my server/ipv4
object, I can setup the name of the interface and on which switch port it
connect exactly and the name of the cable ...
I also use the plugins extensions from the contrib that allow me to run a
custom report for which I select object type server, network switches and
select attribute names, ports, and it will list me all my server and where they
connect to *port name, switch name, port switch, cable name ...
Olivier Contant
System Administrator, Triton Digital
1440 Ste-Catherine W., Suite 1200 | Montreal, Qc, H3G 1R8
Tel.: +1.514.448.4037 #2771 | Toll-Free: +1.866.448.4037 #2771
olivier.contant@xxxxxxxxxxxxxxxxx<mailto:olivier.contant@xxxxxxxxxxxxxxxxx>
From: racktables-users-bounce@xxxxxxxxxxxxx
[mailto:racktables-users-bounce@xxxxxxxxxxxxx] On Behalf Of Michal Sochon
Sent: Thursday, March 20, 2014 2:33 AM
To: racktables-users@xxxxxxxxxxxxx
Subject: [racktables-users] Re: SQL to trace from server to switch.
Nice, but why don't you just push it to
https://github.com/RackTables/racktables-contribs
On Thu, Mar 20, 2014 at 3:08 AM, magnus michaelson
<magnusmichaelson@xxxxxxxxx<mailto:magnusmichaelson@xxxxxxxxx>> wrote:
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<http://a.name> AS switch,
b.name<http://b.name> AS interface
FROM
Object AS a
INNER JOIN
Port AS b
ON a.id<http://a.id> = b.object_id
WHERE
a.objtype_id=8
AND
(
# switch port on porta
# server port on porta
b.id<http://b.id>=
(
SELECT
b.id<http://b.id>
FROM
Port AS b
INNER JOIN
Link AS c ON b.id<http://b.id> = c.porta
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.porta = e.id<http://e.id>
INNER JOIN
Object AS f ON e.object_id = f.id<http://f.id>
WHERE
f.id<http://f.id>= 3702
AND
c.porta != d.porta
)
OR
# switch on portb
# server on porta
b.id<http://b.id> =
(
SELECT
b.id<http://b.id>
FROM
Port AS b
INNER JOIN
Link AS c ON b.id<http://b.id> = c.portb
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.porta = e.id<http://e.id>
INNER JOIN
Object AS f ON e.object_id = f.id<http://f.id>
WHERE
f.id<http://f.id>= 3702
AND
c.porta != d.porta
)
OR
# switch on porta
# server on portb
b.id<http://b.id> =
(
SELECT
b.id<http://b.id>
FROM
Port AS b
INNER JOIN
Link AS c ON b.id<http://b.id> = c.porta
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.portb = e.id<http://e.id>
INNER JOIN
Object AS f ON e.object_id = f.id<http://f.id>
WHERE
f.id<http://f.id>= 3702
AND
c.porta != d.porta
)
OR
# switch on portb
# server on portb
b.id<http://b.id> =
(
SELECT
b.id<http://b.id>
FROM
Port AS b
INNER JOIN
Link AS c ON b.id<http://b.id> = c.portb
INNER JOIN
Link AS d ON c.cable = d.cable
INNER JOIN
Port AS e ON d.portb = e.id<http://e.id>
INNER JOIN
Object AS f ON e.object_id = f.id<http://f.id>
WHERE
f.id<http://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
--
Michal Sochoń
alias _KaszpiR_ kaszpir@xxxxxxxxx<mailto:kaszpir@xxxxxxxxx>
IRC: _KaszpiR_ @ freenode.net<http://freenode.net>
Other related posts:
