/**
* Get Event
*
* @author AvB
**/
function get($minutes = 60, $type = 'all', $module = 'all')
{
$queryobj = new Event_model();
$queryobj = new Reportdata_model();
$fromtime = time() - 60 * $minutes;
$out['items'] = array();
$out['error'] = '';
$sql = "SELECT m.serial_number, module, type, msg, data, m.timestamp,\n\t\t\t\t\tmachine.computer_name\n\t\t\t\tFROM event m \n\t\t\t\tLEFT JOIN reportdata USING (serial_number) \n\t\t\t\tLEFT JOIN machine USING (serial_number) \n\t\t\t\tWHERE m.timestamp > {$fromtime} \n\t\t\t\t" . get_machine_group_filter('AND') . "\n\t\t\t\tORDER BY m.timestamp DESC";
foreach ($queryobj->query($sql) as $obj) {
$out['items'][] = $obj;
}
$obj = new View();
$obj->view('json', array('msg' => $out));
}
/**
* REST interface, returns json with ip address ranges
* defined in conf('ipv4router')
* or passed with GET request
*
* @return void
* @author AvB
**/
function routers()
{
if (!$this->authorized()) {
die('Authenticate first.');
// Todo: return json?
}
$router_arr = array();
// See if we're being parsed a request object
if (array_key_exists('req', $_GET)) {
$router_arr = (array) json_decode($_GET['req']);
}
if (!$router_arr) {
$router_arr = conf('ipv4routers', array());
}
$out = array();
$reportdata = new Reportdata_model();
// Compile SQL
$cnt = 0;
$sel_arr = array('COUNT(1) as count');
foreach ($router_arr as $key => $value) {
if (is_scalar($value)) {
$value = array($value);
}
$when_str = '';
foreach ($value as $k => $v) {
$when_str .= sprintf(" WHEN ipv4router LIKE '%s%%' THEN 1", $v);
}
$sel_arr[] = "SUM(CASE {$when_str} ELSE 0 END) AS r{$cnt}";
$cnt++;
}
$sql = "SELECT " . implode(', ', $sel_arr) . " FROM network\n\t\t\tWHERE ipv4router != '(null)' AND ipv4router != ''";
// Create Out array
if ($obj = current($reportdata->query($sql))) {
$cnt = $total = 0;
foreach ($router_arr as $key => $value) {
$col = 'r' . $cnt++;
$out[] = array('key' => $key, 'cnt' => intval($obj->{$col}));
$total += $obj->{$col};
}
// Add Remaining IP's as other
if ($obj->count - $total) {
$out[] = array('key' => 'Other', 'cnt' => $obj->count - $total);
}
}
$obj = new View();
$obj->view('json', array('msg' => $out));
}
/**
* Flotr2 interface, returns json with ip address ranges
* defined in conf('ip_ranges')
* or passed with GET request
*
* @return void
* @author AvB
**/
function ip()
{
$ip_arr = array();
// See if we're being parsed a request object
if (array_key_exists('req', $_GET)) {
$ip_arr = (array) json_decode($_GET['req']);
}
if (!$ip_arr) {
$ip_arr = conf('ip_ranges');
}
$out = array();
$reportdata = new Reportdata_model();
// Compile SQL
$cnt = 0;
$sel_arr = array('COUNT(1) as count');
foreach ($ip_arr as $key => $value) {
if (is_scalar($value)) {
$value = array($value);
}
$when_str = '';
foreach ($value as $k => $v) {
$when_str .= sprintf(" WHEN remote_ip LIKE '%s%%' THEN 1", $v);
}
$sel_arr[] = "SUM(CASE {$when_str} ELSE 0 END) AS r{$cnt}";
$cnt++;
}
$sql = "SELECT " . implode(', ', $sel_arr) . " FROM reportdata";
// Create Out array
if ($obj = current($reportdata->query($sql))) {
$cnt = $total = 0;
foreach ($ip_arr as $key => $value) {
$col = 'r' . $cnt++;
$out[] = array('label' => $key, 'data' => array(array(0, intval($obj->{$col}))));
$total += $obj->{$col};
}
// Add Remaining IP's as other
$out[] = array('label' => 'Other', 'data' => array(array(0, intval($obj->count - $total))));
}
$obj = new View();
$obj->view('json', array('msg' => $out));
}
<?php
$queryobj = new Reportdata_model();
$now = time();
$hour_ago = $now - 3600;
$today = strtotime('today');
$week_ago = $now - 3600 * 24 * 7;
$month_ago = $now - 3600 * 24 * 30;
$three_month_ago = $now - 3600 * 24 * 90;
$sql = "SELECT COUNT(1) as total, \n\tCOUNT(CASE WHEN timestamp > {$hour_ago} THEN 1 END) AS lasthour, \n\tCOUNT(CASE WHEN timestamp > {$today} THEN 1 END) AS today, \n\tCOUNT(CASE WHEN timestamp > {$week_ago} THEN 1 END) AS lastweek,\n\tCOUNT(CASE WHEN timestamp > {$month_ago} THEN 1 END) AS lastmonth,\n\tCOUNT(CASE WHEN timestamp BETWEEN {$month_ago} AND {$week_ago} THEN 1 END) AS inactive_week,\n\tCOUNT(CASE WHEN timestamp BETWEEN {$three_month_ago} AND {$month_ago} THEN 1 END) AS inactive_month,\n\tCOUNT(CASE WHEN timestamp < {$three_month_ago} THEN 1 END) AS inactive_three_month\n\tFROM reportdata";
$obj = current($queryobj->query($sql));
?>
<div class="col-lg-4 col-md-6">
<div class="panel panel-default">
<div class="panel-heading">
<h3 class="panel-title"><i class="fa fa-group"></i> Active clients</h3>
</div>
<div class="panel-body text-center">
<?php
if ($obj) {
?>
<a href="<?php
echo url('show/listing/clients');
<h3 class="panel-title"><i class="fa fa-power-off"></i> Uptime</h3>
</div>
<div class="panel-body text-center">
<?php
//FIXME use query to count totals!
$machine = new Reportdata_model();
$in_green = 0;
$in_yellow = 0;
$in_red = 0;
$filter = get_machine_group_filter('AND');
$sql = "SELECT timestamp, uptime\n\t\t\t\t\t\t\t\t\t\tFROM reportdata\n\t\t\t\t\t\t\t\t\t\tWHERE uptime <> '0'\n\t\t\t\t\t\t\t\t\t\t{$filter}\n\t\t\t\t\t\t\t\t\t\tORDER BY timestamp DESC";
foreach ($machine->query($sql) as $obj) {
if ($obj->uptime <= 86400) {
$in_green += 1;
} elseif ($obj->uptime >= 604800) {
$in_red += 1;
} else {
$in_yellow += 1;
}
}
// end foreach
?>
<a href="<?php
echo url('show/listing/clients');
?>
" class="btn btn-danger">
