Mitel Forums - The Unofficial Source
Mitel Forums => Mitel MiVoice Business/MCD/3300 => Topic started by: bilbonvidia on September 15, 2008, 07:51:54 AM
-
Hi, I am it admin for a small office and was wondering if there were any freeware programs available which will collect sort the smdr data so I can see calls by ext etc? Or has anyone created their own access database or similar?
Thanks
B
-
This is something I've kept my ear to the ground for for a long time. I've never seen a free or open source product available for the Mitel product line.
I would think that a simple program could be written to collect the data in a single daily flat file that would be named something like "Sep-15-08.txt". A new file would be created everyday so you would always know what day you were looking at. After that you could import it into Excel or Open Office and sort it any way you would like.
Hopefully someone here will know where to get the software, but if not and you happen to find something that will work, please be sure to let us know. We'll promote it here.
Ralph
-
Yeah, Ralph is right once again. Dumping the data to a flat text file is easy. Translating the SMDR into something usable - given the variables in the SMDR options - is the key.
Not a hard programming trick, but beyond my programming skills...
-Chak
-
I have a set of python scripts that are capable of parsing mitel smdr output. They currently are not extremely robust in how they adapt to changes in the smdr format (so you must set your SMDR settings exactly like mine were when I wrote it). Python connects via MySQLdb to a MySQL database and inserts the records. You could then use a query browser or write a web front end to view the records. The version of these that I have that are currently under development are made to integrate with enterprise and are written in C and PHP for the webfrontend. This version can monitor many controllers at once and make relations between calls transfered across IP trunks, but I'm not willing to share the code to all of that.
I will, however, post the python stuff if anyone wants a crude script to parse SMDR to a relational DB.
-
Chad, please post your scripts. Who knows who might pick them up and and make good use of them.
Thanks!
-Chak
-
Call-Logger v4.1 Setup Instructions
Download at http://71.28.21.109/call-logger-setup.exe (http://71.28.21.109/call-logger-setup.exe).
[Editors Note 7/20/12: The link above is no longer active]
Dependencies
Call-Logger is written in and depends on a few packages that are all freely distributed.
- Python (Developed on 2.5 branch)
- mysql-pyhon (The MySQL Connector for Python)
- pywin32 (Python Windows Extensions)
- MySQL Server (Developed on 5.0 branch)
To run Call-Logger you must download all of these packages and install them manually.
Go to http://python.org/download/releases/2.5.2/ and download the x86 release of Python. Call-loggr
may also work on the Win64-AMD64 version but this has not been tested at all. Once the installer is
downloaded just double-click on it and accept all of the default settings.
Go to http://sourceforge.net/project/showfiles.php?group_id=22307&package_id=15775 and download an
appropriate version of mysql-python. Call-Logger v4.1 was developed on MySQL_python-1.2.2.win32-py2.5.exe
Once you have downloaded the package install it.
Go to http://sourceforge.net/project/platformdownload.php?group_id=78018 and download the version of
pywin32 that matches your Python version. For me it was the pywin32-212.win32-py2.5.exe (currently
highlighted in green on the downloads page. Install the package once you have downloaded it.
If you already have a MySQL server installed on your network you can optionally skip this step and
perform the MySQL setup listed below on the existing server. If you do not have an existing MySQL
server or you would like to install a separate instance on your server go to
http://dev.mysql.com/downloads/mysql/5.0.html#win32 and download the "Windows Essentials (x86)"
version of the MySQL installer. Note that if you are running a separate MySQL server the version
number doesn't really matter much as long as it is at least MySQL 4.0.0. Once you have downloaded
the install double-click on it and do a "Complete" install. After the installer finishes MySQL will
ask you if you want to configure the instance now, choose yes. My settings are as follows, but pretty
much anything will do.
- Detailed
- Server Machine
- Multifunctional Database
- Include Bin Directory in Windows PATH
Don't forget to set a root password and *remember* it
All other settings are default from what the install chooses.
MySQL Configuration
- Click on Start->All Programs->MySQL->MySQL Server <version>->MySQL Command Line Client
- Enter your password from the Instance Configuration above
- Copy and paste the following commands:
CREATE DATABASE logger;
USE logger;
CREATE TABLE `calls` (
`id` int(11) NOT NULL auto_increment,
`date` date default NULL,
`time` time default NULL,
`length` varchar(15) default NULL,
`ext` varchar(10) default NULL,
`trunk` varchar(10) default NULL,
`digits` varchar(50) default NULL,
`fdigits` varchar(50) default NULL,
`city` varchar(50) default NULL,
`state` varchar(50) default NULL,
`country` char(2) NOT NULL default 'US',
`transfer` varchar(10) default NULL,
`thirdParty` varchar(10) default NULL,
`accountCode` varchar(50) default NULL,
`aniClassDigits` varchar(50) default NULL,
`dnisDigits` varchar(50) default NULL,
`type` varchar(15) default NULL,
`rawData` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `extensions` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`description` text,
`ext` varchar(50) NOT NULL default 'xxxx',
PRIMARY KEY (`id`),
UNIQUE KEY `ext` (`ext`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `trunks` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`description` text,
`trunk` varchar(255) NOT NULL default 'Txxx',
PRIMARY KEY (`id`),
UNIQUE KEY `trunk` (`trunk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
GRANT INSERT, SELECT, UPDATE ON logger.* TO 'logger'@'127.0.0.1' IDENTIFIED BY 'logger';
scroll through and look for any errors. If there are none you can type "quit;" to exit the CLI.
MySQL configuration is done.
Install Call-Logger
Double-click on the setup package and follow the on-screen instructions.
Editing the Main Config File
We need to edit the main configuration file so that Call-Logger knows where to find things. To do that,
- click on the Windows "Start" button and choose "Run"
- The Run dialog box will appear with a text-box labeled "Open:" Type "Wordpad" in this box and click on "OK"
- Wordpad (a text editor) will open and display a blank document.
- Click on the "File" menu and choose "Open"
- In the Open dialog box, navigate to where you installed Call-Logger. This is C:\Program Files\Call-Logger\
by default. - in the "Files of Type:" select box choose "All Documents (*.*)" and choose the file called config.py and
click OK. Be careful not to choose config.pyc instead of config.py.
Wordpad is now displaying the configuration contents of your installation. If you followed the instructions above
you do not need to edit any of there parameters (The defaults will be fine) except "host". Set this paramater to
the IP address of your PBX. If you did change anything above make the proper changes here to reflect your earlier
settings.
- Save the file and close Wordpad
Install the Services
Navigate to the Folder where you installed Call-Logger (the default is C:\Program Files\Call-Logger\) and
double-click on install-services.bat. Read the ouput and look for any errors (The word "Error"). If there
are none your installation is complete. Restart your server to test the install.
I wrote these instructions really quickly so let me know if you don't understand or have trouble and any input you have. If people are interested I might make improvements and / or write a front end for the database. I found a bug shortly after I built this version that stops the City, State of a call based on area code, prefix from being reported in some cases. I have fixed this bug and will release the fix after some extended testing. The next version will not depend on python, mysql-python or pywin32. I am going to statically link all of the libraries that I need to simplify installation and configuration.
-
Thanks Chadmaynard!
Here is a mirror:
http://www.mitelforums.com/forum/downloads/call-logger-setup.exe (http://www.mitelforums.com/forum/downloads/call-logger-setup.exe)
[Editors Note 7/20/12: The link above is no longer active]
-Chak
-
My SMDR settings are as follows (I haven't played around enough to see which ones are required):
DASS II - Call Charge Information Provided: No
Extended Digit Length: No
MCD - Report Transfers: No
Network Format: Yes
Report Account Codes: No
Report Incoming Calls: Yes
Report Internal Calls: Yes
Report Meter Pulses: Yes
Report Outgoing Calls: Yes
SMDR Meter Unit Per Station: Yes
SMDR Record Transfer: Yes
System Identification: 1
Time Change Reporting: No
Twenty-four Hour Time Reporting: Yes
ANI/DNIS/ISDN/CLASS Number Delivery Reporting: Yes
SMDR Real Time Reporting: No
OLI Node ID Format for Incoming Trunk Calls: No
Extended Time To Answer: No
SMDR File Transfer: No
Standardized Network OLI: Yes
Standardized Call ID Format: Yes
Suite Services Reporting: No
Report Internal Unanswered Calls: No
SMDR Extended Reporting Level 1: No
Report Attendant Name: No
Account Code Reporting for Internal Calls: No
Tag Call Reporting: No
Tag Call Identifier:
Path Reporting for Internal ACD2 Calls: No
Number of destination address digits to mask: 0
SMDR Extended Reporting Level 2: No
Two B-Channel Transfer Reporting: No
The CSV export is attached so you can attempt an import if you wish. I added the extension .txt because the forum wouldn't let me upload a .csv.
-
I also know of a program that our company used to use that might do exactly what you want it to do. I am not to sure how much the software is being sold for but I could send out a few emails. It sounds exactly the type of software you are looking for. Reports get generated in .pdf and you can either write to access or SQL, depending on call volume. Let me know if you need something a bit more then just the scripts.
-
I was sold Call Aware with our system. The problem with it is it will only read one 3300 at a time so real time data from the entire phone system does not happen for me. It also requires it's own server, does not like other apps running.
I just don't even use it any more.