Hi,
I’ve created an PJSIP realtime environment and now I want to implement endpoint status into the MySQL database. I used ODBC to connect Asterisk to a MySQL database. Is their any way of doing that?
Thank you in advance
What do you mean by endpoint status? The result of the qualify option on an AOR - that is whether they are reachable or not?
Yes correctly. I want the availability to be saved in the realtime database, so I can acces it with an 3rd party application, that displays if the endpoint is available or not
kind regards
Edit: I allready found out, that I can use the “qualify_frequency” option, but how can I get the output from it into the database?
This functionality does not currently exist, sorry. You would need to monitor events (using AMI or ARI) and store the information yourself, or poll Asterisk through AMI or the CLI.
Hi,
that sounds quite complicated to do this. Especially if I want to pull the exact Value from the whole string
Without additional code modifications in Asterisk what you precisely want just doesn’t exist.
Maybe a Php script that looks for the 3 attributes (available, unavailable, unreachable)? And then implement that into the mysql database?
I would not say this is recommended and your mileage may vary, but I think this might achievable by defining a contact_status realtime mapping within your sorcery.conf and creating the appropriately defined table within your database. Define something like the following, or add it to a preexisting section, in your sorcery.conf:
[res_pjsip]
contact_status=realtime,ps_contact_status
Assuming you are using res_odbc and already have your database connection settings properly configured with “asterisk” as the databse add something similar to the following under “settings” in extconfig.conf:
[settings]
ps_contact_status => odbc,asterisk
Now you must create a table within your database with the following table definition (at a minimum):
create table ps_contact_status (
id varchar(200),
last_status integer,
status integer,
rtt_start numeric(16,6),
rtt integer
);
The above is a table definition for PostgreSQL. I’ll leave it to you to translate it to MySQL, but it’s important to note that the rtt_start field must always have at least 6 numbers to the right of the decimal. Even if they are all zeros. If MySQL sets that field to just “0” (zero) when the number is something like “00.00” then it won’t work. This is because Asterisk is looking to parse that field as a number with six digits after the decimal.
last_status is a numeric field that maps to the last known status of the endpoint. It should map to the following:
0 = "Unavail"
1 = "Avail"
2 = "Unknown"
3 = "Created"
4 = "Removed"
If this does work out for you I’d say be careful in its use. That table is suppose to be an in memory only table, so one thing to watch out for is stale values if Asterisk is not running (i.e. you may need to manually clear the table of its values). There are probably other things to that may keep this from fully working as you want, but might be worth a shot at trying it out.
2 Likes
Firstly thank you for your help.
But I have a question. How does Asterisk know where it gets the contact_status? My attempt would be, to use a shell script, which searches for the id and gets it’s status and then pass it to the MySQL database
Typically Asterisk stores the contact_status information in memory. One way to get at this data (mentioned by @jcolp) is by monitoring AMI events. Specifically you can watch for the ContactStatus event (raised when the state of a contact changes). The information you need can also be found in the ContactStatusDetail event as well.
So what you would need to do is write a program that listens on your AMI port for these events. Upon receiving one of those events parse out the information you need and then push that into the database.
Your other option is what I laid out before in the other post. That will post the information directly to your database. However, as mentioned there could be problems doing it that way (stale date, etc…). It just depends on your use case and testing it out.
Here’s what I’ve done, and it seems to work pretty well…
Add a user to manager.conf
[devicestate]
secret = some_password
read = call
eventfilter=Event: DeviceStateChange
eventfilter=Event: DialBegin
eventfilter=Event: UnParkedCall
Here’s a PHP program to update a presence table as those events are sent. The include is just for the database connection.
#!/usr/bin/php -q
<?php
ini_set("default_socket_timeout", -1);
include('/opt/asterisk/includes.php');
$web = db_connect_web();
$socket = fsockopen("localhost","5038");
fputs($socket, "Action: Login\r\n");
fputs($socket, "Username: devicestate\r\n");
fputs($socket, "Secret: some_password\r\n\r\n");
$event = "";
while($ret = fgets($socket)){
if(substr($ret,0,6) == "Event:"){
$e = explode(':', $ret);
$event = trim($e[1]);
}
if($event == "DeviceStateChange"){
$data = explode(':', $ret);
if($data[0] == "Timestamp"){
$ts = trim($data[1]);
}
if($data[0] == "Device" && substr(trim($data[1]),0,3) == 'SIP'){
$d = explode('/', trim($data[1]));
$dev = trim($d[1]);
$device = "";
if(is_numeric($dev)){
$device = $dev;
}
}
if($data[0] == "State" && $device != ""){
$state = trim($data[1]);
if($state == "NOT_INUSE"){
//Clear CID fields and update presence state
$sql = "update asterisk.web_presence set state='$state',cidnum = NULL, cidname = NULL, inorout = NULL where ext='$device'";
mysql_query($sql);
}else{
//Update presence state
$sql = "update asterisk.web_presence set state='$state' where ext='$device'";
mysql_query($sql);
}
$event = "";
$device = "";
}
}
if($event == "DialBegin"){
$data = explode(':', $ret);
if($data[0] == "Timestamp"){
$ts = trim($data[1]);
}
if($data[0] == "Channel"){
$c = explode('/',trim($data[1]));
$c2 = explode('-', trim($c[1]));
$channel = trim($c2[0]);
}
if($data[0] == "CallerIDNum"){
$cidnum = trim($data[1]);
}
if($data[0] == "CallerIDName"){
$cidname = trim($data[1]);
}
if($data[0] == "DialString"){
if(substr(trim($data[1]),0,3) == 'SIP' || is_numeric(trim($data[1]))){
if(is_numeric(trim($data[1]))){
$exten = trim($data[1]);
}else{
$e = explode('/', trim($data[1]));
$exten = trim($e[1]);
}
//Update inbound presence call
$sql = "update asterisk.web_presence set cidnum = '$cidnum', cidname = '$cidname', inorout='I' where ext='$exten' and cidnum is null";
mysql_query($sql);
$sql = "update asterisk.web_presence set cidnum = '$exten', inorout='O' where ext='$channel' and cidnum is null";
mysql_query($sql);
}else{
$e = explode('@', trim($data[1]));
$dialed = trim($e[0]);
if($channel != 'gateway'){
//Update outbound presence call
$sql = "update asterisk.web_presence set cidnum = '$dialed', inorout='O' where ext='$channel'";
mysql_query($sql);
}
}
$event = "";
$exten = "";
}
}
if($event == "UnParkedCall"){
$data = explode(':', $ret);
if($data[0] == "Timestamp"){
$ts = trim($data[1]);
}
if($data[0] == "RetrieverChannel"){
$c = explode('/',trim($data[1]));
$c2 = explode('-', trim($c[1]));
$channel = trim($c2[0]);
}
if($data[0] == "ParkeeCallerIDNum"){
$cidnum = trim($data[1]);
}
if($data[0] == "ParkeeCallerIDName"){
$cidname = trim($data[1]);
}
if($data[0] == "ParkingSpace"){
$dialed = trim($data[1]);
$pickup = "$cidnum ($dialed)";
//Update outbound presence call
$sql = "update asterisk.web_presence set cidnum = '$pickup', cidname='$cidname', inorout='O', state='INUSE' where ext='$channel'";
mysql_query($sql);
$event = "";
$channel = "";
}
}
}
mysql_close($web);
sleep(5);
exit;
?>
And the table. If the inorout is blank, they’re checking voicemail.
CREATE TABLE IF NOT EXISTS `web_presence` (
`ext` varchar(32) NOT NULL,
`state` varchar(16) NOT NULL,
`cidnum` varchar(64) DEFAULT NULL,
`cidname` varchar(64) DEFAULT NULL,
`inorout` varchar(1) DEFAULT NULL,
PRIMARY KEY (`ext`),
KEY `state` (`state`)
) ENGINE=InnoDB
4 Likes
Forgot to mention that I run it as an upstart job.
description "Asterisk Presence Updater"
start on filesystem
stop on runlevel [!2]
post-stop exec sleep 5
respawn
exec /usr/bin/php /opt/asterisk/presence.php
1 Like
Hi this looks very cool.
But I read it to late. I’ve already written an C program which uses the “pjsip show aor” command to display the aor with it’s status and then writes it into the MySQL database.
1 Like