. // //#################################################################### //### configuration ################################################## //#################################################################### // Copy dmarcts-report-viewer-config.php.sample to // dmarcts-report-viewer-config.php and edit with the appropriate info // for your database authentication and location. // // Edit the configuration variables in dmarcts-report-viewer.js with your preferences. // // //#################################################################### //### functions ###################################################### //#################################################################### function tmpl_reportList($reports, $sort) { global $options; global $cookie_options; $reportlist[] = ""; if (sizeof($reports) == 0) { $reportlist[] = "
No Reports Match this filter
Click the Reset button or choose a different value for DMARC Result, Month, Domain(s) or Reporter(s).
"; } else { $title_message_th = "Click to toggle sort direction by this column."; $title_message_tr = "Click to view detailed report data."; // Resizer handles // -------------------------------------------------------------------------- $reportlist[] = "
"; $reportlist[] = "
"; $reportlist[] = ""; $reportlist[] = " "; $reportlist[] = " "; $triangle = ($cookie_options['sort'] ? "asc":"desc") . "_triangle "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportlist[] = " "; $reportsum = 0; foreach ($reports as $row) { $row = array_map('html_escape', $row); $reportlist[] = " "; $reportlist[] = " "; // Col 0 $reportlist[] = " "; // Col 0 $reportlist[] = " "; // Col 0 $reportlist[] = " "; // Col 1 $reportlist[] = " "; // Col 3 $reportlist[] = " "; // Col 5 $reportlist[] = " "; // Col 6 $reportlist[] = " "; $reportlist[] = " "; // Col 9 $reportlist[] = " "; $reportsum += $row['rcount']; } $reportlist[] = " "; $reportlist[] = ""; $reportlist[] = "
1
1		Start DateEnd DateDomainReporting OrganizationReport IDMessages
" . get_dmarc_result($row)['status_sort_key'] . "
" . get_report_status($row)['status_sort_key'] . "". format_date($row['mindate'], $cookie_options['date_format']). "". format_date($row['maxdate'], $cookie_options['date_format']). "". $row['domain']. "". $row['org']. "". $row['reportid'] . "". number_format($row['rcount']+0,0). "
Sum:".number_format($reportsum,0)."
"; $reportlist[] = ""; $reportlist[] = ""; } #indent generated html by 2 extra spaces return implode("\n ",$reportlist); } //#################################################################### //### main ########################################################### //#################################################################### // These files are expected to be in the same folder as this script, and must exist. include "dmarcts-report-viewer-config.php"; include "dmarcts-report-viewer-common.php"; // Get all configuration options // -------------------------------------------------------------------------- configure(); $dom_select= ''; $org_select= ''; $per_select= ''; $dmarc_select= ''; $report_status = ''; $where = ''; // Parameters of GET // -------------------------------------------------------------------------- if(isset($_GET['sortorder']) && is_numeric($_GET['sortorder'])){ $sortorder=$_GET['sortorder']+0; }elseif(!isset($_GET['sortorder'])){ $sortorder= isset( $default_sort ) ? $default_sort : 1; }else{ die('Invalid sortorder flag'); } if(isset($_GET['d'])){ $dom_select=$_GET['d']; }else{ $dom_select= ''; } if( $dom_select == "all" ) { $dom_select= ''; } if(isset($_GET['o'])){ $org_select=$_GET['o']; }else{ $org_select= ''; } if( $org_select == "all" ) { $org_select= ''; } if(isset($_GET['p'])){ $per_select=$_GET['p']; }else{ $per_select= date( 'Y-m' ); } if( $per_select == "all" ) { $per_select= ''; } if(isset($_GET['dmarc'])){ $dmarc_select=$_GET['dmarc']; }else{ $dmarc_select= ''; } if(isset($_GET['rptstat'])){ $report_status = $_GET['rptstat']; }else{ $report_status = ''; } // Debug // echo "
D=$dom_select
O=$org_select
"; // echo "
DMARC=$dmarc_select
"; // Make a DB Connection // -------------------------------------------------------------------------- $dbh = connect_db($dbtype, $dbhost, $dbport, $dbname, $dbuser, $dbpass); // Get allowed reports and cache them - using serial as key // -------------------------------------------------------------------------- $reports = array(); // set sort direction // -------------------------------------------------------------------------- $sort = ''; if( $sortorder ) { $sort = "ASC"; } else { $sort = "DESC"; } // Build SQL WHERE clause // DMARC Result // -------------------------------------------------------------------------- switch ($dmarc_select) { case "all": // Everything break; case "DMARC_FAIL": // DMARC Fail $where .= ( $where <> '' ? " AND" : " WHERE" ) . " dmarc_result_min = 0 AND dmarc_result_max = 0"; break; case "DMARC_PASS_AND_FAIL": // DMARC Pass and Fail $where .= ( $where <> '' ? " AND" : " WHERE" ) . " dmarc_result_min = 0 AND (dmarc_result_max = 1 OR dmarc_result_max = 2)"; break; case "DMARC_OTHER_CONDITION": // Other condition: Yellow $where .= ( $where <> '' ? " AND" : " WHERE" ) . " dmarc_result_min >= 3 AND dmarc_result_max >= 3"; break; case "DMARC_PASS": // DMARC Pass $where .= ( $where <> '' ? " AND" : " WHERE" ) . " (dmarc_result_min = 1 OR dmarc_result_min = 2) AND (dmarc_result_max <= 2)"; break; default: break; } // Report Status // -------------------------------------------------------------------------- if ( $report_status != "all" && $report_status != "" ) { $where .= ( $where <> '' ? " AND" : " WHERE" ) . " " . $dmarc_result[$report_status]['status_sql_where']; } // Domains // -------------------------------------------------------------------------- if( $dom_select <> '' ) { $where .= ( $where <> '' ? " AND" : " WHERE" ) . " domain=" . $dbh->quote($dom_select); } // Organisations // -------------------------------------------------------------------------- if( $org_select <> '' ) { $where .= ( $where <> '' ? " AND" : " WHERE" ) . " org=" . $dbh->quote($org_select); } // Periods // -------------------------------------------------------------------------- if( $per_select <> '' ) { $ye = substr( $per_select, 0, 4) + 0; $mo = substr( $per_select, 5, 2) + 0; $where .= ( $where <> '' ? " AND" : " WHERE" ) . " ((extract(year from mindate) = $ye AND extract(month from mindate) = $mo) " . " OR (extract(year from maxdate) = $ye AND extract(month from maxdate) = $mo)) "; } // Include the rcount via left join, so we do not have to make an sql query // for every single report. // -------------------------------------------------------------------------- $sql = " SELECT report.*, rcount, dkim_align_min, spf_align_min, dkim_result_min, spf_result_min, dmarc_result_min, dmarc_result_max FROM report LEFT JOIN ( SELECT SUM(rcount) AS rcount, serial, MIN( (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 2 ELSE 1 END) ) AS dkim_align_min, MIN( (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 2 ELSE 1 END) ) AS spf_align_min, MIN( (CASE WHEN dkimresult = 'fail' THEN 0 WHEN dkimresult = 'pass' THEN 2 ELSE 1 END) ) AS dkim_result_min, MIN( (CASE WHEN spfresult = 'fail' THEN 0 WHEN spfresult = 'pass' THEN 2 ELSE 1 END) ) AS spf_result_min, MIN( (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END) + (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END) ) AS dmarc_result_min, MAX( (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END) + (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END) ) AS dmarc_result_max FROM rptrecord GROUP BY serial ) AS rptrecord ON report.serial = rptrecord.serial $where ORDER BY " . $cookie_options['sort_column'] . ( $cookie_options['sort'] ? " ASC" : " DESC" ) ; // Debug // echo "
sql where = $where
"; // echo "
Data List sql: $sql
"; // echo "
per_select = " . urlencode($per_select) . "
"; $query = $dbh->query($sql); foreach($query as $row) { if (true) { //add data by serial $reports[$row['serial']] = $row; } } // Generate Report List // -------------------------------------------------------------------------- echo tmpl_reportList($reports, $sort); ?>