CDR Report Using ODBC Access

[phonebuff]

Wanted to mine some of the Asterisk CDR data using MS/Access via the ODBC connector.

I downloaded and installed the ODBC driver from here --

http://dev.mysql.com/downloads/connector/odbc/5.1.html

An I set it up I thought with the IP of the Box, Port 3306

User Asteriskuser ==
Password == same as used in amportal.conf
Database == asteriskcdrdb

But no Joy.. Has anyone else had any luck making this work ??

[EMAIL="root@pbx"]root@pbx[/EMAIL]:~ $ mysql -u asteriskuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 449
Server version: 5.0.45 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asterisk           |
| asteriskcdrdb      |
| test               |
+--------------------+
4 rows in set (0.01 sec)

Thanks...

 

[tm1000]

I believe mysql is bound to 127.0.0.1

Youd have to change the configuration options to bind it to the external address:

http://dev.mysql.com/doc/refman/5.1/en/can-not-connect-to-server.html

http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_bind-address

 

[phonebuff]

Netstat -a does not show it listening on the open port 3306, or anywhere else for that matter. I guess this will require that the server configuration itself be modilied ?

root@pbx:~ $ mysqladmin -u asteriskuser -p version
Enter password:
mysqladmin  Ver 8.41 Distrib 5.0.45, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          5.0.45
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 35 min 12 sec
Threads: 3  Questions: 5543  Slow queries: 0  Opens: 31  Flush tables: 1  Open tables: 25  Queries per second avg: 0.970
 
root@pbx:~ $ mysqladmin -u asteriskuser -p -h 172.16.200.150 version
Enter password:
mysqladmin: connect to server at '172.16.200.150' failed
error: 'Host '172.16.200.150' is not allowed to connect to this MySQL server'
 
root@pbx:~ $ mysqladmin -u asteriskuser -p -h 127.0.0.1 version
Enter password:
mysqladmin  Ver 8.41 Distrib 5.0.45, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          5.0.45
Protocol version        10
Connection              127.0.0.1 via TCP/IP
TCP port                3306
Uptime:                 1 hour 38 min 19 sec
Threads: 4  Questions: 5739  Slow queries: 0  Opens: 31  Flush tables: 1  Open tables: 25  Queries per second avg: 0.973

So where do I add the permission for mysql to accept a connection on the host 172.16.200.150. instead of 127.0.0.1

TIA ----

 

[mmb]

You can set IP access permissions to the database through webmin's mysql module. You can set it up under user permissions or under host permissions.

I'd create a new user in mysql, give login to that user from the IP of the machine you're running Access on, and give that user only select permission to the data in only cdr database. That way, your other databases are still secure from remote login.

Clear as mud?

 

[phonebuff]

Well,

No luck getting ODBC connections so I just wrote some long hand SQL and pulled some of the data..

What I am missing is how to determine an outbound call and Dst from an internal call src is Extension Range but what is the second half of the where clause ??

select month(calldate), src, dst, count(*) from asteriskcdrdb.cdr where length(src) = 4 and year(calldate) = 2010 group by month(calldate), src, dst;

Get's all the internal calls, but I don't think I see outbound calling. what's missing in this select ?

Anyone ??

TIA...

 

[phonebuff]

Think this get's me closer to what the customer wants ...

Any feedback ??

mysql> select substring(channel, 1, 8) as org, count(*) as Calls, sum(billsec) from asteriskcdrdb.cdr where year(calldate) = 2010 and lastapp = 'Dial' and length(dst) >= 10 group by org ;