619 lines
18 KiB
PHP
619 lines
18 KiB
PHP
<?php // phpcs:ignoreFile
|
|
/**
|
|
* Alert Log Viewer - View historical alert logs
|
|
*/
|
|
|
|
require __DIR__ . '/session.php';
|
|
require __DIR__ . '/userAccess.php';
|
|
|
|
// ============================================================================
|
|
// Database Configuration
|
|
// ============================================================================
|
|
$config = [
|
|
'host' => '192.168.0.16',
|
|
'database' => 'controls',
|
|
'username' => 'lasucaai',
|
|
'password' => 'is413#dfslw',
|
|
];
|
|
|
|
// ============================================================================
|
|
// Database Connection
|
|
// ============================================================================
|
|
function getAlertLogConnection($config) {
|
|
$conn = mysqli_connect(
|
|
$config['host'],
|
|
$config['username'],
|
|
$config['password'],
|
|
$config['database']
|
|
);
|
|
|
|
if ($conn) {
|
|
mysqli_set_charset($conn, 'utf8mb4');
|
|
}
|
|
|
|
return $conn ?: null;
|
|
}
|
|
|
|
// ============================================================================
|
|
// Data Queries
|
|
// ============================================================================
|
|
function getAlertLogs($conn, $filters = []) {
|
|
$params = [];
|
|
$types = '';
|
|
|
|
$sql = "SELECT id, alert_type, alert_tag, alert_value, threshold_value,
|
|
alert_message, source_page, logged_at
|
|
FROM alert_log
|
|
WHERE 1=1";
|
|
|
|
// Filter by alert type
|
|
if (!empty($filters['type'])) {
|
|
$sql .= " AND alert_type = ?";
|
|
$params[] = $filters['type'];
|
|
$types .= 's';
|
|
}
|
|
|
|
// Filter by tag
|
|
if (!empty($filters['tag'])) {
|
|
$sql .= " AND alert_tag = ?";
|
|
$params[] = $filters['tag'];
|
|
$types .= 's';
|
|
}
|
|
|
|
// Filter by date range
|
|
if (!empty($filters['date_from'])) {
|
|
$sql .= " AND logged_at >= ?";
|
|
$params[] = $filters['date_from'] . ' 00:00:00';
|
|
$types .= 's';
|
|
}
|
|
|
|
if (!empty($filters['date_to'])) {
|
|
$sql .= " AND logged_at <= ?";
|
|
$params[] = $filters['date_to'] . ' 23:59:59';
|
|
$types .= 's';
|
|
}
|
|
|
|
$sql .= " ORDER BY logged_at DESC LIMIT 500";
|
|
|
|
$logs = [];
|
|
|
|
if (!empty($params)) {
|
|
$stmt = mysqli_prepare($conn, $sql);
|
|
if ($stmt) {
|
|
mysqli_stmt_bind_param($stmt, $types, ...$params);
|
|
mysqli_stmt_execute($stmt);
|
|
$result = mysqli_stmt_get_result($stmt);
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$logs[] = $row;
|
|
}
|
|
mysqli_stmt_close($stmt);
|
|
}
|
|
} else {
|
|
$result = mysqli_query($conn, $sql);
|
|
if ($result) {
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$logs[] = $row;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $logs;
|
|
}
|
|
|
|
function getAlertStats($conn) {
|
|
$sql = "SELECT
|
|
COUNT(*) as total_alerts,
|
|
COUNT(DISTINCT alert_tag) as unique_tags,
|
|
MIN(logged_at) as first_alert,
|
|
MAX(logged_at) as last_alert,
|
|
SUM(CASE WHEN alert_type = 'danger' THEN 1 ELSE 0 END) as danger_count,
|
|
SUM(CASE WHEN alert_type = 'warning' THEN 1 ELSE 0 END) as warning_count,
|
|
SUM(CASE WHEN alert_type = 'info' THEN 1 ELSE 0 END) as info_count,
|
|
SUM(CASE WHEN logged_at > DATE_SUB(NOW(), INTERVAL 24 HOUR) THEN 1 ELSE 0 END) as last_24h
|
|
FROM alert_log";
|
|
|
|
$result = mysqli_query($conn, $sql);
|
|
return $result ? mysqli_fetch_assoc($result) : null;
|
|
}
|
|
|
|
function getDistinctTags($conn) {
|
|
$sql = "SELECT DISTINCT alert_tag FROM alert_log ORDER BY alert_tag";
|
|
$result = mysqli_query($conn, $sql);
|
|
$tags = [];
|
|
if ($result) {
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$tags[] = $row['alert_tag'];
|
|
}
|
|
}
|
|
return $tags;
|
|
}
|
|
|
|
function getAlertsByHour($conn, $days = 7) {
|
|
$sql = "SELECT
|
|
DATE(logged_at) as alert_date,
|
|
HOUR(logged_at) as alert_hour,
|
|
COUNT(*) as count
|
|
FROM alert_log
|
|
WHERE logged_at > DATE_SUB(NOW(), INTERVAL ? DAY)
|
|
GROUP BY DATE(logged_at), HOUR(logged_at)
|
|
ORDER BY alert_date, alert_hour";
|
|
|
|
$stmt = mysqli_prepare($conn, $sql);
|
|
$data = [];
|
|
|
|
if ($stmt) {
|
|
mysqli_stmt_bind_param($stmt, 'i', $days);
|
|
mysqli_stmt_execute($stmt);
|
|
$result = mysqli_stmt_get_result($stmt);
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$data[] = $row;
|
|
}
|
|
mysqli_stmt_close($stmt);
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
function getTopAlerts($conn, $limit = 10) {
|
|
$sql = "SELECT alert_tag, alert_type, COUNT(*) as count,
|
|
MIN(alert_value) as min_value,
|
|
MAX(alert_value) as max_value,
|
|
AVG(alert_value) as avg_value,
|
|
MAX(logged_at) as last_seen
|
|
FROM alert_log
|
|
GROUP BY alert_tag, alert_type
|
|
ORDER BY count DESC
|
|
LIMIT ?";
|
|
|
|
$stmt = mysqli_prepare($conn, $sql);
|
|
$data = [];
|
|
|
|
if ($stmt) {
|
|
mysqli_stmt_bind_param($stmt, 'i', $limit);
|
|
mysqli_stmt_execute($stmt);
|
|
$result = mysqli_stmt_get_result($stmt);
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$data[] = $row;
|
|
}
|
|
mysqli_stmt_close($stmt);
|
|
}
|
|
|
|
return $data;
|
|
}
|
|
|
|
// ============================================================================
|
|
// Main Logic
|
|
// ============================================================================
|
|
$conn = getAlertLogConnection($config);
|
|
$connectionError = $conn === null;
|
|
$tableExists = false;
|
|
|
|
$stats = null;
|
|
$logs = [];
|
|
$distinctTags = [];
|
|
$topAlerts = [];
|
|
$alertsByHour = [];
|
|
|
|
// Get filter values
|
|
$filterType = isset($_GET['type']) ? trim($_GET['type']) : '';
|
|
$filterTag = isset($_GET['tag']) ? trim($_GET['tag']) : '';
|
|
$filterDateFrom = isset($_GET['date_from']) ? trim($_GET['date_from']) : '';
|
|
$filterDateTo = isset($_GET['date_to']) ? trim($_GET['date_to']) : '';
|
|
|
|
if (!$connectionError) {
|
|
// Check if table exists
|
|
$tableCheck = mysqli_query($conn, "SHOW TABLES LIKE 'alert_log'");
|
|
$tableExists = $tableCheck && mysqli_num_rows($tableCheck) > 0;
|
|
|
|
if ($tableExists) {
|
|
$stats = getAlertStats($conn);
|
|
$distinctTags = getDistinctTags($conn);
|
|
$topAlerts = getTopAlerts($conn, 10);
|
|
$alertsByHour = getAlertsByHour($conn, 7);
|
|
|
|
$logs = getAlertLogs($conn, [
|
|
'type' => $filterType,
|
|
'tag' => $filterTag,
|
|
'date_from' => $filterDateFrom,
|
|
'date_to' => $filterDateTo,
|
|
]);
|
|
}
|
|
|
|
mysqli_close($conn);
|
|
}
|
|
|
|
// ============================================================================
|
|
// Page Layout
|
|
// ============================================================================
|
|
$pageTitle = 'Alert Log Viewer';
|
|
$pageSubtitle = 'System Alert History';
|
|
$pageDescription = 'View and analyze historical system alerts.';
|
|
$layoutWithoutSidebar = true;
|
|
$layoutReturnUrl = 'overview.php';
|
|
$layoutReturnLabel = 'Back to overview';
|
|
$assetBasePath = '';
|
|
|
|
require __DIR__ . '/includes/layout/header.php';
|
|
?>
|
|
|
|
<style>
|
|
/* Stats Grid */
|
|
.alert-stats {
|
|
display: grid;
|
|
grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
|
|
gap: 1rem;
|
|
margin-bottom: 2rem;
|
|
}
|
|
|
|
.alert-stat {
|
|
background: var(--surface, #1a1a2e);
|
|
border: 1px solid var(--border, #333);
|
|
border-radius: 8px;
|
|
padding: 1rem;
|
|
text-align: center;
|
|
}
|
|
|
|
.alert-stat__value {
|
|
font-size: 2rem;
|
|
font-weight: 700;
|
|
color: var(--text);
|
|
}
|
|
|
|
.alert-stat__label {
|
|
font-size: 0.75rem;
|
|
color: var(--text-muted, #888);
|
|
text-transform: uppercase;
|
|
letter-spacing: 0.05em;
|
|
}
|
|
|
|
.alert-stat--danger .alert-stat__value { color: #ef4444; }
|
|
.alert-stat--warning .alert-stat__value { color: #f59e0b; }
|
|
.alert-stat--info .alert-stat__value { color: #3b82f6; }
|
|
|
|
/* Filter Form */
|
|
.filter-form {
|
|
background: var(--surface, #1a1a2e);
|
|
border: 1px solid var(--border, #333);
|
|
border-radius: 8px;
|
|
padding: 1rem;
|
|
margin-bottom: 1.5rem;
|
|
display: flex;
|
|
flex-wrap: wrap;
|
|
gap: 1rem;
|
|
align-items: flex-end;
|
|
}
|
|
|
|
.filter-group {
|
|
display: flex;
|
|
flex-direction: column;
|
|
gap: 0.25rem;
|
|
}
|
|
|
|
.filter-group label {
|
|
font-size: 0.75rem;
|
|
color: var(--text-muted, #888);
|
|
text-transform: uppercase;
|
|
}
|
|
|
|
.filter-group select,
|
|
.filter-group input {
|
|
padding: 0.5rem 0.75rem;
|
|
border: 1px solid var(--border);
|
|
border-radius: 4px;
|
|
background: var(--surface-alt);
|
|
color: var(--text);
|
|
font-size: 0.875rem;
|
|
min-width: 150px;
|
|
}
|
|
|
|
.filter-btn {
|
|
padding: 0.5rem 1rem;
|
|
background: var(--accent, #3b82f6);
|
|
color: white;
|
|
border: none;
|
|
border-radius: 4px;
|
|
cursor: pointer;
|
|
font-size: 0.875rem;
|
|
}
|
|
|
|
.filter-btn:hover {
|
|
background: var(--accent-hover, #2563eb);
|
|
}
|
|
|
|
.filter-btn--clear {
|
|
background: transparent;
|
|
border: 1px solid var(--border, #333);
|
|
color: var(--text-muted, #888);
|
|
}
|
|
|
|
/* Log Table */
|
|
.log-table-container {
|
|
background: var(--surface, #1a1a2e);
|
|
border: 1px solid var(--border, #333);
|
|
border-radius: 8px;
|
|
overflow: hidden;
|
|
}
|
|
|
|
.log-table {
|
|
width: 100%;
|
|
border-collapse: collapse;
|
|
}
|
|
|
|
.log-table th,
|
|
.log-table td {
|
|
padding: 0.75rem 1rem;
|
|
text-align: left;
|
|
border-bottom: 1px solid var(--border, #333);
|
|
}
|
|
|
|
.log-table th {
|
|
background: rgba(0, 0, 0, 0.2);
|
|
font-size: 0.75rem;
|
|
text-transform: uppercase;
|
|
letter-spacing: 0.05em;
|
|
color: var(--text-muted, #888);
|
|
font-weight: 600;
|
|
position: sticky;
|
|
top: 0;
|
|
}
|
|
|
|
.log-table td {
|
|
font-size: 0.875rem;
|
|
color: var(--text);
|
|
}
|
|
|
|
.log-table tbody tr:hover {
|
|
background: rgba(255, 255, 255, 0.03);
|
|
}
|
|
|
|
/* Alert Type Badges */
|
|
.alert-badge {
|
|
display: inline-block;
|
|
padding: 0.25rem 0.5rem;
|
|
border-radius: 4px;
|
|
font-size: 0.7rem;
|
|
font-weight: 600;
|
|
text-transform: uppercase;
|
|
}
|
|
|
|
.alert-badge--danger {
|
|
background: rgba(239, 68, 68, 0.2);
|
|
color: #ef4444;
|
|
}
|
|
|
|
.alert-badge--warning {
|
|
background: rgba(245, 158, 11, 0.2);
|
|
color: #f59e0b;
|
|
}
|
|
|
|
.alert-badge--info {
|
|
background: rgba(59, 130, 246, 0.2);
|
|
color: #3b82f6;
|
|
}
|
|
|
|
/* Top Alerts */
|
|
.top-alerts {
|
|
display: grid;
|
|
grid-template-columns: repeat(auto-fit, minmax(280px, 1fr));
|
|
gap: 1rem;
|
|
margin-bottom: 2rem;
|
|
}
|
|
|
|
.top-alert-card {
|
|
background: var(--surface, #1a1a2e);
|
|
border: 1px solid var(--border, #333);
|
|
border-radius: 8px;
|
|
padding: 1rem;
|
|
display: flex;
|
|
justify-content: space-between;
|
|
align-items: center;
|
|
}
|
|
|
|
.top-alert-card__info {
|
|
flex: 1;
|
|
}
|
|
|
|
.top-alert-card__tag {
|
|
font-weight: 600;
|
|
color: var(--text);
|
|
margin-bottom: 0.25rem;
|
|
}
|
|
|
|
.top-alert-card__meta {
|
|
font-size: 0.75rem;
|
|
color: var(--text-muted, #888);
|
|
}
|
|
|
|
.top-alert-card__count {
|
|
font-size: 1.5rem;
|
|
font-weight: 700;
|
|
color: var(--accent, #3b82f6);
|
|
}
|
|
|
|
.section-title {
|
|
font-size: 1.125rem;
|
|
font-weight: 600;
|
|
color: var(--text);
|
|
margin-bottom: 1rem;
|
|
}
|
|
|
|
/* Scrollable table container */
|
|
.log-scroll {
|
|
max-height: 500px;
|
|
overflow-y: auto;
|
|
}
|
|
|
|
/* Empty State */
|
|
.empty-state {
|
|
text-align: center;
|
|
padding: 3rem;
|
|
color: var(--text-muted, #888);
|
|
}
|
|
|
|
.empty-state__icon {
|
|
font-size: 3rem;
|
|
margin-bottom: 1rem;
|
|
}
|
|
</style>
|
|
|
|
<?php if ($connectionError): ?>
|
|
<div class="alert-stat" style="border-color: #ef4444; max-width: 500px;">
|
|
<div class="alert-stat__value" style="font-size: 1rem; color: #ef4444;">
|
|
⚠️ Unable to connect to the database.
|
|
</div>
|
|
</div>
|
|
<?php elseif (!$tableExists): ?>
|
|
<div class="empty-state">
|
|
<div class="empty-state__icon">📋</div>
|
|
<h3>No Alert Logs Yet</h3>
|
|
<p>The alert_log table doesn't exist yet. Alerts will be logged when they first trigger.</p>
|
|
</div>
|
|
<?php else: ?>
|
|
|
|
<!-- Stats -->
|
|
<?php if ($stats): ?>
|
|
<div class="alert-stats">
|
|
<div class="alert-stat">
|
|
<div class="alert-stat__value"><?php echo number_format($stats['total_alerts'] ?? 0); ?></div>
|
|
<div class="alert-stat__label">Total Alerts</div>
|
|
</div>
|
|
<div class="alert-stat">
|
|
<div class="alert-stat__value"><?php echo number_format($stats['last_24h'] ?? 0); ?></div>
|
|
<div class="alert-stat__label">Last 24 Hours</div>
|
|
</div>
|
|
<div class="alert-stat alert-stat--danger">
|
|
<div class="alert-stat__value"><?php echo number_format($stats['danger_count'] ?? 0); ?></div>
|
|
<div class="alert-stat__label">Danger</div>
|
|
</div>
|
|
<div class="alert-stat alert-stat--warning">
|
|
<div class="alert-stat__value"><?php echo number_format($stats['warning_count'] ?? 0); ?></div>
|
|
<div class="alert-stat__label">Warning</div>
|
|
</div>
|
|
<div class="alert-stat alert-stat--info">
|
|
<div class="alert-stat__value"><?php echo number_format($stats['info_count'] ?? 0); ?></div>
|
|
<div class="alert-stat__label">Info</div>
|
|
</div>
|
|
<div class="alert-stat">
|
|
<div class="alert-stat__value"><?php echo $stats['unique_tags'] ?? 0; ?></div>
|
|
<div class="alert-stat__label">Unique Tags</div>
|
|
</div>
|
|
</div>
|
|
<?php endif; ?>
|
|
|
|
<!-- Top Alerts -->
|
|
<?php if (!empty($topAlerts)): ?>
|
|
<h3 class="section-title">🔥 Most Frequent Alerts</h3>
|
|
<div class="top-alerts">
|
|
<?php foreach (array_slice($topAlerts, 0, 6) as $alert): ?>
|
|
<div class="top-alert-card">
|
|
<div class="top-alert-card__info">
|
|
<div class="top-alert-card__tag">
|
|
<span class="alert-badge alert-badge--<?php echo htmlspecialchars($alert['alert_type']); ?>">
|
|
<?php echo htmlspecialchars($alert['alert_type']); ?>
|
|
</span>
|
|
<?php echo htmlspecialchars($alert['alert_tag']); ?>
|
|
</div>
|
|
<div class="top-alert-card__meta">
|
|
Range: <?php echo number_format($alert['min_value'], 1); ?> - <?php echo number_format($alert['max_value'], 1); ?>
|
|
| Avg: <?php echo number_format($alert['avg_value'], 1); ?>
|
|
</div>
|
|
</div>
|
|
<div class="top-alert-card__count"><?php echo number_format($alert['count']); ?></div>
|
|
</div>
|
|
<?php endforeach; ?>
|
|
</div>
|
|
<?php endif; ?>
|
|
|
|
<!-- Filters -->
|
|
<h3 class="section-title">📜 Alert Log</h3>
|
|
<form method="get" class="filter-form">
|
|
<div class="filter-group">
|
|
<label>Alert Type</label>
|
|
<select name="type">
|
|
<option value="">All Types</option>
|
|
<option value="danger" <?php echo $filterType === 'danger' ? 'selected' : ''; ?>>Danger</option>
|
|
<option value="warning" <?php echo $filterType === 'warning' ? 'selected' : ''; ?>>Warning</option>
|
|
<option value="info" <?php echo $filterType === 'info' ? 'selected' : ''; ?>>Info</option>
|
|
</select>
|
|
</div>
|
|
<div class="filter-group">
|
|
<label>Tag</label>
|
|
<select name="tag">
|
|
<option value="">All Tags</option>
|
|
<?php foreach ($distinctTags as $tag): ?>
|
|
<option value="<?php echo htmlspecialchars($tag); ?>" <?php echo $filterTag === $tag ? 'selected' : ''; ?>>
|
|
<?php echo htmlspecialchars($tag); ?>
|
|
</option>
|
|
<?php endforeach; ?>
|
|
</select>
|
|
</div>
|
|
<div class="filter-group">
|
|
<label>From Date</label>
|
|
<input type="date" name="date_from" value="<?php echo htmlspecialchars($filterDateFrom); ?>">
|
|
</div>
|
|
<div class="filter-group">
|
|
<label>To Date</label>
|
|
<input type="date" name="date_to" value="<?php echo htmlspecialchars($filterDateTo); ?>">
|
|
</div>
|
|
<button type="submit" class="filter-btn">Filter</button>
|
|
<a href="alert-log.php" class="filter-btn filter-btn--clear">Clear</a>
|
|
</form>
|
|
|
|
<!-- Log Table -->
|
|
<div class="log-table-container">
|
|
<?php if (empty($logs)): ?>
|
|
<div class="empty-state">
|
|
<div class="empty-state__icon">📭</div>
|
|
<p>No alerts match your filters.</p>
|
|
</div>
|
|
<?php else: ?>
|
|
<div class="log-scroll">
|
|
<table class="log-table">
|
|
<thead>
|
|
<tr>
|
|
<th>Time</th>
|
|
<th>Type</th>
|
|
<th>Tag</th>
|
|
<th>Value</th>
|
|
<th>Threshold</th>
|
|
<th>Message</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php foreach ($logs as $log): ?>
|
|
<tr>
|
|
<td style="white-space: nowrap; font-size: 0.8rem;">
|
|
<?php echo htmlspecialchars($log['logged_at']); ?>
|
|
</td>
|
|
<td>
|
|
<span class="alert-badge alert-badge--<?php echo htmlspecialchars($log['alert_type']); ?>">
|
|
<?php echo htmlspecialchars($log['alert_type']); ?>
|
|
</span>
|
|
</td>
|
|
<td style="font-weight: 500;">
|
|
<?php echo htmlspecialchars($log['alert_tag']); ?>
|
|
</td>
|
|
<td style="font-family: monospace;">
|
|
<?php echo $log['alert_value'] !== null ? number_format($log['alert_value'], 2) : '-'; ?>
|
|
</td>
|
|
<td style="font-family: monospace; color: var(--text-muted, #888);">
|
|
<?php echo $log['threshold_value'] !== null ? number_format($log['threshold_value'], 2) : '-'; ?>
|
|
</td>
|
|
<td style="max-width: 300px; overflow: hidden; text-overflow: ellipsis;">
|
|
<?php echo htmlspecialchars($log['alert_message']); ?>
|
|
</td>
|
|
</tr>
|
|
<?php endforeach; ?>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
<div style="padding: 0.75rem 1rem; border-top: 1px solid var(--border, #333); font-size: 0.8rem; color: var(--text-muted, #888);">
|
|
Showing <?php echo count($logs); ?> alerts (max 500)
|
|
</div>
|
|
<?php endif; ?>
|
|
</div>
|
|
|
|
<?php endif; ?>
|
|
|
|
<?php require __DIR__ . '/includes/layout/footer.php'; ?>
|