Here a view to display the cabling from GLPI.
First we need a view with some infos for networkequipment, computers and Phones ( with Name, Room, and so on ) ....
create or replace view v_NetPortDevices as
(select
a.name as name,
CAST(_latin1'Computer' AS CHAR CHARACTER SET utf8 ) COLLATE utf8_general_ci as type ,
a.id as typeid,
b.name as subtype,
c.name as user,
c.registration_number as edataid,
m.name as groupname,
concat(a.name,'.',d.name) as fqdn ,
e.completename as location,
SUBSTRING_INDEX(SUBSTRING_INDEX(e.completename,' ',1),'-',-1) as site,
e.building as building,
e.room as room,
f.name as model,
a.serial as serial,
a.otherserial as inventorynumber,
h.name as manufactur,
i.name as state
from
glpi_computers a
left join glpi_computertypes as b on a.computertypes_id=b.id
left join glpi_users as c on a.users_id_tech=c.id
left join glpi_locations as e on a.locations_id=e.id
left join glpi_domains as d on a.domains_id=d.id
left join glpi_computermodels f on a.computermodels_id=f.id
left join glpi_manufacturers as h on a.manufacturers_id=h.id
left join glpi_states as i on a.states_id=i.id
left join rackname_to_id as k on k.id=a.id and k.itemtype='ComputerModel'
left join glpi_groups as m on a.groups_id_tech=m.id
where a.is_deleted=0 and a.is_template=0)
union
(select
a.name as name,
CAST(_latin1'NetworkEquipment' AS CHAR CHARACTER SET utf8 ) COLLATE utf8_general_ci as type ,
a.id as typeid,
b.name as subtype,
c.name as user,
c.registration_number as edataid,
m.name as groupname,
concat(a.name,'.',d.name) as fqdn ,
e.completename as location,
SUBSTRING_INDEX(SUBSTRING_INDEX(e.completename,' ',1),'-',-1) as site,
e.building as building,
e.room as room,
f.name as model,
a.serial as serial,
a.otherserial as inventorynumber,
h.name as manufactur,
i.name as state
from
glpi_networkequipments a
left join glpi_networkequipmenttypes as b on a.networkequipmenttypes_id=b.id
left join glpi_users as c on a.users_id_tech=c.id
left join glpi_domains as d on a.domains_id=d.id
left join glpi_locations as e on a.locations_id=e.id
left join glpi_networkequipmentmodels f on a.networkequipmentmodels_id=f.id
left join glpi_manufacturers as h on a.manufacturers_id=h.id
left join glpi_states as i on a.states_id=i.id
left join glpi_groups as m on a.groups_id_tech=m.id
where a.is_deleted=0 and a.is_template=0)
union
(select
a.name as name,
CAST(_latin1'Phone' AS CHAR CHARACTER SET utf8 ) COLLATE utf8_general_ci as type ,
a.id as typeid,
b.name as subtype,
c.name as user,
c.registration_number as edataid,
m.name as groupname,
a.name as fqdn,
e.completename as location,
SUBSTRING_INDEX(SUBSTRING_INDEX(e.completename,' ',1),'-',-1) as site,
e.building as building,
e.room as room,
f.name as model,
a.serial as serial,
a.otherserial as inventorynumber,
h.name as manufactur,
i.name as state
from
glpi_phones a
left join glpi_phonetypes as b on a.phonetypes_id=b.id
left join glpi_users as c on a.users_id_tech=c.id
left join glpi_locations as e on a.locations_id=e.id
left join glpi_phonemodels f on a.phonemodels_id=f.id
left join glpi_manufacturers as h on a.manufacturers_id=h.id
left join glpi_states as i on a.states_id=i.id
left join glpi_groups as m on a.groups_id_tech=m.id
where a.is_deleted=0 and a.is_template=0) ;
... and now we create a view with all cabling Infos ...
create or replace view v_CablingNetPorts as
SELECT
`glpi_locations`.`name` AS LocationName,
`glpi_locations`.`ID` AS LocationID,
`glpi_netpoints`.`name` AS NetworkPort,
LeftPort.`itemtype` AS LeftItemType,
LeftPort.`items_id` AS LeftItemsID,
LeftPort.`id` AS LeftID,
LeftDeviceName.`name` as LeftDeviceName,
LeftDeviceName.`room` as LeftDeviceRoom,
LeftPort.`name` AS LeftNamePort,
LeftPort.`mac` AS LeftMAC,
GROUP_CONCAT(ADDRLeft.`name` SEPARATOR ',') AS LeftIP,
RightPort.`itemtype` AS RightItemType,
RightPort.`items_id` AS RightItemsID,
RightPort.`id` AS RightID,
RightDeviceName.`name` as RightDeviceName,
RightDeviceName.`room` as RightDeviceRoom,
RightPort.`name` AS RightNamePort,
RightPort.`mac` AS RightMAC,
GROUP_CONCAT(ADDRRight.`name` SEPARATOR ',') AS RightIP
FROM `glpi_locations`
INNER JOIN `glpi_netpoints` ON
(`glpi_netpoints`.`locations_id` = `glpi_locations`.`id`)
LEFT JOIN `glpi_networkportethernets` ON
(`glpi_networkportethernets`.`netpoints_id` = `glpi_netpoints`.`id`)
LEFT JOIN `glpi_networkports` AS LeftPort ON
(LeftPort.`id` = `glpi_networkportethernets`.`networkports_id` AND
LeftPort.`is_deleted` = 0)
LEFT JOIN `glpi_networknames` AS NAMELeft ON
(NAMELeft.`itemtype` = 'NetworkPort' AND
LeftPort.`id` = NAMELeft.`items_id` AND
NAMELeft.`is_deleted` = 0)
LEFT JOIN `glpi_ipaddresses` AS ADDRLeft ON
(ADDRLeft.`itemtype` = 'NetworkName' AND
NAMELeft.`id` = ADDRLeft.`items_id` AND
ADDRLeft.`is_deleted` = 0)
LEFT JOIN `v_NetPortDevices` AS LeftDeviceName ON
( LeftDeviceName.`type` = LeftPort.`itemtype` AND
LeftDeviceName.`typeid` = LeftPort.`items_id` )
LEFT JOIN `glpi_networkports_networkports` AS LINK ON
(LINK.`networkports_id_1` = LeftPort.`id` OR
LINK.`networkports_id_2` = LeftPort.`id`)
LEFT JOIN `glpi_networkports` AS RightPort ON
(RightPort.`id`=IF(LINK.`networkports_id_1`=LeftPort.`id`, LINK.`networkports_id_2`, LINK.`networkports_id_1`))
LEFT JOIN `glpi_networknames` AS NAMERight ON
(NAMERight.`itemtype` = 'NetworkPort' AND
RightPort.`id` = NAMERight.`items_id` AND
NAMERight.`is_deleted` = 0)
LEFT JOIN `glpi_ipaddresses` AS ADDRRight ON
(ADDRRight.`itemtype` = 'NetworkName' AND
NAMERight.`id` = ADDRRight.`items_id` AND
ADDRRight.`is_deleted` = 0)
LEFT JOIN `v_NetPortDevices` AS RightDeviceName ON
( RightDeviceName.`type` = RightPort.`itemtype` AND
RightDeviceName.`typeid` = RightPort.`items_id` )
GROUP BY LeftPort.`id`
ORDER BY `glpi_locations`.`name`, LeftPort.`name`;
mysql> desc v_NetPortDevices ;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| itemid | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| type | varchar(16) | NO | | | |
| typeid | int(11) | NO | | 0 | |
| subtype | varchar(255) | YES | | NULL | |
| user | varchar(255) | YES | | NULL | |
| edataid | varchar(255) | YES | | NULL | |
| groupname | varchar(255) | YES | | NULL | |
| fqdn | varchar(511) | YES | | NULL | |
| location | text | YES | | NULL | |
| site | text | YES | | NULL | |
| building | varchar(255) | YES | | NULL | |
| room | varchar(255) | YES | | NULL | |
| model | varchar(255) | YES | | NULL | |
| os | varchar(255) | YES | | NULL | |
| serial | varchar(255) | YES | | NULL | |
| inventorynumber | varchar(255) | YES | | NULL | |
| manufactur | varchar(255) | YES | | NULL | |
| state | varchar(255) | YES | | NULL | |
| rack | varchar(255) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
20 rows in set (0.00 sec)
mysql> desc v_CablingNetPorts;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| LocationName | varchar(255) | YES | | NULL | |
| LocationID | int(11) | NO | | 0 | |
| NetworkPort | varchar(255) | YES | | NULL | |
| LeftItemType | varchar(100) | NO | | NULL | |
| LeftItemsID | int(11) | NO | | 0 | |
| LeftID | int(11) | NO | | 0 | |
| LeftDeviceName | varchar(255) | YES | | NULL | |
| LeftDeviceRoom | varchar(255) | YES | | NULL | |
| LeftNamePort | varchar(255) | YES | | NULL | |
| LeftMAC | varchar(255) | YES | | NULL | |
| LeftIP | varchar(341) | YES | | NULL | |
| RightItemType | varchar(100) | YES | | NULL | |
| RightItemsID | int(11) | YES | | 0 | |
| RightID | int(11) | YES | | 0 | |
| RightDeviceName | varchar(255) | YES | | NULL | |
| RightDeviceRoom | varchar(255) | YES | | NULL | |
| RightNamePort | varchar(255) | YES | | NULL | |
| RightMAC | varchar(255) | YES | | NULL | |
| RightIP | varchar(341) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
19 rows in set (0.00 sec)