PHP QueryResultsOutput::GetSortSQL Examples

Example #1

File: base_stat_sensor.php Project: alienfault/ossim

$qro = new QueryResultsOutput("base_stat_sensor.php?caller=" . $caller);
$qro->AddTitle(" ");
$qro->AddTitle(gettext("Sensor"), "sid_a", " ", " ORDER BY acid_event.device_id ASC", "sid_d", " ", " ORDER BY acid_event.device_id DESC");
$qro->AddTitle(gettext("Name"), "", " ", " ", "", " ", " ");
$qro->AddTitle(gettext("Device IP"), "", " ", " ", "", " ", " ");
$events_title = _("Events") . "&nbsp;# <span class='idminfo' txt='" . Util::timezone($tz) . "'>(*)</span>";
$qro->AddTitle($events_title, "occur_a", " ", "  ORDER BY event_cnt ASC", "occur_d", " ", "  ORDER BY event_cnt DESC");
$qro->AddTitle(gettext("Unique Events"), "", "", "", "", "", "");
$qro->AddTitle(gettext("Unique Src."), "", "", "", "", "", "");
$qro->AddTitle(gettext("Unique Dst."), "", "", "", "", "", "");
/*
$qro->AddTitle(gettext("Unique Events"), "sig_a", "", " ORDER BY sig_cnt ASC", "sig_d", "", " ORDER BY sig_cnt DESC");
$qro->AddTitle(gettext("Unique Src."), "saddr_a", "", " ORDER BY saddr_cnt ASC", "saddr_d", "", " ORDER BY saddr_cnt DESC");
$qro->AddTitle(gettext("Unique Dst."), "daddr_a", "", " ORDER BY daddr_cnt ASC", "daddr_d", "", " ORDER BY daddr_cnt DESC");
*/
$sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), "");
$sql = "SELECT acid_event.device_id, HEX(device.sensor_id) AS sensor_id, ifnull(sensor.name,'Unknown') AS name, inet6_ntoa(sensor.ip) AS sensor_ip, inet6_ntoa(device.device_ip) AS device_ip, device.interface, {$counter} " . $sort_sql[0] . $from . $where . " AND device.id=acid_event.device_id GROUP BY acid_event.device_id HAVING event_cnt>0 " . $sort_sql[1];
$sql2 = "SELECT acid_event.device_id, HEX(device.sensor_id) AS sensor_id, ifnull(sensor.name,'Unknown') AS name, inet6_ntoa(sensor.ip) AS sensor_ip, inet6_ntoa(device.device_ip) AS device_ip, device.interface, {$counter} " . $sort_sql[0] . $from . $where2 . " AND device.id=acid_event.device_id GROUP BY acid_event.device_id HAVING event_cnt>0 " . $sort_sql[1];
$sqlsensor = "SELECT " . $nevents . " as sig_cnt, count(distinct(acid_event.ip_src)) as saddr_cnt, count(distinct(acid_event.ip_dst)) as daddr_cnt" . $sort_sql[0] . $from2 . $where1 . " AND acid_event.device_id=DEVICEID";
$_SESSION['_siem_sensor_query'] = $sqlsensor;
if (file_exists('/tmp/debug_siem')) {
    error_log("STATS SENSORS:{$sql}\nSTATS SENSOR UNIQUE:{$sqlsensor}\n", 3, "/tmp/siem");
}
/* Run the Query again for the actual data (with the LIMIT) */
session_write_close();
$result = $qs->ExecuteOutputQuery($sql, $db);
if ($result->baseRecordCount() == 0 && $use_ac) {
    $result = $qs->ExecuteOutputQuery($sql2, $db);
}
$qs->num_result_rows = $result->baseRecordCount();
$et->Mark("Retrieve Query Data");

Example #2

File: base_stat_country.php Project: jhbsz/ossimTest

//$qs->AddValidAction("del_alert");
//$qs->AddValidAction("email_alert");
//$qs->AddValidAction("email_alert2");
//$qs->AddValidAction("csv_alert");
//$qs->AddValidAction("archive_alert");
//$qs->AddValidAction("archive_alert2");
//$qs->AddValidActionOp(gettext("Delete Selected"));
//$qs->AddValidActionOp(gettext("Delete ALL on Screen"));
$qs->SetActionSQL($from . $where);
$et->Mark("Initialization");
$qs->RunAction($submit, PAGE_STAT_UADDR, $db);
$et->Mark("Alert Action");
/* Setup the Query Results Table */
$qro = new QueryResultsOutput("base_stat_uaddr.php?caller=" . $caller . "&addr_type=" . $addr_type);
$qro->AddTitle(" ");
$sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort());
$sql = "(SELECT DISTINCT ip_src, 'S', COUNT(acid_event.cid) as num_events " . $sort_sql[0] . $from . $where . " GROUP BY ip_src HAVING num_events>0 " . $sort_sql[1] . ") UNION (SELECT DISTINCT ip_dst, 'D', COUNT(acid_event.cid) as num_events " . $sort_sql[0] . $from . $where . " GROUP BY ip_dst HAVING num_events>0 " . $sort_sql[1] . ")";
// use accumulate tables only with timestamp criteria
if ($use_ac) {
    $where = $more = $sqla = $sqlb = $sqlc = "";
    if (preg_match("/timestamp/", $criteria_clauses[1])) {
        $where = "WHERE " . str_replace("timestamp", "day", $criteria_clauses[1]);
    }
    $orderby = str_replace("acid_event.", "", $sort_sql[1]);
    // $orderby not included
    $sql = "(SELECT DISTINCT ip_src, 'S', sum(cid) as num_events\n\t\tFROM ac_srcaddr_ipsrc {$where} GROUP BY ip_src HAVING num_events>0) UNION \n\t\t(SELECT DISTINCT ip_dst, 'D', sum(cid) as num_events\n\t\tFROM ac_dstaddr_ipdst {$where} GROUP BY ip_dst HAVING num_events>0)";
}
//echo $sql;
//print_r($_SESSION);
/* Run the Query again for the actual data (with the LIMIT) */
$result = $qs->ExecuteOutputQueryNoCanned($sql, $db);