function mactrack_get_records(&$sql_where, $apply_limits = TRUE, $row_limit = "30") { global $timespan, $group_function, $summary_stats; $match = read_config_option('mt_ignorePorts', TRUE); if ($match == '') { $match = "(Vlan|Loopback|Null)"; db_execute("REPLACE INTO settings SET name='mt_ignorePorts', value='$match'"); } $ignore = "(ifName NOT REGEXP '" . $match . "' AND ifDescr NOT REGEXP '" . $match . "')"; /* issues sql where */ if ($_REQUEST["issues"] == "-2") { // All Interfaces /* do nothing all records */ } elseif ($_REQUEST["issues"] == "-3") { // Non Ignored Interfaces $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . $ignore; } elseif ($_REQUEST["issues"] == "-4") { // Ignored Interfaces $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . " NOT " . $ignore; } elseif ($_REQUEST["issues"] == "-1") { // With Issues $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "((int_errors_present=1 OR int_discards_present=1) AND $ignore)"; } elseif ($_REQUEST["issues"] == "0") { // Up Interfaces $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(ifOperStatus=1 AND $ignore)"; } elseif ($_REQUEST["issues"] == "1") { // Up w/o Alias $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(ifOperStatus=1 AND ifAlias='' AND $ignore)"; } elseif ($_REQUEST["issues"] == "2") { // Errors Up $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(int_errors_present=1 AND $ignore)"; } elseif ($_REQUEST["issues"] == "3") { // Discards Up $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(int_discards_present=1 AND $ignore)"; } elseif ($_REQUEST["issues"] == "7") { // Change < 24 Hours $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(mac_track_interfaces.sysUptime-ifLastChange < 8640000) AND ifLastChange > 0 AND (mac_track_interfaces.sysUptime-ifLastChange > 0)"; } elseif ($_REQUEST["issues"] == "9" && $_REQUEST["bwusage"] != "-1") { // In/Out over 70% $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "((inBound>" . $_REQUEST["bwusage"] . " OR outBound>" . $_REQUEST["bwusage"] . ") AND $ignore)"; } elseif ($_REQUEST["issues"] == "10" && $_REQUEST["bwusage"] != "-1") { // In over 70% $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(inBound>" . $_REQUEST["bwusage"] ." AND $ignore)"; } elseif ($_REQUEST["issues"] == "11" && $_REQUEST["bwusage"] != "-1") { // Out over 70% $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(outBound>" . $_REQUEST["bwusage"] . " AND $ignore)"; } else { } /* filter sql where */ $filter_where = mactrack_create_sql_filter($_REQUEST["filter"], array('ifAlias', 'hostname', 'ifName', 'ifDescr')); if (strlen($filter_where)) { $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "$filter_where"; } /* device_id sql where */ if ($_REQUEST["device"] == "-1") { /* do nothing all states */ } else { $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(mac_track_interfaces.device_id='" . $_REQUEST["device"] . "')"; } /* site sql where */ if ($_REQUEST["site"] == "-1") { /* do nothing all sites */ } else { $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(mac_track_interfaces.site_id='" . $_REQUEST["site"] . "')"; } /* type sql where */ if ($_REQUEST["type"] == "-1") { /* do nothing all states */ } else { $sql_where .= (strlen($sql_where) ? " AND " : "WHERE ") . "(mac_track_devices.device_type_id='" . $_REQUEST["type"] . "')"; } if (isset($_REQUEST["device"]) && $_REQUEST["device"]!=-1){ $sqlorder = "ORDER BY mac_track_interfaces.ifIndex ASC"; } else { $sqlorder = "ORDER BY " . $_REQUEST["sort_column"] . " " . $_REQUEST["sort_direction"]; } $sql_query = "SELECT mac_track_interfaces.*, mac_track_device_types.description AS device_type, mac_track_devices.device_name, mac_track_devices.host_id, mac_track_devices.disabled, mac_track_devices.last_rundate FROM mac_track_interfaces INNER JOIN mac_track_devices ON mac_track_interfaces.device_id=mac_track_devices.device_id INNER JOIN mac_track_device_types ON mac_track_device_types.device_type_id=mac_track_devices.device_type_id $sql_where $sqlorder"; if ($apply_limits) { $sql_query .= " LIMIT " . ($row_limit*($_REQUEST["page"]-1)) . "," . $row_limit; } //echo $sql_query; return db_fetch_assoc($sql_query); }
function mactrack_get_records(&$sql_where, $apply_limits = TRUE, $row_limit = '30') { global $timespan, $group_function, $summary_stats; $match = read_config_option('mt_ignorePorts', TRUE); if ($match == '') { $match = '(Vlan|Loopback|Null)'; db_execute_prepared('REPLACE INTO settings SET name="mt_ignorePorts", value = ?', array($match)); } $ignore = "(ifName NOT REGEXP '" . $match . "' AND ifDescr NOT REGEXP '" . $match . "')"; /* issues sql where */ if (get_request_var('issues') == '-2') { // All Interfaces /* do nothing all records */ } elseif (get_request_var('issues') == '-3') { // Non Ignored Interfaces $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . $ignore; } elseif (get_request_var('issues') == '-4') { // Ignored Interfaces $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . ' NOT ' . $ignore; } elseif (get_request_var('issues') == '-1') { // With Issues $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . "((int_errors_present=1 OR int_discards_present=1) AND {$ignore})"; } elseif (get_request_var('issues') == '0') { // Up Interfaces $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . "(ifOperStatus=1 AND {$ignore})"; } elseif (get_request_var('issues') == '1') { // Up w/o Alias $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . "(ifOperStatus=1 AND ifAlias='' AND {$ignore})"; } elseif (get_request_var('issues') == '2') { // Errors Up $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . "(int_errors_present=1 AND {$ignore})"; } elseif (get_request_var('issues') == '3') { // Discards Up $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . "(int_discards_present=1 AND {$ignore})"; } elseif (get_request_var('issues') == '7') { // Change < 24 Hours $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . '(mac_track_interfaces.sysUptime-ifLastChange < 8640000) AND ifLastChange > 0 AND (mac_track_interfaces.sysUptime-ifLastChange > 0)'; } elseif (get_request_var('issues') == '9' && get_filter_request_var('bwusage') != '-1') { // In/Out over 70% $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . '((inBound>' . get_request_var('bwusage') . ' OR outBound>' . get_request_var('bwusage') . ") AND {$ignore})"; } elseif (get_request_var('issues') == '10' && get_filter_request_var('bwusage') != '-1') { // In over 70% $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . '(inBound>' . get_request_var('bwusage') . " AND {$ignore})"; } elseif (get_request_var('issues') == '11' && get_filter_request_var('bwusage') != '-1') { // Out over 70% $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . '(outBound>' . get_request_var('bwusage') . " AND {$ignore})"; } else { } /* filter sql where */ $filter_where = mactrack_create_sql_filter(get_request_var('filter'), array('ifAlias', 'hostname', 'ifName', 'ifDescr')); if (strlen($filter_where)) { $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . $filter_where; } /* device_id sql where */ if (get_filter_request_var('device_id') == '-1') { /* do nothing all states */ } else { $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . '(mac_track_interfaces.device_id=' . get_request_var('device_id'); } /* site sql where */ if (get_filter_request_var('site_id') == '-1') { /* do nothing all sites */ } else { $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . '(mac_track_interfaces.site_id=' . get_request_var('site_id'); } /* type sql where */ if (get_filter_request_var('device_type_id') == '-1') { /* do nothing all states */ } else { $sql_where .= (strlen($sql_where) ? ' AND ' : 'WHERE ') . '(mac_track_devices.device_type_id=' . get_request_var('device_type_id'); } $sql_query = "SELECT mac_track_interfaces.*,\n\t\tmac_track_device_types.description AS device_type,\n\t\tmac_track_devices.device_name,\n\t\tmac_track_devices.host_id,\n\t\tmac_track_devices.disabled,\n\t\tmac_track_devices.last_rundate\n\t\tFROM mac_track_interfaces\n\t\tINNER JOIN mac_track_devices\n\t\tON mac_track_interfaces.device_id=mac_track_devices.device_id\n\t\tINNER JOIN mac_track_device_types\n\t\tON mac_track_device_types.device_type_id=mac_track_devices.device_type_id\n\t\t{$sql_where}\n\t\tORDER BY " . get_request_var('sort_column') . ' ' . get_request_var('sort_direction'); if ($apply_limits) { $sql_query .= ' LIMIT ' . $row_limit * (get_request_var('page') - 1) . ',' . $row_limit; } //echo $sql_query; return db_fetch_assoc($sql_query); }