db_conn = odbc_connect("$dsn",$_SERVER['PHP_AUTH_USER'],$_SERVER['PHP_AUTH_PW'],SQL_CUR_USE_ODBC); } /* Added by QLJ 19-Jan-2010 */ public function getReportFamilyStats($aFamilyName){ $query = "SELECT *" . ", \"Comment\"::varchar as \"UserComment\" " . "from mis.view_analyst_cs_families " . "where \"CsFamilyName\" = '$aFamilyName' " . "order by \"AnalystCsFamilyId\" desc"; $res = odbc_exec($this->db_conn, $query); $report = ''; $error = ''; if ($res){ while( odbc_fetch_row($res) ) { $report.= odbc_result($res, "AnalystCsFamilyId"); $report .= "|"; $name = ""; $firstname = odbc_result($res,"AnalystFirstName"); $surname = odbc_result($res,"AnalystLastName"); if ($firstname){ $name = $firstname; } if ($surname){ if ($name){ $name .= " $surname"; }else{ $name = $surname; } } $report .= $name; $report .= "|"; $timestamp = odbc_result($res,"AssignedTimestamp"); if ($timestamp){ $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) ); }else{ $timestamp = ""; } $report .= $timestamp; $report .= "|"; $timestamp = odbc_result($res,"UnAssignedTimestamp"); if ($timestamp){ $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) ); }else{ $timestamp = ""; } $report .= $timestamp; $report .= "|"; $isActive = odbc_result($res,"IsActive"); if ($isActive){ $report .= "Yes"; }else{ $report .= "No"; } $report .= "|"; $comment = odbc_result($res,"UserComment"); if (!$comment){ $comment = ""; } $report .= $comment; $report .= "\n"; } }else{ $error = odbc_errormsg($this->db_conn); $error = str_replace('Unknown error;\n', '', $error); } return array( array('tblReportFamilyStatsAnalysts', $report, $error) , array() ); } /* Added by QLJ 19-Jan-2010 */ function creatingSearchPattern($aPattern){ if ( stripos($aPattern, '*') == false ){ $arrPattern = array( $aPattern . "%" ); }else{ $arrPattern = explode('*', $aPattern); if ( $arrPattern[ count($arrPattern) - 1 ] == '' ){ array_pop($arrPattern); } for ($i = 0; $i < count($arrPattern); $i++){ if ($i == 0){ $arrPattern[$i] .= "%"; }else{ $arrPattern[$i] = "%" . $arrPattern[$i] . "%"; } } } return $arrPattern; } /* Added by QLJ 18-Jan-2010 */ function getReportFamilySearch($aPattern){ // Deconstructing pattern $arrPattern = $this->creatingSearchPattern($aPattern); // Building each shard into a where-clause $where = ""; for ($i = 0; $i < count($arrPattern); $i++){ if ($where){ $where .= " and "; } $search = $arrPattern[$i]; $where .= "\"CsFamily\" ilike '$search'"; } if ($where){ $where = " where $where"; } $query = "select DISTINCT(\"CsFamily\") from cs_malware_names $where order by 1 limit 200"; $res = odbc_exec($this->db_conn, $query); $error = ''; $report = ''; if ($res){ while ( odbc_fetch_row($res) ){ $report.= odbc_result($res, "CsFamily"); $report.= "\n"; } }else{ $error = odbc_errormsg($this->db_conn); $error = str_replace('Unknown error;\n', '', $error); } return array('tblReportFamilySearch', $report, $error); } /* Added by QLJ 13-Jan-2010 */ function setTrueOrFalse($aValue){ $retVal = 'false'; if ($aValue === '1' || $aValue === 1 || $aValue === 'true' || $aValue === true){ $retVal = 'true'; } return $retVal; } function editAnalystPerFamilyData($aID, $aType, $aValue){ // Get existing isActive $query = "select *, \"Comment\"::varchar as \"AnalystComment\" from analyst_cs_families where \"AnalystCsFamilyId\" = $aID"; $res = odbc_exec($this->db_conn, $query); $error = ''; if ($res){ $success = false; if ( odbc_fetch_row($res) ){ $isActive = $this->setTrueOrFalse( odbc_result($res, "IsActive") ); $comment = odbc_result($res, "AnalystComment"); $latestDueDate = odbc_result($res, "LatestDueDate"); $isPoly = $this->setTrueOrFalse( odbc_result($res, "IsPoly") ); $needsEmulator = $this->setTrueOrFalse( odbc_result($res, "NeedsEmulator") ); $needsDisassembler = $this->setTrueOrFalse( odbc_result($res, "NeedsDisassembler") ); $needsPackerSupport = $this->setTrueOrFalse( odbc_result($res, "PackerSupportNeeded") ); switch ($aType){ case 'comment': $comment = $aValue; break; case 'latestdue': $latestDueDate = $aValue; break; case 'isactive': $isActive = $this->setTrueOrFalse( $aValue ); break; case 'ispoly': $isPoly = $this->setTrueOrFalse( $aValue ); break; case 'needsemulator': $needsEmulator = $this->setTrueOrFalse( $aValue ); break; case 'needsdisassembler': $needsDisassembler = $this->setTrueOrFalse( $aValue ); break; case 'packersupport': $needsPackerSupport = $this->setTrueOrFalse( $aValue ); break; } if ($latestDueDate){ $latestDueDate = "'$latestDueDate'"; }else{ $latestDueDate = 'null'; } odbc_free_result($res); $query = "select \"mis\".\"analyst_cs_families_upsert\"( $aID::integer" . ", null" . ", null" . ", $isActive::boolean" . ", '$comment'::text" . ", $latestDueDate::date" . ", $isPoly::boolean" . ", $needsEmulator::boolean" . ", $needsDisassembler::boolean" . ", $needsPackerSupport::boolean" . ")"; $res = odbc_exec($this->db_conn, $query); if ($res){ if ( odbc_fetch_row($res) ){ $success = odbc_result($res,"mis"."analyst_cs_families_upsert"); } } } } if (!$success){ $error = odbc_errormsg($this->db_conn); $error = str_replace('Unknown error;\n', '', $error); } return array($success, $error); } /* Added by QLJ 11-Jan-2010 */ public function familyIntellisense($aStub){ $query = "select distinct(\"CsFamily\") as familyname " . "from cs_malware_names " . "where \"CsFamily\" ilike '$aStub%' " . "order by 1 asc " . "limit 50"; $query = "select family_name as familyname from \"lookups\".\"families\" where \"permitted\" and family_name ilike '$aStub%' order by 1 asc limit 100"; $res = odbc_exec($this->db_conn, $query); $arrData = array(); while( odbc_fetch_row($res) ){ array_push( $arrData, odbc_result($res,"familyname") ); } return $arrData; } /* Added by QLJ 11-Jan-2010 */ public function getReportAnalystsPerFamily($aFamilyName){ $query = "SELECT \"AnalystCsFamilyId\"" . ", \"CsVendorName\"" . ", \"AnalystFirstName\"" . ", \"AnalystLastName\"" . ", \"AssignedTimestamp\"" . ", \"UnAssignedTimestamp\"" . ", \"IsActive\"" . ", \"Comment\"::varchar " . "from mis.view_analyst_cs_families " . "where \"CsFamilyName\" = '$aFamilyName'"; $res = odbc_exec($this->db_conn, $query); $report = ''; while( odbc_fetch_row($res) ) { $report .= odbc_result($res,"AnalystCsFamilyId"); $report .= "|"; $name = ""; $firstname = odbc_result($res,"AnalystFirstName"); $surname = odbc_result($res,"AnalystLastName"); if ($firstname){ $name = $firstname; } if ($surname){ if ($name){ $name .= " $surname"; }else{ $name = $surname; } } $report .= $name; $report .= "|"; $timestamp = odbc_result($res,"AssignedTimestamp"); if ($timestamp){ $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) ); }else{ $timestamp = ""; } $report .= $timestamp; $report .= "|"; $timestamp = odbc_result($res,"UnAssignedTimestamp"); if ($timestamp){ $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) ); }else{ $timestamp = ""; } $report .= $timestamp; $report .= "|"; $isActive = odbc_result($res,"IsActive"); if ($isActive){ $report .= "Yes"; }else{ $report .= "No"; } $report .= "|"; $comment = odbc_result($res,"Comment"); if (!$comment){ $comment = ""; } $report .= $comment; $report .= "\n"; } return $report; } /* Added by QLJ 11-Jan-2009 */ public function isFamilyAssignedToSomeone($aFamilyName){ $query = "select \"AnalystUserName\"" . ", \"AnalystFirstName\"" . ", \"AnalystLastName\" " . "FROM \"mis.view_analyst_cs_families\" " . "WHERE \"CsFamilyName\" = '$aFamilyName' " . "AND \"UnAssignedTimestamp\" IS NULL"; $res = odbc_exec($this->db_conn, $query); $userName = ""; $firstName = ""; $lastName = ""; $success = false; if( odbc_fetch_row($res) ){ $success = true; $userName = odbc_result($res, "AnalystUserName"); $firstName = odbc_result($res, "AnalystFirstName"); $lastName = odbc_result($res, "AnalystLastName"); } return array($success, $userName, $firstName, $lastName); } /* Added by QLJ 08-Jan-2009 */ public function addAssignFamilyToAnalyst($aFamilyName, $aAnalystName){ $query = "select \"mis\".\"analyst_cs_families_upsert\"( null::integer" . ", '$aFamilyName'::varchar" . ", '$aAnalystName'::varchar" . ", true::boolean" . ", null::text" . ", null::date" . ", false::boolean" . ", false::boolean" . ", false::boolean" . ", false::boolean" . ")"; $res = odbc_exec($this->db_conn, $query); $error = ""; $success = false; if ($res){ if ( odbc_fetch_row($res) ){ $success = odbc_result($res,"mis"."analyst_cs_families_upsert"); } }else{ $error = odbc_errormsg($this->db_conn); $error = str_replace('Unknown error;\n', '', $error); } return array($success, $error); } /* Added by QLJ 08-Jan-2009 */ public function editAssignFamilyToAnalyst($aFamilyName, $aAnalystName){} /* Added by QLJ 10-Dec-2009 */ public function getReport0($aFrom, $aTo, $aPeriod) { $fromTime = strtotime($aFrom); $fromTime = date("Y-m-d H:i:s", $fromTime); $toTime = strtotime($aTo); $toTime = date("Y-m-d H:i:s", $toTime); $query = "select mal.\"CsFamily\" as \"Family\"" . ", count( DISTINCT(sam.\"SampleId\") ) as \"TotalSamples\"" . ", count(case when sig.\"SigFormatId\" = 11 then true else null end) AS \"DetectedByFirstSigs\"" . ", count(case when sig.\"SigFormatId\" = 2 OR sig.\"SigFormatId\" = 7 then sig.\"SigId\" else null end) AS \"DetectedByPeSigs\"" . ", count(distinct res.\"SampleId\")-count(case when sig.\"SigFormatId\" = 2 OR sig.\"SigFormatId\" = 7 then sig.\"SigId\" else null end) AS \"NotDetectedByPeSigs\"" . ", count(distinct(case when sig.\"SigFormatId\" = 2 OR sig.\"SigFormatId\" = 7 then sig.\"SigId\" else null end)) AS \"PeSigs\" " . "from cs_vendors ven " . "inner join cs_results res on res.\"CsVendorId\" = ven.\"CsVendorId\" " . "inner join \"samples\" sam on sam.\"SampleId\" = res.\"SampleId\" " . "inner join \"samples_sigs\" samsig on samsig.\"SampleId\" = res.\"SampleId\" " . "inner join \"sigs\" sig on sig.\"SigId\" = samsig.\"SigId\" " . "inner join cs_malware_names mal on mal.\"CsMalwareNameId\" = res.\"CsMalwareNameId\" " . "where res.\"DateTimeScanned\" between '$fromTime'::timestamp and '$toTime'::timestamp " . "and ven.\"CsVendorId\" = 8 " . "group by mal.\"CsFamily\""; $fileName = "Report0_Results_$aPeriod". "_" . date( 'Y_m_d_H_i_s', strtotime('now') ); $res = odbc_exec($this->db_conn, $query); // Creating array to hold queried data $arrData = array(); $numRows = 0; while( odbc_fetch_row($res) ) { $familyName = odbc_result($res,"Family"); $totalSamples = odbc_result($res,"TotalSamples"); $detectedByFirstSigs = odbc_result($res,"DetectedByFirstSigs"); $detectedByPeSigs = odbc_result($res,"DetectedByPeSigs"); $notDetectedByPeSigs = odbc_result($res,"NotDetectedByPeSigs"); $numPeSigs = odbc_result($res,"PeSigs"); array_push( $arrData, array( "Family"=>$familyName , "Total Samples"=>$totalSamples , "Detected By First Sigs"=>$detectedByFirstSigs , "Detected By PE Sigs"=>$detectedByPeSigs , "Not Detected By PE Sigs"=>$notDetectedByPeSigs , "PE Sigs"=>$numPeSigs ) ); $numRows++; } if ($numRows > 0){ // Exporting data to xl spreadsheet /* This uses the class made available in excel.php Read documentation for it in sub-folder ./xls */ $export_file = "xlsfile://home/camdpams_www/reports/$fileName.xls"; $fp = fopen($export_file, "wb"); if (!is_resource($fp)) { die("Cannot open $export_file"); } fwrite( $fp, serialize($arrData) ); fclose($fp); return "fileName = $fileName"; }else{ return "fileName = empty"; } } /* Added by QLJ 21-Dec-2009 */ public function getReportSamplesVsSigs2($aFrom, $aTo, $aPeriod) { $fromTime = strtotime($aFrom); $fromTime = date("Y-m-d H:i:s", $fromTime); $toTime = strtotime($aTo); $toTime = date("Y-m-d H:i:s", $toTime); $query = "SELECT * FROM \"mis\".\"get_report_samples_cs_vs_sigs2\"('$fromTime'::timestamp, '$toTime'::timestamp, 8) " . "AS ( \"CsVendorName\" varchar" . ", \"MalwareFamily\" varchar" . ", \"SamplesCnt\" bigint" . ", \"DetectedByPeSigs\" bigint" . ", \"DetectedByGenericSigs\" bigint" . ", \"DetectedByFirstSigs\" bigint" . ", \"NotDetectedByPeAndGenericSigs\" bigint" . ", \"PercentageDetection\" double precision" . ", \"TotalPeSigs\" bigint" . ", \"TotalGenericSigs\" bigint" . ")"; $period = str_replace( " ", "_", $aPeriod ); $fileName = "AllFamiliesReport_" . $period . "_" . str_replace( " ", "_", $_SERVER['PHP_AUTH_USER'] ) . "_" . date( 'Y_m_d_H_i_s', strtotime('now') ); $res = odbc_exec($this->db_conn, $query); // Creating array to hold queried data $arrData = array(); $numRows = 0; while( odbc_fetch_row($res) ) { $vendorName = odbc_result($res, "CsVendorName"); $familyName = odbc_result($res,"MalwareFamily"); $totalSamples = odbc_result($res,"SamplesCnt"); $detectedByFirstSigs = odbc_result($res,"DetectedByFirstSigs"); $detectedByPeSigs = odbc_result($res,"DetectedByPeSigs"); $detectedByGenericSigs = odbc_result($res,"DetectedByGenericSigs"); $notDetectedByPeGenericSigs = odbc_result($res,"NotDetectedByPeAndGenericSigs"); $percentDetected = odbc_result($res,"PercentageDetection"); $totalPeSigs = odbc_result($res,"TotalPeSigs"); $totalGenericSigs = odbc_result($res,"TotalGenericSigs"); array_push( $arrData, array( "VendorName"=>$vendorName , "MalwareFamily"=>$familyName , "Total Samples"=>$totalSamples , "Detected By First Sigs"=>$detectedByFirstSigs , "Detected By PE Sigs"=>$detectedByPeSigs , "Detected By Generic Sigs"=>$detectedByGenericSigs , "Not Detected By PE or Generic Sigs"=>$notDetectedByPeGenericSigs , "Percentage Detection"=>$percentDetected , "Total PE Sigs"=>$totalPeSigs , "Total Generic Sigs"=>$totalGenericSigs ) ); $numRows++; } $retVal1 = "fileName = empty"; $folder = "reports_ondemand/AllFamilies"; $retVal2 = $folder; if ($numRows > 0){ $result = $this->writeToXL("$folder/$fileName.xls", $arrData); if ($result){ $retVal1 = "fileName = $fileName"; } } return array($retVal1, $retVal2); } /* Added by QLJ 23-Dec-2009 */ public function getReportSha1PerFamily($aFrom, $aTo, $aFamily, $aDetectedBy, $aPeriod, $aIncludeVariant) { $fromTime = strtotime($aFrom); $fromTime = date("Y-m-d H:i:s", $fromTime); $toTime = strtotime($aTo); $toTime = date("Y-m-d H:i:s", $toTime); $query = "SELECT * FROM \"mis\".\"get_report_samples_list_sha1_in_family\"('$fromTime'::timestamp" . ", '$toTime'::timestamp" . ", '$aFamily'::varchar" . ", '$aDetectedBy'::varchar ) " . "AS ( \"sha1\" varchar" . ", \"variant\" varchar" . ")"; $familyName = str_replace( " ", "_", $aFamily ); $familyName = str_replace( ".", "_", $familyName ); $period = str_replace( " ", "_", $aPeriod ); $fileName = $familyName . "_Sha1PerFamilyReport_" . $aDetectedBy . "_" . $period. "_" . str_replace( " ", "_", $_SERVER['PHP_AUTH_USER'] ) . "_" . date( 'Y_m_d_H_i_s', strtotime('now') ); $res = odbc_exec($this->db_conn, $query); // Creating array to hold queried data $arrData = array(); $numRows = 0; while( odbc_fetch_row($res) ) { $sha1 = odbc_result($res, "sha1"); $variant = odbc_result($res, "variant"); if ($aIncludeVariant){ $arrResult = array( "Sha1"=>$sha1, "Variant"=>$variant ); }else{ $arrResult = array( "Sha1"=>$sha1 ); } array_push( $arrData, $arrResult ); $numRows++; } $retVal1 = "fileName = empty"; $folder = "reports_ondemand/FamilySpecific"; $retVal2 = $folder; if ($numRows > 0){ $result = $this->writeToXL("$folder/$fileName.xls", $arrData); if ($result){ $retVal1 = "fileName = $fileName"; } } return array($retVal1, $retVal2); } /* Added by QLJ 14-Jan-2010 */ function writeToXL($aPath, $aArrData){ $export_file = "xlsfile://home/camdpams_www/$aPath"; $fp = fopen($export_file, "wb"); $retVal = false; if ( !is_resource($fp) ){ die("Cannot open $export_file"); } fwrite( $fp, serialize($aArrData) ); fclose($fp); if ( file_exists("//home/camdpams_www/$aPath") ){ $retVal = true; } return $retVal; } /* Added by QLJ 05-Jan-2010 */ public function getReportSha1PerVariant($aFrom, $aTo, $aVariant, $aDetectedBy, $aPeriod) { $fromTime = strtotime($aFrom); $fromTime = date("Y-m-d H:i:s", $fromTime); $toTime = strtotime($aTo); $toTime = date("Y-m-d H:i:s", $toTime); $query = "SELECT * FROM \"mis\".\"get_report_samples_list_sha1_in_variant\"('$fromTime'::timestamp" . ", '$toTime'::timestamp" . ", '$aVariant'::varchar" . ", '$aDetectedBy'::varchar) " . "AS ( \"sha1\" varchar" . ")"; $lastPeriod = strripos( $aVariant, "." ); $variantName = str_replace( " ", "_", $aVariant ); $variantName = str_replace( ".", "_", $variantName ); $familyName = substr($variantName, 0, $lastPeriod + 1); $period = str_replace( " ", "_", $aPeriod ); $fileName = $variantName . "_Sha1PerVariantReport_" . $aDetectedBy . "_" . $period . "_" . str_replace( " ", "_", $_SERVER['PHP_AUTH_USER'] ) . "_" . date( 'Y_m_d_H_i_s', strtotime('now') ); $res = odbc_exec($this->db_conn, $query); // Creating array to hold queried data $arrData = array(); $numRows = 0; while( odbc_fetch_row($res) ) { $sha1 = odbc_result($res, "sha1"); $arrResult = array( "Sha1"=>$sha1 ); array_push( $arrData, $arrResult ); $numRows++; } $retVal1 = "fileName = empty"; $folder = "reports_ondemand/VariantSpecific"; $retVal2 = $folder; if ($numRows > 0){ $result = $this->writeToXL("$folder/$fileName.xls", $arrData); if ($result){ $retVal1 = "fileName = $fileName"; } } return array($retVal1, $retVal2); } /* Added by QLJ 06-Jan-2010 */ public function getAnalystNames(){ $query = "select * from \"mis\".\"view_analysts\" order by \"AnalystFirstName\", \"AnalystLastName\""; $res = odbc_exec($this->db_conn, $query); $stream = "["; $bof = true; while( odbc_fetch_row($res) ) { if ($bof){ $bof = false; }else{ $stream .= ","; } $id = odbc_result($res, "UserId"); $firstname = odbc_result($res,"AnalystFirstName"); $surname = odbc_result($res,"AnalystLastName"); $user = odbc_result($res,"UserName"); if ( $firstname ){ $name = $firstname; if ( $surname ){ $name .= " " . $surname; } } $stream .= "['$user', '$name']"; } $stream .= "]"; return $stream; } /* Added by QLJ 07-Jan-2010 */ public function getReportFamiliesPerAnalyst($aName){ trigger_error("getReportFamiliesPerAnalyst() START", E_USER_WARNING); $query = "SELECT *" . ", \"Comment\"::varchar as \"AnalystComment\" " . "from mis.view_analyst_cs_families " . "where \"AnalystUserName\" = '$aName'"; trigger_error("getReportFamiliesPerAnalyst() 1: query = $query", E_USER_WARNING); $res = odbc_exec($this->db_conn, $query); $report = ''; while( odbc_fetch_row($res) ) { $report .= odbc_result($res,"AnalystCsFamilyId"); $report .= "|"; $report .= odbc_result($res,"CsFamilyName"); $report .= "|"; $report .= odbc_result($res,"CsVendorName"); $report .= "|"; $timestamp = odbc_result($res,"AssignedTimestamp"); if ($timestamp){ $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) ); }else{ $timestamp = ""; } $report .= $timestamp; $report .= "|"; $timestamp = odbc_result($res,"UnAssignedTimestamp"); if ($timestamp){ $timestamp = date( 'd/m/Y H:i:s', strtotime($timestamp) ); }else{ $timestamp = ""; } $report .= $timestamp; $report .= "|"; $isActive = odbc_result($res,"IsActive"); if ($isActive){ $report .= "Yes"; }else{ $report .= "No"; } $report .= "|"; $comment = odbc_result($res,"AnalystComment"); if (!$comment){ $comment = ""; } $report .= $comment; $report .= "|"; $date = odbc_result($res,"LatestDueDate"); if ( empty($date) || $date === NULL ){ $date = "None"; }else{ $date = date( 'd/m/Y', strtotime($date) ); } $report .= $date; $report .= "|"; $value = odbc_result($res,"IsPoly"); if ($value){ $report .= "Yes"; }else{ $report .= "No"; } $report .= "|"; $value = odbc_result($res,"NeedsEmulator"); if ($value){ $report .= "Yes"; }else{ $report .= "No"; } $report .= "|"; $value = odbc_result($res,"NeedsDisassembler"); if ($value){ $report .= "Yes"; }else{ $report .= "No"; } $report .= "|"; $value = odbc_result($res,"PackerSupportNeeded"); if ($value){ $report .= "Yes"; }else{ $report .= "No"; } $report .= "\n"; } trigger_error("getReportFamiliesPerAnalyst() STOP", E_USER_WARNING); return $report; } public function getPeriodActive_user($from, $to){ $report = ''; $d1= date("z",strtotime($from)); if(date("Y",strtotime($to))==date("Y",strtotime($from))){$d2=date("z",strtotime($to));} else{$d2 = (date("Y",strtotime($to))-date("Y",strtotime($from)))*365+date("z",strtotime($to));} $k=$d2-$d1; $query="select sum(case when \"CisVer\"='updates40' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u40\", sum(case when \"CisVer\"='updates313' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u313\", sum(case when \"CisVer\"='updates314' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u314\", sum(case when \"CisVer\"='updates50' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u50\", sum(case when \"CisVer\"='updates51' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u51\", sum(case when \"CisVer\"='updates53' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u53\", sum(case when \"CisVer\"='updates54' then \"ActiveUsersCnt\" else null end)::integer/$k as \"u54\", sum(case when \"CisVer\"='updatesgb' then \"ActiveUsersCnt\" else null end)::integer/$k as \"GeekBuddy\", sum(case when \"CisVer\"='updatesmac' then \"ActiveUsersCnt\" else null end)::integer/$k as \"CAV-MAC\" from cis_users_stats where \"Ts\" between '$from' and '$to'"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)){ $x= odbc_result($res,"u50") + odbc_result($res,"u40") + odbc_result($res,"u314") + odbc_result($res,"u313"); $x += odbc_result($res,"u51") + odbc_result($res,"u53") + odbc_result($res,"u54") + odbc_result($res,"GeekBuddy"); $x += (odbc_result($res,"CAV-MAC")==NULL)?0:odbc_result($res,"CAV-MAC"); $report .= (strtotime($to)*1000); $report .= "|"; $report .= $x; $report .= "|"; (odbc_result($res,"u54") == NUll)?$report .=0:$report .=odbc_result($res,"u54"); $report .= "|"; (odbc_result($res,"u53") == NUll)?$report .=0:$report .=odbc_result($res,"u53"); $report .= "|"; (odbc_result($res,"u51") == NUll)?$report .=0:$report .=odbc_result($res,"u51"); $report .= "|"; (odbc_result($res,"u50") == NUll)?$report .=0:$report .=odbc_result($res,"u50"); $report .= "|"; (odbc_result($res,"u40") == NULL)?$report .=0:$report .= odbc_result($res,"u40"); $report .= "|"; (odbc_result($res,"u314")== NULL)?$report .= 0:$report .= odbc_result($res,"u314"); $report .= "|"; (odbc_result($res,"u313")== NULL)?$report .= 0:$report .= odbc_result($res,"u313"); $report .= "|"; (odbc_result($res,"GeekBuddy") == NUll)?$report .=0:$report .=odbc_result($res,"GeekBuddy"); $report .= "|"; (odbc_result($res,"CAV-MAC") == NUll)?$report .=0:$report .=odbc_result($res,"CAV-MAC"); $report .= "\n"; } return $report; } public function getWeekActive_user($from, $to){ $report = ''; $query="select sum(case when \"CisVer\"='updates40' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u40\", sum(case when \"CisVer\"='updates313' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u313\", sum(case when \"CisVer\"='updates314' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u314\", sum(case when \"CisVer\"='updates50' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u50\", sum(case when \"CisVer\"='updates51' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u51\", sum(case when \"CisVer\"='updates53' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u53\", sum(case when \"CisVer\"='updates54' then \"ActiveUsersCnt\" else null end)::integer/7 as \"u54\", sum(case when \"CisVer\"='updatesgb' then \"ActiveUsersCnt\" else null end)::integer/7 as \"GeekBuddy\", sum(case when \"CisVer\"='updatesmac' then \"ActiveUsersCnt\" else null end)::integer/7 as \"CAV-MAC\" from cis_users_stats where \"Ts\" between '$from' and '$to'"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)){ $x= odbc_result($res,"u50") + odbc_result($res,"u40") + odbc_result($res,"u314") + odbc_result($res,"u313"); $report .= (strtotime($to)*1000); $report .= "|"; $report .= $x; $report .= "|"; (odbc_result($res,"u54") == NUll)?$report .=0:$report .=odbc_result($res,"u54"); $report .= "|"; (odbc_result($res,"u53") == NUll)?$report .=0:$report .=odbc_result($res,"u53"); $report .= "|"; (odbc_result($res,"u51") == NUll)?$report .=0:$report .=odbc_result($res,"u51"); $report .= "|"; (odbc_result($res,"u50") == NUll)?$report .=0:$report .=odbc_result($res,"u50"); $report .= "|"; (odbc_result($res,"u40") == NULL)?$report .=0:$report .= odbc_result($res,"u40"); $report .= "|"; (odbc_result($res,"u314")== NULL)?$report .= 0:$report .= odbc_result($res,"u314"); $report .= "|"; (odbc_result($res,"u313")== NULL)?$report .= 0:$report .= odbc_result($res,"u313"); $report .= "|"; (odbc_result($res,"GeekBuddy") == NUll)?$report .=0:$report .=odbc_result($res,"GeekBuddy"); $report .= "|"; (odbc_result($res,"CAV-MAC") == NUll)?$report .=0:$report .=odbc_result($res,"CAV-MAC"); $report .= "\n"; } return $report; } public function getWeekGraph_for_av_active_user($from, $to){ $report = ''; if(date("m",strtotime($from))==1){$w_start=1;} else{$w_start=date("W",strtotime($from));} $w_end=date("W",strtotime($to)); $k = $w_end -$w_start; $dm =1-date("w",strtotime($from)); for($j=0;$j<=$k;$j++){ $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+$dm+$j, date("Y",strtotime($from)))); $w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($w1)) , date("d",strtotime($w1))+6, date("Y",strtotime($w1)))); $dm=$dm+6; $report .= $this->getWeekActive_user($w1,$w2); } $report .= ((strtotime($w2))*1000) + 21600000; $report .= "\n"; return $report; } public function get_sig_gen_analist_data($from,$to){ $report = ''; $query = 'SELECT "SigsSafeInserted" FROM "mis"."get_report_users_activity"(\'' . $from . '\',\'' . $to . '\') AS ("UserName" varchar, "LabName" varchar, "SamplesSubmitted" integer, "SamplesLocked" integer, "SigsInsertedTotal" integer, "SigsUpdatedTotal" integer, "SigsDeletedTotal" integer, "SigsRevertedTotal" integer, "SigsFirstNewInserted" integer, "SigsPeInserted" integer, "SigsSafeInserted" integer, "SigsCommonInserted" integer, "SigsMemoryInserted" integer, "SigsGenericInserted" integer, "DetectRoutinesInserted" integer,"TotalSamplesDetectedByPESigns" integer) where "UserName" = \'dirty_sig_gen\''; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= (strtotime($from)*1000); $report .= "|"; $report .= odbc_result($res,"SigsSafeInserted"); $report .= "\n"; } return $report; } public function getGraph_for_sig_genan_analyst($from, $to, $period){ $report = ''; $i =0; $d1=0; $d2=0; $j=0; switch($period){ case 'daily': $d1=date("z",strtotime($from)); if(date("Y",strtotime($to))==date("Y",strtotime($from))){$d2=date("z",strtotime($to));} else{$d2 = 365+date("z",strtotime($to));} for($j=$d1;$j<=$d2;$j++){ $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+$i, date("Y",strtotime($from)))); $i++; $w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+$i, date("Y",strtotime($from)))); $report .= $this->get_sig_gen_analist_data($w1,$w2); } break; case 'weekly': if(date("m",strtotime($from))==1){$d1=1;} else{$d1=date("W",strtotime($from));} $n= date("w",strtotime($from)); if(date("Y",strtotime($to))>date("Y",strtotime($from))){$d2=42+date("W",strtotime($to));} else{$d2=date("W",strtotime($to));} for($j=$d1;$j<=$d2;$j++){ $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+$i, date("Y",strtotime($from)))); $i+=6; if($j==$d1){$w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+6-$n, date("Y",strtotime($from))));} else{$w2 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+$i, date("Y",strtotime($from))));} $report .= $this->get_sig_gen_analist_data($w1,$w2); } break; case 'monthly': $m1=date("m",strtotime($from)); $m2=date("m",strtotime($to)); for($i=$m1;$i<$m2;$i++){ if($i == $m1){$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , date("d",strtotime($from)), date("Y",strtotime($from))));} else{$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from))));} $j++; $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from)))); $report .= $this->get_sig_gen_analist_data($w1,$w2); } if(date("d",strtotime($to))>1){ $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , 01, date("Y",strtotime($to)))); $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->get_sig_gen_analist_data($w1,$w2); } break; case 'quarter': $m1=date("n",strtotime($from)); $m2=date("n",strtotime($to)); if($m2>$m1){ for($i=0;$i1 && $m1<3){$j=3-$m1;} elseif ($m1==3||$m1==6||$m1==9){$j=3;} elseif ($m1>3 && $m1<6){ $j=6-$m1;} elseif ($m1>6 && $m1<9){$j=9-$m1;} elseif ($m1>9 && $m1<12){$j=12-$m1;} } else{$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from)))); $j+=3;} if($i==ceil(($m2-$m1)/3)-1){$w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j, date("d",strtotime($to)), date("Y",strtotime($from))));} else{$w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from))));} $report .= $this->get_sig_gen_analist_data($w1,$w2); } if(date("d",strtotime($to))>1 || date("m",strtotime($to))<12) { if(date("m",strtotime($to))!=6 && date("m",strtotime($to))!=3 && date("m",strtotime($to))!=9 && date("m",strtotime($to))!=12){ $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to1)) , 01, date("Y",strtotime($to1)))); $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->get_sig_gen_analist_data($w1,$w2); } } } else{ $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->get_sig_gen_analist_data($w1,$w2); } break; case 'year': $m1=date("Y",strtotime($from)); $m2=date("Y",strtotime($to)); for($i=$m1;$i<$m2;$i++){ if($i == $m1){$w1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from))+$j));} else{$w1= date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($from))+$j));} $j++; $w2= date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($from))+$j)); $report .= $this->get_sig_gen_analist_data($w1,$w2); } if(date("d",strtotime($to))>1|| date("m",strtotime($to))>1){ $w1 = date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($to)))); $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->get_sig_gen_analist_data($w1,$w2); } break; case 'd_of_week': $m1=date("w",strtotime($from)); $m2=date("w",strtotime($to)); for($i=$m1;$i<$m2;$i++){ $w1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); $j++; $w2= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+$j, date("Y",strtotime($from)))); $report .= $this->get_sig_gen_analist_data($w1,$w2); } break; case 'h_of_day': $m1=date("H",strtotime($from)); $m2=date("H",strtotime($to)); for($i=$m1;$i<=$m2;$i++){ if($i==$m1){ $w1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($from)), 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from))));} else{ $w1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); } $j++; if($i==$m2-1){ $w2= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($to)), 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); } else{ $w2= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); } $report .= $this->get_sig_gen_analist_data($w1,$w2); } break; } $report .= ((strtotime($w2))*1000) + 21600000; $report .= "\n"; return $report; } public function getMonthGraph_for_av_active_user($from, $to){ $report = ''; $j = 0; $m1=date("m",strtotime($from)); $m2=date("m",strtotime($to)); for($i=$m1;$i<$m2;$i++){ if($i == $m1){$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , date("d",strtotime($from)), date("Y",strtotime($from))));} else{$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from))));} $j++; $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from)))); $report .= $this->getPeriodActive_user($f1,$to1); } if(date("d",strtotime($to))>1){ $f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , 01, date("Y",strtotime($to)))); $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->getPeriodActive_user($f1,$to1); } $report .= ((strtotime($to1))*1000) + 21600000; $report .= "\n"; return $report; } public function getQuarter_for_av_active_user($from, $to){ $report = ''; $j = 0; $m1=date("n",strtotime($from)); $m2=date("n",strtotime($to)); if($m2>$m1){ for($i=0;$i1 && $m1<3){$j=3-$m1;} elseif ($m1==3||$m1==6||$m1==9){$j=3;} elseif ($m1>3 && $m1<6){ $j=6-$m1;} elseif ($m1>6 && $m1<9){$j=9-$m1;} elseif ($m1>9 && $m1<12){$j=12-$m1;} } else{$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from)))); $j+=3; } if($i==ceil(($m2-$m1)/3)-1){$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , date("d",strtotime($to)), date("Y",strtotime($from))));} else{$to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from))+$j , 01, date("Y",strtotime($from))));} $report .= $this->getPeriodActive_user($f1,$to1); } if(date("d",strtotime($to))>1 || date("m",strtotime($to))<12) { if(date("m",strtotime($to))!=6 && date("m",strtotime($to))!=3 && date("m",strtotime($to))!=9 && date("m",strtotime($to))!=12){ $f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to1)) , 01, date("Y",strtotime($to1)))); $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->getPeriodActive_user($f1,$to1); } } } else{ $f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->getPeriodActive_user($f1,$to1); } $report .= ((strtotime($to1))*1000) + 21600000; $report .= "\n"; return $report; } public function getPerHourOfDay_for_av_active_user($from, $to){ $report = ''; $j = 0; $m1=date("H",strtotime($from)); $m2=date("H",strtotime($to)); for($i=$m1;$i<=$m2;$i++){ if($i==$m1){ $f1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($from)), 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from))));} else{ $f1 = date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); } $j++; if($i==$m2-1){ $to1= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, date("i",strtotime($to)), 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); } else{ $to1= date("Y-m-d H:i",mktime(date("H",strtotime($from))+$j, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); } $report .= $this->getPeriodActive_user($f1,$to1); } $report .= ((strtotime($to1))*1000) + 21600000; $report .= "\n"; return $report; } public function getPerDayOfWeek_for_av_active_user($from, $to){ $report = ''; $j = 0; $m1=date("w",strtotime($from)); $m2=date("w",strtotime($to)); for($i=$m1;$i<$m2;$i++){ $f1 = date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from)))); $j++; $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from))+$j, date("Y",strtotime($from)))); $report .= $this->getPeriodActive_user($f1,$to1); } $report .= ((strtotime($to1))*1000) + 21600000; $report .= "\n"; return $report; } public function getYear_for_av_active_user($from, $to){ $report = ''; $j = 0; $m1=date("Y",strtotime($from)); $m2=date("Y",strtotime($to)); for($i=$m1;$i<$m2;$i++){ if($i == $m1){$f1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($from)) , date("d",strtotime($from)), date("Y",strtotime($from))+$j));} else{$f1= date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($from))+$j));} $j++; $to1= date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($from))+$j)); $report .= $this->getPeriodActive_user($f1,$to1); } if(date("d",strtotime($to))>1|| date("m",strtotime($to))>1){ $f1 = date("Y-m-d",mktime(0, 0, 0, 01 , 01, date("Y",strtotime($to)))); $to1= date("Y-m-d",mktime(0, 0, 0, date("m",strtotime($to)) , date("d",strtotime($to)), date("Y",strtotime($to)))); $report .= $this->getPeriodActive_user($f1,$to1); } $report .= ((strtotime($to1))*1000) + 21600000; $report .= "\n"; return $report; } public function getDayGraph_for_av_active_user($from, $to) { $report = ''; $j = 0;$x=$x1=$x2=$x3=$x4=$x5=$x6=$x7=$dst=$tmp=''; $query = "select \"Ts\",array_accum((\"CisVer\"||':'||\"ActiveUsersCnt\")) as \"UserCnt\" from cis_users_stats where \"Ts\" between '$from' and '$to' group by \"Ts\" order by \"Ts\""; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $tmp = str_replace("{","",odbc_result($res,"UserCnt")); $tmp = str_replace("}","",$tmp); $arr = split(",",$tmp); //$arr = split(",",odbc_result($res,"UserCnt")); $report .= (strtotime(odbc_result($res,"Ts"))*1000); for($j = 0;$jdb_conn, $query); while(odbc_fetch_row($res)) { // $report .= (strtotime($from) * 1000 + $i*86400000);//86400000 = 24*60*60*1000 $report .= (strtotime(odbc_result($res,"DayDate"))*1000); $report .= "|"; $report .= odbc_result($res,"DetectedByCavOnly"); $report .= "|"; $report .= odbc_result($res,"DetectedByCsOnly"); $report .= "\n"; $i++; } return $report; } /* Added by Oleg Krotenko 03-Feb-2010 */ public function getMonthGraph_for_CAV($from,$to) { $report = ''; $i = 0; // $query = 'select * from housekeeping.get_report_year_cavvscs('. date('d-F-Y', strtotime($from)) .') as (MN date,CAV integer, CS integer);' ; $query = 'select * from mis.get_report_year_cavvscs( \'' . date('d-F-Y', strtotime($from)) . '\') as (MN date,CAV integer, CS integer);'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= (strtotime(odbc_result($res,"MN"))*1000); $report .= "|"; $report .= odbc_result($res,"CAV"); $report .= "|"; $report .= odbc_result($res,"CS"); $report .= "\n"; } return $report; } public function getReportSigsSummary($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_sigs_summary"(\'' . $from . '\',\'' . $to . '\') as ("ParamName" varchar, "Value" varchar)'; $res = odbc_exec($this->db_conn, $query); $success = false; $error = ''; if ($res){ $success = true; while(odbc_fetch_row($res)) { $report .= odbc_result($res,"ParamName"); $report .= "|"; $report .= odbc_result($res,"Value"); $report .= "\n"; } }else{ $error = odbc_errormsg($this->db_conn); $error = str_replace('Unknown error;\n', '', $error); } return array($success, $report, $error); } public function getReportSigsFormats() { $report = ''; $query = 'SELECT * FROM "mis"."get_report_sigs_formats"() as ("SigFormatName" varchar, "TotalCnt" integer, "ExistingCnt" integer, "DeletedCnt" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"SigFormatName"); $report .= "|"; $report .= odbc_result($res,"TotalCnt"); $report .= "|"; $report .= odbc_result($res,"ExistingCnt"); $report .= "|"; $report .= odbc_result($res,"DeletedCnt"); $report .= "\n"; } return $report; } public function getReportSamplesSummary($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_samples_summary"(\'' . $from . '\',\'' . $to . '\') as ("ParamName" varchar, "Value" varchar)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"ParamName"); $report .= "|"; $report .= odbc_result($res,"Value"); $report .= "\n"; } return $report; } public function getReportSamplesFormats($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_samples_formats"(\'' . $from . '\',\'' . $to . '\') as ("SampleFormatName" varchar, "Count" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"SampleFormatName"); $report .= "|"; $report .= odbc_result($res,"Count"); $report .= "\n"; } return $report; } public function cnt_SpellPower_arr($a,$b){ $c = array(); foreach($a as $val){ $c[]=$b[$val]; } $c_u=array_unique($c); return count($c_u); } public function sum_SpellPower_arr($a,$b){ $c = array(); foreach($a as $val){ $c[]=$b[$val]; } return array_sum($c); } public function getSpellPower($from, $to) { $fromdate = strtotime($from); $fromdate = date("Y-m-d", $fromdate); $todate = strtotime($to); $todate = date("Y-m-d", $todate); $report = ''; $query = "select * from mis.get_report_spell_power_direct('$fromdate','$todate') as (\"AnalystName\" character varying,\"LabName\" character varying,\"SigId\" integer,\"MalwareName\" character varying,\"Total\" integer)"; $res = odbc_exec($this->db_conn, $query); $lab=array(); $analist=array(); $sigs = array(); $totals = array(); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"AnalystName"); $report .= "|"; /* switch(odbc_result($res,"AnalystName")){ case 'signexpl': $report .= "SignExplorer"; break; case 'daisy_ai': $report .= "DAISY"; break; default: */ $report .= odbc_result($res,"LabName"); // break; // } $report .= "|"; $report .= odbc_result($res,"SigId"); $report .= "|"; $report .= odbc_result($res,"MalwareName"); $report .= "|"; $report .= odbc_result($res,"Total"); $report .= "\n"; $lab[]=odbc_result($res,"LabName"); $analist[]=odbc_result($res,"AnalystName"); $sigs[]=odbc_result($res,"SigId"); $totals[]=odbc_result($res,"Total"); } $summary=''; $lab_u=array_unique($lab); foreach ($lab_u as $value) { $summary .=$value; $summary .= "|"; $summary.=$this->cnt_SpellPower_arr(array_keys($lab,$value),$analist); $summary .= "|"; $summary.=$this->cnt_SpellPower_arr(array_keys($lab,$value),$sigs); $summary .= "|"; $summary.=$this->sum_SpellPower_arr(array_keys($lab,$value),$totals); $summary .= "\n"; } return array($report,$summary); } public function getSummary($from, $to) { $report = ''; $query = "select * from mis.get_report_lab_summary_direct('$from','$to') as (\"Team\" character varying, \"Analyst Count\" bigint,\"Total Signs\" bigint,\"Total Detection\" bigint)"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"Team"); $report .= "|"; $report .= odbc_result($res,"Analyst Count"); $report .= "|"; $report .= odbc_result($res,"Total Signs"); $report .= "|"; $report .= odbc_result($res,"Total Detection"); $report .= "\n"; } return $report; } public function getReportUsersActivity($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_users_activity"(\'' . $from . '\',\'' . $to . '\') AS ("UserName" varchar, "LabName" varchar, "SamplesSubmitted" integer, "SamplesLocked" integer, "SigsInsertedTotal" integer, "SigsUpdatedTotal" integer, "SigsDeletedTotal" integer, "SigsRevertedTotal" integer, "SigsFirstNewInserted" integer, "SigsPeInserted" integer, "SigsSafeInserted" integer, "SigsCommonInserted" integer, "SigsMemoryInserted" integer, "SigsGenericInserted" integer, "DetectRoutinesInserted" integer,"TotalSamplesDetectedByPESigns" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"UserName"); $report .= "|"; $report .= odbc_result($res,"LabName"); $report .= "|"; $report .= odbc_result($res,"SamplesSubmitted"); $report .= "|"; $report .= odbc_result($res,"SamplesLocked"); $report .= "|"; $report .= odbc_result($res,"SigsInsertedTotal"); $report .= "|"; $report .= odbc_result($res,"SigsUpdatedTotal"); $report .= "|"; $report .= odbc_result($res,"SigsDeletedTotal"); $report .= "|"; $report .= odbc_result($res,"SigsRevertedTotal"); $report .= "|"; $report .= odbc_result($res,"SigsFirstNewInserted"); $report .= "|"; $report .= odbc_result($res,"SigsPeInserted"); $report .= "|"; $report .= odbc_result($res,"SigsSafeInserted"); $report .= "|"; $report .= odbc_result($res,"SigsCommonInserted"); $report .= "|"; $report .= odbc_result($res,"SigsMemoryInserted"); $report .= "|"; $report .= odbc_result($res,"SigsGenericInserted"); $report .= "|"; $report .= odbc_result($res,"DetectRoutinesInserted"); $report .= "|"; $report .= odbc_result($res,"TotalSamplesDetectedByPESigns"); $report .= "\n"; } return $report; } public function getReportComponentsSummary() { $report = ''; $query = 'SELECT * FROM "mis"."view_components" WHERE "IsActive" IS TRUE'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"ComponentName"); $report .= "|"; $report .= odbc_result($res,"ComponentVersion"); $report .= "|"; $report .= date("Y-m-d", strtotime(odbc_result($res,"ComponentVersionDateTime"))); $report .= "|"; $report .= odbc_result($res,"ScanEngineVersion"); $report .= "|"; $report .= odbc_result($res,"SigsBaseVersion"); $report .= "|"; $report .= odbc_result($res,"ComponentTypeShort"); $report .= "|"; $report .= odbc_result($res,"ServerName"); $report .= "|"; $report .= odbc_result($res,"LastAliveAgo"); $report .= "|"; $report .= odbc_result($res,"MaxResponseSeconds"); $report .= "|"; $report .= odbc_result($res,"LastAliveSecondsAgo"); $report .= "\n"; } return $report; } public function getReportCs($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_components_cs"(\'' . $from . '\',\'' . $to . '\') AS ("CompanyName" varchar, "ProductName" varchar, "EngineVersion" varchar, "SigsBaseUpdate" timestamp, "SpellPower" integer, "SamplesScanned" integer, "MalwaresFound" integer) ORDER BY "MalwaresFound" desc'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"CompanyName"); $report .= "|"; $report .= odbc_result($res,"ProductName"); $report .= "|"; $report .= odbc_result($res,"EngineVersion"); $report .= "|"; $report .= date("Y-m-d");//odbc_result($res,"SigsBaseUpdate"); $report .= "|"; $report .= odbc_result($res,"SpellPower"); $report .= "|"; $report .= odbc_result($res,"SamplesScanned"); $report .= "|"; $report .= odbc_result($res,"MalwaresFound"); $report .= "\n"; } return $report; } public function getReportFpcs($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_components_fpcs"(\'' . $from . '\',\'' . $to . '\') AS ("FpcsName" varchar, "FpSigsDetected" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"FpcsName"); $report .= "|"; $report .= odbc_result($res,"FpSigsDetected"); $report .= "\n"; } return $report; } public function getReportStlt($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_components_stlt"(\'' . $from . '\',\'' . $to . '\') AS ("StltName" varchar, "ProcessedTotalCnt" integer, "ProcessedUniqueCnt" integer, "Untrusted" integer, "TrustedSafe" integer, "TrustedSafeFpc" integer, "TrustedMalware" integer, "UntrustedOld" integer, "UntrustedAnalyst" integer, "UntrustedCisSuspic" integer, "UntrustedCisSafeFpc" integer, "TrustedSafeFpcAnal" integer, "UntrustedCisUnknown" integer, "UntrustedFpc" integer, "TrustedSafeFpcCrawler" integer, "UntrustedCrawler" integer, "UntrustedCrawlerSuspicious" integer, "UntrustedCcsSuspicious" integer, "UntrustedFpcCcs" integer, "IntCisSuspiciousUntrusted" integer,"IntCisAutoUnknownUntrusted" integer,"IntCisUserUnknownUntrusted" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"StltName"); $report .= "|"; $report .= odbc_result($res,"ProcessedTotalCnt"); $report .= "|"; $report .= odbc_result($res,"ProcessedUniqueCnt"); $report .= "|"; $report .= odbc_result($res,"Untrusted"); $report .= "|"; $report .= odbc_result($res,"TrustedSafe"); $report .= "|"; $report .= odbc_result($res,"TrustedSafeFpc"); $report .= "|"; $report .= odbc_result($res,"TrustedMalware"); $report .= "|"; $report .= odbc_result($res,"UntrustedOld"); $report .= "|"; $report .= odbc_result($res,"UntrustedAnalyst"); $report .= "|"; $report .= odbc_result($res,"UntrustedCisSuspic"); $report .= "|"; $report .= odbc_result($res,"UntrustedCisSafeFpc"); $report .= "|"; $report .= odbc_result($res,"TrustedSafeFpcAnal"); $report .= "|"; $report .= odbc_result($res,"UntrustedCisUnknown"); $report .= "|"; $report .= odbc_result($res,"UntrustedFpc"); $report .= "|"; $report .= odbc_result($res,"TrustedSafeFpcCrawler"); $report .= "|"; $report .= odbc_result($res,"UntrustedCrawler"); $report .= "|"; $report .= odbc_result($res,"UntrustedCrawlerSuspicious"); $report .= "|"; $report .= odbc_result($res,"UntrustedCcsSuspicious"); $report .= "|"; $report .= odbc_result($res,"UntrustedFpcCcs"); $report .= "|"; $report .= odbc_result($res,"IntCisSuspiciousUntrusted"); $report .= "|"; $report .= odbc_result($res,"IntCisAutoUnknownUntrusted"); $report .= "|"; $report .= odbc_result($res,"IntCisUserUnknownUntrusted"); $report .= "\n"; } return $report; } public function getReportSpp($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_components_spp"(\'' . $from . '\',\'' . $to . '\') AS ("SppName" varchar, "TotalCnt" integer, "UniqueCnt" integer, "Untrusted" integer, "TrustedSafe" integer, "TrustedSafeFpc" integer, "TrustedMalware" integer, "UntrustedOld" integer, "UntrustedAnalyst" integer, "UntrustedCisSuspicious" integer, "UntrustedCisSafeFpc" integer, "TrustedSafeFpcAnalyst" integer, "UntrustedCisUnknown" integer, "UntrustedFpc" integer, "TrustedSafeFpcCrawler" integer, "UntrustedCrawler" integer, "UntrustedCrawlerSuspicious" integer, "UntrustedCcsSuspicious" integer, "UntrustedFpcCcs" integer, "IntCisSuspiciousUntrusted" integer,"IntCisAutoUnknownUntrusted" integer, "IntCisUserUnknownUntrusted" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"SppName"); $report .= "|"; $report .= odbc_result($res,"TotalCnt"); $report .= "|"; $report .= odbc_result($res,"UniqueCnt"); $report .= "|"; $report .= odbc_result($res,"Untrusted"); $report .= "|"; $report .= odbc_result($res,"TrustedSafe"); $report .= "|"; $report .= odbc_result($res,"TrustedSafeFpc"); $report .= "|"; $report .= odbc_result($res,"TrustedMalware"); $report .= "|"; $report .= odbc_result($res,"UntrustedOld"); $report .= "|"; $report .= odbc_result($res,"UntrustedAnalyst"); $report .= "|"; $report .= odbc_result($res,"UntrustedCisSuspicious"); $report .= "|"; $report .= odbc_result($res,"UntrustedCisSafeFpc"); $report .= "|"; $report .= odbc_result($res,"TrustedSafeFpcAnalyst"); $report .= "|"; $report .= odbc_result($res,"UntrustedCisUnknown"); $report .= "|"; $report .= odbc_result($res,"UntrustedFpc"); $report .= "|"; $report .= odbc_result($res,"TrustedSafeFpcCrawler"); $report .= "|"; $report .= odbc_result($res,"UntrustedCrawler"); $report .= "|"; $report .= odbc_result($res,"UntrustedCrawlerSuspicious"); $report .= "|"; $report .= odbc_result($res,"UntrustedCcsSuspicious"); $report .= "|"; $report .= odbc_result($res,"UntrustedFpcCcs"); $report .= "|"; $report .= odbc_result($res,"IntCisSuspiciousUntrusted"); $report .= "|"; $report .= odbc_result($res,"IntCisAutoUnknownUntrusted"); $report .= "|"; $report .= odbc_result($res,"IntCisUserUnknownUntrusted"); $report .= "\n"; } return $report; } public function getReportCamas($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_components_camas"(\'' . $from . '\',\'' . $to . '\') AS ( "CamasName" varchar, "SamplesProcessedCnt" integer, "VerdictUndefined" integer, "VerdictAnalysing" integer, "VerdictUndetected" integer, "VerdictSuspicious" integer, "VerdictSuspicious+" integer, "VerdictSuspicious++" integer, "VerdictSuspiciousFamily" integer, "VerdictSuspiciousClass" integer, "VerdictUnexecutable" integer, "VerdictSkippedSafe" integer, "VerdictSkippedMalware" integer, "VerdictSkippedDetected" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"CamasName"); $report .= "|"; $report .= odbc_result($res,"SamplesProcessedCnt"); $report .= "|"; $report .= odbc_result($res,"VerdictUndefined"); $report .= "|"; $report .= odbc_result($res,"VerdictAnalysing"); $report .= "|"; $report .= odbc_result($res,"VerdictUndetected"); $report .= "|"; $report .= odbc_result($res,"VerdictSuspicious"); $report .= "|"; $report .= odbc_result($res,"VerdictSuspicious+"); $report .= "|"; $report .= odbc_result($res,"VerdictSuspicious++"); $report .= "|"; $report .= odbc_result($res,"VerdictSuspiciousFamily"); $report .= "|"; $report .= odbc_result($res,"VerdictSuspiciousClass"); $report .= "|"; $report .= odbc_result($res,"VerdictUnexecutable"); $report .= "|"; $report .= odbc_result($res,"VerdictSkippedSafe"); $report .= "|"; $report .= odbc_result($res,"VerdictSkippedMalware"); $report .= "|"; $report .= odbc_result($res,"VerdictSkippedDetected"); $report .= "\n"; } return $report; } public function getReportSamplesSources($from, $to) { $SubmittedTotal =0; $SubmittedUniqueNew =0; $DetectedByCav =0; $DetectedByCs =0; $VerdictCamasSuspicious =0; $VerdictCamasSuspicious_p =0; $a_b =0; $b_c =0; $b_d =0; $report = ''; $query = 'SELECT * FROM "mis"."get_report_samples_sources"(\'' . $from . '\',\'' . $to . '\') AS ("SourceName" varchar, "SubmittedTotal" integer, "SubmittedTotal_PE" integer, "SubmittedUniqueNew" integer, "DetectedByCav" integer, "DetectedByCs" integer, "VerdictCamasSuspicious" integer, "VerdictCamasSuspicious+" integer,"A_B" integer,"B_C" integer,"B_D" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"SourceName"); $report .= "|"; $report .= odbc_result($res,"SubmittedTotal"); $SubmittedTotal += odbc_result($res,"SubmittedTotal"); $report .= "|"; $report .= odbc_result($res,"SubmittedTotal_PE"); $SubmittedTotal_PE += odbc_result($res,"SubmittedTotal_PE"); $report .= "|"; $report .= odbc_result($res,"SubmittedUniqueNew"); $SubmittedUniqueNew += odbc_result($res,"SubmittedUniqueNew"); $report .= "|"; $report .= odbc_result($res,"DetectedByCav"); $DetectedByCav += odbc_result($res,"DetectedByCav"); $report .= "|"; $report .= odbc_result($res,"DetectedByCs"); $DetectedByCs += odbc_result($res,"DetectedByCs"); $report .= "|"; $report .= odbc_result($res,"VerdictCamasSuspicious"); $VerdictCamasSuspicious += odbc_result($res,"VerdictCamasSuspicious"); $report .= "|"; $report .= odbc_result($res,"VerdictCamasSuspicious+"); $VerdictCamasSuspicious_p += odbc_result($res,"VerdictCamasSuspicious+"); $report .= "|"; $report .= odbc_result($res,"A_B"); $a_b += odbc_result($res,"A_B"); $report .= "|"; $report .= odbc_result($res,"B_C"); $b_c += odbc_result($res,"B_C"); $report .= "|"; $report .= odbc_result($res,"B_D"); $b_d += odbc_result($res,"B_D"); $report .= "\n"; } $report .= "Total"; $report .= "|"; $report .= $SubmittedTotal; $report .= "|"; $report .= $SubmittedUniqueNew; $report .= "|"; $report .= $DetectedByCav; $report .= "|"; $report .= $DetectedByCs; $report .= "|"; $report .= $VerdictCamasSuspicious; $report .= "|"; $report .= $VerdictCamasSuspicious_p; $report .= "|"; $report .= $a_b; $report .= "|"; $report .= $b_c; $report .= "|"; $report .= $b_d; $report .= "\n"; return $report; } public function getReportInterfaceSources($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_interface_sources"(\'' . $from . '\',\'' . $to . '\') AS ("InterfaceName" varchar, "SubmittedTotal" integer, "SubmittedUniqueNew" integer, "DetectedByCav" integer, "DetectedByCs" integer, "VerdictCamasSuspicious" integer, "VerdictCamasSuspicious+" integer,"A_B" integer,"B_C" integer,"B_D" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"InterfaceName"); $report .= "|"; $report .= odbc_result($res,"SubmittedTotal"); $report .= "|"; $report .= odbc_result($res,"SubmittedUniqueNew"); $report .= "|"; $report .= odbc_result($res,"DetectedByCav"); $report .= "|"; $report .= odbc_result($res,"DetectedByCs"); $report .= "|"; $report .= odbc_result($res,"VerdictCamasSuspicious"); $report .= "|"; $report .= odbc_result($res,"VerdictCamasSuspicious+"); $report .= "\n"; } return $report; } /* Added by Oleg Krotenko 29-Jan-2010 */ function conformityVendorEmailID_old ($emailid) { $mail = array("anthony.aykut@frame4.com","songbing@antiy.com","arthur@arcabit.com","kkoniec@arcabit.com", "pavel.krcma@avg.com","philipp.wolf@avira.com","andreir@checkpoint.com","mwatchinski@sourcefire.com","weipengfei@ztgame.com", "songliujian@viruschina.com","BONO.M@ikarus.at","chumun@incalab.com","newvirus@incalab.co.kr","gabby@iris.co.il", "yanshaowen@jiangmin.com","submissions@jotti.org","sheebagrace@k7computing.com","mashevsky3@kaspersky.com", "Yury.Mashevsky@kaspersky.com","liangjiajun@kingsoft.com","lijunfeng@kingsoft.com","tf@tomfi.net","Igor_Muttik@avertlabs.com", "zhouzhongyi@micropoint.cn","tony.lee@microsoft.com","gunnar.johansen@Norman.com","aku@novirusthanks.org","newvirus@incalab.co.kr", "Xabier.francisco@pandasecurity.com","Jerome.Segura@paretologic.com","jacques@prevx.com","ranjeet@quickheal.com","shixinfeng@snda.com", "nick@avresearch.net","stuart.taylor@sophos.com","dodig@sunbelt-software.com","A.Sachedina@symantec.com","lion_gu@trendmicro.com.cn", "yangzuohui@unnoo.com","samples@virscan.org","john.hawes@virusbtn.com","mort666@virus.org","gszappanos@virusbuster.hu", "andy@karimov.ru","tesket1@gmail.com","mikula@eset.sk","umesh@avlab.comodo.com","fp_cis@comodo.com","qa_cis@comodo.com","sb_cis@comodo.com", "ur_cis@comodo.com","crawler-dnsites@avlab.comodo.com","zhang95@ahnlab.com","bono.m@ikarus.at","spam@avlab.comodo.com", "mallinks@avlab.comodo.com","lcheck@avira.com","kubecj@asw.cz","camas_cloud@comodo.com","0xf30fc7_8k@gmail.com","udi@binalyze.com", "hivesoft@hotmail.com","milosz.czernikowski@gmail.com"); $vendor = array("Anthony","Antiy_AVL","Arcabit","Arcabit","AVG Anti-Virus","Avira","Checkpoint","ClamAV","Gaint", "Greatsoft","Ikarus","Incalab", "nProtect","IRIS","JiangMin Antivirus Software","Jotti.Org","K7 Computing","Karspersky", "Kaspersky","Kingsoft","Kingsoft","Malcode BFK","Mcafee","Micropoint","Microsoft","Norman","NoVirusThanks.Org","Nprotect", "Panda","Paretologic","Prevx","QuickHeal","Shengda","SonicWALL","Sophos","Sunbelt","Symantec","Trendmicro","UNNOO","Virscan.Org", "Virus Bulletin","Virus.Org","Virusbuster","ProAntivirus","Thomas Sloth","ESet-NOD32","npogroups.org","CIS False-Positive", "CIS Quarantine","CIS SandBox","CIS User Submitted","Download-Sites","AhnLab","Ikarus","Spam Mails","Malware Links Mails", "Malware Links(Avira)","Avast","CAMAS-Cloud","Andreas","binalyze","hivesoft","Morphiusz"); $vendorEmailId= array_combine($mail,$vendor); if (array_key_exists($emailid, $vendorEmailId)){ return $vendorEmailId[$emailid]; } else{$text1 = explode("@",$emailid); $text2 = explode(".",$text1[1]); return $text2[0];} } /*Canged 2011-03-25 Oleg Krotenko*/ function conformityVendorEmailID($emailid) { $src = "email_exception.txt"; $vendorEmailId= array(); $mail = array(); $vendor =array(); if(file_exists($src)){ $handle = fopen($src, "r"); while (!feof($handle)) { $buffer = explode(";",trim(fgets($handle, 4096))); array_push($mail,$buffer[0]); array_push($vendor,$buffer[1]); } fclose($handle); $vendorEmailId= array_combine($mail,$vendor); if (array_key_exists($emailid, $vendorEmailId)){ return $vendorEmailId[$emailid]; } else{$text1 = explode("@",$emailid); $text2 = explode(".",$text1[1]); return $text2[0];} } } public function getReportSamplesSubmitterEmails($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_samples_submitter_emails"(\'' . $from . '\',\'' . $to . '\') AS ("SubmitterEmail" varchar, "SubmittedTotal" integer, "SubmittedUniqueNew" integer, "DetectedByCav" integer, "DetectedByCs" integer, "VerdictCamasSuspicious" integer, "VerdictCamasSuspicious+" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= $this->conformityVendorEmailID(odbc_result($res,"SubmitterEmail")); $report .= "|"; $report .= odbc_result($res,"SubmitterEmail"); $report .= "|"; $report .= odbc_result($res,"SubmittedTotal"); $report .= "|"; $report .= odbc_result($res,"SubmittedUniqueNew"); $report .= "|"; $report .= odbc_result($res,"DetectedByCav"); $report .= "|"; $report .= odbc_result($res,"DetectedByCs"); $report .= "|"; $report .= odbc_result($res,"VerdictCamasSuspicious"); $report .= "|"; $report .= odbc_result($res,"VerdictCamasSuspicious+"); $report .= "\n"; } return $report; } public function getViewSamplesComodoSigned($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."view_samples_signed" WHERE "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\' AND ("IssuerName" IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\') OR "IssuersIssuerName" IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\'))'; $res = odbc_exec($this->db_conn, $query); odbc_binmode($res, ODBC_BINMODE_RETURN); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"SampleId"); $report .= "|"; $report .= odbc_result($res,"SampleSha1"); $report .= "|"; $report .= odbc_result($res,"VerdictName"); $report .= "|"; $report .= odbc_result($res,"SerialNumber"); $report .= "|"; $report .= odbc_result($res,"RevokeVotesCnt"); $report .= "|"; $report .= odbc_result($res,"IsValidOverall"); $report .= "|"; $report .= odbc_result($res,"ReasonOfInvalidity"); $report .= "\n"; } return $report; } public function getViewSamplesSigned($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."view_samples_signed" WHERE "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\' AND ("IssuerName" NOT IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\') AND "IssuersIssuerName" NOT IN (\'UTN-USERFirst-Object\', \'AddTrust Qualified CA Root\'))'; $res = odbc_exec($this->db_conn, $query); odbc_binmode($res, ODBC_BINMODE_RETURN); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"SampleId"); $report .= "|"; $report .= odbc_result($res,"SampleSha1"); $report .= "|"; $report .= odbc_result($res,"VerdictName"); $report .= "|"; $report .= odbc_result($res,"IssuerName"); $report .= "|"; $report .= odbc_result($res,"IssuersIssuerName"); $report .= "|"; $report .= odbc_result($res,"SerialNumber"); $report .= "|"; $report .= odbc_result($res,"RevokeVotesCnt"); $report .= "|"; $report .= odbc_result($res,"IsValidOverall"); $report .= "|"; $report .= odbc_result($res,"ReasonOfInvalidity"); $report .= "\n"; } return $report; } public function getViewSamplesSignedSummary($from, $to) { $report = ''; $query = 'SELECT "IssuerName", count("SampleId") as "SamplesSignedTotalCnt", COUNT(CASE WHEN "VerdictName" = \'Malware\' THEN TRUE ELSE NULL END)::integer AS "SamplesSignedMalwareCnt", COUNT(distinct("SerialNumber")) AS "UniqueSerialsCnt", a."UniqueSerialsNotRevokedCnt" FROM "mis"."view_samples_signed" LEFT JOIN (SELECT "IssuerName", COUNT(DISTINCT "SerialNumber") as "UniqueSerialsNotRevokedCnt" FROM mis.view_samples_signed WHERE "IsRevoked" IS FALSE AND "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\' GROUP BY "IssuerName") a USING("IssuerName") WHERE "FirstCatchedDateTime" BETWEEN \'' . $from . '\' AND \'' . $to . '\' GROUP BY "IssuerName", a."UniqueSerialsNotRevokedCnt"'; $res = odbc_exec($this->db_conn, $query); odbc_binmode($res, ODBC_BINMODE_RETURN); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"IssuerName"); $report .= "|"; $report .= odbc_result($res,"SamplesSignedTotalCnt"); $report .= "|"; $report .= odbc_result($res,"SamplesSignedMalwareCnt"); $report .= "|"; $report .= odbc_result($res,"UniqueSerialsCnt"); $report .= "|"; $report .= odbc_result($res,"UniqueSerialsNotRevokedCnt"); $report .= "\n"; } return $report; } public function getReportSamplesCsVsSigs($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_samples_cs_vs_sigs"(\'' . $from . '\',\'' . $to . '\') as ("CsVendorName" varchar, "MalwareName" varchar, "SamplesCnt" integer, "DetectedByPeSigs" integer, "NotDetectedByPeSigs" integer, "DetectedByFirstSigs" integer, "PeSigs" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"CsVendorName"); $report .= "|"; $report .= odbc_result($res,"MalwareName"); $report .= "|"; $report .= odbc_result($res,"SamplesCnt"); $report .= "|"; $report .= odbc_result($res,"DetectedByFirstSigs"); $report .= "|"; $report .= odbc_result($res,"DetectedByPeSigs"); $report .= "|"; $report .= odbc_result($res,"NotDetectedByPeSigs"); $report .= "|"; $report .= odbc_result($res,"PeSigs"); $report .= "\n"; } return $report; } public function getReportSamplesCsVsSigsvt($from, $to) { $report = ''; $query = 'SELECT * FROM mis.get_report_samples_cs_vs_sigsvt(\'' . $from . '\',\'' . $to . '\') AS("CsVendorName" varchar, "MalwareName" varchar, "SamplesCnt" integer, "DetectedByPeSigs" integer, "NotDetectedByPeSigs" integer, "DetectedByFirstSigs" integer, "PeSigs" integer)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"CsVendorName"); $report .= "|"; $report .= odbc_result($res,"MalwareName"); $report .= "|"; $report .= odbc_result($res,"SamplesCnt"); $report .= "|"; $report .= odbc_result($res,"DetectedByFirstSigs"); $report .= "|"; $report .= odbc_result($res,"DetectedByPeSigs"); $report .= "|"; $report .= odbc_result($res,"NotDetectedByPeSigs"); $report .= "|"; $report .= odbc_result($res,"PeSigs"); $report .= "\n"; } return $report; } public function getreportDSSDetectingConflict($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_dss_detecting_conflict"(\'' . $from . '\',\'' . $to . '\') as ( "AnalistCreatId" character varying(50), "AnalistSubmId" character varying(50), "TimeSigCreat" timestamp without time zone, "SafeSigsId" integer, "AnalistDelId" character varying(50), "TimeSigDel" timestamp without time zone)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"AnalistCreatId"); $report .= "|"; $report .= odbc_result($res,"AnalistSubmId"); $report .= "|"; $report .= odbc_result($res,"TimeSigCreat"); $report .= "|"; $report .= odbc_result($res,"SafeSigsId"); $report .= "|"; $report .= odbc_result($res,"AnalistDelId"); $report .= "|"; $report .= odbc_result($res,"TimeSigDel"); $report .= "\n"; } return $report; } public function getreportDSSNOTDetectingConflict($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_dss_not_detecting_conflict"(\'' . $from . '\',\'' . $to . '\') as ( "AnalistCreatId" character varying(50), "AnalistSubmId" character varying(50), "TimeSigCreat" timestamp without time zone, "SafeSigsId" integer, "AnalistDelId" character varying(50), "TimeSigDel" timestamp without time zone)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"AnalistCreatId"); $report .= "|"; $report .= odbc_result($res,"AnalistSubmId"); $report .= "|"; $report .= odbc_result($res,"TimeSigCreat"); $report .= "|"; $report .= odbc_result($res,"SafeSigsId"); $report .= "|"; $report .= odbc_result($res,"AnalistDelId"); $report .= "|"; $report .= odbc_result($res,"TimeSigDel"); $report .= "\n"; } return $report; } public function getReportDMSConfirmedFP($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_dms_confirmed_fp"(\'' . $from . '\',\'' . $to . '\') as ("Sha1" varchar, "AnalistSubm" character varying(50),"TimeSubmitSample" timestamp without time zone, "MalvareSignId" integer, "AnalistCreateMal" character varying(50),"TimeCreateMalId" timestamp without time zone, "AnalistDelMal" character varying(50),"TimeMalwareSigDel" timestamp without time zone, "TypeOfSign" character varying(50),"FPFixType" interval )'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"Sha1"); $report .= "|"; $report .= odbc_result($res,"AnalistSubm"); $report .= "|"; $report .= odbc_result($res,"TimeSubmitSample"); $report .= "|"; $report .= odbc_result($res,"MalvareSignId"); $report .= "|"; $report .= odbc_result($res,"AnalistCreateMal"); $report .= "|"; $report .= odbc_result($res,"TimeCreateMalId"); $report .= "|"; $report .= odbc_result($res,"AnalistDelMal"); $report .= "|"; $report .= odbc_result($res,"TimeMalwareSigDel"); $report .= "|"; $report .= odbc_result($res,"TypeOfSign"); $report .= "|"; $report .= odbc_result($res,"FPFixType"); $report .= "\n"; } return $report; } public function getReportFPFoundConfirMalware($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_fp_found_confir_malware"(\'' . $from . '\',\'' . $to . '\') as ("Sha1" varchar, "AnalistSubmId" integer,"TimeSubmitSample" timestamp without time zone, "AnalistLockMalId" integer,"TimeMalwareSigLock" timestamp without time zone, "SignId" integer,"Malwname" character varying(250),"TypeOfSign" character varying(50), "TimeCreateSignId" timestamp without time zone, "TimeUpdtSignId" timestamp without time zone, "FPFixType" interval)'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"Sha1"); $report .= "|"; $report .= odbc_result($res,"AnalistSubmId"); $report .= "|"; $report .= odbc_result($res,"TimeSubmitSample"); $report .= "|"; $report .= odbc_result($res,"AnalistLockMalId"); $report .= "|"; $report .= odbc_result($res,"TimeMalwareSigLock"); $report .= "|"; $report .= odbc_result($res,"SignId"); $report .= "|"; $report .= odbc_result($res,"Malwname"); $report .= "|"; $report .= odbc_result($res,"TypeOfSign"); $report .= "|"; $report .= odbc_result($res,"TimeCreateSignId"); $report .= "|"; $report .= odbc_result($res,"TimeUpdtSignId"); $report .= "|"; $report .= odbc_result($res,"FPFixType"); $report .= "\n"; } return $report; } public function getReportFPFoundConfirSafe($from, $to) { $report = ''; $query = 'SELECT * FROM "mis"."get_report_fp_found_confir_safe"(\'' . $from . '\',\'' . $to . '\') AS( "Sha1" varchar, "AnalistSubmId" character varying(50), "TimeSubmitSample" timestamp without time zone, "AnalistLockId" character varying(50), "TimeMarkSafe" timestamp without time zone, "SignId" integer, "Signame" character varying(250), "TypeOfSign" character varying(50), "TimeUpdtSignId" timestamp without time zone, "FPFixType" interval, "FPType" character varying(150))'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"Sha1"); $report .= "|"; $report .= odbc_result($res,"AnalistSubmId"); $report .= "|"; $report .= odbc_result($res,"TimeSubmitSample"); $report .= "|"; $report .= odbc_result($res,"AnalistLockId"); $report .= "|"; $report .= odbc_result($res,"TimeMarkSafe"); $report .= "|"; $report .= odbc_result($res,"SignId"); $report .= "|"; $report .= odbc_result($res,"Signame"); $report .= "|"; $report .= odbc_result($res,"TypeOfSign"); $report .= "|"; //$report .= odbc_result($res,"TimeCreateSignId"); //$report .= "|"; $report .= odbc_result($res,"TimeUpdtSignId"); $report .= "|"; $report .= odbc_result($res,"FPFixType"); $report .= "|"; $report .= odbc_result($res,"FPType"); $report .= "\n"; } return $report; } public function getPendingFps($from, $to) { $report = ''; /* $query = 'SELECT * FROM "mis"."view_pending_fps" where "Submission" BETWEEN \''.$from.'\' and \''.$to.'\' group by "FPC Interface","Sample\'s Source Name","Sample\'s SHA1Checksum","File Name", "CursePoint","Submission","SubmitterEmail","Detection Name by CAV"'; */ $query = "SELECT * FROM mis.get_report_pending_fps('$from','$to') AS( \"FPC Interface\" character varying(50),\"Sample's Source Name\" character varying(50), \"Sample's SHA1Checksum\" character(40),\"File Name\" character varying(256), \"CursePoint\" integer,\"Submission\" timestamp without time zone, \"SubmitterEmail\" character varying(256),\"Detection Name by CAV\" character varying)"; $res = odbc_exec($this->db_conn, $query); odbc_binmode($res, ODBC_BINMODE_RETURN); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"FPC Interface"); $report .= "|"; $report .= odbc_result($res,"Sample's Source Name"); $report .= "|"; $report .= odbc_result($res,"Sample's SHA1Checksum"); $report .= "|"; $report .= odbc_result($res,"File Name"); $report .= "|"; $report .= odbc_result($res,"CursePoint"); $report .= "|"; $report .= odbc_result($res,"Submission"); $report .= "|"; $report .= odbc_result($res,"SubmitterEmail"); $report .= "|"; $report .= odbc_result($res,"Detection Name by CAV"); $report .= "\n"; } return $report; } public function getViewAnalysts() { $report = ''; $query = 'SELECT * FROM "mis"."view_analysts"'; $res = odbc_exec($this->db_conn, $query); odbc_binmode($res, ODBC_BINMODE_RETURN); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"UserId"); $report .= "|"; $report .= odbc_result($res,"UserName"); $report .= "|"; $report .= odbc_result($res,"UserPassword"); $report .= "|"; $report .= odbc_result($res,"AnalystFirstName"); $report .= "|"; $report .= odbc_result($res,"AnalystLastName"); $report .= "|"; $report .= odbc_result($res,"AnalystBirthDate"); $report .= "|"; $report .= odbc_result($res,"AnalystEmail"); //$report .= "|"; //$report .= odbc_result($res,"AnalystPhoto"); $report .= "|"; $report .= odbc_result($res,"LabId"); $report .= "|"; $report .= odbc_result($res,"IsSuperanalyst"); $report .= "\n"; } return $report; } public function analystsUpsert($from) { $report = ''; $query = 'SELECT * FROM "analysts_upsert"(' . $from . ')'; $res = odbc_exec($this->db_conn, $query); return $report; } public function geReportSignchecker($from, $to){ $fromTime = strtotime($from); $fromTime = date("Y-m-d H:i:s", $fromTime); $toTime = strtotime($to); $toTime = date("Y-m-d H:i:s", $toTime); $report = ''; $query = "SELECT * FROM \"mis\".\"get_report_sign_checker\"('$fromTime','$toTime') AS(\"Sha1\" character(40),\"SigId\" integer[])"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"Sha1"); $report .= "|"; $report .= odbc_result($res,"SigId"); $report .= "\n"; } return $report; } public function getReportFlsTest($from,$to,$sigid,$top){ $fromTime = strtotime($from); $fromTime = date("Y-m-d", $fromTime); $toTime = strtotime($to); $toTime = date("Y-m-d", $toTime); $report=''; $query =''; if($sigid=='all'){ $query = "select * from mis.get_report_zero_false_positive('$fromTime','$toTime',$top) as (\"Guid\" character(32), \"SigId\" integer, \"SampleSha1\" character(40), \"FilePath\" character varying(1000), \"IsHidden\" boolean, \"CallerTypeId\" integer, \"IsSigned\" boolean,\"CursePoints\" integer,\"CAV_Heur_Name\" character varying(40), \"CAV_Heur_Level\" character varying(40),\"RPDTC_Verdict\" character varying(40),\"LastSectionEntropy\" real, \"LastSectionAttributes\" integer,\"EpSectionEntropy\" real, \"EpSectionAttributes\" integer, \"Peid_Entropy\" real, \"Peid_Packer\" character varying(100), \"AddedTs\" timestamp without time zone)"; } else{ $query = "select * from mis.get_report_zero_false_positive('$fromTime','$toTime',$top,$sigid) as (\"Guid\" character(32), \"SigId\" integer, \"SampleSha1\" character(40), \"FilePath\" character varying(1000), \"IsHidden\" boolean, \"CallerTypeId\" integer, \"IsSigned\" boolean,\"CursePoints\" integer,\"CAV_Heur_Name\" character varying(40), \"CAV_Heur_Level\" character varying(40),\"RPDTC_Verdict\" character varying(40),\"LastSectionEntropy\" real, \"LastSectionAttributes\" integer,\"EpSectionEntropy\" real, \"EpSectionAttributes\" integer, \"Peid_Entropy\" real, \"Peid_Packer\" character varying(100), \"AddedTs\" timestamp without time zone)"; } $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"Guid"); $report .= "|"; $report .= odbc_result($res,"SigId"); $report .= "|"; $report .= odbc_result($res,"SampleSha1"); $report .= "|"; if(odbc_result($res,"FilePath")){$fname = explode("\\",odbc_result($res,"FilePath")); $report .= $fname[count($fname)-1];} else {$report .= "";} $report .= "|"; $report .= odbc_result($res,"FilePath"); $report .= "|"; if(odbc_result($res,"IsHidden")==1){$report .= 'true';} else{$report .= 'false';} $report .= "|"; switch(odbc_result($res,"CallerTypeId")){ case 1: $report .= "On-Access"; break; case 2: $report .= "On-Demand"; break; case 3: $report .= "Sandbox"; break; default: $report .= odbc_result($res,"CallerTypeId"); break; } $report .= "|"; if(odbc_result($res,"IsSigned")==1){$report .= 'true';} else{$report .= 'false';} $report .= "|"; $report .= odbc_result($res,"CursePoints"); $report .= "|"; $report .= odbc_result($res,"CAV_Heur_Name"); $report .= "|"; $report .= odbc_result($res,"CAV_Heur_Level"); $report .= "|"; $report .= odbc_result($res,"RPDTC_Verdict"); $report .= "|"; $report .= odbc_result($res,"LastSectionEntropy"); $report .= "|"; $report .= odbc_result($res,"LastSectionAttributes"); $report .= "|"; $report .= odbc_result($res,"EpSectionEntropy"); $report .= "|"; $report .= odbc_result($res,"EpSectionAttributes"); $report .= "|"; $report .= odbc_result($res,"Peid_Entropy"); $report .= "|"; $report .= odbc_result($res,"Peid_Packer"); $report .= "|"; if(preg_match("/program files/i",odbc_result($res,"FilePath")) ||preg_match("/program files (x86)/i",odbc_result($res,"FilePath"))) {$prog= 'true';} else{$prog = 'false';} $report .= $prog; $report .= "|"; if(preg_match("/windows/i",odbc_result($res,"FilePath"))){$win= 'true';} else{$win = 'false';} $report .= $win; $report .= "|"; if ($prog =='false' || $win == 'false'){$nostand = 'true';} else{$nostand = 'false';} $report .= $nostand; $report .= "|"; if(odbc_result($res,"Peid_Entropy")>=7){ $report .= 'true';} else{$report .= 'false';} $report .= "\n"; } return $report; } public function geReportFLS($from, $to,$top){ $fromTime = strtotime($from); $fromTime = date("Y-m-d H:i:s", $fromTime); $toTime = strtotime($to); $toTime = date("Y-m-d H:i:s", $toTime); $report = ''; $query = "SELECT * FROM mis.get_report_fls_sigid_verdict('$fromTime','$toTime') AS( \"SigId\" integer, \"SigFormatName\" character varying(50) , \"Malwarename\" character varying(250) ,\"Sign Creator Analyst Name\" character varying (50), \"Verdict_Absent\" bigint , \"Verdict_Absent Uniq\" bigint ,\"Safe\" bigint , \"Uniq Safe\" bigint , \"Malware\" bigint , \"Uniq Malware\" bigint , \"Unknown\" bigint , \"Uniq Unknown\" bigint ,\"Guid Uniq\" bigint , \"Ips Uniq\" bigint ,\"FirstSeen\" timestamp without time zone ,\"LastSeen\" timestamp without time zone , \"Total Caught\" bigint ,\"Total Caught Uniq\" bigint) limit $top"; $i = 0; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $i++; $report .= "$i"; $report .= "|"; $report .= odbc_result($res,"SigId"); $report .= "|"; $report .= odbc_result($res,"SigFormatName"); $report .= "|"; $report .= odbc_result($res,"Verdict_Absent"); $report .= "|"; $report .= odbc_result($res,"Verdict_Absent Uniq"); $report .= "|"; $report .= odbc_result($res,"Safe"); $report .= "|"; $report .= odbc_result($res,"Uniq Safe"); $report .= "|"; $report .= odbc_result($res,"Malware"); $report .= "|"; $report .= odbc_result($res,"Uniq Malware"); $report .= "|"; $report .= odbc_result($res,"Unknown"); $report .= "|"; $report .= odbc_result($res,"Uniq Unknown"); $report .= "|"; $report .= odbc_result($res,"Total Caught"); $report .= "|"; $report .= odbc_result($res,"Total Caught Uniq"); $report .= "|"; $report .= odbc_result($res,"Guid Uniq"); $report .= "|"; $report .= odbc_result($res,"Ips Uniq"); $report .= "|"; $report .= odbc_result($res,"FirstSeen"); $report .= "|"; $report .= odbc_result($res,"LastSeen"); $report .= "\n"; } return $report; } public function CSFamilyToAnalysts() { $report = ''; //$query = 'SELECT * FROM "analysts_upsert"(' . $from . ')'; $query = 'SELECT * FROM mis.view_analyst_cs_families'; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"AnalystCsFamilyId"); $report .= "|"; $report .= odbc_result($res,"AnalystName"); $report .= "|"; $report .= odbc_result($res,"CsVendorName"); $report .= "|"; $report .= 'FAMILY NAME'; $report .= "|"; $report .= 'ACTIVE'; $report .= "|"; $report .= 'COMMENT'; $report .= "\n"; } return $report; } //FVS classifire start public function get_FVS_Classifier($from,$to,$src){ $report = ''; $malic;$safe;$suspic;$unknown;$conflict;$row_total;$camdpams_total; $i=0; if($src=='All'){ $query = "select * from mis.fvs_class('$from','$to') as (\"FVS Malware\" bigint, \"FVS Safe\" bigint ,\"FVS Undefined\" bigint)"; } else{ $query = "select * from mis.fvs_class_camas('$from','$to') as (\"FVS Malware\" bigint, \"FVS Safe\" bigint ,\"FVS Undefined\" bigint)"; } $res = odbc_exec($this->db_conn,$query); while(odbc_fetch_row($res)){ $row_total = 0; switch($i){ case 0: $report .= "CAMDPAMS Safe"; $report .= "|"; break; case 1: $report .= "CAMDPAMS Malware"; $report .= "|"; break; case 2: $report .= "CAMDPAMS Conflict"; $report .= "|"; break; case 3: $report .= "CAMDPAMS Unknown"; $report .= "|"; break; } $report .= odbc_result($res,"FVS Malware"); $report .= "|"; $report .= odbc_result($res,"FVS Safe"); $report .= "|"; $report .= odbc_result($res,"FVS Undefined"); $report .= "|"; $report .= "\n"; $i++; } return $report; } public function get_FVS_Classifier_enhanced($from, $to, $src){ $report = ''; $i=0; $rest; if($src=='All'){ $query = "select * from mis.fvs_class_enhanced('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint, \"Suspicious++\" bigint, \"cnt\" bigint)"; } else{ $query = "select * from mis.fvs_class_camas_enhanced('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint, \"Suspicious++\" bigint, \"cnt\" bigint)"; } $res = odbc_exec($this->db_conn,$query); while(odbc_fetch_row($res)){ $row_total = 0; switch($i){ case 0: $report .= "FVS Malware"; $report .= "|"; break; case 1: $report .= "FVS Safe"; $report .= "|"; break; case 2: $report .= "FVS Undefined"; $report .= "|"; break; } $rest=0; $cnt = odbc_result($res,"cnt"); if($cnt > 0 ){} $report .= odbc_result($res,"Alone"); $report .= "|"; $report .= odbc_result($res,"Suspicious"); $report .= "|"; $report .= odbc_result($res,"Suspicious+"); $report .= "|"; $report .= odbc_result($res,"Suspicious++"); $report .= "|"; $report .= $cnt; $report .= "\n"; $i++; } return $report; } //FVS classifre end public function get_RPDTC_Classifier($from,$to,$src){ $report = ''; $malic;$safe;$suspic;$unknown;$conflict;$row_total;$camdpams_total; $i=0; if($src=='All'){ $query = "select * from mis.rpdtc_class('$from','$to') as (\"RPDTC Malicious\" bigint,\"RPDTC Safe\" bigint,\"RPDTC Suspicious\" bigint, \"RPDTC Unknown\" bigint,\"RPDTC Conflict\" bigint)"; } else{ $query = "select * from mis.rpdtc_class_camas('$from','$to') as (\"RPDTC Malicious\" bigint,\"RPDTC Safe\" bigint,\"RPDTC Suspicious\" bigint, \"RPDTC Unknown\" bigint,\"RPDTC Conflict\" bigint)"; } $res = odbc_exec($this->db_conn,$query); while(odbc_fetch_row($res)){ $row_total = 0; switch($i){ case 0: $report .= "Malware Count"; $report .= "|"; break; case 1: $report .= "Safe Count"; $report .= "|"; break; case 2: $report .= "Conflict Count"; $report .= "|"; break; case 3: $report .= "Unknown Count"; $report .= "|"; break; } $report .= odbc_result($res,"RPDTC Malicious"); $malic += odbc_result($res,"RPDTC Malicious"); $row_total += odbc_result($res,"RPDTC Malicious"); $report .= "|"; $report .= odbc_result($res,"RPDTC Safe"); $safe += odbc_result($res,"RPDTC Safe"); $row_total += odbc_result($res,"RPDTC Safe"); $report .= "|"; $report .= odbc_result($res,"RPDTC Suspicious"); $suspic += odbc_result($res,"RPDTC Suspicious"); $row_total += odbc_result($res,"RPDTC Suspicious"); $report .= "|"; $report .= odbc_result($res,"RPDTC Unknown"); $unknown += odbc_result($res,"RPDTC Unknown"); $row_total += odbc_result($res,"RPDTC Unknown"); $report .= "|"; $report .= odbc_result($res,"RPDTC Conflict"); $conflict += odbc_result($res,"RPDTC Conflict"); $row_total += odbc_result($res,"RPDTC Conflict"); $report .= "|"; $report .= $row_total; $camdpams_total += $row_total; $report .= "\n"; $i++; } $report .= "Total"; $report .= "|"; $report .= $malic; $report .= "|"; $report .= $safe; $report .= "|"; $report .= $suspic; $report .= "|"; $report .= $unknown; $report .= "|"; $report .= $conflict; $report .= "|"; $report .= $camdpams_total; $report .= "\n"; return $report; } public function get_RPDTC_Classifier_enhanced($from, $to, $src){ $report = ''; $i=0; $rest; if($src=='All'){ $query = "select * from mis.rpdtc_class_enhanced_new('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint, \"Suspicious++\" bigint, \"cnt\" bigint)"; } else{ $query = "select * from mis.rpdtc_class_camas_enhanced_new('$from','$to') as (\"Alone\" bigint,\"Suspicious\" bigint, \"Suspicious+\" bigint, \"Suspicious++\" bigint, \"cnt\" bigint)"; } $res = odbc_exec($this->db_conn,$query); while(odbc_fetch_row($res)){ $row_total = 0; switch($i){ case 0: $report .= "Malware Count"; $report .= "|"; break; case 1: $report .= "Safe Count"; $report .= "|"; break; case 2: $report .= "Conflict Count"; $report .= "|"; break; case 3: $report .= "Unknown Count"; $report .= "|"; break; } $rest=0; $cnt = odbc_result($res,"cnt"); if($cnt > 0 ){ $rest = abs(odbc_result($res,"cnt")- odbc_result($res,"Alone")-odbc_result($res,"Suspicious")-odbc_result($res,"Suspicious+") -odbc_result($res,"Suspicious++"));} $report .= odbc_result($res,"Alone"); $report .= "|"; $report .= odbc_result($res,"Suspicious"); $report .= "|"; $report .= odbc_result($res,"Suspicious+"); $report .= "|"; $report .= odbc_result($res,"Suspicious++"); $report .= "|"; // $report .= $rest; $report .= $cnt; $report .= "\n"; $i++; } return $report; } public function geReportCavHeur($from, $to){ $report = ''; $query = "SELECT * FROM mis.get_report_cav_heur('$from','$to') AS(\"SampleSha1\" bpchar, \"Name\" character varying)"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= odbc_result($res,"SampleSha1"); $report .= "|"; $report .= odbc_result($res,"Name"); $report .= "\n"; } return $report; } public function getInfectedWorldView($from,$to,$src){ $report =""; $i=1; $query = "select * from mis.return_fls_infection_view('$from','$to',$src) as (\"MalwareName\" character varying, \"Total\" integer);"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { $report .= $i++; $report .= "|"; $report .= odbc_result($res,"SigId"); $report .= "|"; $report .= preg_replace("/(\.)$/","",(odbc_result($res,"MalwareName")=='taipan.438.b')?"DetectedViaCloud":odbc_result($res,"MalwareName")); $report .= "|"; $report .=odbc_result($res,"Total"); $report .= "\n"; } return $report; } public function getInfectedWorldView_chart($from,$to,$src){ $val =array(); $legend= array(); // $query = "select (case when \"Total\" >= 500 then mis.malware_namebysigid(\"SigId\") else 'Other' end) as \"MalwareName\",sum(\"Total\") as total // from mis.report_fls_infection where (\"Ts\" between '$from' and '$to') and \"DetectSrc\" = $src // group by \"MalwareName\" order by total desc limit $lim"; $query = "select * from mis.return_fls_infection_view('$from','$to',$src,50) as (\"MalwareName\" character varying, \"Total\" integer);"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { array_push($legend,(odbc_result($res,"MalwareName")=='taipan.438.b')?"DetectedViaCloud":odbc_result($res,"MalwareName")); array_push($val,odbc_result($res,"Total")); } return array($legend,$val); } public function getInfectedCountryView_chart($from,$to,$src){ $val =array(); $legend= array(); $query = "select * from mis.return_fls_infection_country('$from','$to',$src,50) as (\"Country\" character varying, \"Total\" integer);"; $res = odbc_exec($this->db_conn, $query); while(odbc_fetch_row($res)) { array_push($legend,odbc_result($res,"Country")); array_push($val,odbc_result($res,"Total")); } return array($legend,$val); } function __destruct() { odbc_close($this->db_conn); } } ?>