Reliable Method for Identifying Active SIP Calls in Homer 11

[thinkaboutcbr]

Hi everyone,

I'm trying to determine how many active calls are currently running on my system using Homer 11.

My environment consists of:

5 PBXs and 1 SBC

All of them are sending SIP/HEP data to Homer.

Since Homer is primarily a SIP capture platform and not a real-time call state engine, I'm trying to infer active calls from the captured SIP signaling. My idea is to identify dialogs that have recent activity and have not yet received a BYE.

Copilot suggested the following query:

WITH recent_calls AS (
SELECT
cid,
MAX(timestamp) AS last_seen
FROM hep_proto_1_call
WHERE timestamp >= NOW() - INTERVAL '5 minutes'
GROUP BY cid
)
SELECT
c.cid,
c.caller,
c.callee,
MAX(c.timestamp) AS last_seen
FROM hep_proto_1_call c
JOIN recent_calls r ON c.cid = r.cid
GROUP BY c.cid, c.caller, c.callee
HAVING SUM(CASE WHEN c.method = 'BYE' THEN 1 ELSE 0 END) = 0
ORDER BY last_seen DESC

My concern is whether this is actually a reliable way to identify active calls, since:

Calls older than 5 minutes but still active might be missed.
Failed dialogs without a BYE could be counted as active.
Re-INVITEs and other SIP transactions may affect the results.

Has anyone implemented a more accurate query in Homer 11 to estimate active calls?

How can i extract the data to a output file / prometheus ?

I am able to extract the number of active calls from the SBC using Zabbix, but I cannot do the same for the PBXs because they are extremely old.

Any suggestions or examples would be greatly appreciated.

[adubovikov]

You're right that Homer 11 is a SIP capture / analytics lake, not a real-time dialog state machine — there is no built-in “active calls” counter.

On the proposed query

“Recent activity + no BYE” is a reasonable approximation, but not reliable as a concurrent gauge:

1. Long quiet calls drop out of a short activity window.
2. Failed / cancelled dialogs often have no BYE (CANCEL, 4xx/5xx) → false “active”.
3. Prefer answered + not terminated over “no BYE only”.
4. Use session_id (Call-ID) as the dialog key; cid is for B2B correlation and can merge multiple legs.
5. Termination: method IN ('BYE','CANCEL') OR cseq_method IN ('BYE','CANCEL').
6. Always constrain date / timestamp (DuckLake partitions).

Example estimate (tune windows for your network):

WITH recent AS (
  SELECT
    session_id,
    bool_or(response_code = '200' AND cseq_method = 'INVITE') AS answered,
    bool_or(method IN ('BYE','CANCEL') OR cseq_method IN ('BYE','CANCEL')) AS terminated,
    max(timestamp) AS last_ts
  FROM hep_proto_1_call
  WHERE date >= current_date - 1
    AND timestamp >= now() - INTERVAL 6 HOUR
  GROUP BY session_id
)
SELECT count(*) AS approx_active
FROM recent
WHERE answered AND NOT terminated
  AND last_ts >= now() - INTERVAL 5 MINUTE;

For B2B, group by correlated cid instead of session_id (and accept some ambiguity).

Prometheus / Zabbix / file

Built-in :9090/metrics exposes ingest / method-response rates (homer_sip_method_response_total, etc.), not concurrent dialogs.

Export options:

• Grafana / Prometheus-style panels: FlightSQL (docs) or POST /api/v4/statistics/query with rawquery
• Zabbix: HTTP agent / script calling that API on a schedule
• File: homer cli --query '…' via cron

For an authoritative concurrent number, keep using the SBC dlg counter; use Homer for an estimate across the old PBXs and for call drill-down (POST /api/v4/transactions/callinfo is per Call-ID, not fleet-wide).