Beispiele SQL-Reports

Auflistung aller IP-Adressen mit Subnet, zugeodnete Komponente und Elterngerät (MS SQL)

SELECT
 tbltaggroup.strName AS Subnet,
tbltaggroup.intCIDR AS CIDR,
tbltag.strName AS IPAddress,
tbltag.strMacAddress AS MACAddress,
tbltag.strDescription AS IPAddressDesc,
B.strName AS Parent,
A.strName AS Device

FROM
  tbltag
LEFT JOIN tbltag2comp ON tbltag.intId_pk = tbltag2comp.intTagtId_fk
LEFT JOIN tblcomponent A ON tbltag2comp.intComponentId_fk = A.intId_pk
LEFT JOIN tblcomponent B ON A.intParentComponentId_fk = B.intId_pk
LEFT JOIN tbltaggroup ON tbltag.intTagGroupId_fk = tbltaggroup.intId_pk

WHERE tbltag.strClassName like 'TBoNisTagIPAddress'

ORDER BY CAST(PARSENAME(tbltag.strName, 4) AS INT),
         CAST(PARSENAME(tbltag.strName, 3) AS INT),
         CAST(PARSENAME(tbltag.strName, 2) AS INT),
         CAST(PARSENAME(tbltag.strName, 1) AS INT)