Archive for August, 2009

Configuring Asterisk to log CDR records via ODBC to a remote MS-SQL

Tuesday, August 18th, 2009

In the following I’ll describe how to setup asterisk to log via ODBC to a remote Microsoft SQL server — I needed this for a client. I’m using a Debian server, file location may differ for your brand of linux distribution.
The following packages exist for debian, the ones needed are marked with “NEEDED”
A good introduction to the Free TDS implementation of ODBC is the userguide of FreeTDS

Debian FreeTDS packages

  • freetds-common – configuration files for FreeTDS SQL client libraries: NEEDED
  • freetds-dev – MS SQL and Sybase client library (static libs and headers)
  • gda2-sybase – FreeTDS backend plugin for GNOME Data Access library for GNOME2
  • libct4 – libraries for connecting to MS SQL and Sybase SQL servers, needed only for sqsh for testing, gets automatically installed with sqsh.
  • libdbd-freetds – Freetds database server driver for libdbi
  • libsybdb5 – libraries for connecting to MS SQL and Sybase SQL servers
  • sqlrelay-freetds – SQL Relay FreeTDS (Sybase and MS SQL Server) connection daemon
  • sqsh – commandline SQL client for MS SQL and Sybase servers depends on libct4: Good for testing
  • tdsodbc – ODBC driver for connecting to MS SQL and Sybase SQL servers “This package includes the ODBC driver for FreeTDS, for use with UnixODBC or iODBC.”: NEEDED

FreeTDS Needs either unixodbc or iodbc, both are ODBC implementations for Linux/Unix. Asterisk is built against unixodbc.
iodbc packages:

  • iodbc – GTK+ config frontend for the iODBC Driver Manager
  • libiodbc2 – iODBC Driver Manager
  • libiodbc2-dev – iODBC Driver Manager (development files)

unixodbc packages:

  • unixodbc – ODBC tools libraries depends on odbcinst1debian1: NEEDED
  • unixodbc-bin – Graphical tools for ODBC management and browsing
  • unixodbc-dev – ODBC libraries for UNIX (development files)

common packages for debian:

  • odbcinst1debian1 – Support library and helper program for accessing odbc ini files: NEEDED

Asterisk depends on unixodbc which should be already installed, so we install the following packages (ignoring any warnings about already-installed packages):


apt-get install unixodbc sqsh tdsodbc

Other Software using ODBC

  • Python seems to have its own access module for MS-SQL:
    python-pymssql – Python database access for MS SQL server and Sybase

Configuraton for Asterisk Logging

  • /etc/freetds/freetds.conf
    
    [global]
            # TDS protocol version
    ;       tds version = 4.2
    
            # Whether to write a TDSDUMP file for diagnostic purposes
            # (setting this to /tmp is insecure on a multi-user system)
    ;       dump file = /tmp/freetds.log
    ;       debug flags = 0xffff
    
            # Command and connection timeouts
    ;       timeout = 10
    ;       connect timeout = 10
    
            # If you get out-of-memory errors, it may mean that your client
            # is trying to allocate a huge buffer for a TEXT field.
            # Try setting 'text size' to a more reasonable limit
            text size = 64512
    
    [logserver]
            host = 172.23.23.4
            port = 1433
            tds version = 8.0
    
  • /etc/odbcinst.ini
    
    [FreeTDS]
    Description = FreeTDS ODBC driver for MSSQL
    Driver = /usr/lib/odbc/libtdsodbc.so
    Setup = /usr/lib/odbc/libtdsS.so
    
  • /etc/odbc.ini
    
    [ODBC Data Sources]
    logserver = MSSQL Log-Server for Asterisk
    
    [logserver]
    description = MSSQL Log-Server for Asterisk
    driver      = /usr/lib/odbc/libtdsodbc.so
    servername  = logserver
    language = us_english
    trace = no
    tracefile = /root/mssql.trace
    
  • /etc/asterisk/cdr_odbc.conf
    
    [global]
    dsn=logserver
    username=asterisk
    password=VERYSECRET
    loguniqueid=yes
    dispositionstring=yes
    table=cdr              ;"cdr" is default table name
    usegmtime=no             ; set to "yes" to log in GMT
    
  • Test using isql

    # isql logserver asterisk "VERYSECRET" -v
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL> select * from cdr;
    [output of current cdr table]
    

Fotovoltaik Förderung

Tuesday, August 11th, 2009

Ich bin ja dabei, noch dieses Jahr eine Fotovoltaik-Anlage bei uns aufs Dach bauen zu lassen. Dazu gibt es die Klimafonds-Photovoltaik-Förderung, die ich beantragt habe. Die Presse hat ja dazu berichtet, dass der Server zusammengebrochen ist.
Der Nachfolgeartikel widerspricht sich, was den Zeitpunkt der Ausschöpfung der Förderung dieses Jahr betrifft, dort steht "Allein die Steiermark und Niederösterreich hätten innerhalb der ersten acht Stunden der Aktion 50 Millionen Euro benötigt" (zur Verfügung standen nur 18 Millionen) und etwas später "Dass es etwa einen Arbeitstag lang dauerte, bis die 18 Millionen an die Konsumenten gebracht wurden, lag am Zusammenbruch des Servers…"
Anyway, hier möchte ich jetzt kurz meine Aufzeichnungen während der Beantragung wiedergeben. Ich war kurz vor 10:00 Uhr online. Um kurz vor 11:30 hatte ich das Formular erfolgreich abgeschickt. Ein Log:
Schon am Vorabend und auch noch um kurz vor 10:00 am 4.8. hatte die Seite <http://www.klimafonds.gv.at/home/foerderungen/photovoltaik.html> einen seltsamen Tomcat-Error der auf einen Fehler in der Datenbank schliessen lässt (ein Duplicate Key). Der Link zur Anmeldung <http://www.service-publicconsulting.at/pv/> mit dem Titel "Photovoltaikförderungsantrag" war aber navigierbar und hatte ein Redirect auf <http://www.pv-service-publicconsulting.at/kkpv/> dieser wiederum führte aber zu einer Überlastungsseite <http://www.service-publicconsulting.at/pv/index_offline.html>
Nach einiger Zeit führen alle Seiten, also <http://www.service-publicconsulting.at/pv/> <http://www.service-publicconsulting.at/pv/index.html> <http://www.service-publicconsulting.at/pv/index_offline.html>
zum selben Text mit einem Link "Photovoltaikförderungsantrag (derzeit nicht verfügbar)"
Dann etwa eine Stunde später (kurz nach 11:00):
"Photovoltaikförderungsantrag (derzeit überlastet)"
Etwa um 11:15 hatten dann die direkten Links <http://www.service-publicconsulting.at/pv/> <http://www.service-publicconsulting.at/pv/index.html>
wieder ein Redirect auf <http://www.pv-service-publicconsulting.at/kkpv/> die wiederum auf <http://www.service-publicconsulting.at/pv/index_offline.html> redirectete.
Kurz darauf (kurz vor 11:30) ging dann erstmalig das Anmeldeformular als Redirect von <http://www.pv-service-publicconsulting.at/kkpv/>
Nach erfolgreicher Anmeldung bekam ich auch einen Email-Zustellversuch auf meinem Server, der aber wegen Greylisting nicht durchkam:


Aug  4 11:29:34 debian3264m postfix/smtpd[18583]: NOQUEUE: reject:
       RCPT from k969.ims-firmen.de[213.174.47.182]: 450 4.7.1
       <ralf@zoo.priv.at>: Recipient address rejected: Greylisted, see

http://isg.ee.ethz.ch/tools/postgrey/help/zoo.priv.at.html;

       from=<pv.service@kommunalkredit.at> to=<ralf@zoo.priv.at>
       proto=ESMTP helo=<bruce.connex.cc>

10 Minuten später kam dann auch die Email durch:


Aug  4 11:39:13 debian3264m postfix/smtpd[19080]: 3D5AE814E:
       client=k969.ims-firmen.de[213.174.47.182]
Aug  4 11:39:13 debian3264m postfix/cleanup[19086]: 3D5AE814E:
       message-id=<1144469538.1283.1249378174385.JavaMail.tomcatkk@bruce.connex.cc>
Aug  4 11:39:13 debian3264m postfix/smtpd[19080]: disconnect from
       k969.ims-firmen.de[213.174.47.182]
Aug  4 11:39:13 debian3264m postfix/qmgr[29847]: 3D5AE814E:
       from=<pv.service@kommunalkredit.at>, size=5844, nrcpt=1 (queue active)
Aug  4 11:39:13 debian3264m postfix/local[19087]: 3D5AE814E:
       to=<web11_ralf@static.88-198-162-230.clients.your-server.de>,
       orig_to=<ralf@zoo.priv.at>, relay=local, delay=0.28,
       delays=0.25/0/0/0.03, dsn=2.0.0, status=sent (delivered to command:
       /usr/bin/procmail -f-)
Aug  4 11:39:13 debian3264m postfix/qmgr[29847]: 3D5AE814E: removed

Hoffen wir mal, dass sich der Aufwand von 1.5 Stunden gelohnt hat. Die Gewährung von Förderungen von der Reihenfolge der Antragstellung bei einem kaputten Server abhängig zu machen lässt natürlich viele Vermutungen über Ungerechtigkeiten zu: Woher weiss ich, dass andere nicht früher durchgekommen sind? Vielleicht haben andere Bewerber noch einige Zeit lang ein Meldung wegen Überlastung bekommen, während andere schon munter ihren Antrag ausgefüllt haben? Wäre man schneller gewesen, wenn man einen Robot programmiert hätte? Vielleicht waren Robots an der Überlastung schuld?
Mir wäre da eine Frist und eine Ziehung unter notarieller Aufsicht deutlich lieber, damit würde klar zugegeben dass es ein Glücksspiel ist …

Cloud computing, Vendor Lock-In and the Future

Tuesday, August 4th, 2009

Cloud Computing is becoming increasingly popular — and it is a danger to your freedom. But we can do something about it.
First, when the term Cloud Computing was introduced, it meant a set of low-level services like virtual machines, databases and file storage. Examples of these are Amazon Elastic Computing Cloud and related services. Since these services are quite low-level, they can be replicated by others, an example is the Eucalyptus project.
This means if you aren’t satisfied with the service one cloud computing provider offers, you either can change the provider or — e.g., using Eucalyptus — roll your own.
But increasingly cloud-computing is a relaunch of the old Software as a Service paradigm under a new name. This means that applications like Textprocessing, Spreadsheets, Wiki, Blog, Voice and Video over IP, collaboration software in general is made available as so-called “Web 2.0″ applications — now called “Cloud Applications” on the web.
When using these services, there is a severe risk of Vendor Lock-In — since the applications may not be available elsewhere, you cannot easily switch the provider. Worse: From some of the Web 2.0 Services like social networks (e.g., Xing, LinkedIn, Facebook) you can’t retrieve your own data. Xing for example has a “mobile export” for data, but this works only for paying customers and only exports address data.
And people have started to realize — e.g., in this facebook group — that multiple incompatible applications — escpecially in the social network sector — puts a large burdon on customers to update multiple personal profiles on multiple sites.
But although it has been noted by the Free Software and Open Source community (e.g., in an interview with Richard Stallman and by Eric S. Raymond in his blog) it has not been widely recognized that cloud computing or software as a service — in particular in the form called “Web 2.0″ — creates a vendor lock-in worse than for proprietary software.
For your social networks this may mean that when you retrieve your data (remember, you helped them build that data!), the social network may throw you out as it happened in that case mentioned by Henry Story and later updated here.
The solution to this problem? Don’t get trapped in a data silo. This may still mean that there can be software as a service offerings. But the software needs to be free (as in free speech). So we can still switch to another provider or decide to host our own service.
But companies won’t do it for us. As Doc Searls notes in Silos End: “These problems cannot be solved by the companies themselves. Companies make silos. It’s as simple as that. Left to their own devices, that’s what they do. Over and over and over again.”
So this can only change if customers make and demand the change. A good rule-of-thumb for software as a service is on the page of the Open Cloud Initiative in the article The four degrees of cloud computing openness. While being a customer of a closed/proprietary cloud with “no access” is clearly a bad idea, open APIs and formats don’t work too well — you don’t have the software to work with your data. So the only valid options that remain are Open APIs, Open Formats and Open Source, and in some cases Open Data.
Still most web applications — like most social network software — are of the completely closed type. There are no open formats and no open APIs. So check your dependencies: What web-applications are you depending on and what is their degree of cloud computing openness?
A word on the license to guarantee openness in cloud-computing. As mentioned in the above-cited interview with Richard Stallman, the GNU General Public License is not enough to keep software in a cloud open. The cloud provider could take the software, make own modifications (which you will depend upon) and not release the modified software to you as a customer. Again you have a vendor lock-in. To prevent this, the GNU Affero General Public License has been designed that prevents closed-source modifications to hosted applications.
Finally, for all sorts of social software — not just social network software but everything that creates more value for more people, usually by linking information — should follow a distributed peer-to-peer approach. We don’t want this data to be a siloed application hosted by a single company. And if there are multiple companies hosting the data we already see the problem with multiple social network providers.
So we need standards and distributed protocols. And the implementation should follow a peer-to-peer approach — like seen in filesharing applications today — to make it resilient to failure and/or take-down orders of hostile bodies (like, e.g., some governments). Lets call this “Web 3.0″.
Examples of such social software are of course the social network sector. We already have a distributed protocol for social networking based on the Friend of a Friend Semantic Web Ontology. With this approach everyone can publish his social networking data and still be in control of who can see what. And the data is under user-control, so it’s possible to remove something.
Another example of social software is probably Money (in the sense of micro- or macro payments in the net). Thomas Greco in the book The End of Money and the Future of Civilization asks for separation of money and the state. A future implementation of money may well be based on a peer-to-peer social software implementation.
These social software needs security solutions. We want to model trust-relationships. Parts of the puzzle are probably OpenID and a newly-proposed scheme by Henry Story called FOAF+SSL mainly used for social networking 3.0 but probably very useful for other social software solutions.
So lets work on solutions for the future.


Impressum/Kontakt