How to use function odbc in the dialplan?

[lordaker]

Hello @everyone,

I want to register all my calls inbound and outbound in a database. I have install mysql-server and MySQL Connector ODBC and I also configured it. I made a database “callsusersdb” in this db ,I have create two tables: callshistory and users. In the table callshistory, I want to register the information about inbount and outbound calls such as date and hour of call, extension used by the user, CID/DID used by the user for make the call, number that he calls and the type of call (inbound/outbound)

Exemple of my table:
callsHistory (Table)
iKey (Primary Key)
Date
NumberExtPhone (Number Extension Phone SIP)
NumberDID (CID/DID)
numberCaller (Number that we have call)
Type (Inbound/outbound)

But I’m looking for a way to do that in my dialplan with odbc.

Best regards,
Lordaker

[ambiorixg12]

CDR Save most of the infomation you want take a look to https://wiki.asterisk.org/wiki/display/AST/Getting+Asterisk+Connected+to+MySQL+via+ODBC

[lordaker]

@ambiorixg12, Thank you for your reply. But I already made that… Read above

I want to register all my calls inbound and outbound in a database. I have install mysql-server and …

I’m looking for the syntax odbc for register some data in a database MySQL…

[ambiorixg12]

After you have configured the ODBC connection use the CDR variables to insert the data on your DB after the call has end

https://wiki.asterisk.org/wiki/display/AST/CDR+Variables

[lordaker]

I see that @ambiorixg12 :
http://forums.asterisk.org/viewtopic.php?f=1&t=96604#wrap
What do you thing about it, It’s near what I want to do…I think

[ambiorixg12]

Both are related, but did you get ODBC connection working ?

[lordaker]

@ambiorixg12

but did you get ODBC connection working ?

Yes, of course…

Connected to Asterisk 13.21.0 currently running on asterisk (pid = 1329)
asterisk*CLI> odbc show all

ODBC DSN Settings
-----------------

  Name:   asterisk
  DSN:    asterisk-connector
    Last connection attempt: 1970-01-01 00:00:00
    Number of active connections: 1 (out of 1)

  Name:   calls-tracking
  DSN:    calls-tracking-connector
    Last connection attempt: 1970-01-01 00:00:00
    Number of active connections: 1 (out of 1)

asterisk*CLI>

Connection to dsn calls-tracking-connector

root@asterisk:/etc/asterisk# isql -v calls-tracking-connector
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

[lordaker]

Hi @everyone,

This is my dialplan for outgoing calls (extensions.conf)

exten => _0X.,1,NoOp(Outgoing Call from ${CALLERID(all)} to ${EXTEN})
 same => n,Set(POSTE=${CHANNEL:6:3})
 same => n,NoOp(SendedCID = ${CALLERID(num)})
 same => n,Set(CALLERID(num)=${DB(CID/${POSTE})})
 same => n,NoOp(SendedCID = ${CALLERID(num)})
 same => n,Set(NOW=${STRFTIME(${EPOCH},,%Y_%m_%d_%H_%M_%S)})
 same => n,System(echo "--appel_sortant --- callerid : ${CALLERID(num)} ---- ${STRFTIME(${EPOCH},,%Y_%m_%d_%H_%M_%S)} ----" >> /var/spool/asterisk/log/debug.txt)
 same => n,Set(REC_FILE_NAME=OUT_${NOW}_${EXTEN}_${POSTE}.wav)
 same => n,Set(TYPE='Out')
 same => n,Set(CALLSHISTORY()=${NOW},${POSTE},${CALLERID(num)},${EXTEN},${TYPE})
 same => n,Answer()
...

But I have this answer in the CLI:

 == Setting global variable 'SIPDOMAIN' to '192.168.40.55'
    -- Executing [042680871@from-internal:1] NoOp("PJSIP/103-00000005", "Outgoing Call from "Arnold" <103> to 042680871") in new stack
    -- Executing [042680871@from-internal:2] Set("PJSIP/103-00000005", "POSTE=103") in new stack
    -- Executing [042680871@from-internal:3] NoOp("PJSIP/103-00000005", "SendedCID = 103") in new stack
    -- Executing [042680871@from-internal:4] Set("PJSIP/103-00000005", "CALLERID(num)=028992018") in new stack
    -- Executing [042680871@from-internal:5] NoOp("PJSIP/103-00000005", "SendedCID = 028992018") in new stack
    -- Executing [042680871@from-internal:6] Set("PJSIP/103-00000005", "NOW=2018_05_28_11_38_00") in new stack
    -- Executing [042680871@from-internal:7] System("PJSIP/103-00000005", "echo "--appel_sortant --- callerid : 028992018 ---- 2018_05_28_11_38_00 ----" >> /var/spool/asterisk/log/debug.txt") in new stack
    -- Executing [042680871@from-internal:8] Set("PJSIP/103-00000005", "REC_FILE_NAME=OUT_2018_05_28_11_38_00_042680871_103.wav") in new stack
    -- Executing [042680871@from-internal:9] Set("PJSIP/103-00000005", "TYPE='Out'") in new stack
    -- Executing [042680871@from-internal:10] Set("PJSIP/103-00000005", "CALLSHISTORY()=2018_05_28_11_38_00,103,028992018,042680871,'Out'") in new stack
[May 28 11:38:00] ERROR[3953][C-00000003]: pbx_functions.c:699 ast_func_write: Function CALLSHISTORY not registered

The query in the func_odbc.conf file

[CALLSHISTORY]
dsn=calls-tracking-connector
writesql=INSERT INTO callshistory(date,numeroPoste,numeroDID,destinataire,sens) VALUES('${ARG1}','${ARG2}','${ARG3}','${ARG4}','${ARG5}')

[lordaker]

@lordaker

[May 28 11:38:00] ERROR[3953][C-00000003]: pbx_functions.c:699 ast_func_write: Function CALLSHISTORY not registered

Somebody have an idea of what’s happen here ?

[jcolp]

You posted this 2 hours ago. It’s polite to wait a period of time before immediately your own post eliciting a response. It’s a holiday in some places, for example, and people are free to respond when they want/if they want.

[lordaker]

@jcolp

You posted this 2 hours ago. It’s polite to wait a period of time before immediately your own post eliciting a response. It’s a holiday in some places, for example, and people are free to respond when they want/if they want

Ah ok, alright . It’s not a problem, I will waiting a moment. Thank you

[johnkiniston]

@lordaker

– Executing [042680871@from-internal:10] Set(“PJSIP/103-00000005”, “CALLSHISTORY()=2018_05_28_11_38_00,103,028992018,042680871,‘Out’”) in new stack [May 28 11:38:00] ERROR[3953][C-00000003]: pbx_functions.c:699 ast_func_write: Function CALLSHISTOR

try ODBC_CALLSHISTORY()

You didn’t specify a prefix in your func_odbc.conf so by default the prefix ODBC gets prepended.

[lordaker]

This is the content of the modules.conf file

[modules]
autoload=yes

;========================= FUNCTION Modules
load => func_odbc.so

;========================== PBX Modules
noload => pbx_gtkconsole.so

;=========================== RESSOURCES Modules
load => res_musiconhold.so
load => res_odbc_transaction.so
load => res_odbc.so

;=========================== CHANNELS Modules
noload => chan_alsa.so
noload => chan_console.so
noload => chan_sip.so
load => chan_pjsip.so

I add this in func_odbc.conf file

[CALLSHISTORY]
prefix=ODBC
dsn=calls-tracking-connector
writesql=INSERT INTO callshistory (date,numeroPoste,numeroDID,destinataire,sens) VALUES('${ARG1}','${ARG2}','${ARG3}','${ARG4}','${ARG5}')

In the extensions.conf

[outgoing-calls]
;========== outgoing Calls
exten => _0X.,1,NoOp(Outgoing Call from ${CALLERID(all)} to ${EXTEN})
 same => n,Set(POSTE=${CHANNEL:6:3})
 same => n,NoOp(SendedCID = ${CALLERID(num)})
 same => n,Set(CALLERID(num)=${DB(CID/${POSTE})})
 same => n,NoOp(SendedCID = ${CALLERID(num)})
 same => n,Set(NOW=${STRFTIME(${EPOCH},,%Y_%m_%d_%H_%M_%S)})
 same => n,System(echo "--appel_sortant --- callerid : ${CALLERID(num)} ---- ${STRFTIME(${EPOCH},,%Y_%m_%d_%H_%M_%S)} ----" >> /var/spool/asterisk/log/debug.txt)
 same => n,Set(REC_FILE_NAME=OUT_${NOW}_${EXTEN}_${POSTE}.wav)
 same => n,Set(TYPE=OUT)
 same => n,Set(ODBC_CALLSHISTORY()=${NOW},${POSTE},${CALLERID(num)},${EXTEN},${TYPE})
...

In CLI Asterisk, I have any error message:

Setting global variable 'SIPDOMAIN' to '192.168.40.55'
    -- Executing [00221705237773@from-internal:1] NoOp("PJSIP/103-0000003a", "Appel International from "Arnold" <103> to 00221705237773") in new stack
    -- Executing [00221705237773@from-internal:2] Set("PJSIP/103-0000003a", "POSTE=103") in new stack
    -- Executing [00221705237773@from-internal:3] NoOp("PJSIP/103-0000003a", "SendedCID = 103") in new stack
    -- Executing [00221705237773@from-internal:4] Set("PJSIP/103-0000003a", "CALLERID(num)=028992018") in new stack
    -- Executing [00221705237773@from-internal:5] NoOp("PJSIP/103-0000003a", "SendedCID = 028992018") in new stack
    -- Executing [00221705237773@from-internal:6] Set("PJSIP/103-0000003a", "NOW=2018-05-29 15:15:07") in new stack
    -- Executing [00221705237773@from-internal:7] System("PJSIP/103-0000003a", "echo "--appel_sortant INTERNATIONAL --- callerid : 028992018 ---- 2018-05-29 15:15:07 ----" >> /var/spool/asterisk/log/debug.txt") in new stack
    -- Executing [00221705237773@from-internal:8] Set("PJSIP/103-0000003a", "REC_FILE_NAME=OUT_2018-05-29 15:15:07_00221705237773_103.wav") in new stack
    -- Executing [00221705237773@from-internal:9] Set("PJSIP/103-0000003a", "TYPE=OUT") in new stack
    -- Executing [00221705237773@from-internal:10] Set("PJSIP/103-0000003a", "ODBC_CALLSHISTORY()=2018-05-29 15:15:07,103,028992018,00221705237773,OUT)") in new stack
    -- Executing [00221705237773@from-internal:11] Answer("PJSIP/103-0000003a", "") in new stack
    -- Executing [00221705237773@from-internal:12] NoOp("PJSIP/103-0000003a", "n° Poste = 103") in new stack
    -- Executing [00221705237773@from-internal:13] GotoIf("PJSIP/103-0000003a", "0?CallAllowed") in new stack
    -- Executing [00221705237773@from-internal:14] GotoIf("PJSIP/103-0000003a", "0?CallAllowed") in new stack
    -- Executing [00221705237773@from-internal:15] GotoIf("PJSIP/103-0000003a", "1?CallAllowed") in new stack
    -- Goto (from-internal,00221705237773,21)
    -- Executing [00221705237773@from-internal:21] NoOp("PJSIP/103-0000003a", "n° Poste = 103 -> Not Recorded") in new stack
    -- Executing [00221705237773@from-internal:22] Set("PJSIP/103-0000003a", "PROV2USE=BelgiumVoIP") in new stack
    -- Executing [00221705237773@from-internal:23] NoOp("PJSIP/103-0000003a", "Provider to use : BelgiumVoIP") in new stack
    -- Executing [00221705237773@from-internal:24] GotoIf("PJSIP/103-0000003a", "0?widevoip") in new stack
    -- Executing [00221705237773@from-internal:25] GotoIf("PJSIP/103-0000003a", "0?selfone") in new stack
    -- Executing [00221705237773@from-internal:26] GotoIf("PJSIP/103-0000003a", "1?BelgiumVoIP") in new stack
    -- Goto (from-internal,00221705237773,31)
    -- Executing [00221705237773@from-internal:31] Set("PJSIP/103-0000003a", "NUM2DIAL=00221705237773") in new stack
    -- Executing [00221705237773@from-internal:32] System("PJSIP/103-0000003a", "echo "--BelgiumVoIP  --- callerid : 028992018 ---- 2018/05/29 15:15:07 ----" >> /var/spool/asterisk/log/debug.txt") in new stack
    -- Executing [00221705237773@from-internal:33] NoOp("PJSIP/103-0000003a", "CD BelgiumVoIP") in new stack
    -- Executing [00221705237773@from-internal:34] Dial("PJSIP/103-0000003a", "PJSIP/00221705237773@belgium-voip,60") in new stack
    -- Called PJSIP/00221705237773@belgium-voip
    -- PJSIP/belgium-voip-0000003b is ringing
  == Spawn extension (from-internal, 00221705237773, 34) exited non-zero on 'PJSIP/103-0000003a'
asterisk*CLI>

But I have nothing in Database. Syntax Error in the func_odbc.conf file or in the dialplan ?

[johnkiniston]

I recommend you increase console verbosity and debug levels and see what happens when your ODBC function writes to the database.

You may need to enable the FULL log in logger.conf and send DEBUG events to it.

You can also enable logging in odbc.ini but that’s outside the scope of this forum.

[lordaker]

Hi @everyone
I comeback to my project today after a longtime. So I want to try with the solution of @ambiorixg12
But I don’t know how to put the differents varaibles that I need in each fields of my table historickcalls.
In my database db_callsusers, I have the table historikcalls

Details of my historikcalls table
Table HistorikCalls
iKey (Primary ID)
date (datetime) --> Date and time when I call or when I receive a call
numberExt (varchar 20) --> Number of extension
numberCID (varchar 20) --> CID from caller
receiver (varchar 20) --> Number I called
type (varchar 5) --> What’s kind of call ? (Inbound or Outbound)

[lordaker]

Hi @everyone,

I try to store the calls I made in a database. I have the same problem like @guru_dev
How you can see by the url below:

Problem in storing cdr in mysql database using ODBC

I am using asterisk 11.18 and following are my configuration. odbcinst.ini [MySQL] Description = ODBC for MySQL Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so FileUsage = 1 Verified that the system is able to see the driver. $ odbcinst -q -d [MySQL] configuration in odbc.ini [asterisk-connector] Description = MySQL connection to 'voiceinn-ncell' database Driver = MySQL Database = mydatabese Server = localhost UserName = root…

When I make a call I have the same message error:
ERROR[4711]: cdr_odbc.c:176 odbc_log: Unable to retrieve database handle. CDR failed.

Can you help me to find a way to fix that ?

[lordaker]

This is what I have in the CLI :

[root@localhost asterisk]# asterisk -rvvv
Asterisk 13.21.1, Copyright (C) 1999 - 2014, Digium, Inc. and others.
Created by Mark Spencer <markster@digium.com>
Asterisk comes with ABSOLUTELY NO WARRANTY; type 'core show warranty' for details.
This is free software, with components licensed under the GNU General Public
License version 2 and other licenses; you are welcome to redistribute it under
certain conditions. Type 'core show license' for details.
=========================================================================
Connected to Asterisk 13.21.1 currently running on localhost (pid = 6137)
localhost*CLI> module reload res_odbc.so
Module 'res_odbc.so' reloaded successfully.
    -- Reloading module 'res_odbc.so' (ODBC resource)
[Jul 11 20:00:04] NOTICE[6389]: res_odbc.c:616 load_odbc_config: Registered ODBC class 'asterisk' dsn->[asterisk-connector]
localhost*CLI> module reload cdr_odbc.so
Module 'cdr_odbc.so' reloaded successfully.
    -- Reloading module 'cdr_odbc.so' (ODBC CDR Backend)
localhost*CLI> module reload cdr_adaptive_odbc.so
Module 'cdr_adaptive_odbc.so' reloaded successfully.
    -- Reloading module 'cdr_adaptive_odbc.so' (Adaptive ODBC CDR backend)
    -- Found adaptive CDR table cdr@asterisk.
localhost*CLI>

[ambiorixg12]

The output looks fine

[lordaker]

Yes, but I don’t know what happen ?

  == Setting global variable 'SIPDOMAIN' to '192.168.40.55'
    -- Executing [101@from-internal:1] NoOp("PJSIP/103-00000006", "## Internal Call to the 101 ##") in new stack
    -- Executing [101@from-internal:2] Verbose("PJSIP/103-00000006", "Call start time: 2018-07-13 14:31:25") in new stack
Call start time: 2018-07-13 14:31:25
    -- Executing [101@from-internal:3] Set("PJSIP/103-00000006", "CDR(useragent)=Arnold") in new stack
    -- Executing [101@from-internal:4] Set("PJSIP/103-00000006", "NOW=20180713_143125") in new stack
    -- Executing [101@from-internal:5] Set("PJSIP/103-00000006", "REC_FILE_NAME=OUT_20180713_143125_101_.wav") in new stack
    -- Executing [101@from-internal:6] MixMonitor("PJSIP/103-00000006", "OUT_20180713_143125_101_.wav,b V(1)") in new stack
    -- Executing [101@from-internal:7] Dial("PJSIP/103-00000006", "PJSIP/101,20,r") in new stack
    -- Called PJSIP/101
  == Begin MixMonitor Recording PJSIP/103-00000006
    -- PJSIP/101-00000007 is ringing
localhost*CLI> core show channels
Channel              Location             State   Application(Data)
PJSIP/101-00000007   101@from-internal:1  Ringing AppDial((Outgoing Line))
PJSIP/103-00000006   101@from-internal:7  Ring    Dial(PJSIP/101,20,r)
2 active channels
1 active call
4 calls processed
localhost*CLI> pjsip show channels

  Channel:  <ChannelId........................................>  <State.....>  <Time.....>
      Exten: <DialedExten.............>  CLCID: <ConnectedLineCID.......>
==========================================================================================

  Channel: PJSIP/101-00000007/AppDial                            Ringing       00:00:07
      Exten: 101                         CLCID: "Arnold" <103>

  Channel: PJSIP/103-00000006/Dial                               Ring          00:00:07
      Exten: 101                         CLCID: "Standard" <101>


Objects found: 2

  == Spawn extension (from-internal, 101, 7) exited non-zero on 'PJSIP/103-00000006'
  == MixMonitor close filestream (mixed)
  == End MixMonitor Recording PJSIP/103-00000006
[Jul 13 14:31:35] ERROR[1419]: cdr_odbc.c:176 odbc_log: Unable to retrieve database handle.  CDR failed.

[lordaker]

So, I recreate the table cdr in my database with this SQL request:

https://wiki.asterisk.org/wiki/display/AST/MySQL+CDR+Backend

And now my log call is registered in the table cdr. But I have always the error message in the CLI.

Can you explain me, why I have this error mesage ?

== Setting global variable 'SIPDOMAIN' to '192.168.40.55'
    -- Executing [102@from-internal:1] NoOp("PJSIP/103-00000015", "## Internal Call to the 102 ##") in new stack
    -- Executing [102@from-internal:2] Verbose("PJSIP/103-00000015", "Call start time: 2018-07-13 17:12:38") in new stack
Call start time: 2018-07-13 17:12:38
    -- Executing [102@from-internal:3] Set("PJSIP/103-00000015", "CDR(useragent)=Arnold") in new stack
    -- Executing [102@from-internal:4] Set("PJSIP/103-00000015", "NOW=20180713_171238") in new stack
    -- Executing [102@from-internal:5] Set("PJSIP/103-00000015", "REC_FILE_NAME=OUT_20180713_171238_102_.wav") in new stack
    -- Executing [102@from-internal:6] MixMonitor("PJSIP/103-00000015", "OUT_20180713_171238_102_.wav,b V(1)") in new stack
    -- Executing [102@from-internal:7] Dial("PJSIP/103-00000015", "PJSIP/102,20,r") in new stack
    -- Called PJSIP/102
  == Begin MixMonitor Recording PJSIP/103-00000015
    -- PJSIP/102-00000016 is ringing
    -- PJSIP/102-00000016 answered PJSIP/103-00000015
    -- Channel PJSIP/102-00000016 joined 'simple_bridge' basic-bridge <d146deb3-7d2a-425f-9740-babd4c9a44c0>
    -- Channel PJSIP/103-00000015 joined 'simple_bridge' basic-bridge <d146deb3-7d2a-425f-9740-babd4c9a44c0>
    -- Channel PJSIP/102-00000016 left 'simple_bridge' basic-bridge <d146deb3-7d2a-425f-9740-babd4c9a44c0>
    -- Channel PJSIP/103-00000015 left 'simple_bridge' basic-bridge <d146deb3-7d2a-425f-9740-babd4c9a44c0>
  == Spawn extension (from-internal, 102, 7) exited non-zero on 'PJSIP/103-00000015'
  == MixMonitor close filestream (mixed)
  == End MixMonitor Recording PJSIP/103-00000015
[Jul 13 17:12:48] ERROR[3558]: cdr_odbc.c:176 odbc_log: Unable to retrieve database handle.  CDR failed.

HeidiSQL 9.5.0.5280

Now, how can I take some informations like datetime, clid, src, dst and send this information in a specific table ? Is it in the dialplan ? How can I do that ? Where can put my request that I want to send into this specific table ?