Avaya IP Office Data Parser

[AndrewParker]

Thank you!

Attached...

[Arthur Grasin]

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.

[AndrewParker]

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.

[Arthur Grasin]

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).

[AndrewParker]

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...

[AndrewParker]

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....

[Arthur Grasin]

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

[AndrewParker]

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

[Arthur Grasin]

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

[AndrewParker]

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?

[Arthur Grasin]

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...

[AndrewParker]

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

[Arthur Grasin]

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

[AndrewParker]

PERFECT!!!! Issue has been fixed.