Register

Revision as of 00:11, 12 February 2013

This module is used to write CDR(Call Detail Record) data in the database.

Functionality

• Writes cdr data in the database
• Writes subscriptions in the database and make the initial notification

Note: You can setup the server params in the default section so you don't have to write them in every other section.
And if default server parameters are available can be overwritten by each section parameters.

Message handlers

• resource.subscribe Write subscriptions to the database and make the initial notification
• call.cdr Get subscriptions from database and notify on call state changes

Routing

None

Configuration

register.conf

• [general] Global module settings that are read only at first initialization
  • expires: int: Will execute the engine.timer query only every that many seconds. Defaults to 30
  • stoperror: regexp: Regular expression matching errors that will stop fallback. Defaults to 'busy'

The following parameters enable handling of individual messages. Each must be enabled manually in this config file:

• user.auth: bool: Activate handler on the "user.auth" message. Defaults to no
• user.register: bool: Activate handler on the "user.register" message. Defaults to no
• user.unregister: bool: Activate handler on the "user.unregister" message. Defaults to no
• engine.timer: bool: Activate handler on the "engine.timer" message. Defaults to no
• call.preroute: bool: Activate handler on the "call.preroute" message. Defaults to no
• call.route: bool: Activate handler on the "call.route" message. Defaults to no
• call.cdr: bool: Activate handler on the "call.cdr" message. Defaults to no
• linetracker: bool: Activate line status tracking on "call.cdr". Defaults to no
• fallback: bool: Activate fallback routing handlers on the "chan.disconnected", "call.answered" and "chan.hangup" messages. Defaults to no
• accounts: bool: Activate client registrations on "user.notify" and "engine.timer". Defaults to no

• [default] This section holds default settings for each of the following message handlers. All these settings can be overriden in individual handler sections
  • priority: int: Priority in Yate's handlers chain. Defaults to 50
  • account: string: Name of the database connection to use. No default

In each of the following sections you have to specify:

- initial query to execute when module is initialized

- query to execute for each received message

- result field to copy in message's textual return value (only for some, mandatory for some)

You can also override the settings from section [default]

Sample queries below are for PostgreSQL. Timestamps and intervals are unfortunately non-portable

• [user.auth] Query and result name for the user.auth message, the result and its presence in the result set is mandatory starting with Rev. 3008
  • query=SELECT password FROM users WHERE username='${username}'
  • result=password

• [user.register] Query for the user.register message
  • query=UPDATE users SET location='${data}',expires=CURRENT_TIMESTAMP + INTERVAL '${expires} s' WHERE username='${username}'

• [user.unregister] Query for the user.unregister message
  • query=UPDATE users SET location=NULL,expires=NULL WHERE expires IS NOT NULL AND username='${username}'

• [engine.timer] Query for the timer message that expires registrations. Also look at the expires setting in section [general]
  • query=UPDATE users SET location=NULL,expires=NULL WHERE expires IS NOT NULL AND expires<=CURRENT_TIMESTAMP

• [call.preroute] Query and result name for the prerouting message
  • query=SELECT location FROM users WHERE username='${called}'
  • result=location
  • priority=120

• [call.route] Query and result name for the routing message
  • offlineauto: boolean: Set the "offline" error for queries that return records but no route. Defaults to yes
  • query=SELECT location,(CASE WHEN location IS NULL THEN 'offline' ELSE NULL END) AS error FROM users WHERE username='${called}'
  • result=location
  • priority=120
  • The authenticated user can be found in the '${username}' parameter, however if the user redirects the call '${diverter}' contains it.

Notes: Here you can use SQL stored function to route your calls and modify their preferences such as CallerID, Called Number, SS7 attributes, and so on. Don't forget that you have to receive the set of records from SQL function in fully correspondence with structure of routing DB table (if you use it). For PostgreSQL the function should be created with the following parameters, for example: CREATE OR REPLACE FUNCTION "<scheme>"."<function name>"(IN text, IN text, IN text) RETURNS SETOF "<scheme>"."<the table with routing DB>" AS $BODY$DECLARE Than you have to call this function from register.conf like this: query=SELECT * FROM <scheme>.<function name>('${called}','${caller}','${address}')

You see that I pass Called Number, Caller Number and Address of Caller to my function, it enough in order to make following functions: different route incoming and outgoing calls (from SS7 PSTN and to SS7 PSTN), control which Caller ID uses SIP client and change it to allowed from predefined range, control of Called PSTN numbers in order deny some directions, manipulate of Caller number within calls from PSTN to SIP client in order to remove geographic code ('022'), limit users with some q-ty of simultaneously calls, route users to system messages like "User is temporary unavailable" (if registration is expired) and so on.

The function returns the following record (set of) in my scenario, for example to call from SIP client with number 801000 to PSTN number 561167 :

prefix: 5

location: sig/561167

descrip: Route to MoldTelecom-55

expires: 2031-01-24 13:00:01

trunk: link1

callednumtype: subscriber

callernumtype: national

screening: user-provided-passed

caller: 22801000

For regexroute.conf this route can be presented like this: ^5.....$=sig/\1;link=link1;callednumtype=subscriber;callernumtype=national;screening=user-provided-passed;caller=22${caller}

And the following for call from PSTN 561167 to SIP client 801201:

prefix: 801201

location: sip/sip:801201@111.111.111.111

descrip: Local SIP client

expires: 2031-01-24 13:00:01

trunk: [Null]

callednumtype: [Null]

callernumtype: [Null]

screening: [Null]

caller: 561167

Not all above columns are mandatory. 'Callednumtype', 'callernumtype' and 'screening' is necessary for SS7. 'Descrip' - only for our internal notes. Don't use 'id' column name even if your SQL allows it. Each attribute with it value should be in the separate column where the column name is attribute name. And you cannot use variables ('${caller}', '${called}', '\1') such as you do it regexroute.conf in the return from SQL, instead you need to return the final routing direction with all needed information.

• [call.cdr] Queries for the CDR updating message
  • critical: boolean: Reject all registrations and routing if query fails. Defaults to yes
  • initquery=UPDATE cdr SET ended=true WHERE ended IS NULL OR NOT ended
  • cdr_initialize=INSERT INTO cdr VALUES(TIMESTAMP 'EPOCH' + INTERVAL '${time} s','${chan}','${address}','${direction}','${billid}','${caller}','${called}',INTERVAL '${duration} s' ,INTERVAL '${billtime} s',INTERVAL '${ringtime} s','${status}','${reason}',false)
  • cdr_update=UPDATE cdr SET caller='${caller}',called='${called}',duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',ringtime=INTERVAL '${ringtime} s',status='${status}',reason='${reason}' WHERE chan='${chan}' AND billid='${billid}'
  • cdr_finalize=UPDATE cdr SET caller='${caller}', called='${called}', duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',ringtime=INTERVAL '${ringtime} s',status='${status}',reason='${reason}',ended=true WHERE chan='${chan}' AND billid='${billid}'

BE CAREFUL: The INSERT/UPDATE queries shown above are only examples. In real world you need to adjust your queries accordingly to handle random order calls of cdr_initialize/cdr_update/cdr_finalize, meaning: For each call yate at least runs one cdr_initialize and one cdr_finalize query. The cdr_update may not be called in case the call ends to fast, so it is: cdr_initialize, [cdr_update, cdr_update, ...] cdr_finalize. Moreover it is NOT guaranteed, that the queries are executed in a specific order (cdr_finalize MAY be called BEFORE cdr_initialize) and it is not guaranteed, that there is a specific time delay between execution of one of these queries, especially when using connection pooling (delay between firing cdr_initialize/cdr_update/cdr_finalize can be below 1ms).

So at best use a SQL procedure that is capable of handling this by using UPSERT (first try UPDATE, then if no record was updated INSERT). Also you can rely on columns billid and chan to be UNIQUE in combination (at best also add a specific yate server column, so that you know which cdr record was issued by which server; billid+chan+server will give you a unique identification criterion). Then use this procedure for cdr_initialize, cdr_update and cdr_finalize. As said, the procedure should be capable of doing UPSERT (to handle INSERTs and UPDATES appropriately) and you should use a UNIQUE constraint on billid+chan(+server) to prevent getting double CDRs for the same call leg. See this page for an example.

• [linetracker] Queries for the line usage tracker
  • critical: boolean: Reject all registrations and routing if query fails
  • critical=no
  • initquery=UPDATE users SET inuse=0
  • cdr_initialize=UPDATE users SET inuse=inuse+1 WHERE username='${external}'
  • cdr_finalize=UPDATE users SET inuse=(CASE WHEN inuse>0 THEN inuse-1 ELSE 0 END) WHERE username='${external}'

• [accounts] Queries for registering as client to other servers

• initquery Query executed only once to create client registrations at startup.

initquery=SELECT account,enabled,protocol,username,password,server FROM accounts WHERE enabled IS TRUE

• timerquery Poll on timer to update registrations if database was changed externally.

timerquery=BEGIN; SELECT account,enabled,protocol,username,password,server FROM accounts WHERE modified IS TRUE; UPDATE accounts SET modified=FALSE; COMMIT

• statusquery update account status after receiving an user.notify.

statusquery=UPDATE accounts SET status='${status}' WHERE account='${internalaccount}'

• [resource.subscribe] Queries used for event subscribe and event state changes notification
  • subscribe_subscribe Query executed on subscribe requests to check authorisation and refresh a subscription. subscribe_subscribe=SELECT * FROM subscriptions_subscribe('${notifier}', '${event}', '${subscriber}', '${data}', '${notifyto}', INTERVAL '${expires} s') as t(notifier text,data text,subscriber text,event text,notifyto text,notifyseq int8)
  • subscribe_unsubscribe Query executed on unsubscribe requests to check authorisation and remove a subscription. subscribe_unsubscribe=SELECT * FROM subscriptions_unsubscribe('${notifier}','${event}', '${subscriber}')
  • subscribe_notify Query executed when nedded to get all subscribers for a specified notifier's event. subscribe_notify=SELECT * FROM subscriptions_notify('${notifier}','${event}') as t(notifier text, data text,subscriber text,event text,notifyto text,notifyseq int8)
  • subscribe_expire Query used to expire subscriptions. subscribe_expire=SELECT * FROM subscriptions_expires() AS t(notifier text,data text,subscriber text,event text,notifyto text,notifyseq int8)

Example of database schema. Please note that the queries above were tested for postgresql. For mysql you would need to adapt them, especially the ones in [call.cdr] section.

See also

• CDR Build Module
• CDR File Module