<html>
<head>
<base href="https://wiki.asterisk.org/wiki">
<link rel="stylesheet" href="/wiki/s/en/2171/18/9/_/styles/combined.css?spaceKey=AST&forWysiwyg=true" type="text/css">
</head>
<body style="background: white;" bgcolor="white" class="email-body">
<div id="pageContent">
<div id="notificationFormat">
<div class="wiki-content">
<div class="email">
<h2><a href="https://wiki.asterisk.org/wiki/display/AST/MSSQL+CDR+Backend">MSSQL CDR Backend</a></h2>
<h4>Page <b>edited</b> by <a href="https://wiki.asterisk.org/wiki/display/~seanbright">Sean Bright</a>
</h4>
<br/>
<h4>Changes (3)</h4>
<div id="page-diffs">
<table class="diff" cellpadding="0" cellspacing="0">
<tr><td class="diff-deleted-lines" style="color:#999;background-color:#fdd;text-decoration:line-through;">sterisk can currently store CDRs into an MSSQL database in two different ways: cdr_odbc or cdr_tds <br></td></tr>
<tr><td class="diff-added-lines" style="background-color: #dfd;">Asterisk can currently store CDRs into a Microsoft SQL Server database in two different ways: cdr_odbc or cdr_tds <br></td></tr>
<tr><td class="diff-unchanged" > <br></td></tr>
<tr><td class="diff-changed-lines" >Call Data Records can be stored using unixODBC (which requires the FreeTDS package) [cdr_odbc] or directly by using just the FreeTDS package <span class="diff-changed-words">[cdr_tds]<span class="diff-added-chars"style="background-color: #dfd;">. </span></span> The following provide some examples known to get asterisk working with mssql. <br></td></tr>
<tr><td class="diff-unchanged" > <br>{note} <br></td></tr>
<tr><td class="diff-snipped" >...<br></td></tr>
</table>
</div> <h4>Full Content</h4>
<div class="notificationGreySide">
<p>Asterisk can currently store CDRs into a Microsoft SQL Server database in two different ways: cdr_odbc or cdr_tds </p>
<p>Call Data Records can be stored using unixODBC (which requires the FreeTDS package) <a href="/wiki/pages/createpage.action?spaceKey=AST&title=cdr_odbc&linkCreation=true&fromPageId=5242916" class="createlink">cdr_odbc</a> or directly by using just the FreeTDS package <a href="/wiki/pages/createpage.action?spaceKey=AST&title=cdr_tds&linkCreation=true&fromPageId=5242916" class="createlink">cdr_tds</a>. The following provide some examples known to get asterisk working with mssql. </p>
<div class='panelMacro'><table class='noteMacro'><colgroup><col width='24'><col></colgroup><tr><td valign='top'><img src="/wiki/images/icons/emoticons/warning.gif" width="16" height="16" align="absmiddle" alt="" border="0"></td><td>Only choose one db connector.</td></tr></table></div>
<h3><a name="MSSQLCDRBackend-ODBCusingcdrodbc"></a>ODBC using cdr_odbc</h3>
<h5><a name="MSSQLCDRBackend-Compile%2Cconfigure%2CandinstallthelatestunixODBCpackage%3A"></a>Compile, configure, and install the latest unixODBC package:</h5>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>tar -zxvf unixODBC-2.2.9.tar.gz && cd unixODBC-2.2.9 && ./configure --sysconfdir=/etc --prefix=/usr --disable-gui && make && make install
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Compile%2Cconfigure%2CandinstallthelatestFreeTDSpackage%3A"></a>Compile, configure, and install the latest FreeTDS package:</h5>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>tar -zxvf freetds-0.62.4.tar.gz && cd freetds-0.62.4 && ./configure --prefix=/usr --with-tdsver=7.0 \ --with-unixodbc=/usr/lib && make && make install
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Compile%2Correcompile%2Casterisksothatitwillnowaddsupportforcdrodbc."></a>Compile, or recompile, asterisk so that it will now add support for cdr_odbc.</h5>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>make clean && ./configure --with-odbc && make update && make && make install
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Setupodbcconfigurationfiles."></a>Setup odbc configuration files.</h5>
<p>These are working examples from my system. You will need to modify for your setup. You are not required to store usernames or passwords here.</p>
<p>/etc/odbcinst.ini</p>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>[FreeTDS]
Description = FreeTDS ODBC driver for MSSQL
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
FileUsage = 1
</pre>
</div></div>
<p>/etc/odbc.ini</p>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>[MSSQL-asterisk]
description = Asterisk ODBC for MSSQL
driver = FreeTDS
server = 192.168.1.25
port = 1433
database = voipdb
tds_version = 7.0
language = us_english
</pre>
</div></div>
<div class='panelMacro'><table class='warningMacro'><colgroup><col width='24'><col></colgroup><tr><td valign='top'><img src="/wiki/images/icons/emoticons/forbidden.gif" width="16" height="16" align="absmiddle" alt="" border="0"></td><td>Only install one database connector. Do not confuse asterisk by using both ODBC (cdr_odbc) and FreeTDS (cdr_tds). This command will erase the contents of cdr_tds.conf
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>[ -f /etc/asterisk/cdr_tds.conf ] > /etc/asterisk/cdr_tds.conf
</pre>
</div></div></td></tr></table></div>
<div class='panelMacro'><table class='noteMacro'><colgroup><col width='24'><col></colgroup><tr><td valign='top'><img src="/wiki/images/icons/emoticons/warning.gif" width="16" height="16" align="absmiddle" alt="" border="0"></td><td>unixODBC requires the freeTDS package, but asterisk does not call freeTDS directly.</td></tr></table></div>
<h5><a name="MSSQLCDRBackend-Nowsetupcdrodbcconfigurationfiles."></a>Now set up cdr_odbc configuration files. </h5>
<p>These are working samples from my system. You will need to modify for your setup. Define your usernames and passwords here, secure file as well.</p>
<p>/etc/asterisk/cdr_odbc.conf</p>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>[global]
dsn=MSSQL-asterisk
username=voipdbuser
password=voipdbpass
loguniqueid=yes
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Andfinally%2Ccreatethe%27cdr%27tableinyourmssqldatabase."></a>And finally, create the 'cdr' table in your mssql database.</h5>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>CREATE TABLE cdr (
[calldate] [datetime] NOT NULL ,
[clid] [varchar] (80) NOT NULL ,
[src] [varchar] (80) NOT NULL ,
[dst] [varchar] (80) NOT NULL ,
[dcontext] [varchar] (80) NOT NULL ,
[channel] [varchar] (80) NOT NULL ,
[dstchannel] [varchar] (80) NOT NULL ,
[lastapp] [varchar] (80) NOT NULL ,
[lastdata] [varchar] (80) NOT NULL ,
[duration] [int] NOT NULL ,
[billsec] [int] NOT NULL ,
[disposition] [varchar] (45) NOT NULL ,
[amaflags] [int] NOT NULL ,
[accountcode] [varchar] (20) NOT NULL ,
[uniqueid] [varchar] (150) NOT NULL ,
[userfield] [varchar] (255) NOT NULL
)
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Startasteriskinverbosemode."></a>Start asterisk in verbose mode.</h5>
<p>You should see that asterisk logs a connection to the database and will now record every call to the database when it's complete.</p>
<h3><a name="MSSQLCDRBackend-TDS%2Cusingcdrtds"></a>TDS, using cdr_tds</h3>
<h5><a name="MSSQLCDRBackend-Compile%2Cconfigure%2CandinstallthelatestFreeTDSpackage%3A"></a>Compile, configure, and install the latest FreeTDS package:</h5>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>tar -zxvf freetds-0.62.4.tar.gz && cd freetds-0.62.4 && ./configure --prefix=/usr --with-tdsver=7.0 make && make install
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Compile%2Correcompile%2Casterisksothatitwillnowaddsupportforcdrtds."></a>Compile, or recompile, asterisk so that it will now add support for cdr_tds.</h5>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>make clean && ./configure --with-tds && make update && make && make install
</pre>
</div></div>
<div class='panelMacro'><table class='warningMacro'><colgroup><col width='24'><col></colgroup><tr><td valign='top'><img src="/wiki/images/icons/emoticons/forbidden.gif" width="16" height="16" align="absmiddle" alt="" border="0"></td><td>Only install one database connector. Do not confuse asterisk by using both ODBC (cdr_odbc) and FreeTDS (cdr_tds). This command will erase the contents of cdr_odbc.conf
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>[ -f /etc/asterisk/cdr_odbc.conf ] > /etc/asterisk/cdr_odbc.conf
</pre>
</div></div></td></tr></table></div>
<h5><a name="MSSQLCDRBackend-Setupcdrtdsconfigurationfiles."></a>Setup cdr_tds configuration files.</h5>
<p>These are working samples from my system. You will need to modify for your setup. Define your usernames and passwords here, secure file as well.</p>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>/etc/asterisk/cdr_tds.conf [global] hostname=192.168.1.25 port=1433 dbname=voipdb user=voipdbuser password=voipdpass charset=BIG5
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Andfinally%2Ccreatethe%27cdr%27tableinyourmssqldatabase."></a>And finally, create the 'cdr' table in your mssql database.</h5>
<div class="preformatted panel" style="border-width: 1px;"><div class="preformattedContent panelContent">
<pre>CREATE TABLE cdr (
[accountcode] [varchar] (20) NULL ,
[src] [varchar] (80) NULL ,
[dst] [varchar] (80) NULL ,
[dcontext] [varchar] (80) NULL ,
[clid] [varchar] (80) NULL ,
[channel] [varchar] (80) NULL ,
[dstchannel] [varchar] (80) NULL ,
[lastapp] [varchar] (80) NULL ,
[lastdata] [varchar] (80) NULL ,
[start] [datetime] NULL ,
[answer] [datetime] NULL ,
[end] [datetime] NULL ,
[duration] [int] NULL ,
[billsec] [int] NULL ,
[disposition] [varchar] (20) NULL ,
[amaflags] [varchar] (16) NULL ,
[uniqueid] [varchar] (150) NULL ,
[userfield] [varchar] (256) NULL
)
</pre>
</div></div>
<h5><a name="MSSQLCDRBackend-Startasteriskinverbosemode."></a>Start asterisk in verbose mode.</h5>
<p>You should see that asterisk logs a connection to the database and will now record every call to the database when it's complete.</p>
</div>
<div id="commentsSection" class="wiki-content pageSection">
<div style="float: right;" class="grey">
<a href="https://wiki.asterisk.org/wiki/users/removespacenotification.action?spaceKey=AST">Stop watching space</a>
<span style="padding: 0px 5px;">|</span>
<a href="https://wiki.asterisk.org/wiki/users/editmyemailsettings.action">Change email notification preferences</a>
</div>
<a href="https://wiki.asterisk.org/wiki/display/AST/MSSQL+CDR+Backend">View Online</a>
|
<a href="https://wiki.asterisk.org/wiki/pages/diffpagesbyversion.action?pageId=5242916&revisedVersion=3&originalVersion=2">View Changes</a>
|
<a href="https://wiki.asterisk.org/wiki/display/AST/MSSQL+CDR+Backend?showComments=true&showCommentArea=true#addcomment">Add Comment</a>
</div>
</div>
</div>
</div>
</div>
</body>
</html>