239 lines
11 KiB
PHP
239 lines
11 KiB
PHP
<?php
|
|
use SyslogDash\SyslogQuery;
|
|
use SyslogDash\GeoIP;
|
|
|
|
$sq = new SyslogQuery();
|
|
|
|
$period = (int)($_GET['period'] ?? 30);
|
|
if (!in_array($period, [7, 30, 90, 365, 0])) $period = 30;
|
|
|
|
$report = $_GET['report'] ?? 'daily';
|
|
$allowedReports = ['daily', 'hosts', 'countries', 'severity', 'tags', 'hourly', 'custom'];
|
|
if (!in_array($report, $allowedReports)) $report = 'daily';
|
|
|
|
$chartData = [];
|
|
$chartType = 'bar';
|
|
$chartLabels = [];
|
|
$chartValues = [];
|
|
$chartLabel = '';
|
|
$extraRows = [];
|
|
|
|
switch ($report) {
|
|
case 'daily':
|
|
$chartData = $sq->getDailyStats($period);
|
|
$chartLabels = array_map(fn($d) => date('d-m', strtotime($d['date'])), $chartData);
|
|
$chartValues = array_column($chartData, 'cnt');
|
|
$chartLabel = 'Berichten per dag';
|
|
$chartType = 'bar';
|
|
$extraRows = $chartData;
|
|
break;
|
|
|
|
case 'hosts':
|
|
$chartData = $sq->getHostStats($period);
|
|
$chartLabels = array_column($chartData, 'FromHost');
|
|
$chartValues = array_column($chartData, 'cnt');
|
|
$chartLabel = 'Top hosts';
|
|
$chartType = 'bar';
|
|
break;
|
|
|
|
case 'countries':
|
|
$chartData = $sq->getCountryStats($period);
|
|
$chartLabels = array_map(fn($c) => GeoIP::getCountryName($c['code']) . ' (' . $c['code'] . ')', $chartData);
|
|
$chartValues = array_column($chartData, 'count');
|
|
$chartLabel = 'Berichten per land';
|
|
$chartType = 'bar';
|
|
break;
|
|
|
|
case 'severity':
|
|
$chartData = $sq->getSeverityStats($period);
|
|
$chartLabels = array_map(fn($s) => SyslogQuery::severityName((int)$s['Priority']), $chartData);
|
|
$chartValues = array_column($chartData, 'cnt');
|
|
$chartLabel = 'Severity verdeling';
|
|
$chartType = 'doughnut';
|
|
break;
|
|
|
|
case 'tags':
|
|
$chartData = $sq->getTagStats($period);
|
|
$chartLabels = array_column($chartData, 'SysLogTag');
|
|
$chartValues = array_column($chartData, 'cnt');
|
|
$chartLabel = 'Top tags';
|
|
$chartType = 'bar';
|
|
break;
|
|
|
|
case 'hourly':
|
|
$db = \SyslogDash\Database::getInstance();
|
|
$table = Database::table();
|
|
$stmt = $db->prepare("
|
|
SELECT HOUR(`ReceivedAt`) AS h, COUNT(*) AS cnt
|
|
FROM `{$table}`
|
|
WHERE `ReceivedAt` >= :since
|
|
GROUP BY HOUR(`ReceivedAt`)
|
|
ORDER BY h ASC
|
|
");
|
|
$stmt->execute([':since' => date('Y-m-d H:i:s', strtotime("-{$period} days"))]);
|
|
$chartData = $stmt->fetchAll();
|
|
$hourlyData = array_fill(0, 24, 0);
|
|
foreach ($chartData as $r) $hourlyData[(int)$r['h']] = (int)$r['cnt'];
|
|
$chartLabels = array_map(fn($h) => sprintf('%02d:00', $h), range(0, 23));
|
|
$chartValues = $hourlyData;
|
|
$chartLabel = 'Berichten per uur';
|
|
$chartType = 'line';
|
|
break;
|
|
|
|
case 'custom':
|
|
$sql = $_POST['sql'] ?? '';
|
|
$customResult = null;
|
|
if ($sql && preg_match('/^\s*SELECT/i', $sql)) {
|
|
$customResult = $sq->runQuery($sql);
|
|
}
|
|
break;
|
|
}
|
|
?>
|
|
<div class="d-flex justify-content-between align-items-center mb-4">
|
|
<h2 class="mb-0"><i class="bi bi-bar-chart-line me-2"></i>BI Rapporten</h2>
|
|
<div class="btn-group btn-group-sm">
|
|
<a href="?page=bi&period=7&report=<?= $report ?>" class="btn btn-outline-primary <?= $period === 7 ? 'active' : '' ?>">7d</a>
|
|
<a href="?page=bi&period=30&report=<?= $report ?>" class="btn btn-outline-primary <?= $period === 30 ? 'active' : '' ?>">30d</a>
|
|
<a href="?page=bi&period=90&report=<?= $report ?>" class="btn btn-outline-primary <?= $period === 90 ? 'active' : '' ?>">90d</a>
|
|
<a href="?page=bi&period=365&report=<?= $report ?>" class="btn btn-outline-primary <?= $period === 365 ? 'active' : '' ?>">365d</a>
|
|
<a href="?page=bi&period=0&report=<?= $report ?>" class="btn btn-outline-primary <?= $period === 0 ? 'active' : '' ?>">Alles</a>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="row g-3 mb-4">
|
|
<div class="col-md-2">
|
|
<div class="list-group list-group-sm">
|
|
<a href="?page=bi&period=<?= $period ?>&report=daily" class="list-group-item list-group-item-action py-2 <?= $report === 'daily' ? 'active' : '' ?>">
|
|
<i class="bi bi-calendar me-2"></i>Dagelijks
|
|
</a>
|
|
<a href="?page=bi&period=<?= $period ?>&report=hourly" class="list-group-item list-group-item-action py-2 <?= $report === 'hourly' ? 'active' : '' ?>">
|
|
<i class="bi bi-clock me-2"></i>Per uur
|
|
</a>
|
|
<a href="?page=bi&period=<?= $period ?>&report=hosts" class="list-group-item list-group-item-action py-2 <?= $report === 'hosts' ? 'active' : '' ?>">
|
|
<i class="bi bi-hdd-network me-2"></i>Top hosts
|
|
</a>
|
|
<a href="?page=bi&period=<?= $period ?>&report=countries" class="list-group-item list-group-item-action py-2 <?= $report === 'countries' ? 'active' : '' ?>">
|
|
<i class="bi bi-globe me-2"></i>Landen
|
|
</a>
|
|
<a href="?page=bi&period=<?= $period ?>&report=severity" class="list-group-item list-group-item-action py-2 <?= $report === 'severity' ? 'active' : '' ?>">
|
|
<i class="bi bi-exclamation-triangle me-2"></i>Severity
|
|
</a>
|
|
<a href="?page=bi&period=<?= $period ?>&report=tags" class="list-group-item list-group-item-action py-2 <?= $report === 'tags' ? 'active' : '' ?>">
|
|
<i class="bi bi-tags me-2"></i>Tags
|
|
</a>
|
|
<a href="?page=bi&period=<?= $period ?>&report=custom" class="list-group-item list-group-item-action py-2 <?= $report === 'custom' ? 'active' : '' ?>">
|
|
<i class="bi bi-code-slash me-2"></i>Custom SQL
|
|
</a>
|
|
</div>
|
|
</div>
|
|
<div class="col-md-10">
|
|
<?php if ($report === 'custom'): ?>
|
|
<div class="card shadow-sm">
|
|
<div class="card-header bg-white"><i class="bi bi-code-slash me-2"></i>Custom SQL Query</div>
|
|
<div class="card-body">
|
|
<form method="POST" action="?page=bi&period=<?= $period ?>&report=custom">
|
|
<div class="mb-3">
|
|
<textarea name="sql" class="form-control font-monospace" rows="4" placeholder="SELECT COUNT(*), FromHost FROM SystemEvents GROUP BY FromHost ORDER BY 1 DESC LIMIT 10"><?= htmlspecialchars($_POST['sql'] ?? '') ?></textarea>
|
|
</div>
|
|
<button type="submit" class="btn btn-primary"><i class="bi bi-play me-1"></i>Uitvoeren</button>
|
|
</form>
|
|
<?php if (isset($customResult)): ?>
|
|
<?php if ($customResult['error']): ?>
|
|
<div class="alert alert-danger mt-3"><?= htmlspecialchars($customResult['error']) ?></div>
|
|
<?php elseif (!empty($customResult['rows'])): ?>
|
|
<div class="table-responsive mt-3" style="max-height:500px">
|
|
<table class="table table-sm table-hover table-bordered small mb-0">
|
|
<thead class="table-light">
|
|
<tr>
|
|
<?php foreach (array_keys($customResult['rows'][0]) as $col): ?>
|
|
<th><?= htmlspecialchars($col) ?></th>
|
|
<?php endforeach; ?>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($customResult['rows'] as $r): ?>
|
|
<tr>
|
|
<?php foreach ($r as $v): ?>
|
|
<td><?= htmlspecialchars(mb_substr((string)$v, 0, 200)) ?></td>
|
|
<?php endforeach; ?>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<small class="text-muted mt-2 d-block"><?= count($customResult['rows']) ?> rijen</small>
|
|
<?php endif; ?>
|
|
<?php endif; ?>
|
|
</div>
|
|
</div>
|
|
<?php else: ?>
|
|
<div class="card shadow-sm mb-4">
|
|
<div class="card-header bg-white d-flex justify-content-between align-items-center">
|
|
<span><i class="bi bi-graph-up me-2"></i><?= $chartLabel ?></span>
|
|
<div>
|
|
<a href="?page=export&report=<?= $report ?>&period=<?= $period ?>" class="btn btn-sm btn-outline-success"><i class="bi bi-download"></i> CSV</a>
|
|
</div>
|
|
</div>
|
|
<div class="card-body">
|
|
<canvas id="biChart" height="300"></canvas>
|
|
</div>
|
|
</div>
|
|
|
|
<?php if ($report === 'daily' && !empty($extraRows)): ?>
|
|
<div class="card shadow-sm">
|
|
<div class="card-header bg-white"><i class="bi bi-table me-2"></i>Dagelijkse gegevens</div>
|
|
<div class="card-body p-0">
|
|
<div class="table-responsive" style="max-height:400px">
|
|
<table class="table table-sm table-hover mb-0">
|
|
<thead class="table-light">
|
|
<tr><th>Datum</th><th>Berichten</th><th>Hosts</th></tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($extraRows as $r): ?>
|
|
<tr>
|
|
<td><?= date('d-m-Y', strtotime($r['date'])) ?></td>
|
|
<td><?= number_format((int)$r['cnt'], 0, ',', '.') ?></td>
|
|
<td><?= $r['hosts'] ?></td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
<?php endif; ?>
|
|
<?php endif; ?>
|
|
</div>
|
|
</div>
|
|
|
|
<?php if ($report !== 'custom' && !empty($chartLabels)): ?>
|
|
<script>
|
|
document.addEventListener('DOMContentLoaded', function () {
|
|
var ctx = document.getElementById('biChart');
|
|
if (!ctx) return;
|
|
var config = {
|
|
type: '<?= $chartType ?>',
|
|
data: {
|
|
labels: <?= json_encode($chartLabels) ?>,
|
|
datasets: [{
|
|
label: '<?= $chartLabel ?>',
|
|
data: <?= json_encode($chartValues) ?>,
|
|
backgroundColor: <?= $chartType === 'doughnut'
|
|
? "['#dc3545','#ffc107','#0dcaf0','#198754','#6f42c1','#fd7e14','#20c997','#0d6efd']"
|
|
: "'#0d6efd'" ?>,
|
|
borderWidth: 1
|
|
}]
|
|
},
|
|
options: {
|
|
responsive: true,
|
|
maintainAspectRatio: false,
|
|
plugins: { legend: { position: 'top', labels: { boxWidth: 12 } } },
|
|
scales: <?= $chartType !== 'doughnut' ? "{ y: { beginAtZero: true } }" : "{}" ?>
|
|
}
|
|
};
|
|
new Chart(ctx, config);
|
|
});
|
|
</script>
|
|
<?php endif; ?>
|
|
<?php $sq = null; ?>
|