/* increment the view if necessary */ $page_title = gettext("Event Listing"); if ($qs->isCannedQuery()) { PrintBASESubHeader($page_title . ": " . $qs->GetCurrentCannedQueryDesc(), $page_title . ": " . $qs->GetCurrentCannedQueryDesc(), $cs->GetBackLink(), 0); } else { PrintBASESubHeader($page_title, $page_title, $cs->GetBackLink(), 0); } /* Connect to the Alert database */ $db = NewBASEDBConnection($DBlib_path, $DBtype); $db->baseDBConnect($db_connect_method, $alert_dbname, $alert_host, $alert_port, $alert_user, $alert_password); if ($event_cache_auto_update == 1) { UpdateAlertCache($db); } $criteria_clauses = ProcessCriteria(); $qro = new QueryResultsOutput("base_qry_main.php" . $qs->SaveStateGET()); $qro->AddTitle(qroReturnSelectALLCheck()); // Timezone $tz = Util::get_timezone(); /* Apply sort criteria */ if ($qs->isCannedQuery()) { $sort_sql = " ORDER BY timestamp DESC "; } else { $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); // 3/23/05 BDB mods to make sort by work for Searches $sort_sql = ""; if (!isset($sort_order)) { $sort_order = NULL; } if ($sort_order == "sip_a") { $sort_sql = " ORDER BY ip_src ASC"; $criteria_sql = str_replace("1 AND ( timestamp", "ip_src >= 0 AND ( timestamp", $criteria_sql);
$et->Mark("Alert Action"); /* If get a valid (sid,cid) store it in $caller. * But if $submit is returning from an alert action * get the (sid,cid) back from $caller */ if ($submit == _("Delete Selected")) { $submit = ImportHTTPVar("caller", VAR_DIGIT | VAR_PUNC); } else { $caller = $submit; } /* Setup the Query Results Table -- However, this data structure is not * really used for output. Rather, it duplicates the sort SQL set in * base_qry_sqlcalls.php */ $qro = new QueryResultsOutput(""); $qro->AddTitle(_("Signature"), "sig_a", " ", " ORDER BY sig_name ASC", "sig_d", " ", " ORDER BY sig_name DESC"); $qro->AddTitle("Timestamp", "time_a", " ", " ORDER BY timestamp ASC ", "time_d", " ", " ORDER BY timestamp DESC "); $qro->AddTitle("Source<BR>Address", "sip_a", " ", " ORDER BY ip_src ASC", "sip_d", " ", " ORDER BY ip_src DESC"); $qro->AddTitle("Dest.<BR>Address", "dip_a", " ", " ORDER BY ip_dst ASC", "dip_d", " ", " ORDER BY ip_dst DESC"); $qro->AddTitle("Layer 4<BR>Proto", "proto_a", " ", " ORDER BY layer4_proto ASC", "proto_d", " ", " ORDER BY layer4_proto DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); /* Apply sort criteria */ if ($sort_sql[1] == "" && !isset($sort_order)) { $sort_order = "time_d"; } if ($sort_order == "sip_a") { $sort_sql[1] = " ORDER BY ip_src ASC,timestamp DESC"; $where = str_replace("1 AND ( timestamp", "ip_src >= 0 AND ( timestamp", $where); } elseif ($sort_order == "sip_d") { $sort_sql[1] = " ORDER BY ip_src DESC,timestamp DESC"; $where = preg_replace("/1 AND \\( timestamp/", "ip_src >= 0 AND ( timestamp", $where);
//$qs->AddValidAction("add_new_ag"); //$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) */
/* mstone 20050309 this is expensive -- don't do it if we're avoiding count() */ /*if ($avoid_counts != 1 && !$use_ac) { $event_cnt = EventCnt($db); if($event_cnt == 0){ $event_cnt = 1; } }*/ /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT acid_event.plugin_id) " . $fromcnt . $where; /* Run the query to determine the number of rows (No LIMIT)*/ $qs->GetNumResultRows($cnt_sql, $db); $debug_time_mode >= 1 ? $et->Mark("Counting Result size") : ''; /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_plugins.php?caller=" . $caller); //$qro->AddTitle(" "); $qro->AddTitle(_("Data Source")); $qro->AddTitle(_("Events"), "occur_a", " ", " ORDER BY events ASC, sensors DESC", "occur_d", ", ", " ORDER BY events DESC, sensors DESC"); $qro->AddTitle(gettext("Sensor") . " #", "sid_a", " ", " ORDER BY sensors ASC, events DESC", "sid_d", " ", " ORDER BY sensors DESC, events DESC"); $qro->AddTitle(gettext("Last Event")); $qro->AddTitle(gettext("Source Address")); $qro->AddTitle(gettext("Dest. Address")); $qro->AddTitle(gettext("Date") . " " . Util::timezone($tz)); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); /* mstone 20050309 add sig_name to GROUP BY & query so it can be used in postgres ORDER BY */ /* mstone 20050405 add sid & ip counts */ $sql = "select SQL_CALC_FOUND_ROWS max(acid_event.cid),acid_event.plugin_id,count(distinct acid_event.plugin_sid) as events,acid_event.timestamp,count(distinct acid_event.sid) as sensors,plugin.name " . $fromcnt . ",ossim.plugin " . $where . " AND plugin.id=acid_event.plugin_id GROUP BY acid_event.plugin_id " . $sort_sql[1]; //echo $sql; $event_cnt = EventCnt($db, "", "", $sql); if ($event_cnt == 0) { $event_cnt = 1; }
//$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_IPLINK, $db); $et->Mark("Alert Action"); /* Run the query to determine the number of rows (No LIMIT)*/ $qs->current_view = 0; $qs->num_result_rows = UniqueLinkCnt($db, $criteria_clauses[0], " WHERE " . $criteria_clauses[1]); $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_iplink.php?fqdn={$fqdn}&caller={$caller}"); $qro->AddTitle(" "); if ($fqdn == "yes") { $qro->AddTitle(gettext("Source FQDN")); } $qro->AddTitle(gettext("Source IP"), "sip_a", "", " ORDER BY ip_src ASC", "sip_d", "", " ORDER BY ip_src DESC"); $qro->AddTitle(gettext("Direction")); $qro->AddTitle(gettext("Destination IP"), "dip_a", "", " ORDER BY ip_dst ASC", "dip_d", "", " ORDER BY ip_dst DESC"); if ($fqdn == "yes") { $qro->AddTitle(gettext("Destination FQDN")); } $qro->AddTitle(gettext("Protocol"), "proto_a", "", " ORDER BY ip_proto ASC", "proto_d", "", " ORDER BY ip_proto DESC"); $qro->AddTitle(gettext("Unique Dst Ports"), "dport_a", "", " ORDER BY clayer4 ASC", "dport_d", "", " ORDER BY clayer4 DESC"); $qro->AddTitle(gettext("Unique Events"), "sig_a", "", " ORDER BY csig ASC", "sig_d", "", " ORDER BY csig DESC"); $qro->AddTitle(gettext("Total Events"), "events_a", "", " ORDER BY ccid ASC", "events_d", "", " ORDER BY ccid DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); $sql = "SELECT acid_event.ip_src, acid_event.ip_dst, acid_event.ip_proto, hex(acid_event.ctx) as ctx, COUNT(DISTINCT acid_event.layer4_dport) as clayer4, COUNT(acid_event.id) as ccid, COUNT(DISTINCT acid_event.plugin_id, acid_event.plugin_sid) csig, HEX(acid_event.src_host) AS src_host, HEX(acid_event.dst_host) AS dst_host " . $sort_sql[0] . $from . $where . " GROUP by ip_src, ip_dst, ip_proto " . $sort_sql[1];
if($event_cnt == 0){ $event_cnt = 1; } }*/ // Timezone $tz = Util::get_timezone(); /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT acid_event.plugin_id, acid_event.plugin_sid) " . $from . $where; /* Run the query to determine the number of rows (No LIMIT)*/ if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $debug_time_mode >= 1 ? $et->Mark("Counting Result size") : ''; /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_alerts.php?caller=" . $caller); $qro->AddTitle(" "); $qro->AddTitle(gettext("Signature"), "sig_a", " ", " ORDER BY plugin_id ASC,plugin_sid", "sig_d", " ", " ORDER BY plugin_id DESC,plugin_sid"); //if ($db->baseGetDBversion() >= 103) $qro->AddTitle(gettext("Classification"), "class_a", ", MIN(sig_class_id) ", " ORDER BY sig_class_id ASC ", "class_d", ", MIN(sig_class_id) ", " ORDER BY sig_class_id DESC "); $qro->AddTitle(gettext("Total") . " #", "occur_a", " ", " ORDER BY sig_cnt ASC", "occur_d", " ", " ORDER BY sig_cnt DESC"); $qro->AddTitle(gettext("Sensor") . " #"); $qro->AddTitle(_("Src. Addr."), "saddr_a", ", count(DISTINCT ip_src) AS saddr_cnt ", " ORDER BY saddr_cnt ASC", "saddr_d", ", count(DISTINCT ip_src) AS saddr_cnt ", " ORDER BY saddr_cnt DESC"); $qro->AddTitle(_("Dst. Addr."), "daddr_a", ", count(DISTINCT ip_dst) AS daddr_cnt ", " ORDER BY daddr_cnt ASC", "daddr_d", ", count(DISTINCT ip_dst) AS daddr_cnt ", " ORDER BY daddr_cnt DESC"); $qro->AddTitle(_("First") . " " . Util::timezone($tz), "first_a", ", min(timestamp) AS first_timestamp ", " ORDER BY first_timestamp ASC", "first_d", ", min(timestamp) AS first_timestamp ", " ORDER BY first_timestamp DESC"); if ($show_previous_alert == 1) { $qro->AddTitle("Previous"); } $qro->AddTitle(_("Last") . " " . Util::timezone($tz), "last_a", ", max(timestamp) AS last_timestamp ", " ORDER BY last_timestamp ASC", "last_d", ", max(timestamp) AS last_timestamp ", " ORDER BY last_timestamp DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); /* mstone 20050309 add sig_name to GROUP BY & query so it can be used in postgres ORDER BY */ /* mstone 20050405 add sid & ip counts */ //$sql = "SELECT DISTINCT signature, count(signature) as sig_cnt, " . "min(timestamp), max(timestamp), sig_name, count(DISTINCT(acid_event.sid)), count(DISTINCT(ip_src)), count(DISTINCT(ip_dst)), sig_class_id " . $sort_sql[0] . $from . $where . " GROUP BY signature, sig_name, sig_class_id " . $sort_sql[1];
$field = $addr_type; $from_src = ""; if ($addr_type == "src_userdomain") { $field = "CONCAT(idm_data.username,'@',idm_data.domain)"; $from_src = " AND idm_data.from_src=1"; } elseif ($addr_type == "dst_userdomain") { $field = "CONCAT(idm_data.username,'@',idm_data.domain)"; $from_src = " AND idm_data.from_src=0"; } $cnt_sql = "SELECT count(DISTINCT {$field}) " . $from . $where . " AND {$field} <> ''"; $qs->GetNumResultRows($cnt_sql, $db); $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_uidmsel.php?caller=" . $caller . "&addr_type=" . $addr_type); //$qro->AddTitle(" "); $qro->AddTitle($results_title, "addr_a", " ", " ORDER BY {$addr_type} ASC", "addr_d", " ", " ORDER BY {$addr_type} DESC"); if ($resolve_IP == 1) { $qro->AddTitle("FQDN"); } $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $events_title = _("Events") . " # <span class='idminfo' txt='" . Util::timezone(Util::get_timezone()) . "'>(*)</span>"; $qro->AddTitle($events_title, "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Unique Events"), "sig_a", " ", " ORDER BY num_sig ASC", "sig_d", " ", " ORDER BY num_sig DESC"); $displaytitle = gettext("Displaying unique " . strtolower($type_name) . " %d-%d of <b>%s</b> matching your selection."); if (!Session::am_i_admin()) { $displaytitle = preg_replace("/\\. <b>.*/", ".", $displaytitle); } $qro->AddTitle("Unique " . gettext(ucfirst($source) . "."), "saddr_a", " ", " ORDER BY num_ip ASC", "saddr_d", " ", " ORDER BY num_ip DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); if (Session::show_entities()) { $sql = "SELECT {$field} as idm, hex(ctx) as context, COUNT(acid_event.id) as num_events, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig, COUNT( DISTINCT ip_" . $source . " ) as num_ip " . $sort_sql[0] . $from . $where . " {$from_src} GROUP BY idm,context HAVING num_events>0 AND idm<>'' " . $sort_sql[1];
//$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"); /* Run the query to determine the number of rows (No LIMIT)*/ //$cnt_sql = "SELECT count(DISTINCT $addr_type_name) " . $from . $where; $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_uaddr.php?caller=" . $caller . "&addr_type=" . $addr_type); $qro->AddTitle(" "); $qro->AddTitle($results_title, "addr_a", " ", " ORDER BY {$addr_type_name} ASC", "addr_d", " ", " ORDER BY {$addr_type_name} DESC"); $qro->AddTitle(gettext("OTX")); if ($resolve_IP == 1) { $qro->AddTitle("FQDN"); } $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $qro->AddTitle(gettext("Events") . " # <span class='idminfo' txt='" . Util::timezone(Util::get_timezone()) . "'>(*)</span>", "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Unique Events"), "sig_a", " ", " ORDER BY num_sig ASC", "sig_d", " ", " ORDER BY num_sig DESC"); if ($addr_type == DEST_IP) { $displaytitle = gettext("Displaying unique destination addresses %d-%d of <b>%s</b> matching your selection."); $qro->AddTitle(gettext("Unique Src. Contacted."), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); } else { $displaytitle = gettext("Displaying unique source addresses %d-%d of <b>%s</b> matching your selection."); $qro->AddTitle(gettext("Unique Dst. Contacted"), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); }
$page = "base_ag_main.php"; $tmp_page_get = "&ag_action=view&ag_id={$ag_id}&submit=x"; $sql = $save_sql; } else { $page = "base_qry_main.php"; $cnt_sql = "SELECT COUNT(acid_event.cid) FROM acid_event " . $join_sql . $where_sql . $criteria_sql; $tmp_page_get = ""; } // Timezone $tz = Util::get_timezone(); /* Run the query to determine the number of rows (No LIMIT)*/ //$qs->GetNumResultRows($cnt_sql, $db); $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("{$page}" . $qs->SaveStateGET() . $tmp_page_get); $qro->AddTitle(qroReturnSelectALLCheck()); //$qro->AddTitle("ID"); $qro->AddTitle("SIGNATURE", "sig_a", " ", " ORDER BY plugin_id ASC,plugin_sid", "sig_d", " ", " ORDER BY plugin_id DESC,plugin_sid"); $qro->AddTitle("DATE", "time_a", " ", " ORDER BY timestamp ASC ", "time_d", " ", " ORDER BY timestamp DESC "); $qro->AddTitle("IP_PORTSRC", "sip_a", " ", " ORDER BY ip_src ASC", "sip_d", " ", " ORDER BY ip_src DESC"); $qro->AddTitle("IP_PORTDST", "dip_a", " ", " ORDER BY ip_dst ASC", "dip_d", " ", " ORDER BY ip_dsat DESC"); //$qro->AddTitle("Asset", "oasset_d_a", " ", " ORDER BY ossim_asset_dst ASC", "oasset_d_d", " ", " ORDER BY ossim_asset_dst DESC"); //$qro->AddTitle("Asset", "oasset_s_a", " ", " ORDER BY ossim_asset_src ASC", "oasset_s_d", " ", " ORDER BY ossim_asset_src DESC", "oasset_d_a", " ", " ORDER BY ossim_asset_dst ASC", "oasset_d_d", " ", " ORDER BY ossim_asset_dst DESC"); $qro->AddTitle("ASSET"); $qro->AddTitle("PRIORITY", "oprio_a", " ", " ORDER BY ossim_priority ASC", "oprio_d", " ", " ORDER BY ossim_priority DESC"); $qro->AddTitle("RELIABILITY", "oreli_a", " ", " ORDER BY ossim_reliability ASC", "oreli_d", " ", " ORDER BY ossim_reliability DESC"); //$qro->AddTitle("Risk", "oriska_a", " ", " ORDER BY ossim_risk_a ASC", "oriska_d", " ", " ORDER BY ossim_risk_a DESC"); $qro->AddTitle("RISK", "oriska_a", " ", " ORDER BY ossim_risk_c ASC", "oriska_d", " ", " ORDER BY ossim_risk_c DESC", "oriskd_a", " ", " ORDER BY ossim_risk_a ASC", "oriskd_d", " ", " ORDER BY ossim_risk_a DESC"); //$qro->AddTitle("L4-proto", "proto_a", " ", " ORDER BY ip_proto ASC", "proto_d", " ", " ORDER BY ip_proto DESC"); $qro->AddTitle("IP_PROTO"); $qro->AddTitle("IP_SRC");
$port_type_sql = "layer4_sport"; break; case DEST_PORT: default: $port_type_sql = "layer4_dport"; break; } // Timezone $tz = Util::get_timezone(); /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT {$port_type_sql}) FROM acid_event " . $criteria_clauses[0] . " WHERE " . $criteria_clauses[1]; /* Run the query to determine the number of rows (No LIMIT)*/ $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_ports.php?caller={$caller}" . "&port_type={$port_type}&proto={$proto}"); $qro->AddTitle(" "); $qro->AddTitle(gettext("Port"), "port_a", " ", " ORDER BY {$port_type_sql} ASC", "port_d", " ", " ORDER BY {$port_type_sql} DESC"); //$qro->AddTitle(gettext("Sensor"), "sensor_a", " ", " ORDER BY num_sensors ASC", "sensor_d", " ", " ORDER BY num_sensors DESC"); $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $qro->AddTitle(gettext("Events") . " # <span class='idminfo' txt='" . Util::timezone(Util::get_timezone()) . "'>(*)</span>", "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Unique Events"), "alerts_a", " ", " ORDER BY num_sig ASC", "alerts_d", " ", " ORDER BY num_sig DESC"); $qro->AddTitle(gettext("Unique Src.")); $qro->AddTitle(gettext("Unique Dst.")); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); $where = " WHERE " . $criteria_clauses[1]; if (Session::show_entities()) { $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT {$port_type_sql}, MIN(ip_proto), hex(ctx) as ctx, COUNT(acid_event.id) as num_events, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig " . $sort_sql[0] . " FROM acid_event " . $criteria_clauses[0] . $where . " GROUP BY " . $port_type_sql . ",ctx HAVING num_events>0 " . $sort_sql[1]; $sqlports = "SELECT count(DISTINCT(ip_src)) as saddr_cnt, count(DISTINCT(ip_dst)) as daddr_cnt " . $sort_sql[0] . " FROM acid_event " . $criteria_clauses[0] . $where . " AND {$port_type_sql}=IP_PORT AND acid_event.ctx=UNHEX('DEVICEID')"; } else { $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT {$port_type_sql}, MIN(ip_proto), device_id, COUNT(acid_event.id) as num_events, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig " . $sort_sql[0] . " FROM device,acid_event " . $criteria_clauses[0] . $where . " AND device.id=acid_event.device_id GROUP BY " . $port_type_sql . ",device_id HAVING num_events>0 " . $sort_sql[1]; $sqlports = "SELECT count(DISTINCT(ip_src)) as saddr_cnt, count(DISTINCT(ip_dst)) as daddr_cnt " . $sort_sql[0] . " FROM acid_event " . $criteria_clauses[0] . $where . " AND {$port_type_sql}=IP_PORT AND acid_event.device_id=DEVICEID";
default: $port_type_sql = "layer4_dport"; break; } // Timezone $tz = Util::get_timezone(); /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT {$port_type_sql}) " . " FROM acid_event " . $criteria_clauses[0] . " WHERE " . $criteria_clauses[1]; /* Run the query to determine the number of rows (No LIMIT)*/ if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_ports.php?caller={$caller}" . "&port_type={$port_type}&proto={$proto}"); $qro->AddTitle(" "); $qro->AddTitle(gettext("Port"), "port_a", " ", " ORDER BY {$port_type_sql} ASC", "port_d", " ", " ORDER BY {$port_type_sql} DESC"); $qro->AddTitle(gettext("Sensor"), "sensor_a", " ", " ORDER BY num_sensors ASC", "sensor_d", " ", " ORDER BY num_sensors DESC"); $qro->AddTitle(gettext("Occurrences"), "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Unique Events"), "alerts_a", " ", " ORDER BY num_sig ASC", "alerts_d", " ", " ORDER BY num_sig DESC"); $qro->AddTitle(gettext("Src. Addr."), "sip_a", " ", " ORDER BY num_sip ASC", "sip_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(gettext("Dest. Addr."), "dip_a", " ", " ORDER BY num_dip ASC", "dip_d", " ", " ORDER BY num_dip DESC"); $qro->AddTitle(_("First") . " " . Util::timezone($tz), "first_a", " ", " ORDER BY first_timestamp ASC", "first_d", " ", " ORDER BY first_timestamp DESC"); $qro->AddTitle(_("Last") . " " . Util::timezone($tz), "last_a", " ", " ORDER BY last_timestamp ASC", "last_d", " ", " ORDER BY last_timestamp DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); $where = " WHERE " . $criteria_clauses[1]; $sql = "SELECT DISTINCT {$port_type_sql}, MIN(ip_proto), " . " COUNT(acid_event.cid) as num_events," . " COUNT( DISTINCT acid_event.sid) as num_sensors, " . " COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig, " . " COUNT( DISTINCT ip_src ) as num_sip, " . " COUNT( DISTINCT ip_dst ) as num_dip, " . " MIN(timestamp) as first_timestamp, " . " MAX(timestamp) as last_timestamp " . $sort_sql[0] . " FROM acid_event " . $criteria_clauses[0] . $where . " GROUP BY " . $port_type_sql . " HAVING num_events>0 " . $sort_sql[1]; //echo "$sql<br>"; // use accumulate tables only with timestamp criteria if ($use_ac) { $more = $sqla = $sqlb = $sqlc = $sqld = "";
break; case DEST_PORT: default: $port_type_sql = "layer4_dport"; break; } // Timezone $tz = Util::get_timezone(); /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT {$port_type_sql}) " . " FROM acid_event " . $criteria_clauses[0] . " WHERE " . $criteria_clauses[1]; /* Run the query to determine the number of rows (No LIMIT)*/ //if (!$use_ac) $qs->GetNumResultRows($cnt_sql, $db); $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_ports.php?caller={$caller}" . "&port_type={$port_type}&proto={$proto}"); $qro->AddTitle(" "); $qro->AddTitle(gettext("Port"), "port_a", " ", " ORDER BY {$port_type_sql} ASC", "port_d", " ", " ORDER BY {$port_type_sql} DESC"); //$qro->AddTitle(gettext("Sensor"), "sensor_a", " ", " ORDER BY num_sensors ASC", "sensor_d", " ", " ORDER BY num_sensors DESC"); $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $qro->AddTitle(gettext("Occurrences"), "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Unique Events"), "alerts_a", " ", " ORDER BY num_sig ASC", "alerts_d", " ", " ORDER BY num_sig DESC"); $qro->AddTitle(gettext("Unique Src."), "sip_a", " ", " ORDER BY num_sip ASC", "sip_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(gettext("Unique Dst."), "dip_a", " ", " ORDER BY num_dip ASC", "dip_d", " ", " ORDER BY num_dip DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); $where = " WHERE " . $criteria_clauses[1]; if (Session::show_entities()) { $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT {$port_type_sql}, MIN(ip_proto), hex(ctx) as ctx, COUNT(acid_event.id) as num_events, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig, COUNT( DISTINCT ip_src ) as num_sip, COUNT( DISTINCT ip_dst ) as num_dip, MIN(timestamp) as first_timestamp, MAX(timestamp) as last_timestamp " . $sort_sql[0] . " FROM acid_event " . $criteria_clauses[0] . $where . " GROUP BY " . $port_type_sql . ",ctx HAVING num_events>0 " . $sort_sql[1]; } else { $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT {$port_type_sql}, MIN(ip_proto), device_id, COUNT(acid_event.id) as num_events, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig, COUNT( DISTINCT ip_src ) as num_sip, COUNT( DISTINCT ip_dst ) as num_dip, MIN(timestamp) as first_timestamp, MAX(timestamp) as last_timestamp " . $sort_sql[0] . " FROM device,acid_event " . $criteria_clauses[0] . $where . " AND device.id=acid_event.device_id GROUP BY " . $port_type_sql . ",device_id HAVING num_events>0 " . $sort_sql[1]; } //echo "$sql<br>";
$qs->RunAction($submit, PAGE_STAT_CLASS, $db); $et->Mark("Alert Action"); /* Get total number of events */ if (!$use_ac) { $event_cnt = EventCnt($db); } /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT sig_class_id) " . $from . $where; /* Run the query to determine the number of rows (No LIMIT)*/ if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_class.php?caller=" . $caller); $qro->AddTitle(" "); $qro->AddTitle(gettext("Classification"), "class_a", " ", " ORDER BY sig_class_id ASC", "class_d", " ", " ORDER BY sig_class_id DESC"); $qro->AddTitle(gettext("Total") . " #", "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Sensor") . " #", "sensor_a", " ", " ORDER BY num_sensors ASC", "sensor_d", " ", " ORDER BY num_sensors DESC"); $qro->AddTitle(_("Sig"), "sig_a", " ", " ORDER BY num_sig ASC", "sig_d", " ", " ORDER BY num_sig DESC"); $qro->AddTitle(_("Scr.Addr"), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(_("Dst.Addr"), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); /*$qro->AddTitle(gettext("First"), "first_a", " ", " ORDER BY first_timestamp ASC", "first_d", " ", " ORDER BY first_timestamp DESC"); $qro->AddTitle(gettext("Last"), "last_a", " ", " ORDER BY last_timestamp ASC",
if (preg_match("/^(.*)AND\s+\(\s+timestamp\s+[^']+'([^']+)'\s+\)\s+AND\s+\(\s+timestamp\s+[^']+'([^']+)'\s+\)(.*)$/", $where, $matches)) { if ($matches[2] != $matches[3]) { $where = $matches[1] . " AND timestamp BETWEEN('" . $matches[2] . "') AND ('" . $matches[3] . "') " . $matches[4]; } else { $where = $matches[1] . " AND timestamp >= '" . $matches[2] . "' " . $matches[4]; } } $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_otx.php?caller=" . $caller); $qro->AddTitle(_('OTX Pulse')); $events_title = _("Events"). " # <span class='idminfo' txt='".Util::timezone(Util::get_timezone())."'>(*)</span>"; $qro->AddTitle("<span id='total_title'>$events_title</span>", "occur_a", " ", " ORDER BY num_events ASC, num_iocs ASC", "occur_d", " ", " ORDER BY num_events DESC, num_iocs DESC"); $qro->AddTitle(_("Indicators #") , "ioc_a", " ", " ORDER BY num_iocs ASC", "ioc_d", " ", " ORDER BY num_iocs DESC"); $qro->AddTitle(' '); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort() , $qs->GetCurrentCannedQuerySort()); $sql = "SELECT SQL_CALC_FOUND_ROWS hex(otx_data.pulse_id) as pulse, COUNT(distinct otx_data.event_id) as num_events, COUNT(distinct otx_data.ioc_hash) as num_iocs ". $sort_sql[0] . $from . $where . " GROUP BY pulse_id " . $sort_sql[1]; // use accumulate tables only with timestamp criteria if (file_exists('/tmp/debug_siem')) { error_log("STATS OTX:$sql\n", 3, "/tmp/siem"); }
$et->Mark("Alert Action"); /* Run the query to determine the number of rows (No LIMIT $cnt_sql = "SELECT count(DISTINCT $addr_type_name) " . $from . $where; if (!$use_ac) $qs->GetNumResultRows($cnt_sql, $db);)*/ $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_uidm.php?caller=" . $caller . "&addr_type=" . $addr_type); if ($addr_type == "userdomain") { $src_field = "CONCAT(idm_data.username,'@',idm_data.domain)"; $dst_field = "CONCAT(idm_data.username,'@',idm_data.domain)"; } else { $src_field = "src_" . $addr_type; $dst_field = "dst_" . $addr_type; } //$qro->AddTitle(" "); $qro->AddTitle($type_name, "addr_a", " ", " ORDER BY ip ASC", "addr_d", " ", " ORDER BY ip DESC"); $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $events_title = _("Events") . " # <span class='idminfo' txt='" . Util::timezone(Util::get_timezone()) . "'>(*)</span>"; $qro->AddTitle($events_title, "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(_("Unique Events Src"), "sigsrc_a", " ", " ORDER BY num_sig_src ASC", "sigsrc_d", " ", " ORDER BY num_sig_src DESC"); $qro->AddTitle(_("Unique Events Dst"), "sigdst_a", " ", " ORDER BY num_sig_dst ASC", "sigdst_d", " ", " ORDER BY num_sig_dst DESC"); $qro->AddTitle(_("Unique Src."), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(_("Unique Dst."), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); if (Session::show_entities()) { $src_sql = "SELECT {$src_field} as ip, COUNT(acid_event.id) as num_events, hex(ctx) as ctx, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_src, 0 as num_sig_dst, 0 as num_sip, COUNT( DISTINCT ip_dst ) as num_dip " . $sort_sql[0] . $from . str_replace("SRC_DST", "1", $where) . " GROUP BY ip,acid_event.ctx HAVING num_events>0 AND ip<>'' "; // . $sort_sql[1]; $dst_sql = "SELECT {$dst_field} as ip, COUNT(acid_event.id) as num_events, hex(ctx) as ctx, 0 as num_sig_src, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_dst, COUNT( DISTINCT ip_src ) as num_sip, 0 as num_dip " . $sort_sql[0] . $from . str_replace("SRC_DST", "0", $where) . " GROUP BY ip,acid_event.ctx HAVING num_events>0 AND ip<>'' "; // . $sort_sql[1]; $sql = "SELECT SQL_CALC_FOUND_ROWS ip,ctx as context,sum(num_events) as num_events,sum(num_sig_src) as num_sig_src, sum(num_sig_dst) as num_sig_dst, sum(num_sip) as num_sip,sum(num_dip) as num_dip\n FROM (({$src_sql}) UNION ({$dst_sql})) as u WHERE ip is not NULL GROUP BY ip,context " . $sort_sql[1]; } else {
//$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"); /* Run the query to determine the number of rows (No LIMIT $cnt_sql = "SELECT count(DISTINCT $addr_type_name) " . $from . $where; if (!$use_ac) $qs->GetNumResultRows($cnt_sql, $db);)*/ $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_uaddress.php?caller=" . $caller . "&addr_type=" . $addr_type); $qro->AddTitle(" "); $qro->AddTitle(_("IP address"), "addr_a", " ", " ORDER BY ip ASC", "addr_d", " ", " ORDER BY ip DESC"); $qro->AddTitle(gettext("Sensor") . " #"); $qro->AddTitle(gettext("Total") . " #", "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(_("Unique Events Src"), "sigsrc_a", " ", " ORDER BY num_sig_src ASC", "sigsrc_d", " ", " ORDER BY num_sig_src DESC"); $qro->AddTitle(_("Unique Events Dst"), "sigdst_a", " ", " ORDER BY num_sig_dst ASC", "sigdst_d", " ", " ORDER BY num_sig_dst DESC"); $qro->AddTitle(_("Src. Addr."), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(_("Dest. Addr."), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); $src_sql = "SELECT DISTINCT ip_src as ip, COUNT(acid_event.cid) as num_events, COUNT( DISTINCT acid_event.sid) as num_sensors, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_src, 0 as num_sig_dst, 0 as num_sip, COUNT( DISTINCT ip_dst ) as num_dip " . $sort_sql[0] . $from . $where . " GROUP BY ip_src HAVING num_events>0 " . $sort_sql[1]; $dst_sql = "SELECT DISTINCT ip_dst as ip, COUNT(acid_event.cid) as num_events, COUNT( DISTINCT acid_event.sid) as num_sensors, 0 as num_sig_src, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_dst, COUNT( DISTINCT ip_src ) as num_sip, 0 as num_dip " . $sort_sql[0] . $from . $where . " GROUP BY ip_dst HAVING num_events>0 " . $sort_sql[1]; $sql = "SELECT SQL_CALC_FOUND_ROWS ip,sum(num_events) as num_events,sum(num_sensors) as num_sensors,sum(num_sig_src) as num_sig_src, sum(num_sig_dst) as num_sig_dst, sum(num_sip) as num_sip,sum(num_dip) as num_dip\n \tFROM (({$src_sql}) UNION ({$dst_sql})) as u WHERE ip>0 GROUP BY ip " . $sort_sql[1]; // use accumulate tables only with timestamp criteria if ($use_ac) { // SRC $where = $more = $sqla = $sqlb = $sqlc = "";
//$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"); /* Run the query to determine the number of rows (No LIMIT $cnt_sql = "SELECT count(DISTINCT $addr_type_name) " . $from . $where; */ $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_uaddress.php?caller=" . $caller . "&addr_type=" . $addr_type); $qro->AddTitle(_("IP address"), "addr_a", " ", " ORDER BY ip ASC", "addr_d", " ", " ORDER BY ip DESC"); $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $qro->AddTitle(gettext("Total Src.") . " #", "occur_a", " ", " ORDER BY src_num_events ASC", "occur_d", " ", " ORDER BY src_num_events DESC"); $qro->AddTitle(_("Unique Events Src"), "sigsrc_a", " ", " ORDER BY num_sig_src ASC", "sigsrc_d", " ", " ORDER BY num_sig_src DESC"); $qro->AddTitle(_("Unique Src. Contacted"), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(gettext("Total Dst.") . " #", "occur_ad", " ", " ORDER BY dst_num_events ASC", "occur_dd", " ", " ORDER BY dst_num_events DESC"); $qro->AddTitle(_("Unique Events Dst"), "sigdst_a", " ", " ORDER BY num_sig_dst ASC", "sigdst_d", " ", " ORDER BY num_sig_dst DESC"); $qro->AddTitle(_("Unique Dest. Contacted"), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); if (Session::show_entities()) { $src_sql = "SELECT ip_src as ip, HEX(src_host) AS host_id, ctx, COUNT(acid_event.id) as src_num_events, 0 as dst_num_events, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_src, 0 as num_sig_dst, 0 as num_sip, COUNT( DISTINCT ip_dst ) as num_dip " . $sort_sql[0] . $from . $where . " GROUP BY ip_src,ctx HAVING src_num_events>0 " . $sort_sql[1]; $dst_sql = "SELECT ip_dst as ip, HEX(dst_host) AS host_id, ctx, 0 as src_num_events, COUNT(acid_event.id) as dst_num_events, 0 as num_sig_src, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_dst, COUNT( DISTINCT ip_src ) as num_sip, 0 as num_dip " . $sort_sql[0] . $from . $where . " GROUP BY ip_dst,ctx HAVING dst_num_events>0 " . $sort_sql[1]; $sql = "SELECT SQL_CALC_FOUND_ROWS ip, hex(ctx) as ctx, sum(src_num_events) as src_num_events,sum(dst_num_events) as dst_num_events, sum(num_sig_src) as num_sig_src, sum(num_sig_dst) as num_sig_dst, sum(num_sip) as num_sip,sum(num_dip) as num_dip, host_id\n \tFROM (({$src_sql}) UNION ({$dst_sql})) as u GROUP BY ip,ctx " . $sort_sql[1]; } else { $src_sql = "SELECT ip_src as ip, HEX(src_host) AS host_id, sensor_id, COUNT(acid_event.id) as src_num_events, 0 as dst_num_events, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_src, 0 as num_sig_dst, 0 as num_sip, COUNT( DISTINCT ip_dst ) as num_dip " . $sort_sql[0] . $from . ",device " . $where . " AND device.id=acid_event.device_id GROUP BY ip_src,device.sensor_id HAVING src_num_events>0 " . $sort_sql[1]; $dst_sql = "SELECT ip_dst as ip, HEX(dst_host) AS host_id, sensor_id, 0 as src_num_events, COUNT(acid_event.id) as dst_num_events, 0 as num_sig_src, COUNT( DISTINCT acid_event.plugin_id, acid_event.plugin_sid ) as num_sig_dst, COUNT( DISTINCT ip_src ) as num_sip, 0 as num_dip " . $sort_sql[0] . $from . ",device " . $where . " AND device.id=acid_event.device_id GROUP BY ip_dst,device.sensor_id HAVING dst_num_events>0 " . $sort_sql[1];
//$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"); /* Run the query to determine the number of rows (No LIMIT)*/ $cnt_sql = "SELECT count(DISTINCT {$addr_type_name}) " . $from . $where; if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_uaddr.php?caller=" . $caller . "&addr_type=" . $addr_type); $qro->AddTitle(" "); $qro->AddTitle($results_title, "addr_a", " ", " ORDER BY {$addr_type_name} ASC", "addr_d", " ", " ORDER BY {$addr_type_name} DESC"); if ($resolve_IP == 1) { $qro->AddTitle("FQDN"); } $qro->AddTitle(gettext("Sensor") . " #"); $qro->AddTitle(gettext("Total") . " #", "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Unique Events"), "sig_a", " ", " ORDER BY num_sig ASC", "sig_d", " ", " ORDER BY num_sig DESC"); if ($addr_type == DEST_IP) { $displaytitle = gettext("Displaying unique destination addresses %d-%d of <b>%s</b> matching your selection. <b>%s</b> total events in database."); $qro->AddTitle(gettext("Src. Addr."), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); } else { $displaytitle = gettext("Displaying unique source addresses %d-%d of <b>%s</b> matching your selection. <b>%s</b> total events in database."); $qro->AddTitle(gettext("Dest. Addr."), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); } if (file_exists("../kml/GoogleEarth.php")) {
//$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"); /* Run the query to determine the number of rows (No LIMIT $cnt_sql = "SELECT count(DISTINCT $addr_type_name) " . $from . $where; */ $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_uaddress.php?caller=" . $caller . "&addr_type=" . $addr_type); $qro->AddTitle(_("IP address"), "addr_a", " ", " ORDER BY ip ASC", "addr_d", " ", " ORDER BY ip DESC"); $qro->AddTitle(gettext("OTX")); if ($resolve_IP == 1) { $qro->AddTitle("FQDN"); } $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $qro->AddTitle(gettext("Events Src.") . " # <span class='idminfo' txt='" . Util::timezone(Util::get_timezone()) . "'>(*)</span>", "occur_a", " ", " ORDER BY src_num_events ASC", "occur_d", " ", " ORDER BY src_num_events DESC"); $qro->AddTitle(_("Unique Events Src"), "sigsrc_a", " ", " ORDER BY num_sig_src ASC", "sigsrc_d", " ", " ORDER BY num_sig_src DESC"); $qro->AddTitle(_("Unique Src. Contacted"), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(gettext("Events Dst.") . " # <span class='idminfo' txt='" . Util::timezone(Util::get_timezone()) . "'>(*)</span>", "occur_ad", " ", " ORDER BY dst_num_events ASC", "occur_dd", " ", " ORDER BY dst_num_events DESC"); $qro->AddTitle(_("Unique Events Dst"), "sigdst_a", " ", " ORDER BY num_sig_dst ASC", "sigdst_d", " ", " ORDER BY num_sig_dst DESC"); $qro->AddTitle(_("Unique Dest. Contacted"), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); // Queries if (Session::show_entities()) { $src_sql = "SELECT ip_src as ip, HEX(src_host) AS host_id, ctx, {$nevents} as src_num_events, 0 as dst_num_events, 0 as num_sip, COUNT( DISTINCT ip_dst ) as num_dip, {$uevent} as num_sig_src, 0 as num_sig_dst " . $sort_sql[0] . $from . $where . " GROUP BY ip_src,ctx HAVING src_num_events>0 " . $sort_sql[1];
$debug_time_mode >= 1 ? $et->Mark("Alert Action") : ''; /* Get total number of events */ /* mstone 20050309 this is expensive -- don't do it if we're avoiding count() */ /*if ($avoid_counts != 1 && !$use_ac) { $event_cnt = EventCnt($db); if($event_cnt == 0){ $event_cnt = 1; } }*/ /* create SQL to get Unique Alerts */ //$cnt_sql = "SELECT count(DISTINCT acid_event.plugin_id,acid_event.plugin_sid) " . $from . $where; /* Run the query to determine the number of rows (No LIMIT)*/ $debug_time_mode >= 1 ? $et->Mark("Counting Result size") : ''; /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_alerts.php?caller=" . $caller); $qro->AddTitle(" "); $qro->AddTitle(gettext("Signature"), "sig_a", " ", " ORDER BY plugin_id ASC,plugin_sid", "sig_d", " ", " ORDER BY plugin_id DESC,plugin_sid"); $events_title = _("Events") . " # <span class='idminfo' txt='" . Util::timezone($tz) . "'>(*)</span>"; $qro->AddTitle("<span id='total_title'>{$events_title}</span>", "occur_a", " ", " ORDER BY sig_cnt ASC", "occur_d", " ", " ORDER BY sig_cnt DESC"); $qro->AddTitle(_("Unique Src. #"), "saddr_a", ", count(DISTINCT ip_src) AS saddr_cnt ", " ORDER BY saddr_cnt ASC", "saddr_d", ", count(DISTINCT ip_src) AS saddr_cnt ", " ORDER BY saddr_cnt DESC"); $qro->AddTitle(_("Unique Dst. #"), "daddr_a", ", count(DISTINCT ip_dst) AS daddr_cnt ", " ORDER BY daddr_cnt ASC", "daddr_d", ", count(DISTINCT ip_dst) AS daddr_cnt ", " ORDER BY daddr_cnt DESC"); /*$qro->AddTitle(gettext("First"), "first_a", ", min(timestamp) AS first_timestamp ", " ORDER BY first_timestamp ASC", "first_d", ", min(timestamp) AS first_timestamp ", " ORDER BY first_timestamp DESC"); if ( $show_previous_alert == 1 ) $qro->AddTitle("Previous"); $qro->AddTitle(gettext("Last"),
$qs->AddValidActionOp(gettext("Delete Selected")); $qs->AddValidActionOp(gettext("Delete ALL on Screen")); $qs->SetActionSQL($from . $where); $et->Mark("Initialization"); $qs->RunAction($submit, PAGE_STAT_SENSOR, $db); $et->Mark("Alert Action"); /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT acid_event.sid) " . $from . $where; /* Run the query to determine the number of rows (No LIMIT)*/ if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_sensor.php?caller=" . $caller); $qro->AddTitle(" "); $qro->AddTitle(gettext("Sensor"), "sid_a", " ", " ORDER BY acid_event.sid ASC", "sid_d", " ", " ORDER BY acid_event.sid DESC"); $qro->AddTitle(gettext("Name"), "", " ", " ", "", " ", " "); $qro->AddTitle(gettext("Total Events"), "occur_a", " ", " ORDER BY event_cnt ASC", "occur_d", " ", " ORDER BY event_cnt DESC"); $qro->AddTitle(gettext("Unique Events"), "sig_a", "", " ORDER BY sig_cnt ASC", "sig_d", "", " ORDER BY sig_cnt DESC"); $qro->AddTitle(gettext("Src. Addr."), "saddr_a", "", " ORDER BY saddr_cnt ASC", "saddr_d", "", " ORDER BY saddr_cnt DESC"); $qro->AddTitle(gettext("Dest. Addr."), "daddr_a", "", " ORDER BY daddr_cnt ASC", "daddr_d", "", " ORDER BY daddr_cnt DESC"); $qro->AddTitle(_("First") . " " . Util::timezone($tz), "first_a", "", " ORDER BY first_timestamp ASC", "first_d", "", " ORDER BY first_timestamp DESC"); $qro->AddTitle(_("Last") . " " . Util::timezone($tz), "last_a", "", " ORDER BY last_timestamp ASC", "last_d", "", " ORDER BY last_timestamp DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), ""); $sql = "SELECT DISTINCT acid_event.sid, count(acid_event.cid) as event_cnt," . " count(distinct acid_event.plugin_id, acid_event.plugin_sid) as sig_cnt, " . " count(distinct(acid_event.ip_src)) as saddr_cnt, " . " count(distinct(acid_event.ip_dst)) as daddr_cnt, " . "min(timestamp) as first_timestamp, max(timestamp) as last_timestamp" . $sort_sql[0] . $from . $where . " GROUP BY acid_event.sid " . $sort_sql[1]; //echo $sql."<br>"; // use accumulate tables only with timestamp criteria /* if ($use_ac) { $where = $more = $sqla = $sqlb = $sqlc = "";
/* mstone 20050309 this is expensive -- don't do it if we're avoiding count() */ /*if ($avoid_counts != 1 && !$use_ac) { $event_cnt = EventCnt($db); if($event_cnt == 0){ $event_cnt = 1; } }*/ /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT acid_event.plugin_id) " . $fromcnt . $where; /* Run the query to determine the number of rows (No LIMIT)*/ $qs->GetNumResultRows($cnt_sql, $db); $debug_time_mode >= 1 ? $et->Mark("Counting Result size") : ''; /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_ptypes.php?caller=" . $caller); //$qro->AddTitle(" "); $qro->AddTitle(gettext("Product Type")); $events_title = _("Events") . " # <span class='idminfo' txt='" . Util::timezone($tz) . "'>(*)</span>"; $qro->AddTitle($events_title, "occur_a", " ", " ORDER BY events ASC, product_type DESC", "occur_d", ", ", " ORDER BY events DESC, product_type DESC"); $qro->AddTitle(Session::show_entities() ? gettext("Context") : gettext("Sensor")); $qro->AddTitle(gettext("Last Event")); $qro->AddTitle(gettext("Date") . " " . Util::timezone($tz)); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); /* mstone 20050309 add sig_name to GROUP BY & query so it can be used in postgres ORDER BY */ /* mstone 20050405 add sid & ip counts */ if (Session::show_entities()) { $sql = "SELECT plugin.product_type,hex(acid_event.ctx) as ctx, {$counter} " . $fromcnt . ",alienvault.plugin " . $where . " AND plugin.id=acid_event.plugin_id\n GROUP BY plugin.product_type,ctx " . $sort_sql[1]; $_SESSION['_siem_plugins_query'] = "SELECT plugin_sid.name as sig_name,timestamp\n {$fromplg}, alienvault.plugin " . $where . " AND acid_event.plugin_id=plugin.id AND plugin.product_type=PLUGIN_ID AND acid_event.ctx=UNHEX('DID')\n ORDER BY timestamp DESC LIMIT 1"; } else { $sql = "SELECT plugin.product_type, device_id as ctx, {$counter} " . $fromcnt . ",device,alienvault.plugin " . $where . " AND device.id=acid_event.device_id AND plugin.id=acid_event.plugin_id\n GROUP BY plugin.product_type,device_id " . $sort_sql[1]; $_SESSION['_siem_plugins_query'] = "SELECT plugin_sid.name as sig_name,timestamp\n {$fromplg}, alienvault.plugin " . $where . " AND acid_event.plugin_id=plugin.id AND plugin.product_type=PLUGIN_ID AND acid_event.device_id=DID\n ORDER BY timestamp DESC LIMIT 1"; }
$qs->RunAction($submit, PAGE_STAT_CLASS, $db); $et->Mark("Alert Action"); /* Get total number of events */ if (!$use_ac) { $event_cnt = EventCnt($db); } /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT sig_class_id) " . $from . $where; /* Run the query to determine the number of rows (No LIMIT)*/ if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_class.php?caller=" . $caller); $qro->AddTitle(" "); $qro->AddTitle(gettext("Classification"), "class_a", " ", " ORDER BY sig_class_id ASC", "class_d", " ", " ORDER BY sig_class_id DESC"); $qro->AddTitle(gettext("Total") . " #", "occur_a", " ", " ORDER BY num_events ASC", "occur_d", " ", " ORDER BY num_events DESC"); $qro->AddTitle(gettext("Sensor") . " #", "sensor_a", " ", " ORDER BY num_sensors ASC", "sensor_d", " ", " ORDER BY num_sensors DESC"); $qro->AddTitle(gettext("Signature"), "sig_a", " ", " ORDER BY num_sig ASC", "sig_d", " ", " ORDER BY num_sig DESC"); $qro->AddTitle(gettext("Source Address"), "saddr_a", " ", " ORDER BY num_sip ASC", "saddr_d", " ", " ORDER BY num_sip DESC"); $qro->AddTitle(gettext("Dest. Address"), "daddr_a", " ", " ORDER BY num_dip ASC", "daddr_d", " ", " ORDER BY num_dip DESC"); $qro->AddTitle(gettext("First"), "first_a", " ", " ORDER BY first_timestamp ASC", "first_d", " ", " ORDER BY first_timestamp DESC"); $qro->AddTitle(gettext("Last"), "last_a", " ", " ORDER BY last_timestamp ASC", "last_d", " ", " ORDER BY last_timestamp DESC"); $sort_sql = $qro->GetSortSQL($qs->GetCurrentSort(), $qs->GetCurrentCannedQuerySort()); $sql = "SELECT DISTINCT sig_class_id, " . " COUNT(acid_event.cid) as num_events," . " COUNT( DISTINCT acid_event.sid) as num_sensors, " . " COUNT( DISTINCT signature ) as num_sig, " . " COUNT( DISTINCT ip_src ) as num_sip, " . " COUNT( DISTINCT ip_dst ) as num_dip, " . " min(timestamp) as first_timestamp, " . " max(timestamp) as last_timestamp " . $sort_sql[0] . $from . $where . " GROUP BY sig_class_id " . $sort_sql[1]; //echo $sql."<br>"; // use accumulate tables only with timestamp criteria if ($use_ac) { $where = $more = $sqla = $sqlb = $sqlc = $sqld = ""; if (preg_match("/timestamp/", $criteria_clauses[1])) {
$qs->AddValidActionOp(gettext("Delete Selected")); $qs->AddValidActionOp(gettext("Delete ALL on Screen")); $qs->SetActionSQL($from1 . $where1); $et->Mark("Initialization"); $qs->RunAction($submit, PAGE_STAT_SENSOR, $db); $et->Mark("Alert Action"); /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT acid_event.device_id) " . $from . $where; /* Run the query to determine the number of rows (No LIMIT)*/ if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $et->Mark("Counting Result size"); /* Setup the Query Results Table */ $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") . " # <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];
/*if ($avoid_counts != 1 && !$use_ac) { $event_cnt = EventCnt($db); if($event_cnt == 0){ $event_cnt = 1; } }*/ /* create SQL to get Unique Alerts */ $cnt_sql = "SELECT count(DISTINCT acid_event.plugin_id,acid_event.plugin_sid) " . $from . $where; /* Run the query to determine the number of rows (No LIMIT)*/ if (!$use_ac) { $qs->GetNumResultRows($cnt_sql, $db); } $debug_time_mode >= 1 ? $et->Mark("Counting Result size") : ''; /* Setup the Query Results Table */ $qro = new QueryResultsOutput("base_stat_alerts_graph.php?caller=" . $caller); $qro->AddTitle(" "); $qro->AddTitle(gettext("Signature"), "sig_a", " ", " ORDER BY plugin_id ASC,plugin_sid", "sig_d", " ", " ORDER BY plugin_id DESC,plugin_sid"); $qro->AddTitle(gettext("Total") . " #", "occur_a", " ", " ORDER BY sig_cnt ASC", "occur_d", " ", " ORDER BY sig_cnt DESC"); $qro->AddTitle(gettext("Sensor") . " #"); $qro->AddTitle(_("Src. Addr."), "saddr_a", ", count(DISTINCT ip_src) AS saddr_cnt ", " ORDER BY saddr_cnt ASC", "saddr_d", ", count(DISTINCT ip_src) AS saddr_cnt ", " ORDER BY saddr_cnt DESC"); $qro->AddTitle(_("Dst. Addr."), "daddr_a", ", count(DISTINCT ip_dst) AS daddr_cnt ", " ORDER BY daddr_cnt ASC", "daddr_d", ", count(DISTINCT ip_dst) AS daddr_cnt ", " ORDER BY daddr_cnt DESC"); /*$qro->AddTitle(gettext("First"), "first_a", ", min(timestamp) AS first_timestamp ", " ORDER BY first_timestamp ASC", "first_d", ", min(timestamp) AS first_timestamp ", " ORDER BY first_timestamp DESC"); if ( $show_previous_alert == 1 ) $qro->AddTitle("Previous"); $qro->AddTitle(gettext("Last"),