/**
* Return all subnets, including statistics
*
* @param string $order Property to sort by, default: null
* @param string $direction One of [asc|desc].
* @return \Zend\Db\ResultSet\AbstractResultSet Result set producing \Model\Network\Subnet
*/
public function getSubnets($order = null, $direction = 'asc')
{
$orderBy = '';
if ($order) {
$direction = $direction == 'desc' ? 'DESC' : 'ASC';
if ($order == 'CidrAddress') {
$orderBy = "ORDER BY ipsubnet {$direction}, ipmask {$direction}";
} else {
$order = $this->_subnets->getHydrator()->extractName($order);
if ($order) {
$orderBy = "ORDER BY {$order} {$direction}";
}
}
}
$query = <<<EOT
SELECT
networks.ipsubnet AS netid,
networks.ipmask AS mask,
COUNT(networks.ipmask) AS num_inventoried,
(SELECT COUNT(mac) FROM netmap WHERE
netid = networks.ipsubnet AND
mask = networks.ipmask AND
mac NOT IN(SELECT macaddr FROM networks) AND
mac IN(SELECT macaddr FROM network_devices)
) AS num_identified,
(SELECT COUNT(mac) FROM netmap WHERE
netid = networks.ipsubnet AND
mask = networks.ipmask AND
mac NOT IN(SELECT macaddr FROM networks) AND
mac NOT IN(SELECT macaddr FROM network_devices)
) AS num_unknown,
subnet.name
FROM networks
LEFT JOIN subnet ON networks.ipsubnet=subnet.netid AND networks.ipmask=subnet.mask
WHERE
ipsubnet != '0.0.0.0' AND
NOT(ipsubnet = '127.0.0.0' AND ipmask='255.0.0.0') AND
description NOT LIKE '%PPP%'
GROUP BY ipsubnet, ipmask, name
{$orderBy}
EOT;
return $this->_subnets->getAdapter()->query($query, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE, $this->_subnets->getResultSetPrototype());
}
/**
* Return all subnets, including statistics
*
* @param string $order Property to sort by, default: null
* @param string $direction One of [asc|desc].
* @return \Zend\Db\ResultSet\AbstractResultSet Result set producing \Model\Network\Subnet
*/
public function getSubnets($order = null, $direction = 'asc')
{
$orderBy = '';
if ($order) {
$direction = $direction == 'desc' ? 'DESC' : 'ASC';
if ($order == 'CidrAddress') {
$orderBy = "ORDER BY netid {$direction}, mask {$direction}";
} else {
$order = $this->_subnets->getHydrator()->extractName($order);
if ($order) {
$orderBy = "ORDER BY {$order} {$direction}";
}
}
}
// The first query covers only subnets with at least 1 inventoried
// interface, but includes subnets without scanned interfaces.
// The second query covers only subnets with at least 1 scanned
// interface, but includes subnets without inventoried interfaces.
// The UNION eliminates possible overlap.
$query = <<<EOT
SELECT
networks.ipsubnet AS netid,
networks.ipmask AS mask,
COUNT(networks.ipmask) AS num_inventoried,
(SELECT COUNT(mac) FROM netmap WHERE
netid = networks.ipsubnet AND
mask = networks.ipmask AND
mac NOT IN(SELECT macaddr FROM networks) AND
mac IN(SELECT macaddr FROM network_devices)
) AS num_identified,
(SELECT COUNT(mac) FROM netmap WHERE
netid = networks.ipsubnet AND
mask = networks.ipmask AND
mac NOT IN(SELECT macaddr FROM networks) AND
mac NOT IN(SELECT macaddr FROM network_devices)
) AS num_unknown,
name
FROM networks
LEFT JOIN subnet ON networks.ipsubnet=subnet.netid AND networks.ipmask=subnet.mask
WHERE
ipsubnet != '0.0.0.0' AND
NOT(ipsubnet = '127.0.0.0' AND ipmask='255.0.0.0') AND
description NOT LIKE '%PPP%'
GROUP BY ipsubnet, ipmask, name
UNION
SELECT
netmap.netid,
netmap.mask,
(SELECT COUNT(*) FROM networks WHERE
networks.ipsubnet = netmap.netid AND
networks.ipmask = netmap.mask AND
networks.description NOT LIKE '%PPP%'
) AS num_inventoried,
(SELECT COUNT(mac) FROM netmap netmap3 WHERE
netmap3.netid = netmap.netid AND
netmap3.mask = netmap.mask AND
netmap3.mac NOT IN(SELECT macaddr FROM networks) AND
netmap3.mac IN(SELECT macaddr FROM network_devices)
) AS num_identified,
(SELECT COUNT(mac) FROM netmap netmap3 WHERE
netmap3.netid = netmap.netid AND
netmap3.mask = netmap.mask AND
netmap3.mac NOT IN(SELECT macaddr FROM networks) AND
netmap3.mac NOT IN(SELECT macaddr FROM network_devices)
) AS num_unknown,
subnet.name
FROM netmap
LEFT JOIN subnet ON netmap.netid=subnet.netid AND netmap.mask=subnet.mask
GROUP BY netmap.netid, netmap.mask, subnet.name
{$orderBy}
EOT;
return $this->_subnets->getAdapter()->query($query, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE, $this->_subnets->getResultSetPrototype());
}
