SQL reports

all VLANs with subnets

This page describes a sample to generate a report for all VLANs with subnets.

MS SQL server

SELECT 
  tbltag.strVlanId ID,
  tbltag.strName VLAN,
  tbltag.strDescription Description,
  'Subnet' = 
  CASE
    WHEN tbltaggroup.strName IS NOT NULL 
    THEN CONCAT(tbltaggroup.strName, '/', tbltaggroup.intCIDR)
    ELSE ''
    END
FROM
  tbltag
LEFT JOIN tbltaggroup
ON
  tbltag.intId_pk = tbltaggroup.intVlanId_fk
WHERE
  tbltag.strClassName = 'TBoNisTagVLAN'
ORDER BY ID ASC

SQLite

SELECT 
  tbltag.strVlanId ID,
  tbltag.strName VLAN,
  tbltag.strDescription Description,
  tbltaggroup.strName || '/' || tbltaggroup.intCIDR Subnet
FROM
  tbltag
LEFT JOIN tbltaggroup
ON
  tbltag.intId_pk = tbltaggroup.intVlanId_fk
WHERE
  tbltag.strClassName = 'TBoNisTagVLAN'
ORDER BY ID ASC

SQL-Based Reports

Report: Components