Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
vendors:cisco:routers:radius:radiusvoicecalls [2026/10/27 20:47] – created gerardorourkevendors:cisco:routers:radius:radiusvoicecalls [2026/11/12 15:33] (current) – [SQL Queries] gerardorourke
Line 1: Line 1:
-# Radius Voice Records - Low Level Design+====== Radius Voice Records - Low Level Design ======
  
-Oct 27, 2026 · @Gerard+===== 1. Purpose and scope =====
  
-## 1. Purpose and scope+''radiusVoiceCalls.php'' keeps one row per voice call in ''dbo.radiusVoiceCalls'' (database ''eirFinesseGadget_db''), built up from two sources: FreeRADIUS accounting records and Cisco Finesse agent-desktop events.
  
-`radiusVoiceCalls.php` keeps one row per voice call in `dbo.radiusVoiceCalls` (database `eirFinesseGadget_db`), built up from two sources: FreeRADIUS accounting records and Cisco Finesse agent-desktop events.+* **Interim-Update** (FreeRADIUS) inserts the row when the call is first reported. 
 +* **Stop** (FreeRADIUS) closes the call: disconnect time, cause, duration, ''callActive = 0''. 
 +* **finesse-start** (Cisco Finesse) adds the agent, dialog and queue details to the existing row.
  
-- **Interim-Update** (FreeRADIUS) inserts the row when the call is first reported. +Another system reads the table, filtering on ''callActive'' and ''customerPhoneNumber'', to see active calls and call history for a customer.
-- **Stop** (FreeRADIUS) closes the call: disconnect time, cause, duration, `callActive = 0`. +
-- **finesse-start** (Cisco Finesse) adds the agent, dialog and queue details to the existing row.+
  
-Another system reads the table, filtering on `callActive` and `customerPhoneNumber`, to see active calls and call history for a customer. +**Runtime:** one PHP file (''radiusVoiceCalls.php'') plus ''include/sql_db.php'' (the PDO connection), on Apache httpd with PHP 8.x and the ''pdo_sqlsrv'' driver, writing to SQL Server.
- +
-**Runtime:** one PHP file (`radiusVoiceCalls.php`) plus `include/sql_db.php` (the PDO connection), on Apache httpd with PHP 8.x and the `pdo_sqlsrv` driver, writing to SQL Server.+
  
 **Out of scope:** FreeRADIUS Start records, any other Finesse event type, retries of failed requests, and purging or archiving old rows. **Out of scope:** FreeRADIUS Start records, any other Finesse event type, retries of failed requests, and purging or archiving old rows.
  
-## 2. Architecture and data flow+===== 2. Architecture and data flow =====
  
 [embedded content: architecture and data flow · 6 components\] [embedded content: architecture and data flow · 6 components\]
Line 25: Line 23:
 **How one call's row is built up** **How one call's row is built up**
  
-1. The first Interim-Update for a call arrives from FreeRADIUS. The script inserts the row with `callActive = 1`. +- The first Interim-Update for a call arrives from FreeRADIUS. The script inserts the row with ''callActive = 1''. 
-2. While the call is active, a `finesse-start` from the Finesse server adds the agent, dialog and queue details to that row. +- While the call is active, a ''finesse-start'' from the Finesse server adds the agent, dialog and queue details to that row. 
-3. The Stop from FreeRADIUS closes the call: disconnect time, cause, duration, and `callActive = 0`.+- The Stop from FreeRADIUS closes the call: disconnect time, cause, duration, and ''callActive = 0''.
  
 The reading system can query the row at any point in this sequence. The reading system can query the row at any point in this sequence.
  
-## 3. Request interface+===== 3. Request interface =====
  
-The script accepts HTTP `PUT` with a JSON object body at one URL, from allowed source IPs only. Every response is JSON in the form `{"Reply-Message": "..."}`.+The script accepts HTTP ''PUT'' with a JSON object body at one URL, from allowed source IPs only. Every response is JSON in the form ''{"Reply-Message": "..."}''.
  
 **Checks, in the order they run** **Checks, in the order they run**
  
-| # | Check | Rule | Failure | +^ # ^ Check ^ Rule ^ Failure ^ 
-| --- | --- | --- | --- | +| 1 | Client IP | ''REMOTE_ADDR'' must be in ''$allowed_ips'' (an IPv4-mapped ''::ffff:'' prefix is removed first). ''X-Forwarded-For'' is never used. | 403 ''Forbidden'' | 
-| 1 | Client IP | `REMOTE_ADDR` must be in `$allowed_ips` (an IPv4-mapped `::ffff:` prefix is removed first). `X-Forwarded-For` is never used. | 403 `Forbidden` | +| 2 | Method | Must be ''PUT''. | 405 ''Method not allowed'', with an ''Allow: PUT'' header | 
-| 2 | Method | Must be `PUT`. | 405 `Method not allowed`, with an `Allow: PUT` header | +| 3 | api-key | The ''api-key'' header must equal ''$api_key'' (compared with ''hash_equals''). Not required when the client is ''127.0.0.1''. | 401 ''Invalid api-key'' |
-| 3 | api-key | The `api-key` header must equal `$api_key` (compared with `hash_equals`). Not required when the client is `127.0.0.1`. | 401 `Invalid api-key` |+
 | 4 | Body | Not empty, at most 65,536 bytes, valid JSON, and a JSON object (not a list). | 400 or 413 | | 4 | Body | Not empty, at most 65,536 bytes, valid JSON, and a JSON object (not a list). | 400 or 413 |
-| 5 | Status type | Must name one of the three types below. | 422 `Missing or unknown Acct-Status-Type` |+| 5 | Status type | Must name one of the three types below. | 422 ''Missing or unknown Acct-Status-Type'' |
  
 **Request types** **Request types**
  
-| Status type | Key it arrives in | Sent by | Effect on the table | +^ Status type ^ Key it arrives in ^ Sent by ^ Effect on the table ^ 
-| --- | --- | --- | --- | +| ''Interim-Update'' | ''Acct-Status-Type'' | FreeRADIUS rest module | Inserts the row, only the first time an ''h323-conf-id'' is seen | 
-| `Interim-Update` | `Acct-Status-Type` | FreeRADIUS rest module | Inserts the row, only the first time an `h323-conf-id` is seen | +| ''Stop'' | ''Acct-Status-Type'' | FreeRADIUS rest module | Updates the existing row and closes the call | 
-| `Stop` | `Acct-Status-Type` | FreeRADIUS rest module | Updates the existing row and closes the call | +| ''finesse-start'' | ''acctStatusType'' | Cisco Finesse server | Updates the existing row with agent details |
-| `finesse-start` | `acctStatusType` | Cisco Finesse server | Updates the existing row with agent details |+
  
-The script reads `Acct-Status-Type` first and falls back to `acctStatusType`. The value is matched case-insensitively. Any other value, including the earlier `Cisco-Finesse` placeholder, returns 422.+The script reads ''Acct-Status-Type'' first and falls back to ''acctStatusType''. The value is matched case-insensitively. Any other value, including the earlier ''Cisco-Finesse'' placeholder, returns 422.
  
 All values are read as text. A field that is missing, empty or not a plain value (an object or list) is treated as absent. All values are read as text. A field that is missing, empty or not a plain value (an object or list) is treated as absent.
Line 59: Line 55:
 **Example Interim-Update body** **Example Interim-Update body**
  
-```json+<code json>
 { {
   "Acct-Session-Id": "00002C27",   "Acct-Session-Id": "00002C27",
Line 73: Line 69:
   "Acct-Status-Type": "Interim-Update"   "Acct-Status-Type": "Interim-Update"
 } }
-```+</code>
  
 **Example finesse-start body** **Example finesse-start body**
  
-```json+<code json>
 { {
   "acctStatusType": "finesse-start",   "acctStatusType": "finesse-start",
Line 90: Line 86:
   "queueName": "eircomSK01.SG"   "queueName": "eircomSK01.SG"
 } }
-```+</code>
  
-## 4. Processing flow+===== 4. Processing flow =====
  
 &#91;embedded content: request processing · 5 checks, 3 handlers\] &#91;embedded content: request processing · 5 checks, 3 handlers\]
Line 98: Line 94:
 A failed check stops the request and returns the code shown in its box. Section 8 lists every code and message. A failed check stops the request and returns the code shown in its box. Section 8 lists every code and message.
  
-### Interim-Update+==== Interim-Update ====
  
-1. Read the call ID (spaces removed), the gateway and the session ID. If any of the three is missing, return 422. +- Read the call ID (spaces removed), the gateway and the session ID. If any of the three is missing, return 422. 
-2. Work out the customer phone number from the call direction and the E.164 rules (section 7). +- Work out the customer phone number from the call direction and the E.164 rules (section 7). 
-3. Open a transaction and look up the call ID (Q1). If a row exists, log the request at DEBUG and return 200 without writing. +- Open a transaction and look up the call ID (Q1). If a row exists, log the request at DEBUG and return 200 without writing. 
-4. Otherwise insert the row (Q2) and commit. A duplicate-key error from a simultaneous request is retried once.+- Otherwise insert the row (Q2) and commit. A duplicate-key error from a simultaneous request is retried once.
  
-### Stop+==== Stop ====
  
-1. If `h323-conf-id` is missing, return 422. +- If ''h323-conf-id'' is missing, return 422. 
-2. Convert the times, work out `callDuration`, and convert the disconnect cause from hex (section 7). +- Convert the times, work out ''callDuration'', and convert the disconnect cause from hex (section 7). 
-3. Look up the call ID (Q3). If there is no row, log an ERROR with the request and return 404. +- Look up the call ID (Q3). If there is no row, log an ERROR with the request and return 404. 
-4. Update the row (Q4), which sets `callActive = 0`, and commit.+- Update the row (Q4), which sets ''callActive = 0'', and commit.
  
-### finesse-start+==== finesse-start ====
  
-1. Decide how to find the call: by a real `h323ConfId`, or by phone number when `h323ConfId` is `not_set`, empty or missing. With neither a real ID nor a phone number, return 422. +- Decide how to find the call: by a real ''h323ConfId'', or by phone number when ''h323ConfId'' is ''not_set'', empty or missing. With neither a real ID nor a phone number, return 422. 
-2. Prepare the agent fields, cutting each to its column length. +- Prepare the agent fields, cutting each to its column length. 
-3. Find the row (Q5, or Q6 and Q7). +- Find the row (Q5, or Q6 and Q7). 
-4. One row: update it (Q8) and return 200. No rows: return 404. More than one row left after the window: return 422 and update nothing.+- One row: update it (Q8) and return 200. No rows: return 404. More than one row left after the window: return 422 and update nothing.
  
-## 5. Data model+===== 5. Data model =====
  
-One table, `dbo.radiusVoiceCalls`, holds one row per call. `h323_conf_id` is the only key used to find a call: `Acct_Session_Id` is stored but never searched, because different gateways can reuse the same value.+One table, ''dbo.radiusVoiceCalls'', holds one row per call. ''h323_conf_id'' is the only key used to find a call: ''Acct_Session_Id'' is stored but never searched, because different gateways can reuse the same value.
  
 **Columns and which request writes them** **Columns and which request writes them**
  
-| Column | Type | Written by | Value | +^ Column ^ Type ^ Written by ^ Value ^ 
-| --- | --- | --- | --- | +| ''id'' | int, identity, primary key | database | Row number | 
-| `id` | int, identity, primary key | database | Row number | +| ''DbDateTime'' | datetime, required | Interim-Update | ''GETDATE()'' on the SQL Server, at insert | 
-| `DbDateTime` | datetime, required | Interim-Update | `GETDATE()` on the SQL Server, at insert | +| ''Acct_Session_Id'' | varchar(50), required | Interim-Update | ''Acct-Session-Id'', stored only | 
-| `Acct_Session_Id` | varchar(50), required | Interim-Update | `Acct-Session-Id`, stored only | +| ''h323_conf_id'' | varchar(50), required, unique | Interim-Update | ''h323-conf-id'' with all spaces removed | 
-| `h323_conf_id` | varchar(50), required, unique | Interim-Update | `h323-conf-id` with all spaces removed | +| ''h323_gw_id'' | varchar(100), required | Interim-Update | ''h323-gw-id'' | 
-| `h323_gw_id` | varchar(100), required | Interim-Update | `h323-gw-id` | +| ''customerPhoneNumber'' | varchar(50) | Interim-Update | Inbound (''answer''): ''User-Name''. Outbound (''originate''): ''Called-Station-Id'' converted to E.164. Any other origin: NULL | 
-| `customerPhoneNumber` | varchar(50) | Interim-Update | Inbound (`answer`): `User-Name`. Outbound (`originate`): `Called-Station-Id` converted to E.164. Any other origin: NULL | +| ''inboundCall'' | bit | Interim-Update | 1 if ''h323-call-origin'' is ''answer'', otherwise 0 | 
-| `inboundCall` | bit | Interim-Update | 1 if `h323-call-origin` is `answer`, otherwise 0 | +| ''callActive'' | bit | Interim-Update, Stop | Set to 1 at insert, set to 0 by Stop | 
-| `callActive` | bit | Interim-Update, Stop | Set to 1 at insert, set to 0 by Stop | +| ''h323_setup_time'' | datetime | Interim-Update | ''h323-setup-time'', Irish local time | 
-| `h323_setup_time` | datetime | Interim-Update | `h323-setup-time`, Irish local time | +| ''h323_connect_time'' | datetime | Interim-Update | ''h323-connect-time'', Irish local time | 
-| `h323_connect_time` | datetime | Interim-Update | `h323-connect-time`, Irish local time | +| ''h323_disconnect_time'' | datetime | Stop | ''h323-disconnect-time'', Irish local time | 
-| `h323_disconnect_time` | datetime | Stop | `h323-disconnect-time`, Irish local time | +| ''callDuration'' | int | Stop | Seconds from connect to disconnect (see section 7) | 
-| `callDuration` | int | Stop | Seconds from connect to disconnect (see section 7) | +| ''h323_disconnect_cause'' | int | Stop | ''h323-disconnect-cause'', converted from hex to decimal | 
-| `h323_disconnect_cause` | int | Stop | `h323-disconnect-cause`, converted from hex to decimal | +| ''GW_CalledNumber'' | varchar(50) | Interim-Update | ''gw-cdn'' | 
-| `GW_CalledNumber` | varchar(50) | Interim-Update | `gw-cdn` | +| ''Called_Station_Id'' | varchar(50) | Interim-Update | ''Called-Station-Id'', as received | 
-| `Called_Station_Id` | varchar(50) | Interim-Update | `Called-Station-Id`, as received | +| ''User_Name'' | varchar(50) | Interim-Update | ''User-Name'' | 
-| `User_Name` | varchar(50) | Interim-Update | `User-Name` | +| ''NAS_IP_Address'' | varchar(15) | Interim-Update | ''NAS-IP-Address'' | 
-| `NAS_IP_Address` | varchar(15) | Interim-Update | `NAS-IP-Address` | +| ''h323_remote_address'' | varchar(50) | Stop | ''h323-remote-address'' | 
-| `h323_remote_address` | varchar(50) | Stop | `h323-remote-address` | +| ''Acct_Session_Time'' | int | Stop | ''Acct-Session-Time'' | 
-| `Acct_Session_Time` | int | Stop | `Acct-Session-Time` | +| ''contactCentreCall'' | bit | Interim-Update | 1 if ''h323-gw-id'' is in ''$CCE_CUBES'' (case-insensitive), otherwise 0 | 
-| `contactCentreCall` | bit | Interim-Update | 1 if `h323-gw-id` is in `$CCE_CUBES` (case-insensitive), otherwise 0 | +| ''contactCentreAgentConnected'' | bit, default 0 | finesse-start | Set to 1 | 
-| `contactCentreAgentConnected` | bit, default 0 | finesse-start | Set to 1 | +| ''dialogId'' | varchar(50) | finesse-start | ''dialogId'', kept as text (may have a leading 0) | 
-| `dialogId` | varchar(50) | finesse-start | `dialogId`, kept as text (may have a leading 0) | +| ''agentId'' | varchar(32) | finesse-start | ''agentId'' | 
-| `agentId` | varchar(32) | finesse-start | `agentId` | +| ''agentFirstName'' | varchar(50) | finesse-start | ''agentFirstName'' | 
-| `agentFirstName` | varchar(50) | finesse-start | `agentFirstName` | +| ''agentLastName'' | varchar(50) | finesse-start | ''agentLastName'' | 
-| `agentLastName` | varchar(50) | finesse-start | `agentLastName` | +| ''agentExtension'' | varchar(20) | finesse-start | ''agentExtension'', kept as text | 
-| `agentExtension` | varchar(20) | finesse-start | `agentExtension`, kept as text | +| ''cceCallType'' | varchar(50) | finesse-start | ''callType'' | 
-| `cceCallType` | varchar(50) | finesse-start | `callType` | +| ''queueName'' | varchar(50) | finesse-start | ''queueName'', including the literal ''not_set'' | 
-| `queueName` | varchar(50) | finesse-start | `queueName`, including the literal `not_set` | +| ''agentUpdates'' | int, default 0 | finesse-start | Increased by 1 on every Finesse update | 
-| `agentUpdates` | int, default 0 | finesse-start | Increased by 1 on every Finesse update | +| ''recordUpdates'' | int | all three | 1 at insert, then increased by 1 on every Stop and Finesse update |
-| `recordUpdates` | int | all three | 1 at insert, then increased by 1 on every Stop and Finesse update |+
  
 **Keys and indexes** **Keys and indexes**
  
-| Name | Columns | Purpose | +^ Name ^ Columns ^ Purpose ^ 
-| --- | --- | --- | +| ''PK_radiusVoiceCalls'' (clustered) | ''id'' | Updates by row | 
-| `PK_radiusVoiceCalls` (clustered) | `id` | Updates by row | +| ''UQ_h323_conf_id'' (unique) | ''h323_conf_id'' | Every lookup by call ID, and the guard against duplicate inserts | 
-| `UQ_h323_conf_id` (unique) | `h323_conf_id` | Every lookup by call ID, and the guard against duplicate inserts | +| ''IX_radiusVoiceCalls_phone_active'' (recommended, not yet created) | ''customerPhoneNumber'', ''callActive''; includes ''h323_connect_time'', ''DbDateTime'' | The Finesse lookup by phone number, and the reading system's filter |
-| `IX_radiusVoiceCalls_phone_active` (recommended, not yet created) | `customerPhoneNumber`, `callActive`; includes `h323_connect_time`, `DbDateTime` | The Finesse lookup by phone number, and the reading system's filter |+
  
-```sql+<code sql>
 CREATE NONCLUSTERED INDEX [IX_radiusVoiceCalls_phone_active] CREATE NONCLUSTERED INDEX [IX_radiusVoiceCalls_phone_active]
     ON [dbo].[radiusVoiceCalls] ([customerPhoneNumber], [callActive])     ON [dbo].[radiusVoiceCalls] ([customerPhoneNumber], [callActive])
     INCLUDE ([h323_connect_time], [DbDateTime]);     INCLUDE ([h323_connect_time], [DbDateTime]);
-```+</code>
  
-## 6. SQL queries+===== 6. SQL queries =====
  
-Each request opens its own connection (`getDbConnection()`), runs all its statements inside one transaction, and commits or rolls back as a unit. Every statement is a prepared statement with bound parameters, so no request value is ever concatenated into SQL. Date and time values are bound as ISO 8601 text (`2026-10-20T13:51:52.011`), which SQL Server reads the same way whatever its language or `DATEFORMAT` setting.+Each request opens its own connection (''getDbConnection()''), runs all its statements inside one transaction, and commits or rolls back as a unit. Every statement is a prepared statement with bound parameters, so no request value is ever concatenated into SQL. Date and time values are bound as ISO 8601 text (''2026-10-20T13:51:52.011''), which SQL Server reads the same way whatever its language or ''DATEFORMAT'' setting.
  
-### Interim-Update (`insertInterimIfNew`)+==== Interim-Update (''insertInterimIfNew'') ====
  
 **Q1. Does the call already have a row?** **Q1. Does the call already have a row?**
  
-```sql+<code sql>
 SELECT id SELECT id
   FROM dbo.radiusVoiceCalls WITH (UPDLOCK, HOLDLOCK)   FROM dbo.radiusVoiceCalls WITH (UPDLOCK, HOLDLOCK)
  WHERE h323_conf_id = :conf  WHERE h323_conf_id = :conf
-```+</code>
  
-The lookup uses `UQ_h323_conf_id`. `UPDLOCK, HOLDLOCK` keeps the looked-up key range locked until the transaction ends, so a second request for the same call waits instead of inserting in the gap between this check and the insert. If a row comes back, nothing is written and the script logs the request at DEBUG and returns 200.+The lookup uses ''UQ_h323_conf_id''. ''UPDLOCK, HOLDLOCK'' keeps the looked-up key range locked until the transaction ends, so a second request for the same call waits instead of inserting in the gap between this check and the insert. If a row comes back, nothing is written and the script logs the request at DEBUG and returns 200.
  
 **Q2. Insert the row (only when Q1 found nothing)** **Q2. Insert the row (only when Q1 found nothing)**
  
-```sql+<code sql>
 INSERT INTO dbo.radiusVoiceCalls INSERT INTO dbo.radiusVoiceCalls
   (DbDateTime, Acct_Session_Id, h323_conf_id, h323_gw_id, GW_CalledNumber, Called_Station_Id,   (DbDateTime, Acct_Session_Id, h323_conf_id, h323_gw_id, GW_CalledNumber, Called_Station_Id,
Line 199: Line 193:
    :usr, :nas, :setup, :conn, :inb,    :usr, :nas, :setup, :conn, :inb,
    :cpn, 1, :cce, 1)    :cpn, 1, :cce, 1)
-```+</code>
  
-`callActive` and `recordUpdates` are set to 1 in the statement. Optional values that were absent from the request are bound as NULL.+''callActive'' and ''recordUpdates'' are set to 1 in the statement. Optional values that were absent from the request are bound as NULL.
  
-**Duplicate-key retry.** If two requests for the same new call overlap and the insert fails with a duplicate-key error on `UQ_h323_conf_id`, the transaction is rolled back and the whole function runs once more. The second attempt finds the row in Q1 and returns without writing. Any other database error, or a second failure, returns 500.+**Duplicate-key retry.** If two requests for the same new call overlap and the insert fails with a duplicate-key error on ''UQ_h323_conf_id'', the transaction is rolled back and the whole function runs once more. The second attempt finds the row in Q1 and returns without writing. Any other database error, or a second failure, returns 500.
  
-### Stop (`updateStop`)+==== Stop (''updateStop'') ====
  
 **Q3. Find the row** **Q3. Find the row**
  
-```sql+<code sql>
 SELECT id, h323_disconnect_time SELECT id, h323_disconnect_time
   FROM dbo.radiusVoiceCalls WITH (UPDLOCK)   FROM dbo.radiusVoiceCalls WITH (UPDLOCK)
  WHERE h323_conf_id = :conf  WHERE h323_conf_id = :conf
-```+</code>
  
 No row means the Stop arrived without an Interim-Update (for example a call that never connected): the script logs an ERROR with the request and returns 404. A row that already has a disconnect time logs a WARNING and is still updated. No row means the Stop arrived without an Interim-Update (for example a call that never connected): the script logs an ERROR with the request and returns 404. A row that already has a disconnect time logs a WARNING and is still updated.
Line 219: Line 213:
 **Q4. Close the call** **Q4. Close the call**
  
-```sql+<code sql>
 UPDATE dbo.radiusVoiceCalls SET UPDATE dbo.radiusVoiceCalls SET
     h323_disconnect_time  = COALESCE(:disc,   h323_disconnect_time),     h323_disconnect_time  = COALESCE(:disc,   h323_disconnect_time),
Line 229: Line 223:
     recordUpdates         = ISNULL(recordUpdates, 0) + 1     recordUpdates         = ISNULL(recordUpdates, 0) + 1
  WHERE id = :id  WHERE id = :id
-```+</code>
  
-`COALESCE` means a value that could not be read from the Stop request leaves the stored value as it was.+''COALESCE'' means a value that could not be read from the Stop request leaves the stored value as it was.
  
-### finesse-start (`findFinesseRow`, then update)+==== finesse-start (''findFinesseRow'', then update) ====
  
-**Q5. Find by call ID** (when `h323ConfId` is a real value)+**Q5. Find by call ID** (when ''h323ConfId'' is a real value)
  
-```sql+<code sql>
 SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK) WHERE h323_conf_id = :conf SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK) WHERE h323_conf_id = :conf
-```+</code>
  
 No row returns 404. There is no fallback to the phone number. No row returns 404. There is no fallback to the phone number.
  
-**Q6. Find by phone number** (when `h323ConfId` is `not_set`, empty or missing)+**Q6. Find by phone number** (when ''h323ConfId'' is ''not_set'', empty or missing)
  
-```sql+<code sql>
 SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK) SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK)
  WHERE customerPhoneNumber = :ph AND callActive = 1  WHERE customerPhoneNumber = :ph AND callActive = 1
-```+</code>
  
 No rows returns 404. One row is updated. More than one row goes to Q7. No rows returns 404. One row is updated. More than one row goes to Q7.
Line 254: Line 248:
 **Q7. Narrow by connect time** (only when Q6 returned more than one row) **Q7. Narrow by connect time** (only when Q6 returned more than one row)
  
-```sql+<code sql>
 SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK) SELECT id FROM dbo.radiusVoiceCalls WITH (UPDLOCK)
  WHERE customerPhoneNumber = :ph AND callActive = 1 AND h323_connect_time >= :cutoff  WHERE customerPhoneNumber = :ph AND callActive = 1 AND h323_connect_time >= :cutoff
-```+</code>
  
-`:cutoff` is the current time in Europe/Dublin, computed in PHP, minus `$FINESSE_MATCH_SECONDS` (default 5). It is computed in PHP because `h323_connect_time` holds Irish local time and the SQL Server clock may use another zone. One row is updated, no rows returns 404, and more than one row returns 422 without updating anything.+'':cutoff'' is the current time in Europe/Dublin, computed in PHP, minus ''$FINESSE_MATCH_SECONDS'' (default 5). It is computed in PHP because ''h323_connect_time'' holds Irish local time and the SQL Server clock may use another zone. One row is updated, no rows returns 404, and more than one row returns 422 without updating anything.
  
 **Q8. Write the agent details** **Q8. Write the agent details**
  
-```sql+<code sql>
 UPDATE dbo.radiusVoiceCalls SET UPDATE dbo.radiusVoiceCalls SET
     dialogId = :dlg, agentId = :aid, agentFirstName = :fn, agentLastName = :ln,     dialogId = :dlg, agentId = :aid, agentFirstName = :fn, agentLastName = :ln,
Line 271: Line 265:
     recordUpdates = ISNULL(recordUpdates, 0) + 1     recordUpdates = ISNULL(recordUpdates, 0) + 1
  WHERE id = :id  WHERE id = :id
-```+</code>
  
-Values are written exactly as sent, including `not_set`. A field missing from the request is written as NULL.+Values are written exactly as sent, including ''not_set''. A field missing from the request is written as NULL.
  
-## 7. Data conversions+===== 7. Data conversions =====
  
 Request values are cleaned before they reach SQL. Anything that cannot be converted is stored as NULL and logged at WARNING, and the rest of the request still goes ahead. Request values are cleaned before they reach SQL. Anything that cannot be converted is stored as NULL and logged at WARNING, and the rest of the request still goes ahead.
  
-| Value | Rule | Example | +^ Value ^ Rule ^ Example ^ 
-| --- | --- | --- | +| Text fields | Trimmed. Empty becomes NULL. Longer than the column: cut to the column length and logged at WARNING with the column name and original length. | ''"  "'' becomes NULL | 
-| Text fields | Trimmed. Empty becomes NULL. Longer than the column: cut to the column length and logged at WARNING with the column name and original length. | `"  "` becomes NULL | +| Call ID (''h323-conf-id'', ''h323ConfId'') | All whitespace removed. For Finesse, a missing, empty or ''not_set'' value (any case) means "no call ID". | ''DB0B4B6B CBBB11F1 AE7699CA 3DE394D9'' becomes ''DB0B4B6BCBBB11F1AE7699CA3DE394D9'' | 
-| Call ID (`h323-conf-id`, `h323ConfId`) | All whitespace removed. For Finesse, a missing, empty or `not_set` value (any case) means "no call ID". | `DB0B4B6B CBBB11F1 AE7699CA 3DE394D9` becomes `DB0B4B6BCBBB11F1AE7699CA3DE394D9` | +| Cisco times | Format ''HH:MM:SS.fff ZONE Day Mon D YYYY''. A leading ''*'' or ''.'' (clock not synchronised) is dropped. ''IRL'' is read as Europe/Dublin, so summer and winter time are handled. ''UTC'' and ''GMT'' are read as UTC and converted to Irish time. Any other zone token is assumed to be Irish time and logged at WARNING. The result is stored as Irish local time to the millisecond. Unreadable or impossible dates become NULL. | ''13:51:52.011 IRL Tue Oct 20 2026'' is stored as ''2026-10-20 13:51:52.011'' | 
-| Cisco times | Format `HH:MM:SS.fff ZONE Day Mon D YYYY`. A leading `*` or `.` (clock not synchronised) is dropped. `IRL` is read as Europe/Dublin, so summer and winter time are handled. `UTC` and `GMT` are read as UTC and converted to Irish time. Any other zone token is assumed to be Irish time and logged at WARNING. The result is stored as Irish local time to the millisecond. Unreadable or impossible dates become NULL. | `13:51:52.011 IRL Tue Oct 20 2026` is stored as `2026-10-20 13:51:52.011` | +| Disconnect cause | Sent as hex. A leading ''0x'' is allowed, 1 to 7 hex digits. Stored as a decimal number. Invalid values become NULL. | ''10'' is stored as ''16'' (normal call clearing) | 
-| Disconnect cause | Sent as hex. A leading `0x` is allowed, 1 to 7 hex digits. Stored as a decimal number. Invalid values become NULL. | `10` is stored as `16` (normal call clearing) | +| Whole numbers (''Acct-Session-Time'') | Must be an integer from 0 to 2,147,483,647. | ''9'' | 
-| Whole numbers (`Acct-Session-Time`) | Must be an integer from 0 to 2,147,483,647. | `9` | +| Gateway match | ''h323-gw-id'' compared with ''$CCE_CUBES'' ignoring case. | Match sets ''contactCentreCall'' to 1 |
-| Gateway match | `h323-gw-id` compared with `$CCE_CUBES` ignoring case. | Match sets `contactCentreCall` to 1 |+
  
-### Call duration+==== Call duration ====
  
-Duration is only worked out when the Stop request carries `h323-connect-time`, which a call that never connected does not have. In that case `callDuration` stays NULL.+Duration is only worked out when the Stop request carries ''h323-connect-time'', which a call that never connected does not have. In that case ''callDuration'' stays NULL.
  
-1. Both times are read as time-zone-aware timestamps, so the clocks going back in autumn cannot distort the result. +- Both times are read as time-zone-aware timestamps, so the clocks going back in autumn cannot distort the result. 
-2. `callDuration` is the difference in seconds, rounded to a whole second (8.945 seconds is stored as 9). +- ''callDuration'' is the difference in seconds, rounded to a whole second (8.945 seconds is stored as 9). 
-3. If either time is unreadable, or the disconnect is before the connect, the script uses `Acct-Session-Time` instead and logs the reason. The repeated hour at the autumn clock change (01:00 to 02:00) is ambiguous, and a call spanning it can hit this fallback.+- If either time is unreadable, or the disconnect is before the connect, the script uses ''Acct-Session-Time'' instead and logs the reason. The repeated hour at the autumn clock change (01:00 to 02:00) is ambiguous, and a call spanning it can hit this fallback.
  
-### Customer phone number+==== Customer phone number ====
  
-The value stored in `customerPhoneNumber` depends on the call direction, taken from `h323-call-origin` (case-insensitive).+The value stored in ''customerPhoneNumber'' depends on the call direction, taken from ''h323-call-origin'' (case-insensitive).
  
-| Origin | Direction | Value stored | +^ Origin ^ Direction ^ Value stored ^ 
-| --- | --- | --- | +| ''answer'' | Inbound | ''User-Name'', unchanged | 
-| `answer` | Inbound | `User-Name`, unchanged | +| ''originate'' | Outbound | ''Called-Station-Id'', converted to E.164 with the rules below |
-| `originate` | Outbound | `Called-Station-Id`, converted to E.164 with the rules below |+
 | Anything else | Unknown | NULL (logged at DEBUG) | | Anything else | Unknown | NULL (logged at DEBUG) |
  
-Outbound numbers arrive with an extra leading `0`, the outside-line prefix. The rules are tried in this order, and the first match wins.+Outbound numbers arrive with an extra leading ''0'', the outside-line prefix. The rules are tried in this order, and the first match wins.
  
-| Starts with | Becomes | Example | +^ Starts with ^ Becomes ^ Example ^ 
-| --- | --- | --- | +| ''000'' | ''+'' followed by the rest | ''000442012345678'' becomes ''+442012345678'' | 
-| `000` | `+` followed by the rest | `000442012345678` becomes `+442012345678` | +| ''00'' | ''+353'' followed by the rest | ''00858769707'' becomes ''+353858769707'' | 
-| `00` | `+353` followed by the rest | `00858769707` becomes `+353858769707` | +| ''0'' | ''+353'' followed by the rest | ''01800123123'' becomes ''+3531800123123'' | 
-| `0` | `+353` followed by the rest | `01800123123` becomes `+3531800123123` | +| Anything else (an extension, or already ''+'') | Unchanged | ''12800'' stays ''12800'' |
-| Anything else (an extension, or already `+`) | Unchanged | `12800` stays `12800` |+
  
-## 8. Configuration, logging and error handling+===== 8. Configuration, logging and error handling =====
  
-### Configuration+==== Configuration ====
  
-All settings are variables and constants at the top of `radiusVoiceCalls.php`.+All settings are variables and constants at the top of ''radiusVoiceCalls.php''.
  
-| Setting | Default | Meaning | +^ Setting ^ Default ^ Meaning ^ 
-| --- | --- | --- | +| ''$DEBUG'' | ''false'' | ''true'' writes DEBUG lines to the PHP error log. Errors and warnings are always written. | 
-| `$DEBUG` | `false` | `true` writes DEBUG lines to the PHP error log. Errors and warnings are always written. | +| ''$allowed_ips'' | ''['127.0.0.1']'' | Source IPs allowed to call the script. Add the FreeRADIUS server and the Finesse server. | 
-| `$allowed_ips` | `['127.0.0.1']` | Source IPs allowed to call the script. Add the FreeRADIUS server and the Finesse server. | +| ''$api_key'' | ''1234'' (placeholder) | Required in the ''api-key'' header from every client except ''127.0.0.1''. | 
-| `$api_key` | `1234` (placeholder) | Required in the `api-key` header from every client except `127.0.0.1`. | +| ''$CCE_CUBES'' | one test gateway | ''h323-gw-id'' values that belong to Cisco CCE. A match sets ''contactCentreCall'' to 1. | 
-| `$CCE_CUBES` | one test gateway | `h323-gw-id` values that belong to Cisco CCE. A match sets `contactCentreCall` to 1. | +| ''$FINESSE_MATCH_SECONDS'' | ''5'' | Window for narrowing a phone-number lookup that matches several active calls (section 6, Q7). | 
-| `$FINESSE_MATCH_SECONDS` | `5` | Window for narrowing a phone-number lookup that matches several active calls (section 6, Q7). | +| ''MAX_BODY_BYTES'' | 65,536 | Largest request body accepted. | 
-| `MAX_BODY_BYTES` | 65,536 | Largest request body accepted. | +| ''LOCAL_TZ'' | ''Europe/Dublin'' | Time zone for ''IRL'' times and for the Finesse window. |
-| `LOCAL_TZ` | `Europe/Dublin` | Time zone for `IRL` times and for the Finesse window. |+
  
-The database host, name, user and password live in `include/sql_db.php`, which the script loads with `require_once __DIR__ . "/include/sql_db.php"`. The database name there must be `eirFinesseGadget_db`.+The database host, name, user and password live in ''include/sql_db.php'', which the script loads with ''require_once __DIR__ . "/include/sql_db.php"''. The database name there must be ''eirFinesseGadget_db''.
  
-### Logging+==== Logging ====
  
-Lines go to the PHP error log in the form `|radiusVoiceCalls.php|LEVEL|{json}`. The JSON always has a `msg`, plus `client_ip`, and `acct_session_id`, `h323_conf_id` and `dialog_id` once the request has supplied them, so one call can be followed across lines. Error lines include the request body (cut to 2,000 characters when the JSON itself is invalid). The `api-key` value is never logged.+Lines go to the PHP error log in the form ''|radiusVoiceCalls.php|LEVEL|{json}''. The JSON always has a ''msg'', plus ''client_ip'', and ''acct_session_id'', ''h323_conf_id'' and ''dialog_id'' once the request has supplied them, so one call can be followed across lines. Error lines include the request body (cut to 2,000 characters when the JSON itself is invalid). The ''api-key'' value is never logged.
  
-| Level | Used for | +^ Level ^ Used for ^ 
-| --- | --- | +| ''DEBUG'' (only when ''$DEBUG'' is true) | Every request received, results of each handler, repeat Interim-Updates that were ignored, the connect-time window being applied, fields absent from a Finesse update | 
-| `DEBUG` (only when `$DEBUG` is true) | Every request received, results of each handler, repeat Interim-Updates that were ignored, the connect-time window being applied, fields absent from a Finesse update | +| ''WARNING'' | Rejected requests (wrong IP, method or api-key), values truncated or converted to NULL, a Stop for a call that already has a disconnect time, a negative duration | 
-| `WARNING` | Rejected requests (wrong IP, method or api-key), values truncated or converted to NULL, a Stop for a call that already has a disconnect time, a negative duration | +| ''ERROR'' | Invalid JSON, missing required fields, unknown status type, no matching row, more than one matching row, database failures, unhandled exceptions |
-| `ERROR` | Invalid JSON, missing required fields, unknown status type, no matching row, more than one matching row, database failures, unhandled exceptions |+
  
-### Response codes+==== Response codes ====
  
-| Code | Message | When | +^ Code ^ Message ^ When ^ 
-| --- | --- | --- | +| 200 | ''OK'' | Request processed. This includes an Interim-Update that was ignored because the row already exists. | 
-| 200 | `OK` | Request processed. This includes an Interim-Update that was ignored because the row already exists. | +| 400 | ''Empty request body'', ''Invalid JSON'', ''JSON body must be an object'' | Body problem | 
-| 400 | `Empty request body`, `Invalid JSON`, `JSON body must be an object` | Body problem | +| 401 | ''Invalid api-key'' | Header missing or wrong | 
-| 401 | `Invalid api-key` | Header missing or wrong | +| 403 | ''Forbidden'' | Client IP not allowed | 
-| 403 | `Forbidden` | Client IP not allowed | +| 404 | ''Record not found'' | Stop or finesse-start found no matching row | 
-| 404 | `Record not found` | Stop or finesse-start found no matching row | +| 405 | ''Method not allowed'' | Anything other than PUT | 
-| 405 | `Method not allowed` | Anything other than PUT | +| 413 | ''Request body too large'' | Over 65,536 bytes | 
-| 413 | `Request body too large` | Over 65,536 bytes | +| 422 | ''Missing required field(s): ...'', ''Missing or unknown Acct-Status-Type'', ''More than one matching record'' | Validation failed, or the phone-number lookup was ambiguous | 
-| 422 | `Missing required field(s): ...`, `Missing or unknown Acct-Status-Type`, `More than one matching record` | Validation failed, or the phone-number lookup was ambiguous | +| 500 | ''Database error'', ''Internal error'' | Any database or unexpected failure. The transaction is rolled back, so nothing partial is stored. |
-| 500 | `Database error`, `Internal error` | Any database or unexpected failure. The transaction is rolled back, so nothing partial is stored. |+
  
-**Required fields.** An Interim-Update needs `Acct-Session-Id`, `h323-conf-id` and `h323-gw-id`, because those columns cannot be NULL. A Stop needs `h323-conf-id`. A finesse-start needs either a real `h323ConfId` or a `phoneNumber`. Every other field is optional.+**Required fields.** An Interim-Update needs ''Acct-Session-Id'', ''h323-conf-id'' and ''h323-gw-id'', because those columns cannot be NULL. A Stop needs ''h323-conf-id''. A finesse-start needs either a real ''h323ConfId'' or a ''phoneNumber''. Every other field is optional.
  
-## 9. Operations, assumptions and known limitations+===== 9. Operations, assumptions and known limitations =====
  
-### Deployment checklist+==== Deployment checklist ====
  
-- Apache must pass `PUT` requests to PHP. Check that no `Limit` or `LimitExcept` rule blocks the method. +* Apache must pass ''PUT'' requests to PHP. Check that no ''Limit'' or ''LimitExcept'' rule blocks the method. 
-- PHP needs the `pdo_sqlsrv` extension and the Microsoft ODBC driver for SQL Server. +* PHP needs the ''pdo_sqlsrv'' extension and the Microsoft ODBC driver for SQL Server. 
-- The database login needs `SELECT`, `INSERT` and `UPDATE` on `dbo.radiusVoiceCalls`. +* The database login needs ''SELECT'', ''INSERT'' and ''UPDATE'' on ''dbo.radiusVoiceCalls''. 
-- `include/sql_db.php` must point at `eirFinesseGadget_db`. If the password contains `$`, use single quotes around it in that file. +* ''include/sql_db.php'' must point at ''eirFinesseGadget_db''. If the password contains ''$'', use single quotes around it in that file. 
-- Fill in `$allowed_ips`, `$api_key` and `$CCE_CUBES`. Add the FreeRADIUS server and the Finesse server to `$allowed_ips`. +* Fill in ''$allowed_ips'', ''$api_key'' and ''$CCE_CUBES''. Add the FreeRADIUS server and the Finesse server to ''$allowed_ips''. 
-- Serve the script directly from Apache. Behind a local reverse proxy every request looks like `127.0.0.1` and would skip the api-key check. +* Serve the script directly from Apache. Behind a local reverse proxy every request looks like ''127.0.0.1'' and would skip the api-key check. 
-- Use HTTPS at Apache, because the api-key travels in a header. +* Use HTTPS at Apache, because the api-key travels in a header. 
-- Create the index in section 5. +* Create the index in section 5. 
-- Read the PHP error log for lines starting `|radiusVoiceCalls.php|`.+* Read the PHP error log for lines starting ''|radiusVoiceCalls.php|''.
  
-### Known behaviours+==== Known behaviours ====
  
-| Behaviour | Consequence | +^ Behaviour ^ Consequence ^ 
-| --- | --- | +| The row is only inserted by the first Interim-Update for an ''h323-conf-id''. Later ones are ignored. | Changes in later Interim-Updates (for example a different ''Called-Station-Id'') are never stored, and ''recordUpdates'' is not increased for them. |
-| The row is only inserted by the first Interim-Update for an `h323-conf-id`. Later ones are ignored. | Changes in later Interim-Updates (for example a different `Called-Station-Id`) are never stored, and `recordUpdates` is not increased for them. |+
 | A Stop with no row returns 404 and logs an ERROR. | Expected for calls that never connected, because no Interim-Update was sent. The ERROR lines can be noisy. | | A Stop with no row returns 404 and logs an ERROR. | Expected for calls that never connected, because no Interim-Update was sent. The ERROR lines can be noisy. |
 | A finesse-start that arrives before the Interim-Update returns 404. | There is no retry. The Finesse side must send after the call has been reported. | | A finesse-start that arrives before the Interim-Update returns 404. | There is no retry. The Finesse side must send after the call has been reported. |
-| The `not_set` lookup compares the stored connect time with the web server's clock, with a lower limit only. | A large clock difference between the gateway and the web server can push a call outside the 5-second window. | +| The ''not_set'' lookup compares the stored connect time with the web server's clock, with a lower limit only. | A large clock difference between the gateway and the web server can push a call outside the 5-second window. | 
-| The phone-number lookup is an exact text match. | It relies on `customerPhoneNumber` and the Finesse `phoneNumber` both being E.164. |+| The phone-number lookup is an exact text match. | It relies on ''customerPhoneNumber'' and the Finesse ''phoneNumber'' both being E.164. |
 | finesse-start overwrites its columns with whatever it is sent. | A later update replaces earlier agent details, and a missing field becomes NULL. | | finesse-start overwrites its columns with whatever it is sent. | A later update replaces earlier agent details, and a missing field becomes NULL. |
-| `contactCentreAgentConnected` is a bit. | It means "a Finesse update has been received". `agentUpdates` is the counter. | +| ''contactCentreAgentConnected'' is a bit. | It means "a Finesse update has been received". ''agentUpdates'' is the counter. | 
-| Times are stored as Irish local time with no zone. | At the autumn clock change, times in the repeated hour are ambiguous. Durations fall back to `Acct-Session-Time` when needed. | +| Times are stored as Irish local time with no zone. | At the autumn clock change, times in the repeated hour are ambiguous. Durations fall back to ''Acct-Session-Time'' when needed. | 
-| If a Stop is lost, `callActive` stays 1. | The reading system sees a call that never ends, and the phone-number lookup can match more than one row. |+| If a Stop is lost, ''callActive'' stays 1. | The reading system sees a call that never ends, and the phone-number lookup can match more than one row. | 
 + 
 +**Recommended housekeeping (not built).** A scheduled SQL job that sets ''callActive = 0'' on rows older than a chosen age, for example 12 hours, would clear stuck calls.
  
-**Recommended housekeeping (not built).** A scheduled SQL job that sets `callActive = 0` on rows older than a chosen age, for example 12 hours, would clear stuck calls.+===== Troubleshooting ===== 
 +==== SQL Queries ==== 
 +=== Find Active Calls older than 3 hours ==== 
 +<code sql> 
 +SELECT * FROM radiusVoiceCalls 
 +WHERE h323_connect_time < DATEADD(HOUR,-3,GETDATE()) 
 +AND callActive = 1 
 +order by id 
 +</code>
  
-### Test status+=== Update invalid Active Calls  ==== 
 +=== STEP 1 - Update stale calls (more than 3 hours active) as non active with a disconnect cause of 128=== 
 +Why disconnect cause of 128? 
 +  * Valid Disconnect causes are from 1 - 127, so 128 will never be sent by Radius - so we KNOW any record that has this connect cause was updated by this SQL query (which will run as a SQL job ever ~10 mins)  
 +  * We can find these calls later - by checking how many rows have a disconnect cause of 128 
 +<code sql> 
 +UPDATE radiusVoiceCalls 
 +SET  
 +callActive = 0, 
 +h323_disconnect_cause = 128, 
 +h323_disconnect_time = GetDate()
  
-The script passes a PHP syntax check. The conversion functions, the Finesse row lookup and the request handling were exercised with a stand-in database that returned scripted rows and recorded each statement. It has not yet been run against a real SQL Server, so the first live Interim-Update, Stop and finesse-start should be checked against the table and the log.+WHERE h323_connect_time < DATEADD(HOUR,-3,GETDATE()) 
 +AND callActive = 1 
 +</code>
  
 +=== STEP 2 - Update the calls as non active with a disconnect cause of 128 ===
 +We can find these calls later - by checking how many rows have a disconnect cause of 128
 +<code sql>
 +UPDATE radiusVoiceCalls
 +SET 
 +callDuration = DATEDIFF(SECOND, h323_connect_time, h323_disconnect_time),
 +Acct_Session_Time = DATEDIFF(SECOND, h323_connect_time, h323_disconnect_time)
 +WHERE 
 +h323_disconnect_cause = 128
 +AND callDuration is Null
 +</code>
  • vendors/cisco/routers/radius/radiusvoicecalls.1793134070.txt.gz
  • Last modified: 2026/10/27 20:47
  • by gerardorourke