function SIEM_trend($conn) { require_once '../dashboard/sections/widgets/data/sensor_filter.php'; $tz = Util::get_timezone(); $tzc = Util::get_tzc($tz); $data = array(); $fringe = gmdate('U') - 3600; $fringe = gmdate('Y-m-d H:i:s', $fringe); $ctx_where = Session::get_ctx_where() != '' ? " AND ctx IN (" . Session::get_ctx_where() . ")" : ""; list($join, $where) = make_asset_filter('event'); $sql = "SELECT COUNT(id) as num_events, minute(convert_tz(timestamp,'+00:00','{$tzc}')) as minute\n\t\t\t\tFROM alienvault_siem.acid_event {$join}\n\t\t\t\tWHERE 1=1 {$where} {$ctx_where} AND timestamp >= '{$fringe}' \n\t\t\t\tGROUP BY minute\n\t\t\t\tORDER BY timestamp ASC"; if (!($rg =& $conn->Execute($sql))) { print $conn->ErrorMsg(); } else { while (!$rg->EOF) { $data[$rg->fields['minute']] = $rg->fields['num_events']; $rg->MoveNext(); } } return $data; }
function SIEM_trends_week($param = '') { global $tz; $tzc = Util::get_tzc($tz); $data = array(); $plugins = ''; $plugins_sql = ''; $db = new ossim_db(TRUE); $dbconn = $db->connect(); $_asset_where = make_asset_filter(); $asset_where = $_asset_where[1]; $sensor_where = make_ctx_filter() . $asset_where; $tax_join = ''; if (preg_match("/taxonomy\\=(.+)/", $param, $found)) { if ($found[1] == 'honeypot') { $tax_join = 'alienvault.plugin_sid p, '; $tax_where = 'AND acid_event.plugin_id = p.plugin_id AND acid_event.plugin_sid = p.sid AND p.category_id = 19'; } $param = ''; } elseif ($param == 'ossec%') { $plugins_sql = 'AND acid_event.plugin_id between ' . OSSEC_MIN_PLUGIN_ID . ' AND ' . OSSEC_MAX_PLUGIN_ID; $plugins = OSSEC_MIN_PLUGIN_ID . '-' . OSSEC_MAX_PLUGIN_ID; } $sqlgraph = "SELECT SUM(acid_event.cnt) as num_events, day(convert_tz(timestamp,'+00:00','{$tzc}')) AS intervalo, monthname(convert_tz(timestamp,'+00:00','{$tzc}')) AS suf \n FROM {$tax_join} alienvault_siem.ac_acid_event acid_event\n WHERE timestamp BETWEEN '" . gmdate("Y-m-d 00:00:00", gmdate("U") - 604800) . "' AND '" . gmdate("Y-m-d 23:59:59") . "' {$plugins_sql} {$sensor_where} {$tax_where} \n GROUP BY suf, intervalo \n ORDER BY suf, intervalo"; $rg = $dbconn->CacheExecute($sqlgraph); if (!$rg) { Av_exception::write_log(Av_exception::DB_ERROR, $dbconn->ErrorMsg()); } else { while (!$rg->EOF) { $hours = $rg->fields['intervalo'] . ' ' . substr($rg->fields['suf'], 0, 3); $data[$hours] = $rg->fields['num_events']; $rg->MoveNext(); } } $db->close(); return $param != '' ? array($data, $plugins) : $data; }
$plugins .= ',' . $p_id; } } $plugins = preg_replace("/^,/", '', $plugins); //Risk $risk = 0; //Filters $src_ip = bin2hex(inet_pton(long2ip(GET('f_src_ip')))); $dst_ip = bin2hex(inet_pton(long2ip(GET('f_dst_ip')))); $src_port = intval(GET('f_src_port')); $dst_port = intval(GET('f_dst_port')); $protocol = intval(GET('f_protocol')); if ($from_snort) { session_write_close(); include_once AV_MAIN_ROOT_PATH . '/dashboard/sections/widgets/data/sensor_filter.php'; list($join, $asset_where) = make_asset_filter('event', $acid_table); $where = make_ctx_filter($acid_table) . $asset_where; // Read from acid_event $where .= $plugins != '' ? " AND plugin.id in ({$plugins})" : ""; $where .= GET('f_src_ip') != '' && $src_ip != '' ? " AND {$acid_table}.ip_src=unhex('{$src_ip}')" : ''; $where .= GET('f_dst_ip') != '' && $dst_ip != '' ? " AND {$acid_table}.ip_dst=unhex('{$dst_ip}')" : ''; $where .= $src_port != 0 ? " AND {$acid_table}.layer4_sport={$src_port}" : ''; $where .= $dst_port != 0 ? " AND {$acid_table}.layer4_dport={$dst_port}" : ''; $where .= $protocol != 0 ? " AND {$acid_table}.ip_proto={$protocol}" : ''; // Limit in second select when sensor is specified (OJO) $key_index = $plugins != '' ? '' : str_replace("IND", "timestamp", $key_index); $sql = "select {$acid_table}.plugin_id, {$acid_table}.plugin_sid,\n TO_SECONDS(timestamp)-62167219200+TO_SECONDS(UTC_TIMESTAMP())-TO_SECONDS(NOW()) as id,\n hex({$acid_table}.id) as event_id,\n plugin_sid.name as plugin_sid_name,\n ip_src, ip_dst,\n HEX(src_host) AS src_host, HEX(dst_host) AS dst_host, HEX(src_net) AS src_net, HEX(dst_net) AS dst_net,\n HEX({$acid_table}.ctx) AS ctx,\n convert_tz(timestamp,'+00:00','{$tzc}') as timestamp1,\n ossim_risk_a as risk_a, ossim_risk_c as risk_c,\n layer4_sport as src_port, layer4_dport as dst_port,\n ossim_priority as priority, ossim_reliability as reliability,\n ossim_asset_src as asset_src, ossim_asset_dst as asset_dst,\n ip_proto as protocol, device.interface, device.id as device_id\n FROM alienvault_siem.device, {$acid_table} {$key_index} LEFT JOIN alienvault.plugin_sid ON plugin_sid.plugin_id={$acid_table}.plugin_id AND plugin_sid.sid={$acid_table}.plugin_sid LEFT JOIN alienvault.plugin ON plugin.id={$acid_table}.plugin_id WHERE device.id = {$acid_table}.device_id " . $where . " order by timestamp desc limit {$max_rows}"; if (file_exists("/tmp/debug_siem")) { error_log("{$sql}\n", 3, "/tmp/realtime"); } // QUERY DEBUG:
} } $nodata_text = _('No events found'); $db = new ossim_db(TRUE); $conn = $db->connect(); session_write_close(); $data = ''; $urls = ''; $colors = '"#E9967A","#9BC3CF"'; $range = 604800; //24*60*60*7 --> Week $h = 250; // Graph Height $f_url = "../forensics/base_qry_main.php?clear_allcriteria=1&time_range=week&time[0][0]=+&time[0][1]=>%3D&time[0][2]=" . gmdate("m", $timetz - $range) . "&time[0][3]=" . gmdate("d", $timetz - $range) . "&time[0][4]=" . gmdate("Y", $timetz - $range) . "&time[0][5]=&time[0][6]=&time[0][7]=&time[0][8]=+&time[0][9]=+&submit=Query+DB&num_result_rows=-1&time_cnt=1&sort_order=time_d"; $tz = Util::get_timezone(); list($join, $asset_where) = make_asset_filter("event", "a"); $sensor_where = make_ctx_filter("a") . $asset_where; $query = "SELECT sum(a.cnt) as num_events,c.cat_id,c.id,c.name FROM alienvault_siem.ac_acid_event a,alienvault.plugin_sid p,alienvault.subcategory c WHERE c.id=p.subcategory_id AND p.plugin_id=a.plugin_id AND p.sid=a.plugin_sid AND a.timestamp BETWEEN '" . gmdate("Y-m-d H:i:s", strtotime(date("Y-m-d 00:00:00")) - $range + -$tz) . "' AND '" . gmdate("Y-m-d 23:59:59") . "' {$sensor_where} TAXONOMY group by c.id,c.name order by num_events desc LIMIT 10"; switch (GET('type')) { // Top 10 Events by Product Type - Last Week case "source_type": $types = $ac = array(); if (!($rp = $conn->CacheExecute("SELECT plugin.id, product_type.name AS source_type FROM alienvault.plugin, alienvault.product_type WHERE product_type.id = plugin.product_type"))) { print $conn->ErrorMsg(); } else { while (!$rp->EOF) { if ($rp->fields["source_type"] == '') { $rp->fields["source_type"] = _('Unknown type'); } $types[$rp->fields["id"]] = $rp->fields["source_type"]; $rp->MoveNext();