PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]; try { $pdo = new PDO($dsn, $user, $pass, $options); } catch (\PDOException $e) { die("DB connection failed: " . $e->getMessage() . PHP_EOL); } /************************************* * CREATE TABLE IF NOT EXISTS *************************************/ $pdo->exec("CREATE TABLE IF NOT EXISTS anomaly_alerts_sent ( id INT AUTO_INCREMENT PRIMARY KEY, anomaly_epoch BIGINT NOT NULL, network VARCHAR(64), date_sent TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE(anomaly_epoch, network) )"); /************************************* * FETCH ALARMS *************************************/ $stmt = $pdo->query('SELECT id, subscriber_id, network, alarm_timestamp FROM test1_standing_alarms_view'); // $stmt = $pdo->query('SELECT id, subscriber_id, network, alarm_timestamp FROM standing_alarms_view'); $rows = $stmt->fetchAll(); $networkTimestamps = []; /************************************* * BUILD NETWORK+TIMESTAMP PAIRS *************************************/ foreach ($rows as $row) { $networkKey = substr($row['network'], 0, 9); // Normalize timestamp to Y-m-d H:i (minute-level) $timestamp = date('Y-m-d H:i', strtotime($row['alarm_timestamp'])); $pairKey = $networkKey . '||' . $timestamp; if (!isset($networkTimestamps[$pairKey])) { $networkTimestamps[$pairKey] = []; } $networkTimestamps[$pairKey][] = $row; } /************************************* * FIND NEW ANOMALIES AND SEND EMAILS *************************************/ foreach ($networkTimestamps as $pairKey => $alarms) { // Extract unique subscriber_ids $uniqueSubs = []; foreach ($alarms as $alarm) { $uniqueSubs[$alarm['subscriber_id']] = true; } if (count($uniqueSubs) > 1) { list($networkKey, $timestamp) = explode('||', $pairKey); $epoch = strtotime($timestamp); // Check if this anomaly event was already alerted $checkStmt = $pdo->prepare("SELECT 1 FROM anomaly_alerts_sent WHERE anomaly_epoch = ? AND network = ?"); $checkStmt->execute([$epoch, $networkKey]); if (!$checkStmt->fetch()) { // Not sent, so send the email $to = "kchamberlain@rtccom.com"; $subject = "New Anomaly Detected | $networkKey $timestamp"; $body = '

New Anomaly Detected

TYPE

Network: '.$networkKey.'

Timestamp: '.$timestamp.'

Subscribers Affected:

' . htmlspecialchars($alarm['subscriber_id']) . '

Action may be required!

'; $headers = "MIME-Version: 1.0\r\n"; $headers .= "Content-type: text/html; charset=UTF-8\r\n"; $headers .= "From: anomaly@rtccom.com\r\n"; // Send the email mail($to, $subject, $body, $headers); // Record as sent $insertStmt = $pdo->prepare("INSERT INTO anomaly_alerts_sent (anomaly_epoch, network) VALUES (?, ?)"); $insertStmt->execute([$epoch, $networkKey]); echo "Email sent for anomaly at $networkKey $timestamp (epoch: $epoch)\n"; } } } // CHECK FOR CLEARED ANOMS ------------------------------------------------------------ $currentAnomalies = []; foreach ($networkTimestamps as $pairKey => $alarms) { $uniqueSubs = []; foreach ($alarms as $alarm) { $uniqueSubs[$alarm['subscriber_id']] = true; } if (count($uniqueSubs) > 1) { list($networkKey, $timestamp) = explode('||', $pairKey); $epoch = strtotime($timestamp); $currentAnomalies[$networkKey . '||' . $epoch] = [ 'network' => $networkKey, 'epoch' => $epoch, 'timestamp' => $timestamp ]; } } // Get all previous anomaly alerts $alerted = $pdo->query("SELECT anomaly_epoch, network FROM anomaly_alerts_sent")->fetchAll(PDO::FETCH_ASSOC); foreach ($alerted as $alert) { // Does ANY alarm remain in the DB for this anomaly's network/epoch? $stillActive = false; foreach ($rows as $row) { $networkKey = substr($row['network'], 0, 9); $timestamp = date('Y-m-d H:i', strtotime($row['alarm_timestamp'])); $epoch = strtotime($timestamp); if ($networkKey == $alert['network'] && $epoch == $alert['anomaly_epoch']) { $stillActive = true; break; // Found a matching alarm, so it's not truly cleared yet } } // Only proceed if this anomaly is truly cleared (no alarms left for that network/epoch) if (!$stillActive) { // Check if already sent cleared $checkCleared = $pdo->prepare("SELECT 1 FROM anomaly_cleared_sent WHERE anomaly_epoch = ? AND network = ?"); $checkCleared->execute([$alert['anomaly_epoch'], $alert['network']]); if (!$checkCleared->fetch()) { // Compose the cleared email $to = "kchamberlain@rtccom.com"; $subject = "Anomaly Cleared | {$alert['network']} {$alert['anomaly_epoch']}"; $body = '

✅ Network Anomaly Cleared

Network: '.$alert['network'].'

Epoch: '.$alert['anomaly_epoch'].'

This anomaly has cleared and is no longer present in your active alarms.

'; $headers = "MIME-Version: 1.0\r\n"; $headers .= "Content-type: text/html; charset=UTF-8\r\n"; $headers .= "From: anomaly@rtccom.com\r\n"; mail($to, $subject, $body, $headers); // Mark as cleared $ins = $pdo->prepare("INSERT INTO anomaly_cleared_sent (anomaly_epoch, network) VALUES (?, ?)"); $ins->execute([$alert['anomaly_epoch'], $alert['network']]); echo "Cleared email sent for anomaly at {$alert['network']} epoch: {$alert['anomaly_epoch']}\n"; } } } ?>