init_page('SSC Compliance Report', 'ssc'); ## Pull bond groups and ips from cache/db. $bgc = new BondgroupCache(); $bondgroups = $bgc->get_bondgroups(); #echo 'List of (active) bond group ids and company names:
', print_r($bondgroups), '
'; exit; # Make ip -> bond group name (inverse) map. (This ought to be in memcache...) $ip2name = array(); foreach ($bondgroups as $bgid => $bginfo) { $bgname = $bginfo['NAME']; foreach ($bginfo['ACTIVEIPS'] as $aip) { $ip2name[strtoupper(dechex($aip))] = $bgname; } foreach ($bginfo['DISABLEDIPS'] as $dip) { $ip2name[strtoupper(dechex($dip))] = $bgname; } } # echo 'ip2name map:
', print_r($ip2name), '
'; exit; function ip2name($iphexstr) { global $ip2name; if (isset($ip2name[$iphexstr])) { return $ip2name[$iphexstr]; } else { return '(unknown)'; } } ## Pull blacklist id/name info $db = new db($ss_user, $ss_pass, $ss_server) or die('DB connection failed!'); $blsql = "SELECT blacklist_id AS ID, shortname AS NAME, category AS cat FROM ssc_blacklists ORDER BY category, description"; $db->parse_stmt($blsql); $db->execute() or die('Can\'t db->execute().'); $bldata = array(); $db->fetch_all($bldata); $db->free(); $blinfo = array(); foreach($bldata['ID'] as $rownum => $id) { $blinfo[$id] = array(); $blinfo[$id]['NAME'] = $bldata['NAME'][$rownum]; $blinfo[$id]['CAT'] = $bldata['CAT'][$rownum]; } # echo 'Blacklist Info:
', print_r($blinfo), '
'; ## Process form variables if (isset($_GET['gid']) and $_GET['gid'] != -1) { $gid = $_GET['gid']; } if (isset($_GET['gname']) and $_GET['gname'] != -1) { $gid = $_GET['gname']; } if (isset($_GET['ipstate']) and $_GET['ipstate'] != -1) { $ipstate = $_GET['ipstate']; } if (isset($_GET['showall'])) { $showall = 1; } else { $showall = 0; } $numips = 0; $iplist = array(); if (!isset($ipstate) and !isset($gid)) { # Summary page. Get the 100 worst offending IPs (as measured by the # number of critical and significant blacklists they're on) and show # data for them. # The SQL is more complicated than I like, but briefly: The innermost # query pulls every (ip, blacklist) pair from the table and finds # the newest row for that pair. The next query takes all those most # recent rows, and counts how many of them have result = 1 ("on list") # for each IP, and orders them from on most blacklists to on least. # Finally, the outermost query throws away the blacklist ids and # consolidates the ips, de-raws them, and takes the top 100. $ipsql = "SELECT RAWTOHEX(badip) AS ip FROM (SELECT UNIQUE(ip_address) AS badip, COUNT(*) OVER (PARTITION BY ip_address) AS numlistson FROM (SELECT ip_address, blacklist_id, result, check_dt, MAX(check_dt) OVER (PARTITION BY ip_address, blacklist_id) AS most_recent FROM ssc_bl_check_log WHERE blacklist_id IN (SELECT BLACKLIST_ID FROM ssc_blacklists WHERE category IN (1, 2)) ) WHERE check_dt = most_recent AND result = 1 ORDER BY numlistson desc, ip_address) WHERE rownum < 101"; $db->parse_stmt($ipsql); $db->execute() or die('Can\'t db->execute().'); $dbdata = array(); $db->fetch_all($dbdata); $db->free(); #echo 'IP data: ', print_r($dbdata); exit; $worstips = $dbdata['IP']; #echo 'Worst IPs: ', print_r($worstips); exit; foreach ($worstips as $badip) { $badip = strtoupper($badip); array_push($iplist, "HEXTORAW('$badip')"); } #echo 'IPList: ', print_r($iplist); exit; } elseif (isset($gid)) { # Specific bond group view. Pull list of active and/or disabled # ips for bond group, and use that in the WHERE clause. array_push($iplist, "HEXTORAW('00000000')"); # Ensure WHERE clause if (!isset($ipstate) or !strcasecmp($ipstate, 'a')) { foreach ($bondgroups[$gid]['ACTIVEIPS'] as $ip) { if (!$showall and ++$numips > 100) { break; } $hexip = dechex($ip); array_push($iplist, "HEXTORAW('$hexip')"); } } if (!isset($ipstate) or !strcasecmp($ipstate, 'd')) { foreach ($bondgroups[$gid]['DISABLEDIPS'] as $ip) { if (!$showall and ++$numips > 100) { break; } $hexip = dechex($ip); array_push($iplist, "HEXTORAW('$hexip')"); } } } else { } # If no $gid and no $ipstate, don't use a WHERE clause. $whereips = ''; if (count($iplist)) { $whereips = 'AND IP_ADDRESS IN (' . implode($iplist, ', ') . ')'; } #echo 'ip count = ' . count($iplist) . ", where clause = $whereips
"; $datasql = "SELECT rawtohex(ip_address) AS ip, blacklist_id, result, TO_CHAR(check_dt, 'YYYYMMDD HH24MISS') as cdate, ROUND(SYSDATE - check_dt) AS dayson FROM ssc_bl_check_log WHERE check_dt BETWEEN TRUNC(SYSDATE) - 90 AND TRUNC(SYSDATE) + 1 $whereips ORDER BY ip_address, blacklist_id, check_dt DESC"; # echo 'Select SQL: 
', $datasql, '
'; $db->parse_stmt($datasql); $db->execute() or die('Can\'t db->execute().'); $dbdata = array(); $numresults = $db->fetch_all($dbdata); $db->free(); #echo "Num results: $numresults, DB Data:
", print_r($dbdata), '
'; if (!isset($gid)) { function sortbybondgroupname($ip1, $ip2) { $res = strnatcasecmp(ip2name($ip1), ip2name($ip2)); if(! $res) { $res = strcasecmp($ip1, $ip2); } return $res; } uasort($dbdata['IP'], 'sortbybondgroupname'); # echo "IPs (after bgname):
", print_r($dbdata['IP']), '
'; # Now sub-sort by db rownum, within each block of IPs. # This is to maintain the blacklist id -> date listed correspondence. $resultips = array(); $lastip = 'FFFFFFFF'; foreach ($dbdata['IP'] as $rownum => $ip) { if ($ip != $lastip) { if (isset($sortedips)) { ksort($sortedips); foreach ($sortedips as $srownum => $sip) { $resultips[$srownum] = $sip; # array_merge() loses key } } $sortedips = array(); $lastip = $ip; } $sortedips[$rownum] = $ip; } if (isset($sortedips)) { ksort($sortedips); foreach ($sortedips as $srownum => $sip) { $resultips[$srownum] = $sip; # array_merge() loses $srownum. } } unset($lastip); $dbdata['IP'] = $resultips; #echo "IPs (after sort):
", print_r($dbdata['IP']), '
'; exit; } include($skin->get_header()); ?>

SSC Compliance Reporting - DNSBLs


">



ERROR: IP State can only be used when a bond group is selected.

'; } echo '   No DNSBL data found.'; include($skin->get_footer()); exit; } # Print out table with results. echo ' '; echo '
'; echo ''; echo ''; # Get first blid's category foreach ($blinfo as $blid => $blname) { $lastkitty = $blname['CAT']; break; } # Print out the blacklist names, interspersing category dividers. # Also, count the number of blacklists in each category. $lastkitty = -1; $numlistsincat = array(); foreach ($blinfo as $blid => $blname) { $kitty = $blname['CAT']; if ($kitty != $lastkitty) { echo ''; $lastkitty = $kitty; } echo ""; if (isset($numlistsincat[$kitty])) { $numlistsincat[$kitty]++; } else { $numlistsincat[$kitty] = 1; } } echo "\n"; #echo 'Number of blacklists, by category: ', print_r($numlistsincat); exit; $numblcrit = $numlistsincat[1]; $numblsig = $numlistsincat[2]; $numblinfo = $numlistsincat[3]; echo ''; echo ' '; echo ' '; echo ''; $ipblids = array(); # Print out header for first IP, which may not have rownum = 0. foreach ($dbdata['IP'] as $frownum => $fip) { $currbl = $dbdata['BLACKLIST_ID'][$frownum]; $currip = $fip; echo ' '; echo "'; break; } # Accum all DNSBL IDs for the currip, print them when we hit next IP. foreach ($dbdata['IP'] as $rownum => $ip) { if ($currip != $ip) { # Current IP's data. foreach ($blinfo as $blid => $blname) { if (array_key_exists($blid, $ipblids) and $ipblids[$blid]) { echo ''; } else { echo ''; } } echo "\n"; # Next IP's header. $ipblids = array(); echo ' '; echo "'; } $currip = $ip; # If the IP is on the currbl, record the number of days until it goes off. # If it's not on, don't bother recording anything. $currbl = $dbdata['BLACKLIST_ID'][$rownum]; if (! isset($ipblids[$currbl])) { if ($dbdata['RESULT'][$rownum] == 1) { # IP is on $currbl $ipblids[$currbl] = $dbdata['DAYSON'][$rownum]; } else { unset($ipblids[$currbl]); # Not on BL, make sure that's unset. } } if (isset($ipblids[$currbl])) { if ($ipblids[$currbl] >= 0 and $dbdata['RESULT'][$rownum] == 1) { $ipblids[$currbl] = $dbdata['DAYSON'][$rownum]; } else { $ipblids[$currbl] = 0 - $ipblids[$currbl]; # Make it < 0 } } } # Finish off last IP's Hit/OK table row. foreach ($blinfo as $blid => $blname) { if (array_key_exists($blid, $ipblids)) { echo ''; } else { echo ''; } } echo '
$blname[NAME]
Group IPCriticalSignificantInformational
', ip2name($currip), '", hex2ip($currip), '', abs($ipblids[$blid]), 'OK
', ip2name($ip), '", hex2ip($ip), '', $ipblids[$blid], 'OK
'; if (!$showall and $numips > 100) { if (strstr($uri, '?') === FALSE) { $uri .= '?showall'; } else { $uri .= '&showall'; } echo "

More than 100 IPs were found, but only the first 100 are shown. To see all IPs, click here."; } include($skin->get_footer()); ?>