Notification during call pickup in Asterisk

Not a big background

At work, from time to time it is necessary to intercept calls, which in principle does not matter. But it happens that it is necessary to call back to the caller.
I didn’t see the number accordingly, and I usually have to go to cdr in mysql or go to my partner’s phone and look for a phone number.
In principle, you can ask your partner to include call forwarding on the telephone, but it takes him a couple of minutes, and we are not looking for easy ways.

So, what we have: a PBX on Asterisk (version = 1.8.15.1), the function of call pickup and call notifications via jabber is implemented.

Information about setting up call interception, sending notifications using jabber is sufficient in the Internet, so I will immediately describe the implementation of receiving a notification when a call is intercepted.

Let's start with Mysql (version = Server version: 5.5.31-0 + wheezy1 (Debian)):

In the asteriskcdrdb database, we create 2 pickup_message and pickup_group tables.
In the pickup_message table, we will have only 2 columns: pg_id and message:
pg_id - id pickup groups in Asterisk;
message - the field where notifications will be recorded;
')

mysql> CREATE TABLE pickup_message -> (pg_id smallint unsigned, 

The SMALLINT and UNSIGNED parameters for the pg_id column are selected because only short integer numbers are planned. Although you can use names for interception groups.

  -> message varchar(128), -> CONSTRAINT pk_pg_id PRIMARY KEY (pg_id) 

In the pg_id column, we will have only unique records, and the column will serve for a bunch of 2 tables.

  -> ); Query OK, 0 rows affected (0.09 sec) 

The pickup_group table contains information on the belonging of the internal number (peer) to a specific interception group (pg_id).
Also for a bunch of 2 tables we add FOREIGN KEY () and REFERENCES.

 mysql> CREATE TABLE pickup_group -> (pg_id SMALLINT UNSIGNED, -> peer SMALLINT UNSIGNED, -> CONSTRAINT fk_pg_id FOREIGN KEY (pg_id) -> REFERENCES pickup_message (pg_id) -> ); Query OK, 0 rows affected (0.08 sec) 

Fill in the test for both tables with the following information interception group 1, internal numbers 309 and 373:

 mysql> insert into pickup_message values (1, NULL); Query OK, 1 row affected (0.05 sec) mysql> insert into pickup_group values (1, 309); Query OK, 1 row affected (0.06 sec) mysql> insert into pickup_group values (1, 373); Query OK, 1 row affected (0.04 sec) mysql> select * from pickup_message; +-------+---------+ | pg_id | message | +-------+---------+ | 1 | NULL | | 33 | NULL | +-------+---------+ 2 rows in set (0.00 sec) mysql> select * from pickup_group; +-------+------+ | pg_id | peer | +-------+------+ | 1 | 309 | | 1 | 373 | +-------+------+ 2 rows in set (0.00 sec) 

Go to the settings of Asterisk, edit the extensions.conf:

In the context where a set of internal numbers is processed:

 exten => _ZXX,1,Macro(jabb-personal-pickup,${EXTEN}) same => n,Dial(SIP/${EXTEN},120,t) same => n,Hangup 

Macro for sending notifications (one variable $ {ARG1} is passed to the macro, the number that was dialed):

 [macro-jabb-personal-pickup] exten => s,1,Set(text=   ${CALLERID(number)}) same => n,MYSQL(Connect connid 127.0.0.1 root PASSWORD asteriskcdrdb) same => n,MYSQL(Query resultid ${connid} select pickup_message.pg_id from pickup_message inner join pickup_group where peer = '${ARG1}';) ; #           same => n,MYSQL(Fetch fetchid ${resultid} pg_id) same => n,GotoIf($["${fetchid}"="1"]?yes:no) same => n(yes),MYSQL(Query resultid ${connid} update pickup_message set message ='${text}' where pg_id = '${pg_id}') ; #    ,      message  text same => n(no),MYSQL(Clear ${resultid}) same => n,MYSQL(Query resultid ${connid} select jabb_id from jabber where number=${ARG1}) same => n,MYSQL(Fetch fetchid ${resultid} jabb_id) same => n,GotoIf($["${fetchid}"="1"]?ok:bad) same => n(ok),JabberSend(asterisk,${jabb_id},${text}) same => n(bad),MYSQL(Clear ${resultid}) same => n,MYSQL(Disconnect ${connid}) 

And the actual context when intercepting a call (* 8). Here we check which group we belong to and whether there are any messages in our group.
Those. when an incoming call is received to an internal number, information about the caller is recorded in the group cell, and by interception we take this information.
In principle, everything is simple:

 exten => *8,1,NoOp(pickup) same => n,MYSQL(Connect connid 127.0.0.1 root PASSWORD asteriskcdrdb) same => n,MYSQL(Query resultid ${connid} select pickup_message.message from pickup_message inner join pickup_group where peer = ${CALLERID(number)}) ; #         same => n,MYSQL(Fetch fetchid ${resultid} message) same => n,GotoIf($["${fetchid}"="1"]?message:no) ; #      same => n(message),Set(text= . ${message}) ;#   text       same => n,MYSQL(Clear ${resultid}) same => n,MYSQL(Query resultid ${connid} select jabb_id from jabber where number=${CALLERID(number)}) ;#  id jabber  peer'a, ..     same => n,MYSQL(Fetch fetchid ${resultid} jabb_id) same => n,GotoIf($["${fetchid}"="1"]?yes:no) ; #       same => n(yes),JabberSend(asterisk,${jabb_id},${text}) same => n(no),MYSQL(Clear ${resultid}) same => n,MYSQL(Query resultid ${connid} update pickup_message set message ='NULL' where message = '${message}') same => n,MYSQL(Disconnect ${connid}) same => n,PickUP() 

As a result, when a call is intercepted, messages like this come to jabber:

 [15:09:07] <Office Manager>  .  . 302 [15:12:08] <Office Manager>  .  . 226 [15:25:47] <Office Manager>  .  . 106 [15:32:40] <Office Manager>  . 116 

I hope for someone this information will be useful.