NetworkPort View in GLPI

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)

Back to Top
We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.