AGG Software Forums

I am currently trying to implement an SMDR logging solution, I have been evaluating your product, i have managed to configure it to import the SMDR data into a SQL DB in real time, which is exactly what we are looking for, however, the only fields imported were:

CALL_TIME, CALL_DURATION, CALL_DURATION_S, RING, CALLER_PHONE, CALL_TYPE, DIALLED_PHONE, ACC

The fields that are available in the SMDR on an Avaya IP office are:

Call_Start, Call_Duration, Ring_Duration, Caller_Direction, Dialled_number, Dialled_number, Account_Is_Internal, Call_ID_Continuation, Party1_Device, Party1_Name, Party2_Device, Party2_Name, Hold_Time, Park_Time, AuthValid, AuthCode, User_Charged, Call_Charge,
Currency, Amount_at_Last_User_Change, Call_Units, Units_at_Last_User_Change, Cost_per_Unit, MarkUp

Is there any plans to modify the Avaya IP Office Parser to cater for the other fields presented in the SMDR?

Simply attach a file with your data example with description of columns within the file and we'll prepare the parser for you. Because your PBX is freely configurable, then attach fields configuration from your PBX too.

Thanks for the reply, my data file is attached.

Here is the script i used to create the table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PBXDATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PBXDATA]
GO

CREATE TABLE [dbo].[PBXDATA] (
[ID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL,
Call_Start [datetime] NULL ,
Call_Duration [datetime] NULL ,
Ring_Duration [nvarchar] (40) NULL ,
Caller_Direction [nvarchar] (40) NULL ,
Dialled_number [nvarchar] (40) NULL ,
Dialled_number1 [nvarchar] (40) NULL ,
Account_Is_Internal [nvarchar] (40) NULL ,
Call_ID_Continuation [nvarchar] (40) NULL ,
Party1_Device [nvarchar] (40) NULL ,
Party1_Name [nvarchar] (40) NULL ,
Party2_Device [nvarchar] (40) NULL ,
Party2_Name [nvarchar] (40) NULL ,
Hold_Time [nvarchar] (40) NULL ,
Park_Time [nvarchar] (40) NULL ,
AuthValid [nvarchar] (40) NULL ,
AuthCode [nvarchar] (40) NULL ,
User_Charged [nvarchar] (40) NULL ,
Call_Charge [nvarchar] (40) NULL ,
Currency [nvarchar] (40) NULL ,
Amount_at_Last_User_Change [nvarchar] (40) NULL ,
Call_Units [nvarchar] (40) NULL ,
Units_at_Last_User_Change [nvarchar] (40) NULL ,
Cost_per_Unit [nvarchar] (40) NULL ,
MarkUp [nvarchar] (40) NULL,
) ON [PRIMARY]
GO

Please let me know if you need any more information from me?

THANK YOU!

Hi there,

When do you think you will be able to look into this parser issue for me?

thanks

Andrew

If we can get this sorted i am 99% certain we will be buying your software, can you please give me some indication on this issue?

many thanks

Andrew

Andrew, I'm sorry for my late reply. Please, use the settings from the attached image and capture the data to a binary log file. Did you create the previous file with help of our data export plug-in?

That's the SMDR file that the phone switch creates.

I have the logs created already, i have attached one for you now.

Please, unpack the attached file to Program folder\Plugins\pbxparser\Configs and replace the original file with new one. We've added parser items for all columns. You can bind it to your database table columns within the data export plug-in. Please, let me know about results.

Arthur,

Thanks for this, i started to bind to the database, but it appears the Parser does not have all the fields.

I came across these three before i stopped looking at it:

Call_Start, Calller_Direction, Dialled_Number

Is the parser you complied incomplete as to the Fields i listed in my DB Table?

thank you for your help...

These columns are mapped to standard columns of our software:

Call_Start to CALL_TIME
Calller_Direction to CALL_TYPE
Dialled_Number to DIALED_PHONE

Arhtur, would you be soo kind as to show me the translations for all the elements in the Parser to the titles i have used in my DB similar to your last post, but to cover all items?

Have some errors also:

30/04/09 12:16:09
30/04/09 12:16:09 Avaya IP Office Phone [CSV]. Item: CALL_UNITS. Error occurred while converting string value "" to "Integer" data type  ('' is not a valid integer value)
30/04/09 12:16:09 Avaya IP Office Phone [CSV]. Item: LAST_UNITS. Error occurred while converting string value "" to "Integer" data type  ('' is not a valid integer value)
30/04/09 12:16:09 pbxlogger. Unable to write to database [[Microsoft][ODBC SQL Server Driver]Invalid precision value]
30/04/09 12:16:09
30/04/09 12:16:10 pbxlogger. Unable to write to database [[Microsoft][ODBC SQL Server Driver]Invalid precision value]
30/04/09 12:16:10

Most fields have same item name and description (item name is in uppercase), except

Call_Start - CALL_TIME
Ring duration - RING
Caller - CALLER_PHONE
Direction - CALL_TYPE
Called_number - CALLED_PHONE
Dialled_Number - DIALED_PHONE
Is_Internal - FLAG1
Call ID - CALL_ID
Continuation - FLAG2
AuthValid - FLAG3

P.S. The updated version is attached. The data type for CALL_UNITS, LAST_UNITS has been changed to "string".

Thanks again.

Error i get now is:

30/04/09 12:47:47 pbxlogger. Unable to write to database [[Microsoft][ODBC SQL Server Driver]Invalid precision value]
30/04/09 12:47:47

I'll try to create a table with your structure and test it with your data. Could you create a backup copy of your current configuration from the File menu and attach it?

Thank you!

Attached...

Please,

1. Change the column data type from nvarchar to varchar.
2. Specify "EMPTY STRING AS NULL=TRUE" (w/o quotes) in the "Additional attributes" field within the ODBC plug-in settings.
3. Import the attached file on the "Binding" page within the ODBC plug-in settings.

Arthur, still get the error:

01/05/09 09:49:10 pbxlogger. Unable to write to database [[Microsoft][ODBC SQL Server Driver]Invalid precision value]
01/05/09 09:49:10

Here is the SQL script:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PBXDATA_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PBXDATA_1]
GO

CREATE TABLE [dbo].[PBXDATA_1] (
[ID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL,
Call_Start [datetime] NULL ,
Call_Duration [datetime] NULL ,
Ring_Duration [varchar] (40) NULL ,
Caller_Direction [varchar] (40) NULL ,
Dialled_number [varchar] (40) NULL ,
Dialled_number1 [varchar] (40) NULL ,
Account_Is_Internal [varchar] (40) NULL ,
Call_ID_Continuation [varchar] (40) NULL ,
Party1_Device [varchar] (40) NULL ,
Party1_Name [varchar] (40) NULL ,
Party2_Device [varchar] (40) NULL ,
Party2_Name [varchar] (40) NULL ,
Hold_Time [varchar] (40) NULL ,
Park_Time [varchar] (40) NULL ,
AuthValid [varchar] (40) NULL ,
AuthCode [varchar] (40) NULL ,
User_Charged [varchar] (40) NULL ,
Call_Charge [varchar] (40) NULL ,
Currency [varchar] (40) NULL ,
Amount_at_Last_User_Change [varchar] (40) NULL ,
Call_Units [varchar] (40) NULL ,
Units_at_Last_User_Change [varchar] (40) NULL ,
Cost_per_Unit [varchar] (40) NULL ,
MarkUp [varchar] (40) NULL,
) ON [PRIMARY]
GO

I renamed the attached file to .ini and imported it the ODBC.

I also added the line you mentioned, without quotes.

You don't need to rename this file to *.ini, because you should load it on the "Binding" page (the button near the Import button).

I have done it this way now.

I used the "LOAD and SAVE" button, which said it needed an ini file - this did seem to work, btw, and populated the correct bindings...

RESULT! This imported the data! Thank you! However, i have noticed a problem with my SQL script, i merged two column names, the script should be:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PBXDATA_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PBXDATA_1]
GO

CREATE TABLE [dbo].[PBXDATA_1] (
[ID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL,
Call_Start [datetime] NULL ,
Call_Duration [datetime] NULL ,
Ring_Duration [varchar] (40) NULL ,
Caller_Direction [varchar] (40) NULL ,
Dialled_number [varchar] (40) NULL ,
Dialled_number1 [varchar] (40) NULL ,
Account [varchar] (40) NULL ,
Is_Internal [varchar] (40) NULL ,
Call_ID [varchar] (40) NULL ,
Continuation [varchar] (40) NULL ,
Party1_Device [varchar] (40) NULL ,
Party1_Name [varchar] (40) NULL ,
Party2_Device [varchar] (40) NULL ,
Party2_Name [varchar] (40) NULL ,
Hold_Time [varchar] (40) NULL ,
Park_Time [varchar] (40) NULL ,
AuthValid [varchar] (40) NULL ,
AuthCode [varchar] (40) NULL ,
User_Charged [varchar] (40) NULL ,
Call_Charge [varchar] (40) NULL ,
Currency [varchar] (40) NULL ,
Amount_at_Last_User_Change [varchar] (40) NULL ,
Call_Units [varchar] (40) NULL ,
Units_at_Last_User_Change [varchar] (40) NULL ,
Cost_per_Unit [varchar] (40) NULL ,
MarkUp [varchar] (40) NULL,
) ON [PRIMARY]
GO

You will see that ACCOUNT_IS_INTERNAL should be ACCOUNT and IS_INTERNAL, and CALL_ID_CONTINUATIONS should be CALL_ID and CONTINUATION.

do you need to modify the parser you sent me to allow for this? Sorry for not spotting this sooner!

thanks for all your help....

No, we don't need to modify the parser. You should only change binding settings...

Great news!

can you please tell me what parser reference i need to bind the following columns too?

Call_ID
Continuation
Account
Is_Internal

thanks

Call_ID - CALL_ID
Continuation - FLAG2
Account - ACC
Is_Internal - FLAG1

Arthur - Thanks for all your previous Help on this, we did end up buying the product!

I think i have come across another problem though, perhaps you can help?

The logger does not log to the DB unless its a connected call, if I ring an no one answers, it does not log...

It seems when the string ends in #0D#0A it is logged, and when it ends in #0D#00 it does not.

Is this by design, can i make a change to ensure all Telephone activity is logged?

Please, attach a data example (you may capture your data to log file with help of our software). It is possible we should correct the parser module...

This is from the PBX Logger window:

<20090605090111.353>
2009/06/05 08:59:21,00:01:49,2,901256880822,I,2142,822142,,0,1001049,0,E2142,Paul Kerr,T9009,Line 9.1,0,0,,,,,,,,,,,,,,#0D#0A
<20090605090628.701>
2009/06/05 09:05:54,00:00:12,21,2123,O,6110,6110,,0,1001051,0,E2123,Andrew Parker,T9009,Line 9.2,0,0,,,Andrew Parker,0000.00,,0000.00,0,0,618,1.00,U,Andrew Parker,,#0D#00
<20090605090816.875>
2009/06/05 09:07:17,00:00:57,4,,I,2097,589999,,0,1001052,0,E2097,Extn2097,T9009,Line 9.2,0,0,,,,,,,,,,,,,,#0D#0A
<20090605090941.204>
2009/06/05 09:08:14,00:01:24,2,2161,O,2123,2123,,1,1001053,0,E2161,Server Room,E2123,Andrew Parker,0,0,,,,,,,,,,,,,,#0D#0A

Please, update the parser configuration. Replace the original file with the new file from the archive (unpack it first).

PERFECT!!!! Issue has been fixed.