[racktables-users] Re: SQL to trace from server to switch.
- From: Michał Sochoń <kaszpir@xxxxxxxxx>
- To: racktables-users@xxxxxxxxxxxxx
- Date: Thu, 20 Mar 2014 07:32:41 +0100
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> 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 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
>
>
>
--
Michal Sochoń
alias _KaszpiR_ kaszpir@xxxxxxxxx
IRC: _KaszpiR_ @ freenode.net
Other related posts:
