How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Steve Your Own Question
Steve
Steve, Consultant
Category: Programming
Satisfied Customers: 263
Experience:  Steve is a consultant in the areas of computer software and programming, information management and networking.
47680681
Type Your Programming Question Here...
Steve is online now
A new question is answered every 9 seconds

First realize that I am a SQL newbie. We have a phone system

Customer Question

First realize that I am a SQL newbie. We have a phone system based on Asterisk and we are attempting to pull CDR records to our CRM platform. We have limited access to the Asterisk CLI. We have been using PHPAdmin and exporting the records manually and importing them; however this is not preferred. Their tech support asked if we had tried accessing the database via ODBC on port 3306. I have tried to use SQL Management Studio to connect to the local IP address with the known credentials we use for PHPAdmin and it fails. Any suggestions?
Submitted: 11 months ago.
Category: Programming
Expert:  Rylan Sharpe replied 11 months ago.
Hello, I am Rylan and I am happy to help you with your question. If you are using phpMyAdmin, that means the data is most like MySQL. Can you confirm what kind of database it is?
Customer: replied 11 months ago.
Here is what I know ...
Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.1.73 - Source distribution
Protocol version: 10
User: [email protected]
Server charset: UTF-8 Unicode (utf8)Web serverApache/2.2.15 (CentOS)
Database client version: libmysql - 5.1.73
PHP extension: mysql DocumentationphpMyAdminVersion information: 4.0.4.1
Expert:  Rylan Sharpe replied 11 months ago.
OKay, great. If you wee trying to use SQL Management Studio, it will not work with MySQL. Please download MySQL Workbench. It is a great, free product that will get you connected.
Customer: replied 11 months ago.
I have downloaded the software; however it says I am unable to connect from my IP address. I do have access to PHPAdmin. I have no problem going with Secure Remote Assistance but I would want you to complete the process to where we have job that exports records from the database in near real time to a location on our ISeries can then be read by the CRM programs.
Expert:  Rylan Sharpe replied 11 months ago.
Workbench is not going to create an automated system that will pull the data and dump it somewhere. It will be a way of manually pulling data. Is your goal for a system to automatically query the MySQL database then post it to your server?
Customer: replied 11 months ago.
Yes that it the goal. The AS400 / ISeries has ODBC connectors. This database contains our Call Detail Records.
Expert:  Rylan Sharpe replied 11 months ago.
Okay. I got it. The credentials to connect over the 3306 are going to be different than for phpMyAdmin. So, most likely we will need to create a new user on the MySQL which the AS400 will use. Let me check how Asterisk is setup.
Expert:  Rylan Sharpe replied 11 months ago.
Please take a look at this page, which has a nice document. The SQL credentials are not the same as phpmyadmin, but this page shows the default values. When you can get Workbench to connect, then you will have the required information to get the AS400 to pull the data. Does this help: https://vps.powerpbx.org/clients/knowledgebase/34/How-to-query-the-Asterisk-MySQL-database.html
Customer: replied 11 months ago.
The problem with this is that our phone system is based in Asterisk but apparently they have encapsulated it somehow. I have very limited access to the CLI. See attached. It will not allow me to change directory or load the GUI.php. We have found countless articles on how have Asterisk write out CDR just never could implement them.
Expert:  Rylan Sharpe replied 11 months ago.
Got it. This is becoming more of a Asterisk issue, not an SQL one. So, I am going to release this question for others to help, if they understand Asterisk.
Expert:  Steve replied 11 months ago.
Hi, my name is ***** ***** I think I can help you with your request. But I'm a little confused by what you wrote and have a couple of questions/comments:1) What version of Asterisk?2) Can you type cdr show status from the CLI and paste the output here please? Then type odbc show all and paste the output here.3) What data do you want to export? The entire CDR? What are your query filters? Based on the last date/time you extracted CDR records?4) You say you can access the CDR records from the Asterisk database through PHPMyAdmin, right? And you've been successfully exporting the data that way? So the database can't really be encapsulated, because you can already get to it through PHPMyAdmin, am I correct?2) Asterisk won't be able to initiate the export on its own. That means you'll have to set up a job to run periodically which exports the CDRs to the file, and then have some automated way on your CRM to recognize that a new file is there that needs to be imported. In Linux, jobs can be scheduled with CRON, and you can choose how often you want to export the records (every second, minute, hour, day, etc).
Customer: replied 11 months ago.
Hey Steve. Thanks for reaching out to me. I suspect that you will be able to help us fairly quickly as you are using some of the lingo I ran into last time we try to automate our process.1) What version of Asterisk? Ver. 1.8.23.12) Can you type cdr show status from the CLI and paste the output here please?Call Detail Record (CDR) settings
----------------------------------
Logging: Enabled
Mode: Simple
Log unanswered calls: No* Registered Backends
-------------------
mysql
cdr-custom
Adaptive ODBC
csvThen type odbc show all and paste the output here.ODBC DSN Settings
-----------------Name: asteriskcdrdb
DSN: MySQL-asteriskcdrdb
Last connection attempt: 2016-04-15 13:52:37Name: meetme
DSN: MySQL-Asterisk
Last connection attempt: 2016-04-15 13:52:373) What data do you want to export? The CRM already looks to a network location to import the data. It has been programmed around receive the entire CDR string per call. Ideally we would get the process down to where a job was writing out the data to this file (using CRON?) in near real time.4) You say you can access the CDR records from the Asterisk database through PHPMyAdmin, right? Yes.And you've been successfully exporting the data that way? YesSo the database can't really be encapsulated, because you can already get to it through PHPMyAdmin, am I correct? Yes, I used the word encapsulated as I don't seem to have access to root functions that several of the articles on this issue refer to. (e.g. cd /var/www/html wget https://www.adminer.org/static/download/4.2.3/adminer-4.2.3.php)5) Asterisk won't be able to initiate the export on its own. That means you'll have to set up a job to run periodically which exports the CDRs to the file, and then have some automated way on your CRM to recognize that a new file is there that needs to be imported. In Linux, jobs can be scheduled with CRON, and you can choose how often you want to export the records (every second, minute, hour, day, etc). It was this lack of access to the Linux shell (or unfamiliarity with how to get to it) that has prevented us from explorer CRON jobs before.
Customer: replied 11 months ago.
This the product that the client is using. http://spydurpbx.com/ It consists of several pieces mounted a board. I put a monitor and keyboard on the server piece and can see a login. This maybe the place I need to access the terminal; however the credentials that let me in via the web browser and to access PHPAdmin do not work.
Expert:  Steve replied 11 months ago.
You know there is a CDR CSV module in Asterisk that will write every CDR to a comma-separated CSV file, in real time. Wouldn't that take care of it for you? The module will write the CDRs to /var/log/asterisk/cdr-csv/Master.csv . Use the following setup parameters for the module (/etc/asterisk/cdr.conf): [general]enable=yesloggingsafeshutdown=yes[csv]usegmtime=yesloguniqueid=yesloguserfield=yes
Customer: replied 11 months ago.
That would probably be ace; however I cannot access these directories to make changes. I have requested a user name and password ***** I can access the terminal. Otherwise I am stuck using a Win7 machine and though I did try to access via UNC ( \\10.10.8.60\etc$ and \\10.10.8.60\c$) I had no luck. Any guidance. I can access the Asterisk CLI from a web interface; however it is limited in the core commands I sent before.
Expert:  Steve replied 11 months ago.
I really think the best solution is for you to get a login and password ***** be able to access the system, not just for this issue but for other issues that might come up in the future. Not having access to Asterisk as an Administrator will really handicap you. And there is an exact solution to your problem that already exists via the CDR CSV module in Asterisk -- you just need to be able to turn it on. What are your prospects for getting the login?
Customer: replied 11 months ago.
Slim to none. I spoke with their tech support and sales guys yesterday. They do not provide shell access. They have ACD features that they want to sell. They have paths to interface with larger CRM packages. It is just way overkill for how the client uses the data. In the end they are talking between themselves to see what they can do. Ideally they can remotely make the changes you spoke of and activate this feature. The only trick with the solution would be the CRM looks at a network location now for the data. I guess they would also have to make /var/log/asterisk/cdr-csv/Master.csv an accessible share or ideally this feature can be told to output to say \\fileprintsvr\cdr-reports.
Expert:  Steve replied 11 months ago.
Can you check to see if the file is already there? CSV showed up as a registered backend when you did the ODBC Show.
Expert:  Steve replied 11 months ago.
Also, in the list of commands you sent me, it shows that you can execute a shell command. Try: !cat /var/log/asterisk/cdr-csv/Master.csv!ls /var/log/asterisk!cat /etc/asterisk/cdr.confinside the interface (your instructions say that "!" is for executing a shell command).
Customer: replied 11 months ago.
! cat /var/log/asterisk/cdr-csv/Master.csv
does not error; however it shows not detail either.
I executed the other commands as well. No response either positive or negative.
Expert:  Steve replied 11 months ago.
Can you give me some idea of what kind of user interface you're using to get to the CLI and to PHPMyAdmin? Maybe some screen prints of what your options are? I'm trying to figure out if there's a way to do something to grab those records automatically, but based on what you've told me so far, without shell access, I don't know that there is. But if I can see what your options are, maybe there is a solution.

Related Programming Questions