report.conf

Network Administration Visualized 网络管理可视化源码

#

swport {
$sql="
 SELECT b1.sysname, s1.ifindex, s1.interface, m1.module, s1.port, s1.link, s1.speed, s1.duplex, 
   s1.media, CASE WHEN s1.trunk THEN 'Y' ELSE '' END as trunk, 
   CASE WHEN s1.trunk THEN 'see list' ELSE '' END AS allowvlan, 
   CASE WHEN vlan.vlan IS NOT NULL THEN vlan.vlan ELSE s1.vlan END AS vlan,
   CASE WHEN vlan.vlan IS NOT NULL THEN 'Y' ELSE '' END AS derived,
   s1.portname, b2.sysname AS to_netboxid,
   s2.interface AS remote_interface, s1.swportid,
   b1.netboxid AS netboxid, b1.catid FROM swport AS s1
  JOIN module AS m1 USING (moduleid)
  JOIN netbox AS b1 USING (netboxid)
  LEFT JOIN netbox AS b2 ON (to_netboxid=b2.netboxid)
  LEFT JOIN swportvlan ON (s1.swportid=swportvlan.swportid AND (trunk=false OR trunk IS NULL))
  LEFT JOIN vlan using(vlanid)
  LEFT JOIN swport AS s2 ON (s1.to_swportid=s2.swportid)
  LEFT JOIN module AS m2 ON (s2.moduleid=m2.moduleid)";
$title="Switch ports";
$hide = "swportid,netboxid,catid";
$name_sysname = "Switch";
$name_to_netboxid = "Connected to";
$name_remote_interface="Remote if";
$name_speed = "Mbps";
$name_portname = "Description";
$order_by="b1.sysname,m1.module,s1.port,s1.ifindex";
$url_sysname="netbox?sysname=$sysname";
$url_module="modules?sysname=$sysname&module=$module";
$url_port="/machinetracker/swp?switch=$sysname&module=$module&port=$port";
$url_netboxid="netbox?netboxid=$netboxid";
$url_trunk="swporttrunk?swportid=$swportid";
$url_allowvlan="allowedvlan?allowedvlan_both.swportid=$swportid";
$url_vlan="prefix?vlan=$vlan";
$url_to_netboxid="netbox?sysname=$to_netboxid";
$url_remote_interface="swport?b1.sysname=$to_netboxid&s1.interface=$remote_interface";
$explain_to_netboxid="Netbox connected";
$explain_remote_interface="Interface on connected netbox";
$explain_derived="VLAN derived by topology";
$explain_Description="ifAlias or similar description";
}

allowedvlan {
$sql="
  SELECT allowedvlan_both.swportid, sysname, interface, module, port, allowedvlan FROM allowedvlan_both
  JOIN swport ON(swport.swportid=allowedvlan_both.swportid2) JOIN
  module USING(moduleid) JOIN netbox USING(netboxid)";
$hide = "swportid";
$title="Allowed VLANs on switchport";
$order_by="netboxid,allowedvlan";
}

swporttrunk {
$sql="
 SELECT b1.sysname, s1.ifindex, s1.interface, m1.module, s1.port, 
   s1.link, s1.speed, s1.duplex, 
   s1.media, CASE WHEN s1.trunk THEN 'Y' ELSE '' END as trunk, 
   vlan.vlan, CASE direction WHEN 'o' THEN 'Up' WHEN 'n' THEN 'Down' ELSE 'N/A' END as direction,
   s1.portname, b2.sysname AS to_netboxid,
   s2.interface AS remote_interface, s1.swportid,
   b1.netboxid AS netboxid, b1.catid 
  FROM swport AS s1
  JOIN module AS m1 USING (moduleid)
  JOIN netbox AS b1 USING (netboxid)
  LEFT JOIN netbox AS b2 ON (to_netboxid=b2.netboxid)
  LEFT JOIN swportvlan ON (s1.swportid=swportvlan.swportid)
  LEFT JOIN vlan using(vlanid)
  LEFT JOIN swport AS s2 ON (s1.to_swportid=s2.swportid)
";
$title="Switch ports with trunk ports expanded";
$hide = "swportid,netboxid,catid";
$name_sysname = "Switch";
$name_to_netboxid = "Connected to";
$name_remote_interface="Remote if";
$name_speed = "Mbps";
$name_portname = "Description";
$order_by="b1.sysname,ifindex";
$url_sysname="netbox?sysname=$sysname";
$url_module="modules?sysname=$sysname&module=$module";
$url_port="/machinetracker/swp?switch=$sysname&module=$module&port=$port";
$url_netboxid="netbox?netboxid=$netboxid";
$url_trunk="swporttrunk?swportid=$swportid";
$url_vlan="prefix?vlan=$vlan";
$url_to_netboxid="netbox?sysname=$to_netboxid";
$url_remote_interface="swport?b1.sysname=$to_netboxid&s1.interface=$remote_interface";
$explain_to_netboxid="Netbox connected";
$explain_remote_interface="Interface on connected netbox";
$explain_Description="ifAlias or similar description";
}

topowalk {
$sql="
  SELECT b1.sysname,m1.module AS module1,s1.port AS port1,b2.sysname AS tonetboxid,
    m2.module AS module2,s2.port AS port2 
  FROM swport AS s1 
  INNER JOIN module AS m1 USING (moduleid) 
  JOIN netbox AS b1 USING (netboxid) 
  JOIN netbox AS b2 ON (to_netboxid=b2.netboxid) 
  LEFT JOIN swport AS s2 ON (s1.to_swportid=s2.swportid) 
  LEFT JOIN module as m2 on s2.moduleid=m2.moduleid";
$extra="swbak,gwbak";
$url_gwbak="topowalkgw?b1.sysname=$tonetboxid";
$url_swbak="topowalk?b1.sysname=$tonetboxid";
}

topowalkgw{
$sql="
 SELECT b1.sysname,interface,b2.sysname AS to_netboxid,module,s2.port 
 FROM gwport 
 JOIN module USING (moduleid) 
 JOIN netbox AS b1 USING (netboxid) 
 JOIN netbox AS b2 ON (to_netboxid=b2.netboxid) 
 LEFT JOIN swport AS s2 ON (gwport.to_swportid=s2.swportid)";
$title = "Router topology";
$order_by="b1.sysname,interface";
$extra="swbak,gwbak";
$url_sysname="netbox?sysname=$sysname";
$url_to_netboxid="netbox?sysname=$to_netboxid";
$url_gwbak="topowalkgw?b1.sysname=$to_netboxid";
$url_swbak="topowalk?b1.sysname=$to_netboxid";
}



# ---------------------------------------------------------------------
# room,sted,type
#

room {
$sql="
  SELECT roomid,count(netboxid) AS nb,locationid,descr,opt1,opt2,opt3,opt4 
  FROM room 
  LEFT JOIN netbox USING (roomid)
  GROUP BY roomid,locationid,descr,opt1,opt2,opt3,opt4";
$title = "Room";
$name_roomid="Room";
$name_nb="IP Devices";
$name_locationid="Location";
$name_descr="Description";
$explain_devices="Number of registered IP devices in this room";
$order_by="roomid";
$url_nb="netbox?roomid=$roomid";
$url_locationid="location?locationid=$locationid";
}

location {
$sql="
  SELECT locationid,count(roomid) AS rooms,location.descr
  FROM location 
  LEFT JOIN room USING (locationid) 
  GROUP BY locationid,location.descr";
$title = "Locations";
$order_by="locationid";
$name_locationid="Location";
$name_descr="Description";
$url_rooms="room?locationid=$locationid";
}

type {
$sql="
  SELECT vendorid,typename,count(distinct netboxid) AS ipcount,
    count(moduleid) AS modcount,typeid,type.descr,sysobjectid 
  FROM type 
  LEFT JOIN netbox USING (typeid) 
  LEFT JOIN module USING (netboxid) 
  GROUP by type.typename,vendorid,typeid,type.descr,sysobjectid HAVING count(netbox)>0";
$title = "Types of equipment";
$name_typename="Type";
$name_vendorid="Vendor";
$name_descr="Description";
$order_by="vendorid,typename";
$hide = "typeid";
$url_vendorid="vendor?vendorid=$vendorid";
$url_ipcount="netbox?typeid=$typeid";     
$url_modcount="modules?typeid=$typeid";
$explain_ipcount="Number of IP addresses related to this type";
$explain_modcount="Number of modules related to this type";
}


vendor {
$sql = "
  SELECT vendorid,count(distinct netboxid) AS ipcount,count(moduleid) AS modcount
  FROM module 
  RIGHT JOIN netbox USING (netboxid) 
  JOIN type USING (typeid) 
  RIGHT JOIN vendor USING (vendorid) 
  GROUP BY vendorid 
  ORDER by vendorid";
$title = "Vendors";
$name_vendorid="Vendor";
$explain_ipcount="Number of IP addresses related to this vendor";
$explain_modcount="Number of modules related to this vendor";
$url_vendorid="type?vendorid=$vendorid";
$url_ipcount="netbox?type.vendorid=$vendorid";
}

product {
$sql = "
  SELECT vendorid,productno,count(deviceid) AS devs,descr 
  FROM product 
  RIGHT JOIN device USING (productid) 
  GROUP BY vendorid,productno,descr";
$title = "Products";
$name_vendorid="Vendor";
$name_productno="Product";
$name_devs="Devices";
$explain_devs="Number of devices registered";
$url_vendorid="type?vendorid=$vendorid";
$url_productno="device?productno=$productno";
}

# ---------------------------------------------------------------------
# usage,org
#
usage {
$sql="
  SELECT usageid,count(prefixid) AS netcount,descr 
  FROM usage 
  LEFT JOIN vlan USING (usageid) 
  LEFT JOIN prefix USING (vlanid) 
  GROUP BY usageid,descr";    
$title = "Usage";
$name_netcount = "Number of networks";
$name_usageid="Usage"; 
$name_descr="Description";
$order_by="usageid";
$url_netcount="prefix?usageid=$usageid";
}

org {
$sql = "
  SELECT orgid,count(prefixid) AS netcount,parent,descr,opt1,opt2,opt3
  FROM org 
  LEFT JOIN vlan USING (orgid) 
  LEFT JOIN prefix USING (vlanid) 
  GROUP BY orgid,parent,descr,opt1,opt2,opt3";
$title = "Organisation";
$name_descr="Description";
$name_parent="Parent";
$name_orgid="Organisational unit";
$order_by="orgid";
$url_parent="org?parent=$parent";
$url_netcount="prefix?orgid=$orgid";
}

# ---------------------------------------------------------------------
# other reports
#

netboxsnmpoid{
$sql="
  SELECT sysname,oidkey,snmpoid,mib,oidname,descr AS Description,netboxid,frequency 
  FROM netbox 
  JOIN netboxsnmpoid USING (netboxid) 
  JOIN snmpoid USING (snmpoidid)";
$title="IP Device SNMP OID support";
$hide="netboxid";
$order_by="sysname,oidkey,snmpoid";
$url_sysname="netbox?sysname=$sysname";
$url_oidkey="netboxsnmpoid?oidkey=$oidkey";
$name_frequency="interval";
$explain_interval="Interval between collection, in seconds";
}

#############################

# spanning tree blocked
stpblock {
$sql = "
  SELECT swportblocked.vlan,sysname,module,port,link,portname 
  FROM swportblocked 
  JOIN swport USING (swportid) 
  JOIN module USING (moduleid) 
  JOIN netbox USING (netboxid) 
  WHERE link='y'";
$order_by="vlan,sysname,module,port";
$title = "STP blocked ports";
}

# So far, this thing is just for debugging.  The report generator cannot
# handle searching in a report when the query contains an SQL UNION.
interfaces {
$sql = "
   SELECT n.sysname, p.ifindex, p.interface, p.speed, 'swport' as kind, p.moduleid
   FROM swport AS p
   JOIN module AS m USING (moduleid)
   JOIN netbox AS n USING (netboxid)
  UNION
   SELECT n.sysname, p.ifindex, p.interface, p.speed, 'gwport' as kind, p.moduleid
   FROM gwport AS p
   JOIN module AS m USING (moduleid)
   JOIN netbox AS n USING (netboxid)
   ";
$title = "Interfaces";
$hide = "moduleid";
$order_by = "sysname,ifindex";
}


unrecognizedCDP {
$sql = "
SELECT
  sysname,
  var AS ifindex,
  interface,
  portname AS Description,
  val AS name
FROM netboxinfo ni
JOIN netbox USING (netboxid)
LEFT JOIN (SELECT netboxid, ifindex, interface, portname
           FROM module
           JOIN swport USING (moduleid)
           UNION
           SELECT netboxid, ifindex, interface, portname
           FROM module
           JOIN gwport USING (moduleid)) ports ON (ni.netboxid=ports.netboxid AND ni.var=ports.ifindex)
WHERE ni.key='unrecognizedCDP'
";
$order_by="sysname, ifindex";
$title="Unrecognized CDP entries";
$url_sysname="netbox?sysname=$sysname";
$url_name="/ipinfo?ip=$name";
$name_sysname="Reported by (sysname)"
$name_name="Unrecognized name";
}
#
# EOF
#