Advanced Ring Group Call Monitoring for ViciDial/Asterisk

PHP Parser + MySQL + Real-time Dashboard

Table of Contents

1. Introduction: Why Ring Group Visibility Matters
2. Architecture Overview
3. Database Schema (6 Tables)
4. Ring Group Dialplan Configuration
5. The PHP Log Parser
6. SIP Status Updater
7. Real-time Dashboard
8. Analytics Queries
9. Cron Setup
10. Tips, Edge Cases, and Scaling

1. Introduction: Why Ring Group Visibility Matters

In any ViciDial or Asterisk-based call center, the ring group is the safety net of last resort. When no agents are logged into ViciDial queues -- after hours, during breaks, on weekends -- incoming calls fall through to a ring group that dials a pool of Zoiper/softphone extensions simultaneously. If someone picks up, the call is saved. If nobody picks up after enough retry cycles, the caller hangs up or gets voicemail.

The problem is that Asterisk and ViciDial give you almost zero visibility into what happens inside a ring group. Standard CDR records tell you a call came in and how long it lasted, but they do not tell you:

• Which of the 40 extensions in the ring group were actually online?
• Which extensions rang? For how long?
• Which extensions were busy on another call?
• Which extensions were offline (not registered)?
• Who finally answered? Or did nobody answer?
• What was the SIP-level event flow for each extension?

Without this data, you are flying blind. You cannot tell whether a missed call happened because all 40 Zoiper phones were offline, or because 38 were offline and the 2 that were online were busy on other calls. You cannot tell if a specific agent consistently ignores ring group calls. You cannot tell if a Zoiper client is silently failing to register.

This system solves all of those problems by parsing Asterisk log files, extracting per-extension call events, and storing them in 6 MySQL tables that power a real-time web dashboard. A cron job runs every 5 minutes, giving you near-real-time data with no impact on Asterisk performance.

What You Will Build

By the end of this tutorial, you will have:

• A PHP parser that extracts detailed ring group events from /var/log/asterisk/messages
• 6 MySQL tables storing call status, SIP traces, daily stats, hangup causes, and SIP registration history
• A shell script that captures live SIP peer registration status
• SQL queries for analytics: answer rates, offline patterns, agent performance
• A web dashboard with call trace timelines, live status grids, and agent leaderboards

Prerequisites

• A running ViciDial or standalone Asterisk server
• MySQL/MariaDB 10.x+ (same database as ViciDial)
• PHP 7.x or 8.x (CLI and web)
• Root access to the Asterisk server
• A ring group defined in customexte.conf (or equivalent dialplan file)

2. Architecture Overview

                    +----------------------------+
                    |    Asterisk PBX             |
                    |                            |
                    |  /var/log/asterisk/messages |
                    |  (Asterisk log output)      |
                    +-------------+--------------+
                                  |
                    +-------------v--------------+
                    |  ringgroup_parser_enhanced  |
                    |  (PHP, cron every 5 min)    |
                    |                            |
                    |  Parses log lines for:      |
                    |  - Dial attempts            |
                    |  - Ringing events           |
                    |  - Answer events            |
                    |  - Busy/offline rejections  |
                    |  - Hangup causes            |
                    |  - SIP response codes       |
                    +-------------+--------------+
                                  |
                    +-------------v--------------+
                    |      MySQL/MariaDB          |
                    |                            |
                    |  ringgroup_call_status      |
                    |  ringgroup_call_trace       |
                    |  ringgroup_daily_stats      |
                    |  ringgroup_hangup_causes    |
                    |  ringgroup_sip_history      |
                    |  ringgroup_sip_latest (view)|
                    +-------------+--------------+
                                  |
              +-------------------+-------------------+
              |                                       |
+-------------v--------------+          +-------------v--------------+
|  sip_status_updater.sh     |          |  ringgroup_view.php        |
|  (cron every minute,       |          |  (Web dashboard)           |
|   6 runs x 10s = near-     |          |                            |
|   real-time SIP status)    |          |  Views:                    |
|                            |          |  - Incoming Calls list     |
|  Writes: sip_status.json   |          |  - Call Trace timeline     |
+----------------------------+          |  - Live SIP Status grid    |
                                        |  - Agent Performance       |
                                        +----------------------------+

Data Flow

1. Asterisk writes verbose log entries to /var/log/asterisk/messages as calls flow through the ring group dialplan
2. ringgroup_parser_enhanced.php (cron, every 5 minutes) reads the log file, matches patterns for each extension in the ring group, and inserts per-extension status records and SIP trace events into MySQL
3. sip_status_updater.sh (cron, every minute) runs asterisk -rx "sip show peers" and writes the current registration status of all extensions to a JSON file
4. ringgroup_view.php (web dashboard) reads from MySQL and the JSON file to display call history, trace timelines, live status, and agent performance

3. Database Schema (6 Tables)

3.1 ringgroup_call_status

The core table. One row per extension per call. If your ring group has 40 extensions and a call comes in, this table gets 40 rows -- one for each extension showing whether it was dialed, rang, answered, was busy, or was offline.

CREATE TABLE `ringgroup_call_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `call_uniqueid` varchar(50) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `caller_number` varchar(50) DEFAULT NULL,
  `call_time` datetime NOT NULL,
  `dial_status` enum('DIALED','NOT_DIALED') DEFAULT 'NOT_DIALED',
  `ring_status` enum('RINGING','NO_RING') DEFAULT 'NO_RING',
  `answer_status` enum('ANSWERED','NOT_ANSWERED') DEFAULT 'NOT_ANSWERED',
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `dial_cause` varchar(100) DEFAULT NULL
    COMMENT 'Detailed dial failure reason (e.g., OFFLINE, BUSY usage limit of 1, Called -> BUSY 486)',
  `sip_response` varchar(50) DEFAULT NULL
    COMMENT 'SIP response code and text (e.g., 180 Ringing, 486 Busy Here, 200 OK)',
  `hangupcause` int(3) DEFAULT NULL
    COMMENT 'Q.931 hangup cause code',
  `hangupcause_text` varchar(100) DEFAULT NULL
    COMMENT 'Q.931 cause description',
  `busy_reason` enum('USAGE_LIMIT','486_BUSY','DND','UNKNOWN') DEFAULT NULL
    COMMENT 'Why extension was busy',
  `call_attempt_time` datetime DEFAULT NULL
    COMMENT 'When dial attempt started',
  `ring_start_time` datetime DEFAULT NULL
    COMMENT 'When ringing started',
  `answer_time` datetime DEFAULT NULL
    COMMENT 'When answered',
  `ring_duration_ms` int(11) DEFAULT NULL
    COMMENT 'How long it rang in milliseconds before busy/answer/timeout',
  PRIMARY KEY (`id`),
  KEY `idx_call_uniqueid` (`call_uniqueid`),
  KEY `idx_extension` (`extension`),
  KEY `idx_call_time` (`call_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Per-extension dial/ring/answer status for every ring group call';

Scale reference: In a production environment with ~170 ring group calls per day and 40 extensions, this table accumulates roughly 6,800 rows/day. After 25 days, expect ~170K rows and ~20 MB of data.

3.2 ringgroup_call_trace

SIP-level event timeline for each call. Think of this as a simplified Homer/VoIP Monitor trace, but stored in your own MySQL database. Each event is one row: INVITE sent, 180 Ringing received, 486 Busy Here received, 200 OK (answered), BYE (hangup), etc.

CREATE TABLE `ringgroup_call_trace` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `call_uniqueid` varchar(50) NOT NULL,
  `trace_time` datetime(3) NOT NULL
    COMMENT 'Timestamp with milliseconds',
  `event_type` enum(
    'INVITE','TRYING','RINGING','PROGRESS',
    'ANSWER','CANCEL','BYE','ACK',
    'REJECT','TIMEOUT','ERROR'
  ) NOT NULL,
  `source_channel` varchar(100) DEFAULT NULL,
  `dest_channel` varchar(100) DEFAULT NULL,
  `extension` varchar(10) DEFAULT NULL,
  `sip_method` varchar(20) DEFAULT NULL,
  `sip_response_code` int(11) DEFAULT NULL,
  `sip_response_text` varchar(100) DEFAULT NULL,
  `direction` enum('INBOUND','OUTBOUND') DEFAULT 'INBOUND',
  `raw_log` text DEFAULT NULL
    COMMENT 'Original Asterisk log line for debugging',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_call_uniqueid` (`call_uniqueid`),
  KEY `idx_trace_time` (`trace_time`),
  KEY `idx_extension` (`extension`),
  KEY `idx_event_type` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='SIP-level call trace for ring group debugging';

Scale reference: Each call generates 5-15 trace events (more if many extensions are online and ringing). After 25 days, expect ~630K rows and ~108 MB of data. The raw_log column is the main space consumer -- you can truncate it or skip storing it to save space.

3.3 ringgroup_daily_stats

Pre-aggregated daily statistics per extension. Populated by a nightly cron job or on-demand query. Useful for dashboards that need fast reads without scanning the full call_status table.

CREATE TABLE `ringgroup_daily_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stat_date` date NOT NULL,
  `extension` varchar(10) NOT NULL,
  `total_calls` int(11) DEFAULT 0,
  `times_rang` int(11) DEFAULT 0,
  `times_answered` int(11) DEFAULT 0,
  `times_busy` int(11) DEFAULT 0,
  `times_offline` int(11) DEFAULT 0,
  `times_timeout` int(11) DEFAULT 0,
  `avg_ring_duration_ms` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp()
    ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_date_ext` (`stat_date`,`extension`),
  KEY `idx_stat_date` (`stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Daily aggregated stats per extension';

3.4 ringgroup_hangup_causes

Reference table for Q.931/ISDN hangup cause codes. Seeded once with 28 common cause codes. Used by the dashboard to translate numeric codes into human-readable descriptions.

CREATE TABLE `ringgroup_hangup_causes` (
  `cause_code` int(11) NOT NULL,
  `cause_text` varchar(100) NOT NULL,
  `category` enum('NORMAL','BUSY','CONGESTION','UNAVAILABLE','ERROR','OTHER') NOT NULL,
  PRIMARY KEY (`cause_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Q.931 hangup cause code reference';

-- Seed data
INSERT INTO ringgroup_hangup_causes (cause_code, cause_text, category) VALUES
(1,   'Unallocated number',               'UNAVAILABLE'),
(16,  'Normal clearing',                   'NORMAL'),
(17,  'User busy',                         'BUSY'),
(18,  'No user responding',                'UNAVAILABLE'),
(19,  'No answer from user',               'UNAVAILABLE'),
(20,  'Subscriber absent',                 'UNAVAILABLE'),
(21,  'Call rejected',                      'BUSY'),
(22,  'Number changed',                    'UNAVAILABLE'),
(27,  'Destination out of order',          'UNAVAILABLE'),
(28,  'Invalid number format',             'ERROR'),
(29,  'Facility rejected',                 'ERROR'),
(31,  'Normal unspecified',                'NORMAL'),
(34,  'No circuit available',              'CONGESTION'),
(38,  'Network out of order',              'UNAVAILABLE'),
(41,  'Temporary failure',                 'CONGESTION'),
(42,  'Switching equipment congestion',    'CONGESTION'),
(44,  'Requested circuit unavailable',     'CONGESTION'),
(50,  'Requested facility not subscribed', 'ERROR'),
(52,  'Outgoing calls barred',             'ERROR'),
(54,  'Incoming calls barred',             'ERROR'),
(58,  'Bearer capability not available',   'ERROR'),
(65,  'Bearer capability not implemented', 'ERROR'),
(69,  'Facility not implemented',          'ERROR'),
(79,  'Service not implemented',           'ERROR'),
(88,  'Incompatible destination',          'ERROR'),
(102, 'Recovery on timer expiry',          'CONGESTION'),
(111, 'Protocol error',                    'ERROR'),
(127, 'Interworking unspecified',          'OTHER');

3.5 ringgroup_sip_history

Tracks SIP registration status changes over time. Each row represents a status transition (e.g., OK to UNREACHABLE, or UNREACHABLE to OK). Only records changes, not continuous polling data, keeping the table compact.

CREATE TABLE `ringgroup_sip_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `extension` varchar(10) NOT NULL,
  `status` varchar(20) NOT NULL
    COMMENT 'OK, UNREACHABLE, UNKNOWN, UNREGISTERED, NOT REGISTERED',
  `ip_address` varchar(45) DEFAULT NULL,
  `port` int(11) DEFAULT NULL,
  `latency_ms` int(11) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `recorded_at` datetime NOT NULL DEFAULT current_timestamp(),
  `prev_status` varchar(20) DEFAULT NULL
    COMMENT 'Previous status for change tracking',
  PRIMARY KEY (`id`),
  KEY `idx_extension` (`extension`),
  KEY `idx_recorded_at` (`recorded_at`),
  KEY `idx_extension_time` (`extension`,`recorded_at` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='SIP registration status history';

3.6 ringgroup_sip_latest (View)

A convenience view that returns only the most recent status for each extension. Used by the dashboard for the "Live Status" grid.

CREATE VIEW `ringgroup_sip_latest` AS
SELECT h1.*
FROM ringgroup_sip_history h1
JOIN (
    SELECT extension, MAX(recorded_at) AS max_time
    FROM ringgroup_sip_history
    GROUP BY extension
) h2 ON h1.extension = h2.extension
    AND h1.recorded_at = h2.max_time;

4. Ring Group Dialplan Configuration

The ring group is defined in Asterisk's customexte.conf (or whatever file your ViciDial instance uses for custom extensions). The key design principle is massive redundancy: dial all extensions simultaneously with a short timeout, then repeat many times.

Why 50 Repeated Dial() Attempts?

Each Dial() command has a 4-second timeout (4 in the options). If all extensions are busy or offline, Asterisk returns immediately and moves to the next Dial() line. If at least one extension is online and ringing, Asterisk waits the full 4 seconds for an answer. By repeating the Dial() 50 times, you create a total ring window of up to 200 seconds (3 minutes 20 seconds) -- long enough that someone walking to their desk can still answer.

This also means that if an extension becomes available mid-call (e.g., they finish another call), they will be dialed on the next cycle and can pick up.

Example Dialplan

Replace extension numbers and the ring group name with your own values.

; =============================================================
; Ring Group: afterhours
; Safety net for inbound calls when no ViciDial agents are logged in
; 40 SIP extensions, 4-second timeout, 50 attempts = ~200s total ring window
; =============================================================

[default]

exten => afterhours,1,AGI(agi://127.0.0.1:4577/call_log)
same => n,Progress(220)

; Attempt 1
same => n,Dial(SIP/8001&SIP/8002&SIP/8003&SIP/8004&SIP/8005&SIP/8006&SIP/8007&SIP/8008&SIP/8009&SIP/8010&SIP/8011&SIP/8012&SIP/8013&SIP/8014&SIP/8015&SIP/8016&SIP/8017&SIP/8018&SIP/8019&SIP/8020&SIP/8021&SIP/8022&SIP/8023&SIP/8024&SIP/8025&SIP/8026&SIP/8027&SIP/8028&SIP/8029&SIP/8030&SIP/8031&SIP/8032&SIP/8033&SIP/8034&SIP/8035&SIP/8036&SIP/8037&SIP/8038&SIP/8039&SIP/8040,4,tTo)

; Attempt 2
same => n,Dial(SIP/8001&SIP/8002&SIP/8003&SIP/8004&SIP/8005&SIP/8006&SIP/8007&SIP/8008&SIP/8009&SIP/8010&SIP/8011&SIP/8012&SIP/8013&SIP/8014&SIP/8015&SIP/8016&SIP/8017&SIP/8018&SIP/8019&SIP/8020&SIP/8021&SIP/8022&SIP/8023&SIP/8024&SIP/8025&SIP/8026&SIP/8027&SIP/8028&SIP/8029&SIP/8030&SIP/8031&SIP/8032&SIP/8033&SIP/8034&SIP/8035&SIP/8036&SIP/8037&SIP/8038&SIP/8039&SIP/8040,4,tTo)

; ... Repeat Dial() lines 3 through 49 (identical to above) ...

; Attempt 50 (final)
same => n,Dial(SIP/8001&SIP/8002&SIP/8003&SIP/8004&SIP/8005&SIP/8006&SIP/8007&SIP/8008&SIP/8009&SIP/8010&SIP/8011&SIP/8012&SIP/8013&SIP/8014&SIP/8015&SIP/8016&SIP/8017&SIP/8018&SIP/8019&SIP/8020&SIP/8021&SIP/8022&SIP/8023&SIP/8024&SIP/8025&SIP/8026&SIP/8027&SIP/8028&SIP/8029&SIP/8030&SIP/8031&SIP/8032&SIP/8033&SIP/8034&SIP/8035&SIP/8036&SIP/8037&SIP/8038&SIP/8039&SIP/8040,4,tTo)

; If nobody answered after all attempts, hang up gracefully
same => n,Hangup()

Dial() Options Explained

How This Generates Log Data

Each Dial() attempt produces log lines in /var/log/asterisk/messages for every extension:

• Successful dial: Called SIP/8001
• Ringing: SIP/8001-00000abc is ringing
• Answered: SIP/8001-00000abc answered
• Busy (usage limit): Call to peer '8001' rejected due to usage limit of 1
• Offline: Unable to create channel ... cause 20 ... Subscriber absent
• Busy (SIP 486): SIP/8001-00000abc is busy
• Timeout: Nobody picked up in 4000 ms
• All busy: Everyone is busy/congested at this time (40:2/0/38)

With 40 extensions and 50 attempts, a single incoming call can generate hundreds to thousands of log lines. The parser extracts and deduplicates this into structured database records.

Applying the Dialplan

After editing customexte.conf:

# Reload the dialplan without restarting Asterisk (safe for live calls)
asterisk -rx "dialplan reload"

# Verify the extension loaded correctly
asterisk -rx "dialplan show afterhours@default"

5. The PHP Log Parser

This is the heart of the system. The parser reads /var/log/asterisk/messages, matches specific regex patterns for ring group events, builds an in-memory call structure, and writes the results to MySQL.

Key Design Decisions

1. Idempotent inserts: The parser checks if a call already exists in the database before inserting. Running it twice for the same time window is safe.
2. Full extension coverage: For every call, the parser creates a record for every extension in the ring group, even extensions that were not explicitly mentioned in the logs (those are marked as OFFLINE/NOT_DIALED by default).
3. Call ID based grouping: Asterisk log lines contain a call ID like [C-00001abc]. All events for the same call are grouped by this ID.
4. Configurable lookback: The parser accepts a hours_back argument so the cron job can specify how far back to scan.

Complete Parser Script

Save this as ringgroup_parser_enhanced.php on your Asterisk server:

<?php
/**
 * ringgroup_parser_enhanced.php
 * Enhanced Asterisk log parser for Ring Group call tracking
 *
 * Features:
 * - Detailed dial causes (BUSY, OFFLINE, USAGE_LIMIT, etc.)
 * - SIP response tracking
 * - Q.931 hangup cause mapping
 * - Call trace timeline (Homer-like)
 *
 * Usage:
 *   php ringgroup_parser_enhanced.php [hours_back] [--verbose]
 *
 * Cron example (every 5 minutes):
 *   */5 * * * * /usr/bin/php /path/to/ringgroup_parser_enhanced.php 1
 */

// ===================================================================
// Configuration -- Edit these values for your environment
// ===================================================================
$config = array(
    'db_host' => 'localhost',
    'db_user' => 'cron',
    'db_pass' => 'YOUR_DB_PASSWORD',
    'db_name' => 'asterisk',
    'messages_file' => '/var/log/asterisk/messages',
    'ringgroup_name' => 'afterhours',  // Extension name in dialplan
    'ringgroup_extensions' => array(
        '8001','8002','8003','8004','8005','8006','8007','8008','8009','8010',
        '8011','8012','8013','8014','8015','8016','8017','8018','8019','8020',
        '8021','8022','8023','8024','8025','8026','8027','8028','8029','8030',
        '8031','8032','8033','8034','8035','8036','8037','8038','8039','8040'
    )
);

// Q.931 hangup cause code descriptions
$HANGUP_CAUSES = array(
    1  => 'Unallocated number',
    16 => 'Normal clearing',
    17 => 'User busy',
    18 => 'No user responding',
    19 => 'No answer from user',
    20 => 'Subscriber absent',
    21 => 'Call rejected',
    22 => 'Number changed',
    27 => 'Destination out of order',
    28 => 'Invalid number format',
    31 => 'Normal unspecified',
    34 => 'No circuit available',
    38 => 'Network out of order',
    41 => 'Temporary failure',
    42 => 'Switching equipment congestion',
    44 => 'Requested circuit unavailable',
    102 => 'Recovery on timer expiry',
    127 => 'Interworking unspecified'
);

// ===================================================================
// Database Connection
// ===================================================================
$mysqli = new mysqli($config['db_host'], $config['db_user'],
                     $config['db_pass'], $config['db_name']);
if ($mysqli->connect_error) {
    die("MySQL connection failed: " . $mysqli->connect_error . "\n");
}
$mysqli->set_charset("utf8");

// ===================================================================
// Helper: Initialize extension status array
// ===================================================================
function init_extension_status() {
    return array(
        'dial_status'      => 'UNKNOWN',
        'dial_cause'       => null,
        'ring_status'      => 'NO_RING',
        'answer_status'    => 'NOT_ANSWERED',
        'sip_response'     => null,
        'hangupcause'      => null,
        'hangupcause_text' => null,
        'busy_reason'      => null,
        'call_attempt_time' => null,
        'ring_start_time'  => null,
        'answer_time'      => null,
        'ring_duration_ms' => null
    );
}

// ===================================================================
// Helper: Parse Asterisk log timestamp
// ===================================================================
function parse_log_timestamp($line, $year) {
    // Asterisk log format: [Jan 27 17:53:45]
    if (!preg_match('/^\[([A-Za-z]+)\s+(\d+)\s+(\d+:\d+:\d+)\]/', $line, $ts)) {
        return null;
    }
    $time_str = sprintf("%s %s %s %s", $ts[2], $ts[1], $year, $ts[3]);
    return strtotime($time_str);
}

// ===================================================================
// Helper: Extract call ID from log line
// ===================================================================
function extract_call_id($line) {
    // Asterisk uses [C-00001abc] format for call IDs
    if (preg_match('/\[C-([0-9a-f]+)\]/', $line, $m)) {
        return $m[1];
    }
    return null;
}

// ===================================================================
// Core: Parse the Asterisk messages log file
// ===================================================================
function parse_messages_enhanced($file, $start_time, $end_time,
                                  $extensions, $ringgroup_name) {
    global $HANGUP_CAUSES;

    $calls       = array();
    $call_traces = array();
    $useragents  = array();
    $year        = date('Y');

    if (!file_exists($file) || !is_readable($file)) {
        echo "Cannot read file: $file\n";
        return array('calls' => $calls, 'traces' => $call_traces,
                     'useragents' => $useragents);
    }

    $handle = fopen($file, "r");
    if (!$handle) {
        return array('calls' => $calls, 'traces' => $call_traces,
                     'useragents' => $useragents);
    }

    while (($line = fgets($handle)) !== false) {
        $log_time = parse_log_timestamp($line, $year);
        if (!$log_time || $log_time < $start_time || $log_time > $end_time) {
            continue;
        }

        $call_id      = extract_call_id($line);
        $log_datetime = date('Y-m-d H:i:s', $log_time);

        // ---- Capture user agent strings for SIP peers ----
        if (preg_match('/Saved useragent "([^"]+)" for peer (\d+)/',
                       $line, $ua)) {
            $useragents[$ua[2]] = $ua[1];
        }

        if (!$call_id) continue;

        // Initialize call structure on first encounter
        if (!isset($calls[$call_id])) {
            $calls[$call_id] = array(
                'time'          => $log_time,
                'datetime'      => $log_datetime,
                'caller'        => '',
                'called'        => '',
                'answered_by'   => null,
                'ringgroup_hit' => false,
                'extensions'    => array()
            );
        }
        if (!isset($call_traces[$call_id])) {
            $call_traces[$call_id] = array();
        }

        // ---- Extract caller/called numbers ----
        if (preg_match('/CALLER=\+?([0-9]+)/', $line, $m)) {
            $calls[$call_id]['caller'] = $m[1];
        }
        if (preg_match('/CALLED=([0-9]+)/', $line, $m)) {
            $calls[$call_id]['called'] = $m[1];
        }
        if (preg_match('/Phone call from ([0-9]+) to \+?([0-9]+)/',
                       $line, $m)) {
            $calls[$call_id]['called'] = $m[1];
            $calls[$call_id]['caller'] = $m[2];
        }

        // ---- Detect ring group execution ----
        $rg_pattern = '/Executing \['
                    . preg_quote($ringgroup_name, '/') . '@default/';
        if (preg_match($rg_pattern, $line)) {
            $calls[$call_id]['ringgroup_hit'] = true;

            $call_traces[$call_id][] = array(
                'time'      => $log_datetime,
                'event'     => 'RINGGROUP_START',
                'extension' => null,
                'sip_code'  => null,
                'sip_text'  => null,
                'detail'    => "Ring group '$ringgroup_name' started",
                'raw'       => trim($line)
            );
        }

        // ============================================================
        // PATTERN 1: OFFLINE - Subscriber absent (cause 20)
        // ============================================================
        if (preg_match('/Unable to create channel.*cause 20'
                     . '.*Subscriber absent/i', $line)) {

            $call_traces[$call_id][] = array(
                'time'      => $log_datetime,
                'event'     => 'DIAL_FAILED',
                'extension' => null,
                'sip_code'  => null,
                'sip_text'  => 'cause 20 - Subscriber absent',
                'detail'    => 'OFFLINE (not registered)',
                'raw'       => trim($line)
            );
        }

        // ============================================================
        // PATTERN 2: BUSY - Usage limit rejection
        // The call was not even attempted because the extension already
        // has the maximum number of active calls (usually 1).
        // ============================================================
        if (preg_match("/Call to peer '(\d+)' rejected due to "
                     . "usage limit of (\d+)/", $line, $m)) {
            $ext   = $m[1];
            $limit = $m[2];

            if (in_array($ext, $extensions, true)) {
                if (!isset($calls[$call_id]['extensions'][$ext])) {
                    $calls[$call_id]['extensions'][$ext]
                        = init_extension_status();
                }

                $calls[$call_id]['extensions'][$ext]['dial_status']
                    = 'NOT_DIALED';
                $calls[$call_id]['extensions'][$ext]['dial_cause']
                    = "BUSY (usage limit of $limit)";
                $calls[$call_id]['extensions'][$ext]['busy_reason']
                    = 'USAGE_LIMIT';
                $calls[$call_id]['extensions'][$ext]['call_attempt_time']
                    = $log_datetime;

                $call_traces[$call_id][] = array(
                    'time'      => $log_datetime,
                    'event'     => 'REJECT',
                    'extension' => $ext,
                    'sip_code'  => null,
                    'sip_text'  => null,
                    'detail'    => "BUSY - Extension $ext at usage "
                                 . "limit ($limit)",
                    'raw'       => trim($line)
                );
            }
        }

        // ============================================================
        // PATTERN 3: Couldn't call - failed dial attempt
        // ============================================================
        if (preg_match("/Couldn't call [Ss][Ii][Pp]\/(\d+)/",
                       $line, $m)) {
            $ext = $m[1];
            if (in_array($ext, $extensions, true)) {
                if (!isset($calls[$call_id]['extensions'][$ext])) {
                    $calls[$call_id]['extensions'][$ext]
                        = init_extension_status();
                }
                if (empty($calls[$call_id]['extensions'][$ext]
                          ['dial_cause'])) {
                    $calls[$call_id]['extensions'][$ext]['dial_cause']
                        = 'DIAL_FAILED (unknown reason)';
                }
                $calls[$call_id]['extensions'][$ext]['dial_status']
                    = 'NOT_DIALED';
            }
        }

        // ============================================================
        // PATTERN 4: Successfully called (INVITE sent)
        // ============================================================
        if (preg_match('/Called [Ss][Ii][Pp]\/(\d+)/', $line, $m)) {
            $ext = $m[1];
            if (in_array($ext, $extensions, true)) {
                if (!isset($calls[$call_id]['extensions'][$ext])) {
                    $calls[$call_id]['extensions'][$ext]
                        = init_extension_status();
                }
                $calls[$call_id]['extensions'][$ext]['dial_status']
                    = 'DIALED';
                $calls[$call_id]['extensions'][$ext]['call_attempt_time']
                    = $log_datetime;

                $call_traces[$call_id][] = array(
                    'time'      => $log_datetime,
                    'event'     => 'INVITE',
                    'extension' => $ext,
                    'sip_code'  => null,
                    'sip_text'  => null,
                    'detail'    => "Dialing extension $ext",
                    'raw'       => trim($line)
                );
            }
        }

        // ============================================================
        // PATTERN 5: Extension is ringing (180 Ringing)
        // ============================================================
        if (preg_match('/SIP\/(\d+)-[0-9a-f]+ is ringing/',
                       $line, $m)) {
            $ext = $m[1];
            if (in_array($ext, $extensions, true)) {
                if (!isset($calls[$call_id]['extensions'][$ext])) {
                    $calls[$call_id]['extensions'][$ext]
                        = init_extension_status();
                }
                $calls[$call_id]['extensions'][$ext]['ring_status']
                    = 'RINGING';
                $calls[$call_id]['extensions'][$ext]['ring_start_time']
                    = $log_datetime;
                $calls[$call_id]['extensions'][$ext]['sip_response']
                    = '180 Ringing';

                $call_traces[$call_id][] = array(
                    'time'      => $log_datetime,
                    'event'     => 'RINGING',
                    'extension' => $ext,
                    'sip_code'  => 180,
                    'sip_text'  => 'Ringing',
                    'detail'    => "Extension $ext is ringing",
                    'raw'       => trim($line)
                );
            }
        }

        // ============================================================
        // PATTERN 6: Early media / Session Progress (183)
        // ============================================================
        if (preg_match('/SIP\/(\d+)-[0-9a-f]+ is making progress/',
                       $line, $m)) {
            $ext = $m[1];
            if (in_array($ext, $extensions, true)) {
                if (!isset($calls[$call_id]['extensions'][$ext])) {
                    $calls[$call_id]['extensions'][$ext]
                        = init_extension_status();
                }
                $calls[$call_id]['extensions'][$ext]['sip_response']
                    = '183 Session Progress';

                $call_traces[$call_id][] = array(
                    'time'      => $log_datetime,
                    'event'     => 'PROGRESS',
                    'extension' => $ext,
                    'sip_code'  => 183,
                    'sip_text'  => 'Session Progress',
                    'detail'    => "Extension $ext early media",
                    'raw'       => trim($line)
                );
            }
        }

        // ============================================================
        // PATTERN 7: Extension is busy (SIP 486)
        // Unlike usage limit (Pattern 2), this means the SIP INVITE
        // was actually sent and the device responded with 486.
        // ============================================================
        if (preg_match('/SIP\/(\d+)-[0-9a-f]+ is busy/',
                       $line, $m)) {
            $ext = $m[1];
            if (in_array($ext, $extensions, true)) {
                if (!isset($calls[$call_id]['extensions'][$ext])) {
                    $calls[$call_id]['extensions'][$ext]
                        = init_extension_status();
                }
                $calls[$call_id]['extensions'][$ext]['dial_cause']
                    = 'Called -> BUSY (486)';
                $calls[$call_id]['extensions'][$ext]['sip_response']
                    = '486 Busy Here';
                $calls[$call_id]['extensions'][$ext]['busy_reason']
                    = '486_BUSY';
                $calls[$call_id]['extensions'][$ext]['hangupcause']
                    = 17;
                $calls[$call_id]['extensions'][$ext]['hangupcause_text']
                    = 'User busy';

                $call_traces[$call_id][] = array(
                    'time'      => $log_datetime,
                    'event'     => 'REJECT',
                    'extension' => $ext,
                    'sip_code'  => 486,
                    'sip_text'  => 'Busy Here',
                    'detail'    => "Extension $ext returned busy",
                    'raw'       => trim($line)
                );
            }
        }

        // ============================================================
        // PATTERN 8: Extension answered (200 OK)
        // ============================================================
        if (preg_match('/SIP\/(\d+)-[0-9a-f]+ answered/',
                       $line, $m)) {
            $ext = $m[1];
            if (in_array($ext, $extensions, true)) {
                if (!isset($calls[$call_id]['extensions'][$ext])) {
                    $calls[$call_id]['extensions'][$ext]
                        = init_extension_status();
                }
                $calls[$call_id]['extensions'][$ext]['answer_status']
                    = 'ANSWERED';
                $calls[$call_id]['extensions'][$ext]['answer_time']
                    = $log_datetime;
                $calls[$call_id]['extensions'][$ext]['sip_response']
                    = '200 OK';
                $calls[$call_id]['answered_by'] = $ext;

                // Calculate ring duration
                $rs = $calls[$call_id]['extensions'][$ext]
                      ['ring_start_time'];
                if (!empty($rs)) {
                    $ring_start  = strtotime($rs);
                    $answer_time = $log_time;
                    $calls[$call_id]['extensions'][$ext]
                        ['ring_duration_ms']
                        = ($answer_time - $ring_start) * 1000;
                }

                $call_traces[$call_id][] = array(
                    'time'      => $log_datetime,
                    'event'     => 'ANSWER',
                    'extension' => $ext,
                    'sip_code'  => 200,
                    'sip_text'  => 'OK',
                    'detail'    => "Extension $ext answered the call",
                    'raw'       => trim($line)
                );
            }
        }

        // ============================================================
        // PATTERN 9: Everyone is busy/congested
        // Format: (total:called/answered/unreachable)
        // ============================================================
        if (preg_match('/Everyone is busy\/congested at this time '
                     . '\((\d+):(\d+)\/(\d+)\/(\d+)\)/', $line, $m)) {
            $call_traces[$call_id][] = array(
                'time'      => $log_datetime,
                'event'     => 'TIMEOUT',
                'extension' => null,
                'sip_code'  => null,
                'sip_text'  => null,
                'detail'    => "All busy/congested (Total: $m[1], "
                             . "Called: $m[2], Answered: $m[3], "
                             . "Unreachable: $m[4])",
                'raw'       => trim($line)
            );
        }

        // ============================================================
        // PATTERN 10: Nobody picked up (timeout)
        // ============================================================
        if (preg_match('/Nobody picked up in (\d+) ms/', $line, $m)) {
            $call_traces[$call_id][] = array(
                'time'      => $log_datetime,
                'event'     => 'TIMEOUT',
                'extension' => null,
                'sip_code'  => 408,
                'sip_text'  => 'Request Timeout',
                'detail'    => "No answer within {$m[1]}ms",
                'raw'       => trim($line)
            );
        }

        // ============================================================
        // PATTERN 11: Hangup with cause code
        // ============================================================
        if (preg_match('/Hangup.*cause (\d+)/', $line, $m)) {
            $cause      = (int)$m[1];
            $cause_text = isset($HANGUP_CAUSES[$cause])
                        ? $HANGUP_CAUSES[$cause]
                        : "Unknown cause $cause";

            $call_traces[$call_id][] = array(
                'time'      => $log_datetime,
                'event'     => 'BYE',
                'extension' => null,
                'sip_code'  => null,
                'sip_text'  => "Q.931 Cause $cause",
                'detail'    => $cause_text,
                'raw'       => trim($line)
            );
        }
    }

    fclose($handle);

    // Filter to only calls that actually hit the ring group
    $ringgroup_calls  = array();
    $ringgroup_traces = array();

    foreach ($calls as $call_id => $call_data) {
        if ($call_data['ringgroup_hit']
            || !empty($call_data['extensions'])) {
            $ringgroup_calls[$call_id]  = $call_data;
            if (isset($call_traces[$call_id])) {
                $ringgroup_traces[$call_id] = $call_traces[$call_id];
            }
        }
    }

    return array(
        'calls'      => $ringgroup_calls,
        'traces'     => $ringgroup_traces,
        'useragents' => $useragents
    );
}

// ===================================================================
// Store call status records (one per extension per call)
// ===================================================================
function store_call_status_enhanced($mysqli, $call_id, $call_data,
                                     $useragents, $all_extensions,
                                     $verbose = false) {
    // Idempotency check -- skip if already processed
    $check = $mysqli->prepare(
        "SELECT id FROM ringgroup_call_status "
      . "WHERE call_uniqueid = ? LIMIT 1"
    );
    $check->bind_param('s', $call_id);
    $check->execute();
    $check->store_result();
    if ($check->num_rows > 0) {
        $check->close();
        if ($verbose) echo "  Call $call_id already exists, skipping\n";
        return false;
    }
    $check->close();

    $stmt = $mysqli->prepare("
        INSERT INTO ringgroup_call_status
        (call_uniqueid, call_time, caller_number, extension, user_agent,
         dial_status, dial_cause, ring_status, answer_status,
         sip_response, hangupcause, hangupcause_text, busy_reason,
         call_attempt_time, ring_start_time, answer_time,
         ring_duration_ms)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");

    if (!$stmt) {
        echo "Prepare error: " . $mysqli->error . "\n";
        return false;
    }

    $call_time_str = $call_data['datetime'];
    $caller        = !empty($call_data['caller'])
                   ? $call_data['caller'] : '';

    $inserted = 0;
    foreach ($all_extensions as $ext) {
        $ua = isset($useragents[$ext]) ? $useragents[$ext] : null;

        // Defaults: assume extension was offline unless we have
        // explicit data showing otherwise
        $dial_status      = 'NOT_DIALED';
        $dial_cause       = 'OFFLINE (not registered)';
        $ring_status      = 'NO_RING';
        $answer_status    = 'NOT_ANSWERED';
        $sip_response     = null;
        $hangupcause      = 20;   // Subscriber absent
        $hangupcause_text = 'Subscriber absent';
        $busy_reason      = null;
        $call_attempt_time = null;
        $ring_start_time  = null;
        $answer_time      = null;
        $ring_duration_ms = null;

        // Override with actual parsed data if available
        if (isset($call_data['extensions'][$ext])) {
            $d = $call_data['extensions'][$ext];

            $dial_status      = $d['dial_status'];
            $dial_cause       = $d['dial_cause'];
            $ring_status      = $d['ring_status'];
            $answer_status    = $d['answer_status'];
            $sip_response     = $d['sip_response'];
            $hangupcause      = $d['hangupcause'];
            $hangupcause_text = $d['hangupcause_text'];
            $busy_reason      = $d['busy_reason'];
            $call_attempt_time = $d['call_attempt_time'];
            $ring_start_time  = $d['ring_start_time'];
            $answer_time      = $d['answer_time'];
            $ring_duration_ms = $d['ring_duration_ms'];

            // If dialed successfully but no failure cause, clear the
            // default offline cause
            if ($dial_status === 'DIALED' && empty($dial_cause)) {
                $dial_cause       = 'DIALED';
                $hangupcause      = null;
                $hangupcause_text = null;
            }
        }

        $stmt->bind_param(
            'ssssssssssisssssi',
            $call_id, $call_time_str, $caller, $ext, $ua,
            $dial_status, $dial_cause, $ring_status, $answer_status,
            $sip_response, $hangupcause, $hangupcause_text,
            $busy_reason, $call_attempt_time, $ring_start_time,
            $answer_time, $ring_duration_ms
        );

        if ($stmt->execute()) {
            $inserted++;
        }
    }

    $stmt->close();
    if ($verbose) echo "  Stored $inserted extension records for "
                     . "call $call_id\n";
    return true;
}

// ===================================================================
// Store call trace events
// ===================================================================
function store_call_traces($mysqli, $call_id, $traces,
                            $verbose = false) {
    if (empty($traces)) return 0;

    $stmt = $mysqli->prepare("
        INSERT INTO ringgroup_call_trace
        (call_uniqueid, trace_time, event_type, extension,
         sip_response_code, sip_response_text, raw_log)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    ");

    if (!$stmt) {
        echo "Prepare error for traces: " . $mysqli->error . "\n";
        return 0;
    }

    $valid_events = array(
        'INVITE','TRYING','RINGING','PROGRESS','ANSWER',
        'CANCEL','BYE','ACK','REJECT','TIMEOUT','ERROR'
    );

    $inserted = 0;
    foreach ($traces as $trace) {
        $event_type = $trace['event'];

        // Map internal event names to valid ENUM values
        if (!in_array($event_type, $valid_events)) {
            if (strpos($event_type, 'RINGGROUP') !== false) {
                $event_type = 'INVITE';
            } elseif (strpos($event_type, 'DIAL_FAILED') !== false) {
                $event_type = 'REJECT';
            } else {
                $event_type = 'ERROR';
            }
        }

        $stmt->bind_param(
            'ssssiis',
            $call_id,
            $trace['time'],
            $event_type,
            $trace['extension'],
            $trace['sip_code'],
            $trace['detail'],
            $trace['raw']
        );

        if ($stmt->execute()) {
            $inserted++;
        }
    }

    $stmt->close();
    if ($verbose) echo "  Stored $inserted trace events for "
                     . "call $call_id\n";
    return $inserted;
}

// ===================================================================
// Main processing function
// ===================================================================
function process_ringgroup_calls($mysqli, $config, $hours_back,
                                  $verbose = false) {
    $start_time = strtotime("-{$hours_back} hours");
    $end_time   = time();

    if ($verbose) {
        echo "Processing ring group calls from "
           . date('Y-m-d H:i:s', $start_time) . " to "
           . date('Y-m-d H:i:s', $end_time) . "\n";
    }

    $parsed = parse_messages_enhanced(
        $config['messages_file'],
        $start_time,
        $end_time,
        $config['ringgroup_extensions'],
        $config['ringgroup_name']
    );

    $calls_processed = 0;
    $traces_stored   = 0;

    foreach ($parsed['calls'] as $call_id => $call_data) {
        if (store_call_status_enhanced(
            $mysqli, $call_id, $call_data,
            $parsed['useragents'],
            $config['ringgroup_extensions'],
            $verbose
        )) {
            $calls_processed++;

            if (isset($parsed['traces'][$call_id])) {
                $traces_stored += store_call_traces(
                    $mysqli, $call_id,
                    $parsed['traces'][$call_id], $verbose
                );
            }
        }
    }

    return array(
        'calls'      => $calls_processed,
        'traces'     => $traces_stored,
        'useragents' => count($parsed['useragents'])
    );
}

// ===================================================================
// CLI Execution
// ===================================================================
if (php_sapi_name() === 'cli') {
    $hours   = isset($argv[1]) ? (int)$argv[1] : 1;
    $verbose = in_array('--verbose', $argv)
            || in_array('-v', $argv);

    echo "=== Ring Group Enhanced Parser ===\n";
    echo "Processing last {$hours} hour(s)...\n";

    $result = process_ringgroup_calls(
        $mysqli, $config, $hours, $verbose
    );

    echo "\nResults:\n";
    echo "  Calls processed: {$result['calls']}\n";
    echo "  Trace events:    {$result['traces']}\n";
    echo "  User agents:     {$result['useragents']}\n";
    echo "Done.\n";
}

$mysqli->close();

Understanding the Parsing Patterns

The parser matches 11 distinct Asterisk log patterns. Here is what each one looks like in the raw log file and what the parser extracts:

6. SIP Status Updater

The SIP status updater is a shell script that runs via cron and captures the current registration status of all SIP peers. It runs asterisk -rx "sip show peers" and writes the output to a JSON file that the web dashboard reads.

Why a Separate Script?

The web dashboard needs to show live registration status (which extensions are online right now). Running Asterisk CLI commands from a PHP web script requires root access, which is a security risk. Instead, a cron script running as root writes the data to a file that the web server can read safely.

The Script

Save this as sip_status_updater.sh in the same directory as the parser:

#!/bin/bash
# ================================================================
# SIP Status Updater
# Captures current SIP peer registration status for the dashboard.
# Runs via cron every minute, executing 6 times with 10-second
# intervals for near-real-time updates.
# ================================================================

OUTPUT_FILE="/var/www/html/ringgroup/sip_status.json"
PARSER_SCRIPT="/var/www/html/ringgroup/sip_parser.php"
TEMP_FILE="/tmp/sip_peers_output.txt"

update_status() {
    # Get SIP peers status directly
    /usr/sbin/asterisk -rx "sip show peers" > "$TEMP_FILE" 2>/dev/null

    # Validate output (should contain "Name/username" header)
    if ! grep -q "Name/username" "$TEMP_FILE" 2>/dev/null; then
        echo '{"error":"Cannot connect to Asterisk","timestamp":"'\
"$(date '+%Y-%m-%d %H:%M:%S')"'"}' > "$OUTPUT_FILE"
        return 1
    fi

    # Parse output using PHP helper script
    php "$PARSER_SCRIPT" < "$TEMP_FILE" > "${OUTPUT_FILE}.tmp"

    # Atomically move to final location (prevents partial reads)
    mv "${OUTPUT_FILE}.tmp" "$OUTPUT_FILE"
    chmod 644 "$OUTPUT_FILE"
}

# Run 6 times per minute (every 10 seconds) for near-real-time
for i in 1 2 3 4 5 6; do
    update_status
    [ $i -lt 6 ] && sleep 10
done

# Cleanup temp file
rm -f "$TEMP_FILE"

The SIP Parser Helper (PHP)

This companion PHP script reads the raw sip show peers output from stdin and converts it to JSON:

<?php
/**
 * sip_parser.php
 * Parses "asterisk -rx 'sip show peers'" output into JSON.
 * Called by sip_status_updater.sh, reads from stdin.
 */

$peers = array();
$lines = file('php://stdin', FILE_IGNORE_NEW_LINES);

foreach ($lines as $line) {
    // Skip header and summary lines
    if (strpos($line, 'Name/username') !== false) continue;
    if (strpos($line, 'sip peers') !== false) continue;
    if (strpos($line, 'Monitored:') !== false) continue;
    if (strpos($line, 'Unmonitored:') !== false) continue;
    if (trim($line) === '') continue;

    // Parse peer line
    // Format: 8001/8001    192.168.1.100    D  Yes  Yes  A  5060  OK (15 ms)
    // Format: 8002/8002    (Unspecified)    D  Yes  Yes  A  0     UNKNOWN
    if (preg_match('/^(\d+)\/\S+\s+(\S+)\s+.*?\s+(\d+)\s+(\S+)(.*)$/',
                   $line, $m)) {
        $ext     = $m[1];
        $ip      = $m[2];
        $port    = (int)$m[3];
        $status  = $m[4];
        $latency = null;

        // Extract latency from "OK (15 ms)" or "UNREACHABLE"
        if (preg_match('/\((\d+)\s*ms\)/', $m[5], $lat)) {
            $latency = (int)$lat[1];
        }

        // Normalize IP
        if ($ip === '(Unspecified)' || $ip === '(null)') {
            $ip = null;
        }

        $peers[$ext] = array(
            'ip'      => $ip,
            'port'    => $port,
            'status'  => $status,
            'latency' => $latency
        );
    }
}

echo json_encode(array(
    'timestamp' => date('Y-m-d H:i:s'),
    'peers'     => $peers,
    'count'     => count($peers)
), JSON_PRETTY_PRINT) . "\n";

Example JSON Output

{
    "timestamp": "2026-03-12 14:32:15",
    "peers": {
        "8001": {
            "ip": "192.168.1.100",
            "port": 5060,
            "status": "OK",
            "latency": 15
        },
        "8002": {
            "ip": null,
            "port": 0,
            "status": "UNKNOWN",
            "latency": null
        },
        "8003": {
            "ip": "10.0.0.55",
            "port": 5060,
            "status": "OK",
            "latency": 42
        }
    },
    "count": 3
}

7. Real-time Dashboard

The dashboard is a single-file PHP application with four views:

1. Incoming Calls -- List of all ring group calls with per-call summary (how many extensions were dialed, rang, answered, were busy/offline)
2. Call Trace -- Drill-down into a single call showing the SIP event timeline for every extension (similar to Homer/VoIP Monitor)
3. Live Status -- Grid of all ring group extensions with real-time registration status (online/offline), auto-refreshing every 5 seconds via AJAX
4. Agent Performance -- Leaderboard showing which extensions answer the most calls, their answer rate, average ring time, and offline frequency

Key Dashboard Features

Status badges provide at-a-glance call outcomes:

Call trace timeline shows a vertical timeline of SIP events with color-coded nodes:

• Blue node: INVITE/dial attempt
• Purple node: RINGING (180)
• Green node: ANSWER (200 OK)
• Red node: REJECT (486/usage limit)
• Yellow node: TIMEOUT

Live status grid shows each extension as a card:

• Green border + "ONLINE": Extension registered, shows IP and latency
• Red border + "OFFLINE": Extension not registered
• Click any extension to see its registration history (last 24 hours of status changes)

Auto-refresh: The live status page polls the server every 5 seconds via AJAX, with a toggle to pause auto-refresh. Status changes trigger a pulse animation so you can spot extensions going offline in real time.

Dashboard Configuration

The dashboard reads from a config.php file in the same directory:

<?php
// config.php - Dashboard configuration
return array(
    'db_host' => 'localhost',
    'db_user' => 'cron',
    'db_pass' => 'YOUR_DB_PASSWORD',
    'db_name' => 'asterisk',

    // Extensions in your ring group (must match parser config)
    'ringgroup_extensions' => array(
        '8001','8002','8003','8004','8005','8006','8007','8008',
        '8009','8010','8011','8012','8013','8014','8015','8016',
        '8017','8018','8019','8020','8021','8022','8023','8024',
        '8025','8026','8027','8028','8029','8030','8031','8032',
        '8033','8034','8035','8036','8037','8038','8039','8040'
    ),

    'page_title'    => 'Ring Group Status',
    'default_limit' => 500,
    'max_limit'     => 5000,
);

File Layout

/var/www/html/ringgroup/
    config.php                          # Shared configuration
    ringgroup_parser_enhanced.php       # Cron parser (CLI)
    ringgroup_view.php                  # Web dashboard
    sip_status_updater.sh              # Cron SIP status (bash)
    sip_parser.php                     # SIP output parser (PHP)
    sip_status.json                    # Current SIP status (auto-generated)

8. Analytics Queries

These SQL queries provide the actionable insights that make this system valuable. Run them directly or embed them in Grafana panels, scheduled reports, or custom dashboards.

8.1 Call Summary by Day

How many ring group calls per day, with answer rate:

SELECT
    DATE(call_time) AS call_date,
    COUNT(DISTINCT call_uniqueid) AS total_calls,
    COUNT(DISTINCT CASE
        WHEN answer_status = 'ANSWERED'
        THEN call_uniqueid END) AS answered_calls,
    COUNT(DISTINCT call_uniqueid) -
        COUNT(DISTINCT CASE
            WHEN answer_status = 'ANSWERED'
            THEN call_uniqueid END) AS missed_calls,
    ROUND(
        COUNT(DISTINCT CASE
            WHEN answer_status = 'ANSWERED'
            THEN call_uniqueid END)
        / COUNT(DISTINCT call_uniqueid) * 100, 1
    ) AS answer_rate_pct
FROM ringgroup_call_status
WHERE call_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(call_time)
ORDER BY call_date DESC;

8.2 Agent Leaderboard (Who Answers the Most?)

Ranked list of extensions by answer count and answer rate:

SELECT
    extension,
    COUNT(DISTINCT call_uniqueid) AS total_calls_seen,
    SUM(CASE WHEN dial_status = 'DIALED' THEN 1 ELSE 0 END)
        AS times_dialed,
    SUM(CASE WHEN ring_status = 'RINGING' THEN 1 ELSE 0 END)
        AS times_ringing,
    SUM(CASE WHEN answer_status = 'ANSWERED' THEN 1 ELSE 0 END)
        AS times_answered,
    SUM(CASE WHEN dial_cause LIKE '%OFFLINE%'
             OR dial_cause LIKE '%Subscriber%' THEN 1 ELSE 0 END)
        AS times_offline,
    SUM(CASE WHEN dial_cause LIKE '%BUSY%'
             OR dial_cause LIKE '%486%' THEN 1 ELSE 0 END)
        AS times_busy,
    ROUND(
        SUM(CASE WHEN answer_status = 'ANSWERED' THEN 1 ELSE 0 END)
        / NULLIF(SUM(CASE WHEN dial_status = 'DIALED'
                          THEN 1 ELSE 0 END), 0) * 100, 1
    ) AS answer_rate_pct,
    ROUND(AVG(ring_duration_ms)) AS avg_ring_ms,
    MAX(user_agent) AS softphone
FROM ringgroup_call_status
WHERE call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY extension
ORDER BY times_answered DESC, times_dialed DESC;

8.3 Missed Call Analysis

Calls where nobody answered -- how many extensions were online vs offline:

SELECT
    rcs.call_uniqueid,
    MIN(rcs.call_time) AS call_time,
    MAX(rcs.caller_number) AS caller,
    COUNT(DISTINCT rcs.extension) AS total_extensions,
    SUM(CASE WHEN rcs.dial_status = 'DIALED'
             THEN 1 ELSE 0 END) AS online_count,
    SUM(CASE WHEN rcs.dial_cause LIKE '%OFFLINE%'
             OR rcs.dial_cause LIKE '%Subscriber%'
             THEN 1 ELSE 0 END) AS offline_count,
    SUM(CASE WHEN rcs.dial_cause LIKE '%BUSY%'
             OR rcs.dial_cause LIKE '%486%'
             THEN 1 ELSE 0 END) AS busy_count,
    SUM(CASE WHEN rcs.ring_status = 'RINGING'
             THEN 1 ELSE 0 END) AS ringing_count
FROM ringgroup_call_status rcs
WHERE rcs.call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY rcs.call_uniqueid
HAVING SUM(CASE WHEN rcs.answer_status = 'ANSWERED'
                THEN 1 ELSE 0 END) = 0
ORDER BY call_time DESC;

8.4 Hourly Call Distribution

When do ring group calls come in? Useful for scheduling staff:

SELECT
    HOUR(call_time) AS hour_of_day,
    COUNT(DISTINCT call_uniqueid) AS total_calls,
    COUNT(DISTINCT CASE
        WHEN answer_status = 'ANSWERED'
        THEN call_uniqueid END) AS answered,
    COUNT(DISTINCT call_uniqueid) -
        COUNT(DISTINCT CASE
            WHEN answer_status = 'ANSWERED'
            THEN call_uniqueid END) AS missed
FROM ringgroup_call_status
WHERE call_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY HOUR(call_time)
ORDER BY hour_of_day;

8.5 Extension Availability Over Time

How often is each extension actually online when calls come in?

SELECT
    extension,
    COUNT(*) AS total_call_opportunities,
    SUM(CASE WHEN dial_status = 'DIALED' THEN 1 ELSE 0 END)
        AS times_online,
    SUM(CASE WHEN dial_cause LIKE '%OFFLINE%'
             OR dial_cause LIKE '%Subscriber%' THEN 1 ELSE 0 END)
        AS times_offline,
    ROUND(
        SUM(CASE WHEN dial_status = 'DIALED' THEN 1 ELSE 0 END)
        / COUNT(*) * 100, 1
    ) AS online_rate_pct
FROM ringgroup_call_status
WHERE call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY extension
ORDER BY online_rate_pct ASC;

This query is particularly valuable: it reveals extensions that are supposed to be available but are consistently offline. An extension with a 10% online rate is someone whose Zoiper is crashing or whose computer is off.

8.6 Find What Call an Extension Was Busy On

When an extension shows "BUSY (usage limit)", this query finds the call they were actually on:

SELECT
    busy.call_uniqueid AS missed_call_id,
    busy.call_time AS missed_call_time,
    busy.caller_number AS missed_caller,
    active.call_uniqueid AS active_call_id,
    active.call_time AS active_call_time,
    active.caller_number AS active_caller,
    active.answer_time AS answered_at
FROM ringgroup_call_status busy
JOIN ringgroup_call_status active
    ON active.extension = busy.extension
    AND active.answer_status = 'ANSWERED'
    AND active.answer_time < busy.call_time
    AND active.answer_time >= DATE_SUB(busy.call_time,
                                        INTERVAL 30 MINUTE)
    AND active.call_uniqueid != busy.call_uniqueid
WHERE busy.extension = '8005'
  AND busy.busy_reason = 'USAGE_LIMIT'
  AND busy.call_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY busy.call_time DESC;

8.7 SIP Registration Uptime Report

Using the ringgroup_sip_history table:

SELECT
    extension,
    COUNT(*) AS status_changes,
    SUM(CASE WHEN status = 'OK' THEN 1 ELSE 0 END)
        AS times_came_online,
    SUM(CASE WHEN status != 'OK' THEN 1 ELSE 0 END)
        AS times_went_offline,
    MIN(recorded_at) AS first_event,
    MAX(recorded_at) AS last_event
FROM ringgroup_sip_history
WHERE recorded_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY extension
ORDER BY times_went_offline DESC;

8.8 Populate Daily Stats (Aggregation Query)

Run this nightly to populate ringgroup_daily_stats:

INSERT INTO ringgroup_daily_stats
    (stat_date, extension, total_calls, times_rang,
     times_answered, times_busy, times_offline, avg_ring_duration_ms)
SELECT
    DATE(call_time) AS stat_date,
    extension,
    COUNT(*) AS total_calls,
    SUM(CASE WHEN ring_status = 'RINGING' THEN 1 ELSE 0 END),
    SUM(CASE WHEN answer_status = 'ANSWERED' THEN 1 ELSE 0 END),
    SUM(CASE WHEN busy_reason IS NOT NULL THEN 1 ELSE 0 END),
    SUM(CASE WHEN dial_cause LIKE '%OFFLINE%'
             OR dial_cause LIKE '%Subscriber%' THEN 1 ELSE 0 END),
    ROUND(AVG(ring_duration_ms))
FROM ringgroup_call_status
WHERE DATE(call_time) = DATE(NOW() - INTERVAL 1 DAY)
GROUP BY DATE(call_time), extension
ON DUPLICATE KEY UPDATE
    total_calls       = VALUES(total_calls),
    times_rang        = VALUES(times_rang),
    times_answered    = VALUES(times_answered),
    times_busy        = VALUES(times_busy),
    times_offline     = VALUES(times_offline),
    avg_ring_duration_ms = VALUES(avg_ring_duration_ms);

9. Cron Setup

Parser Cron (Every 5 Minutes)

The parser looks back 1 hour each run. The idempotency check prevents duplicate inserts, so the overlapping window is intentional and ensures no calls are missed during log rotation boundaries.

# Ring group log parser - runs every 5 minutes, scans last 1 hour
*/5 * * * * /usr/bin/php /var/www/html/ringgroup/ringgroup_parser_enhanced.php 1 >> /var/log/ringgroup_parser.log 2>&1

SIP Status Updater (Every Minute)

The script internally loops 6 times with 10-second sleeps, giving near-real-time status updates:

# SIP status updater - runs every minute (6 updates per minute internally)
* * * * * /var/www/html/ringgroup/sip_status_updater.sh >> /var/log/sip_updater.log 2>&1

Daily Stats Aggregation (Nightly)

# Aggregate yesterday's ring group stats at 02:00
0 2 * * * /usr/bin/mysql -u cron -pYOUR_DB_PASSWORD asterisk < /var/www/html/ringgroup/sql/aggregate_daily.sql >> /var/log/ringgroup_stats.log 2>&1

Parser Log Rotation

Add a logrotate config to prevent the parser log from growing unbounded:

# /etc/logrotate.d/ringgroup
/var/log/ringgroup_parser.log /var/log/sip_updater.log /var/log/ringgroup_stats.log {
    weekly
    rotate 4
    compress
    missingok
    notifempty
}

10. Tips, Edge Cases, and Scaling

Log Rotation Impact

This is the most common pitfall. When Asterisk rotates its log file (/var/log/asterisk/messages), the parser must handle the transition:

• Symptom: Calls during the rotation window are missed.
• Solution: The parser scans the last 1 hour but runs every 5 minutes. This 12x overlap window means a call only needs to exist in the log file for at least 5 minutes to be captured. As long as log rotation happens cleanly (rotate, compress, restart logging), the worst case is missing calls that happen in the exact second of rotation.
• Best practice: Configure log rotation to use copytruncate instead of create, which avoids the gap entirely:

/var/log/asterisk/messages {
    daily
    rotate 7
    compress
    copytruncate
    missingok
}

• Additional safeguard: After a log rotation, run the parser manually with a larger lookback window to catch any missed calls:

php /var/www/html/ringgroup/ringgroup_parser_enhanced.php 24 --verbose

Parsing Edge Cases

1. Year boundary: The Asterisk log format does not include the year ([Jan 27 17:53:45]). The parser assumes the current year. Around midnight on December 31, a log line from Dec 31 23:59:59 and one from Jan 1 00:00:01 will both be parsed as the current year. This is handled correctly because the parser only looks at the most recent hours.

2. Duplicate extensions in Dial(): If you accidentally list an extension twice in the Dial() command (e.g., SIP/8020&SIP/8020), Asterisk will only dial it once. The parser handles this correctly because it keys on the extension number.

3. Channel naming: Asterisk appends a unique suffix to channel names (SIP/8001-00000abc). The parser strips this suffix using the regex SIP\/(\d+)-[0-9a-f]+ to extract just the extension number.

4. Multiple ring group names: If you have multiple ring groups (e.g., afterhours and support), you need separate parser instances or modify the parser to accept multiple ring group names.

5. PJSIP vs. chan_sip: This parser is designed for chan_sip (channel names like SIP/8001). If you are using PJSIP, channel names will be PJSIP/8001 -- adjust the regex patterns accordingly.

Scaling for High Volume

Database growth: At 40 extensions per ring group and 170 calls per day:

Mitigation strategies:

1. Partition by month: Add range partitioning on call_time to keep queries fast:

ALTER TABLE ringgroup_call_status
PARTITION BY RANGE (TO_DAYS(call_time)) (
    PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

2. Archive old data: Move records older than 90 days to an archive table:

INSERT INTO ringgroup_call_status_archive
SELECT * FROM ringgroup_call_status
WHERE call_time < DATE_SUB(NOW(), INTERVAL 90 DAY);

DELETE FROM ringgroup_call_status
WHERE call_time < DATE_SUB(NOW(), INTERVAL 90 DAY);

3. Skip raw_log storage: The raw_log column in ringgroup_call_trace is the biggest space consumer. For high-volume systems, set it to NULL in the parser to save ~60% of trace table space.

4. Increase parser interval: On low-volume systems, you can run the parser every 15 or 30 minutes instead of every 5. Just increase the lookback window proportionally.

Security Considerations

1. Database user: Create a dedicated database user with minimal privileges:

CREATE USER 'ringgroup'@'localhost'
    IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT SELECT, INSERT ON asterisk.ringgroup_call_status
    TO 'ringgroup'@'localhost';
GRANT SELECT, INSERT ON asterisk.ringgroup_call_trace
    TO 'ringgroup'@'localhost';
GRANT SELECT, INSERT, UPDATE ON asterisk.ringgroup_daily_stats
    TO 'ringgroup'@'localhost';
GRANT SELECT ON asterisk.ringgroup_hangup_causes
    TO 'ringgroup'@'localhost';
GRANT SELECT, INSERT ON asterisk.ringgroup_sip_history
    TO 'ringgroup'@'localhost';
GRANT SELECT ON asterisk.ringgroup_sip_latest
    TO 'ringgroup'@'localhost';
FLUSH PRIVILEGES;

2. Web access: Protect the dashboard with HTTP authentication or IP restrictions in your web server config. The dashboard displays caller phone numbers and internal extension information.

3. File permissions: The sip_status.json file should be readable by the web server but not writable:

chown root:www-data /var/www/html/ringgroup/sip_status.json
chmod 644 /var/www/html/ringgroup/sip_status.json

Monitoring the Monitor

Add a simple health check to verify the parser is running:

#!/bin/bash
# Check if parser has run in the last 10 minutes
LAST_RUN=$(stat -c %Y /var/log/ringgroup_parser.log 2>/dev/null)
NOW=$(date +%s)
AGE=$(( NOW - LAST_RUN ))

if [ "$AGE" -gt 600 ]; then
    echo "WARNING: Ring group parser has not run in ${AGE} seconds"
    # Send alert via email, Slack, etc.
fi

Integration with Grafana

All 6 tables can be queried directly from Grafana using a MySQL data source. Recommended panels:

• Time series: Calls per hour (answered vs missed), from ringgroup_call_status
• Stat panel: Current online/offline count, from ringgroup_sip_latest
• Table: Agent leaderboard, from the agent performance query (Section 8.2)
• Heatmap: Call volume by hour and day-of-week
• Alert: Trigger when online extension count drops below a threshold

Conclusion

This system transforms a black box (the Asterisk ring group) into a fully observable component of your call center infrastructure. With 6 database tables, a 5-minute cron job, and a lightweight PHP dashboard, you gain visibility into:

• Every call: Which extensions were dialed, which rang, which answered, which were offline or busy
• Every extension: Online/offline history, answer rate, average ring time
• Every failure: Why a call was missed -- was it because nobody was online, or because everyone was busy?

The total overhead is minimal: a few seconds of PHP execution every 5 minutes, and a few hundred MB of database storage per year. The value is enormous: you can now investigate missed calls, hold agents accountable for their availability, and prove to management exactly what happens to after-hours calls.

Built and tested on ViciDial 2.14 with Asterisk 16/20 and MariaDB 10.5/10.11. Compatible with any Asterisk installation that uses chan_sip and writes to /var/log/asterisk/messages.