Do you any example SQL commands?
We have a Mitel system and I want to log to SQL so we are evaluating your product, I can connect to a MS SQL Databse with the SQL Addin but how do I get it to write the data?
I'm happy to write the sql but where can I find out the field names supplied as parameters?
thanks.
You may use the ODBC module and don't create SQL commands at all (but you need an ODBC driver for your database) or you may use the following command (as example):
INSERT INTO pbxdata (FLAG1,
CALL_TIME, DURATION, DURATION_S, CALLER_PHONE,
FLAG2, FLAG3, DIALED_PHONE, CALL_TYPE,
FLAG4, FLAG5, FLAG6, EXT, ACC)
values (:FLAG1,
:CALL_TIME, :CALL_DURATION, :CALL_DURATION_S, :CALLER_PHONE,
:FLAG2, :FLAG3, :DIALED_PHONE, :CALL_TYPE,
:FLAG4, :FLAG5, :FLAG6, :EXT, :ACC)
The script for the table is below (you should create it manually):
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_TIME] [datetime] NULL ,
[DURATION] [datetime] NULL ,
[DURATION_S] [int] NULL ,
[DIRECTION] [nvarchar] (4) NULL ,
[CALL_TYPE] [nvarchar] (8) NULL ,
[FLAG1] [nvarchar] (32) NULL ,
[FLAG2] [nvarchar] (32) NULL ,
[FLAG3] [nvarchar] (32) NULL ,
[FLAG4] [nvarchar] (16) NULL ,
[FLAG5] [nvarchar] (16) NULL ,
[FLAG6] [nvarchar] (16) NULL ,
[FLAG7] [nvarchar] (16) NULL ,
[CALLER_PHONE] [nvarchar] (40) NULL ,
[DIALED_PHONE] [nvarchar] (40) NULL ,
[TRUNK] [nvarchar] (16) NULL ,
[CO] [nvarchar] (10) NULL ,
[ACC] [nvarchar] (16) NULL ,
[EXT] [nvarchar] (10) NULL ,
[RING] [datetime] NULL ,
[COST] [float] NULL
) ON [PRIMARY]
GO
Of course, you may add your columns and remove unnecessary columns.
OK I seem to have got further but now get an error with the datetime format
21/04/09 15:43:20 a8002304\sqlexpress:mitel. Data have been processed successful
21/04/09 15:43:20 Mitel. Item: CALL_TIME. Error occurred while converting date/time string value "04/21 15:38:" with the template "mm"/"dd hh:nnA/P" (On format: string: :)
21/04/09 15:43:13 a8002304\sqlexpress:mitel. Data have been processed successful
21/04/09 15:43:13 Mitel. Item: CALL_TIME. Error occurred while converting date/time string value "04/21 15:42:" with the template "mm"/"dd hh:nnA/P" (On format: string: :)
Using your SQL, getting CALL_TIME is of unsupported type. So it's almost working, still working through it.
It seems that your PBX outputs date/time in a different format. Could you post a data example from the program's screen? What PBX model do you have? Could you change the date format within the PBX?
OK It all works but not recording the date and time of the call (The 2 date columns) here is an extract from the log files
<20090421160000.614> 04/21 15:59:04 5016 83578399 T103 001 15016 Q0018353 A
<20090421160007.114> 04/21 15:59:11 5016 83578399 T103 001 15016 S0010224 A
<20090421160013.161> 04/21 15:59:17 5016 83578399 T103 001 15016 U0017667 A
<20090421160014.708>-04/21 15:51:03 0000:08:18 T124 0007 5054 5054 001 0881249300 5054 Q0010077 A
<20090421160014.787> 04/21 15:58:49 0000:00:32 5188 0414279839 A T108 001 15188 Q0015699 A
<20090421160017.709> 04/21 15:59:21 5016 83578399 T103 001 15016 S0012655 A
<20090421160019.052> 04/21 15:58:26 0000:01:00 T138 0006 8058 8058 001 0418666502 8058 Q0013391 A
<20090421160022.724> 04/21 15:59:26 5016 83578399 T108 001 15016 Q0015053 A
<20090421160026.006> 04/21 15:58:21 0000:01:12 4141 88521592 A T109 001 15278 U0012237 A
<20090421160031.115> 04/21 15:59:33 0000:00:05 4141 88521592 A T109 114 001 15278 U0012237 A
<20090421160033.162> 04/21 15:59:37 5016 83578399 T110 001 15016 Q0016026 A
<20090421160046.304> 04/21 15:59:50 5016 83578399 T109 001 15016 S0016977 A
<20090421160051.335> 04/21 15:59:25 0000:00:32 T135 0008 1359 T 8010 001 0884093512 1359 Q0018134 A
<20090421160051.945> 04/21 15:59:29 0000:00:29 T118 0004 5006 5006 001 0883045157 5006 Q0017795 A
<20090421160054.242> 04/21 15:59:29 0000:00:32 5157 83045006 A T103 001 15157 O0012767 A
<20090421160058.758> 04/21 16:00:02 5016 83578399 T101
Does that help? I'm in Australia so use a dd/mm/yy format generally, but writing to sql should be using a yyyy-mm-dd hh:mm:ss format.
I doubt we would want to change the date format on all of our phone systems.
I'm sorry, I don't know model of your PBX and verify parser settings for it. The problem within the parser module. The parser should convert the date from the string type to the date/time type that the database should recognize without problems.
I'll try a few different Parser options tomorrow and find out exactly what type of system it is, I think it's a Mitel 3300, our phones are Mitel 5224 IP Phones
The format of your data is slightly different with our example (for Mitel 3300 ICP):
03/04 11:57 00:00:52 X900 899 ASX999
03/04 11:57 00:01:12 310 7062000570 A X900
03/04 11:58 00:00:44 X999 7707000706 X900
03/04 11:58 00:00:44 X999 001 97700001706 X900
03/04 11:57 00:01:21 X902 004 0059 370 370 X 373 7060009104 0059
As you see the date/time column doesn't contain seconds, the "Duration" column is short. Could change format of the output within your PBX?
It's getting there but we are not seeing some fields being mapped properly, we have the Mitel 3300 ICP.
Sample Source Data looks like this
04/22 10:57:12 0000:04:17 T130 0007 5011 5011 001 0353502336 5011 S0010557 A
04/22 11:00:45 0000:00:57 5058 *0008 99 I LGUER 99 001
04/22 11:01:26 0000:00:20 5257 0267657366 A T102 001 15257 S0015158 A
-04/22 10:54:08 0000:07:49 5162 44043784 A X9999 001 15162 M0010067 A
04/22 11:01:42 0000:00:31 5113 0006 5105 I 5105 001
04/22 11:02:18 0000:00:17 8075 0011 1314 I 1314 001
04/22 10:59:38 0000:02:59 8313 82626866 A T104 001 18313 Q0017522 A
04/22 11:02:07 0000:00:34 5101 0458964071 A T102 001 15101 Q0017797 A
04/22 11:02:18 0000:00:28 8038 82635030 A T109 001 18038 S0011851 A
Sample SQL data looks like (I've changed the duration to be a NVARCHar instead of Date/Time) because it's not translating.
ID CALL_TIME DURATION DURATION_S DIRECTION CALL_TYPE FLAG1 FLAG2 FLAG3 FLAG4 FLAG5 FLAG6 FLAG7 CALLER_PHONE DIALED_PHONE TRUNK CO ACC EXT RING COST
1 NULL 12:00:00 AM 0 NULL 0 07 50 50 NULL 17 T1 11 NULL NULL 035350233 NULL NULL
2 2009-04-22 11:00:00.000 18991230 0 NULL *0 08 99 I LG NULL 57 50 NULL NULL NULL NULL
3 2009-04-22 11:01:00.000 18991230 0 NULL 02 A T1 NULL 20 52 67657366 NULL NULL 15257 NULL NULL
4 2009-04-22 10:54:00.000 18991230 0 NULL - 44 A X9 NULL 49 51 043784 NULL NULL 15162 NULL NULL
5 2009-04-22 11:01:00.000 18991230 0 NULL 0 06 51 I 51 NULL 31 51 05 NULL NULL NULL NULL
6 2009-04-22 11:02:00.000 18991230 0 NULL 0 11 13 I 13 NULL 17 80 14 NULL NULL NULL NULL
7 2009-04-22 10:59:00.000 18991230 0 NULL 82 A T1 NULL 59 83 626866 NULL NULL 18313 NULL NULL
8 2009-04-22 11:02:00.000 18991230 0 NULL 04 A T1 NULL 34 51 58964071 NULL NULL 15101 NULL NULL
9 2009-04-22 11:02:00.000 18991230 0 NULL 82 A T1 NULL 28 80 635030 NULL NULL 18038 NULL NULL
10 2009-04-22 11:01:00.000 18991230 0 NULL 83 A T1 NULL 48 50 006124 NULL NULL 15079 NULL NULL
11 2009-04-22 11:01:00.000 18991230 0 NULL 87 A T1 NULL 55 83 551815 NULL NULL 18311 NULL NULL
12 2009-04-22 11:02:00.000 18991230 0 NULL 0 07 50 I 50 NULL 51 13 95 NULL NULL NULL NULL
13 2009-04-22 11:03:00.000 18991230 0 NULL 0 18 50 40 NULL 07 T1 55 NULL NULL 088223205 NULL NULL
14 2009-04-22 11:03:00.000 18991230 0 NULL R * 04 T1 NULL 01 LG 00626146 NULL NULL 18082 T T1 NULL NULL
15 2009-04-22 11:02:00.000 18991230 0 NULL 83 A T1 NULL 31 80 051770 NULL NULL 18038 NULL NULL
16 2009-04-22 11:00:00.000 18991230 0 NULL 08 A T1 NULL 01 50 87521933 NULL NULL 15009 NULL NULL
17 2009-04-22 11:02:00.000 18991230 0 NULL 0 01 50 40 NULL 35 T1 55 NULL NULL 043927712 NULL NULL
18 2009-04-22 11:03:00.000 18991230 0 NULL * 04 A T1 NULL 22 T1 00626146 NULL NULL 18082 NULL NULL
19 2009-04-22 11:03:00.000 18991230 0 NULL 0 19 50 40 NULL 09 T1 55 NULL NULL 088223215 NULL NULL
20 2009-04-22 11:03:00.000 18991230 0 NULL 83 A T1 NULL 28 83 037174 NULL NULL 18328 NULL NULL
21 2009-04-22 11:05:00.000 18991230 0 NULL 0 03 P9 41 NULL 11 T1 93 963 963 NULL NULL 088520212 X 80 NULL NULL
Source extension isn't appearing in the database? and Call duration
I'm sorry for my late reply.
Please, unpack the attached file (the customized parser module for your data output) to Program folder\Plugins\pbxparser\Configs, near files with same extension.
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...