report.conf

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

#
# $Id: report.conf 3968 2007-04-13 11:46:11Z mortenv $
# 
# Configuration file for the report generator
#
# variables starts with '$', and comments with '#'
# values must be within double quates (") and lines must end with a semicolon (;)
# $sql is the sql-query. line break and white space is allowed.
# $order_by defines the default ordering of the sql query. By clicking on the column headers
#   in the report, you can sort/reverse sort on other criteria.
# $title ($overskrift) is the report title as shown on web
# $extra ($ekstra) are extra columns that can be used for hyperlinks (see $url)
# $hide ($skjul) are columns that must be included in the $sql because they are used in
#   a $url, see below. $hide will not show the column in the report.
# $sum shows the total of a given column (may not work...)
# $count angir kolonner som skal telles
# $name_MYCOL ($navn_MYCOL) changes the column header.
# $url_MYCOL makes a hyperlink
# $explain_MYCOL ($forklar_MYCOL) shows an explaination as popup in the browser when you
# hold the curser over the column header.
#
# ---------------------------------------------------------------------
# netbox related reports
#

netbox {
$sql="
SELECT roomid,sysname,ip,catid,sub,typeid,typename,
       orgid,netbox.up,modules,swport,gwport, prefixcount,
       CASE WHEN Mem THEN 'Mem' ELSE NULL END AS Mem,snmp,
       sw_ver AS Software,serial,val AS Function,netbox.netboxid,prefixid
       FROM type
       RIGHT OUTER JOIN netbox USING (typeid)
       LEFT JOIN (SELECT netboxid,count(netboxid) AS gwport
                       FROM gwport JOIN module USING (moduleid)
                       GROUP BY netboxid) tempaggr1 USING (netboxid)
       LEFT JOIN (SELECT netboxid,count(netboxid) AS swport
                       FROM swport JOIN module USING (moduleid)
                       GROUP BY netboxid) tempaggr2 USING (netboxid)
       LEFT JOIN (SELECT netboxid,count(netboxid) AS modules
                       FROM module
                       GROUP BY netboxid) tempaggr3 USING (netboxid)
       LEFT JOIN (SELECT netboxid,count(netboxid) AS sub
                       FROM netboxcategory
                       GROUP BY netboxid) tempaggr4 USING (netboxid)
       LEFT JOIN (SELECT netboxid,count(netboxid) AS snmp
                       FROM netboxsnmpoid 
                       GROUP BY netboxid) tempaggr5 USING (netboxid)
       LEFT JOIN (SELECT netboxid, count(netboxid) > 0 AS Mem
                       FROM mem
                       GROUP by netboxid) tempaggr6 USING (netboxid)
       LEFT JOIN (SELECT netboxid, count(gwportid) AS prefixcount
                  FROM gwportprefix
                  JOIN gwport USING (gwportid)
                  JOIN module USING (moduleid)
                  GROUP BY netboxid) tempaggr7 USING (netboxid)
       LEFT JOIN device ON (netbox.deviceid=device.deviceid)
       LEFT JOIN netboxinfo ON (netbox.netboxid=netboxinfo.netboxid 
                                AND var='function')
";
$hide = "netboxid,prefixid,typeid";
$order_by="roomid";
$title = "Devices in Operation";
$name_sysname="Name";
$name_catid="Cat";
$name_ip="IP address";
$name_typename="Type";
$name_roomid="Room";
$name_orgid="Org";
$name_up="Up";
$name_modules="#mod";
$name_swport="#swp";
$name_gwport="#gwp";
$name_prefixcount="#prefixes";
$url_roomid="room?roomid=$roomid";
$url_sysname="/browse/$sysname";
$url_typename="type?typeid=$typeid";
$url_orgid="org?orgid=$orgid";
$url_catid="netbox?catid=$catid";
$url_sub="netboxinfo?netboxid=$netboxid";
$url_modules="modules?sysname=$sysname";
$url_Mem="mem?sysname=$sysname";
$url_swport="swport?b1.netboxid=$netboxid";
$url_gwport="gwport?netbox.netboxid=$netboxid";
$url_up="/status/history/?type=boxes&id=$netboxid";
$url_Function="netbox?val=$Function";
$url_snmp="netboxsnmpoid?netboxid=$netboxid";
$url_prefixcount="gwip?sysname=$sysname";
$explain_Mem="Memory";
$explain_swp="Switch Ports";
$explain_gwp="Router Ports";
$explain_up="Status";
$explain_snmp="#SNMP OIDs classified";
$explain_sub="Sub category"; 
$sum="swport";
}

netboxinfo {
$sql="SELECT roomid,sysname,catid,category AS subcat,netboxid 
      FROM netbox 
      LEFT JOIN netboxcategory USING (netboxid)":
$title="Netboxinfo";
$hide="netboxid";
$url_subcat="netboxinfo?category=$subcat";
}

uptime {
$sql="
SELECT roomid,sysname,host(ip),catid,typeid,typename,prefixid,upsince,now()-upsince as uptime
FROM netbox 
JOIN type USING (typeid)";
$title="Uptime";
$hide="typeid,prefixid";
$name_catid="Category";
$name_typename="Type";
$name_roomid="Room";
$name_sysname="Name";
$order_by="upsince desc";
$url_roomid="room?roomid=$roomid";
$url_sysname="/browse/$sysname";
$url_typename="type?typeid=$typeid";
$url_orgid="org?orgid=$orgid";
$url_ip="prefix?prefixid=$prefixid"; 
$url_catid="netbox?catid=$catid";
}


srv {
$sql = "
 SELECT roomid,sysname,host(ip),catid,orgid, count(distinct category) as subcats,
        count(serviceid) as services, val as function, netbox.netboxid,prefixid 
 FROM netbox 
 LEFT JOIN service USING (netboxid) 
 LEFT JOIN netboxcategory USING (netboxid) 
 LEFT JOIN netboxinfo ON (netboxinfo.netboxid=netbox.netboxid AND netboxinfo.var='function') 
 WHERE catid='SRV' 
 GROUP BY roomid,sysname,host(ip),catid,orgid,val,netbox.netboxid,netbox.ip,prefixid";
$title = "Servers";
$name_sysname="Name";
$url_sysname="/browse/$sysname";
$name_ip="IP";
$name_orgid="Org";
$name_roomid="Room";
$order_by="roomid";
$hide = "netboxid,prefixid";          
$url_subcats="netboxinfo?netboxid=$netboxid";
$url_ip="prefix?prefixid=$prefixid";
$url_roomid="room?roomid=$roomid";
$url_typeid="type?typeid=$typeid";
$url_orgid="org?orgid=$orgid";
$url_services="servicemon?netboxid=$netboxid";
$url_function="netbox?val=$function";
$url_catid="netbox?catid=$catid";
}



servicemon {
$sql="
  SELECT sysname,handler,version,netboxid 
  FROM service 
  LEFT JOIN netbox USING (netboxid)";
$title="Services being monitored";
$name_orgid="Owner";
$hide="netboxid";
$url_sysname="srv?sysname=$sysname";
}
 

device {
$sql = "
 SELECT serial,roomid,sysname,module 
 FROM device 
 JOIN module USING (deviceid) 
 JOIN netbox USING (netboxid)";
$title = "Devices";
$name_roomid="Room";
$name_serial="Serial Number";
$name_sysname="Netbox";
$name_module="Module";
$order_by="roomid,sysname,module,serial";
$url_roomid="room?roomid=$roomid";
$url_sysname="netbox?sysname=$sysname";
}


modules {
$sql="
 SELECT roomid,sysname,module AS mn,swport,gwport,model,descr,serial,sw_ver AS Software,hw_ver AS Hardware,fw_ver AS Firmware
 FROM netbox 
 JOIN module USING (netboxid) 
 JOIN device ON module.deviceid=device.deviceid
 LEFT JOIN (SELECT moduleid,count(moduleid) AS gwport
                 FROM gwport
                 GROUP BY moduleid) tempaggr1 USING (moduleid)
 LEFT JOIN (SELECT moduleid,count(moduleid) AS swport
                 FROM swport
                 GROUP BY moduleid) tempaggr2 USING (moduleid)
";
$title = "Modules";
$order_by="roomid,sysname,mn";
$name_swport="#swp";
$name_gwport="#gwp";
$url_roomid="room?roomid=$roomid";
$url_sysname="netbox?sysname=$sysname";
$url_swport="swport?b1.sysname=$sysname&m1.module=$mn";
$url_gwport="gwport?netbox.sysname=$sysname&module.module=$mn";
$url_model="modules?model=$model";
$explain_mn="Module number";
}

mem {
$sql="
 SELECT sysname,catid,typename,memtype,device,to_char(size,'999G999G999G999') AS size,
  to_char(used,'999G999G999G999') AS used,
  to_char(size-used,'999G999G999G999') AS free,
  TO_CHAR((used/(size+0.0001))*100, '999')::int AS used_pst 
 FROM netbox JOIN mem USING (netboxid) 
 LEFT JOIN type USING (typeid)";
$order_by="sysname,catid,memtype,device";
$title = "Memory";
$name_used_pst = "used %"
$url_sysname="netbox?sysname=$sysname";
$url_memtype="mem?memtype=$memtype";
$url_device="mem?device=$device";
$url_typename="type?typename=$typename";
}

# ---------------------------------------------------------------------
# router port / prefix - related reports
#
gwport {
$sql= "
 SELECT netbox.sysname, gwport.ifindex, gwport.interface, module.module,
        gwport.link, gwport.speed, prefixcount, metric, netbox.netboxid,
        gwportid, gwport.portname, n2.sysname AS otherbox, swport.interface AS remoteif
 FROM netbox
 JOIN module ON (netbox.netboxid=module.netboxid) 
 JOIN gwport ON (module.moduleid=gwport.moduleid) 
 LEFT JOIN (SELECT gwportid, COUNT(gwportid) AS prefixcount
                 FROM gwportprefix
                 GROUP BY gwportid) tempaggr1 USING (gwportid)
 LEFT JOIN netbox AS n2 ON (gwport.to_netboxid=n2.netboxid)
 LEFT JOIN swport ON (gwport.to_swportid=swport.swportid)
";
$title = "Router ports";
$hide = "netboxid,gwportid";
$name_sysname="Router";
$name_interface="Interface";
$name_gwip="IP address";
$name_speed="Mbps";
$name_portname="Description";
$name_prefixcount="#prefix";
$name_otherbox="Connected to";
$name_remoteif="Remote if";
$order_by="sysname,ifindex";
$url_sysname="netbox?sysname=$sysname";
$url_module="modules?sysname=$sysname&module=$module";
$url_prefixcount="gwip?sysname=$sysname&ifindex=$ifindex";
$url_otherbox="netbox?sysname=$otherbox";
$url_remoteif="swport?b1.sysname=$otherbox&s1.interface=$remoteif";
}

gwip {
$sql= "
 SELECT sysname,ifindex,interface,module,speed,
        CASE WHEN hsrp THEN 'Y' ELSE NULL END AS hsrp,
        gwip,netaddr as prefix,vlan,nettype,netident,portname AS Description,
        netboxid,prefix.prefixid
 FROM netbox 
 JOIN module USING (netboxid) 
 JOIN gwport USING (moduleid) 
 JOIN gwportprefix USING (gwportid) 
 JOIN prefix ON gwportprefix.prefixid=prefix.prefixid 
 JOIN vlan USING (vlanid)";
$title = "Router port prefixes";
$hide = "netboxid,prefixid";
$name_sysname="Router";
$name_interface="Router port";
$name_gwip="IP address";
$name_speed="Mbps";
$order_by="sysname,ifindex,gwip";
$url_prefix="prefix?prefixid=$prefixid";
$url_sysname="netbox?sysname=$sysname";
$url_vlan="swporttrunk?vlan.vlan=$vlan";
$url_module="modules?sysname=$sysname&module=$module";
$url_interface="gwip?sysname=$sysname&ifindex=$ifindex";
}

prefix {
$sql="
SELECT host(netaddr::inet) AS Netaddr, 
       masklen(netaddr::inet) AS m,
       vlan,
       gwcount,
       nettype,
       netident,
       orgid AS org,
       usageid AS usage,
       description,
       active_ip_cnt as act,
       prefix.prefixid,
       vlan.vlanid
FROM prefix
JOIN vlan USING (vlanid)
LEFT JOIN (SELECT prefixid, COUNT(gwportid) AS gwcount
           FROM gwportprefix
           GROUP BY prefixid) tempaggr1 USING (prefixid)
LEFT JOIN prefix_active_ip_cnt USING (prefixid)
";
$title = "Prefixes";
$order_by="netaddr";
$name_netaddr = "Network";
$name_m="Mask";
$name_act="Active IPs";
$hide = "prefixid,vlanid";
$url_gwcount="gwip?vlanid=$vlanid";
$url_vlan="swporttrunk?vlan.vlan=$vlan";
$url_orgid="org?orgid=$orgid";
$url_usageid="usage?usageid=$usageid";
$url_act="/machinetracker/ip?prefixid=$prefixid&days=-1";
$url_nettype="prefix?nettype=$nettype";
$explain_m="Mask of subnet";
$explain_act="No of currently active IP addresses";
$explain_gwcount="No of gateways connected";
$sum="act";
}


# ---------------------------------------------------------------------
# switch port related reports