Differences

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

--- vendors:cisco:routers:radius:radiusvoicecalls [2026/10/27 21:04] – [Test status] gerardorourke
+++ vendors:cisco:routers:radius:radiusvoicecalls [2026/11/12 15:33] (current) – [SQL Queries] gerardorourke
@@ Line 376: / Line 376: @@
 **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>
+=== 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()
+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>