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 = 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:
$rs = $conn->Execute($sql);
}
$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();
}
