Build Your Own Custom CDR Applications With Shoretel

ShoreTel

ShoreTel

Now that you have a Shoretel / Mitel Connect server in your own datacenter, you may find that you need to run some SQL commands within the Shoretel database. Fortunately, Shoretel uses MySQL 5.0 running on ports 4308 and 4309.  The good news is that you can use standard SQL commands to query the call database and make your own custom reports from scratch.  Here’s a little DOS batchfile that will connect to the database and let you run commands as the root user.  There are some read-only users in the database for use by external call reporting applications.  One such user is ‘st_cdrreport’ with password ‘passwordcdrreport’ Be careful or you could break shoretel. The usernames and passwords are the default shoretel database passwords.  They well known by everybody.  Any Shoretel service organization that also works in IT Consulting should already know this information. This are not a big secret.  But don’t try to change these passwords on your own system or you will break your Shoretel Director.  You have been warned.  You should also be warned that Shoreline Communications recommends that the Windows firewall be turned off, therefore exposing these ports to the rest of your network. So, please be extra careful, and consider your security requirements.  The configuration database is on port 4308 and the call data reporting (CDR) is on port 4309.  Some examples can be found in “C:\Program Files (x86)\Shoreline Communications\ShoreWare Server\MySQL\MySQL Server\Examples”  The DOS batchfiles below should be saved to FILE.BAT and then saved to a folder on the C: drive.

The scripts below are for internal applications running on the shoretel server.  For external applications, you will need these connect strings below. These are example connect strings for VB.net applications.  We have tested these on modern versions of shoretel from Version 12.1 to 15.0. If you have Shoretel 7 or earlier, it uses Access Database instead.

Dim conn1Str As String = "server=shoretel64;user=st_cdrreport;database=shorewarecdr;port=4309;password=passwordcdrreport;"
Dim conn2Str As String = "server=shoretel64;user=st_configread;database=shoreware;port=4308;password=passwordconfigread;"

The Config Database on port 4308

@ECHO OFF
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
\Environment" /v PROCESSOR_ARCHITECTURE') DO SET OSARCH=%%B

if %OSARCH%==AMD86 goto EIGHTSIX
if %OSARCH%==x86 goto EIGHTSIX
if %OSARCH%==x64 goto SIXFOUR
if %OSARCH%==AMD64 goto SIXFOUR

echo "database tables available for mysql"
echo "  shorewarecdr and shoreware "

:EIGHTSIX
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Shoreline Teleworks\ShoreWare Server" /v 
Location') DO SET ServerPath=%%B
"%ServerPath%MySQLCDR\MySQL Server\bin\mysql.exe" -D Shoreware --user=root --password=shorewaredba --port=4308
goto END
)

:SIXFOUR
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Shoreline Teleworks\ShoreWare 
Server" /v Location') DO SET ServerPath=%%B
"%ServerPath%MySQLCDR\MySQL Server\bin\mysql.exe" -D Shoreware --user=root --password=shorewaredba --port=4308
goto END
)

:END
pause

The CDR Database on port 4309

@ECHO OFF
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment" /v PROCESSOR_ARCHITECTURE') DO SET OSARCH=%%B

if %OSARCH%==AMD86 goto EIGHTSIX
if %OSARCH%==x86 goto EIGHTSIX
if %OSARCH%==x64 goto SIXFOUR
if %OSARCH%==AMD64 goto SIXFOUR

echo "database tables available for mysql"
echo "  shorewarecdr and shoreware "

:EIGHTSIX
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Shoreline Teleworks\ShoreWare Server" /v Location') DO SET ServerPath=%%B
"%ServerPath%MySQLCDR\MySQL Server\bin\mysql.exe" -D shorewarecdr --user=root --password=shorewaredba --port=4309
goto END
)

:SIXFOUR
FOR /F "tokens=2* delims= " %%A IN ('REG QUERY "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Shoreline Teleworks\ShoreWare Server" /v Location') DO SET ServerPath=%%B
"%ServerPath%MySQLCDR\MySQL Server\bin\mysql.exe" -D shorewarecdr --user=root --password=shorewaredba --port=4309
goto END
)

:END
pause

Leave a comment