LibreNMS DB View for NocoDB

CREATE OR REPLACE VIEW view_ports AS
SELECT
    d.device_id,
    INET6_NTOA(d.ip) AS ip,
    d.sysName,
    d.display,
    p.port_id,
    p.ifIndex,
    p.ifName,
    p.ifAlias,
    p.ifDescr,
    p.ifAdminStatus,
    p.ifConnectorPresent,
    p.ifOperStatus,
    p.ifMtu
FROM
    devices d
JOIN
    ports p ON d.device_id = p.device_id;

CREATE OR REPLACE VIEW view_devices AS
SELECT
    d.device_id,
    INET6_NTOA(d.ip) AS ip,
    d.display,
    d.sysName,
    l.location as sysLocation,
    d.sysContact,
    REPLACE(REPLACE(d.icon, '.svg', ''), '.png', '') as vendor,
    d.hardware,
    d.sysDescr,
    d.os,
    d.version,
    d.bgpLocalAs,
    d.serial
FROM
    devices d
LEFT JOIN
    locations l ON d.location_id = l.id;