<

Go Premium for a chance to win a PS4. Enter to Win

x

Cisco Call Manager 6 CDR -> SQL server

Published on
22,290 Points
14,790 Views
10 Endorsements
Last Modified:
Ever wanted to query Cisco Call Manager CDR records from MS SQL Server? Here's how!

CUCM can be configured to upload a CDR file to a given FTP server every minute. This article will show you how to set this up, schedule the import of this data and run a basic query against the data. From this point you will be able to create your own RS reports based on the imported data.

Requirements

Good knowledge of CUCM and T-SQL
FTP Server (Contactable via a UNC path from the SQL server)
 - FTP account for CDR reports with its own home directory (RW access)
MS SQL Server (I used SQL 2005 although there is no reason for this not to work in other versions)
Cisco Call Manager (I used CUCM 6.1.2, I'm sure this will work for any release of CUCM 6)

Step 1 - SQL Tables

You need to create a new database on the SQL server to house the data (e.g., "CDR") .

cdrimport.fmt
cdrimport.fmt needs to be placed locally on the SQL server, this needs to be referenced in "usp_import_data", mine is h:\sqldata\cdrimport.fmt.
9.0
78
1	SQLCHAR		0	12	","		1	cdrRecordType			""
2	SQLCHAR		0	12	","		2	globalCallID_callManagerId	""
3	SQLCHAR		0	12	","		3	globalCallID_callId		""
4	SQLCHAR		0	12	","		4	origLegCallIdentifier		""
5	SQLCHAR		0	12	","		5	dateTimeOrigination		""
6	SQLCHAR		0	12	","		6	origNodeId			""
7	SQLCHAR		0	12	","		7	origSpan			""
8	SQLCHAR		0	12	",\""		8	origIpAddr			""
9	SQLCHAR		0	50	"\",\""		9	callingPartyNumber	Latin1_General_CI_AS
10	SQLCHAR		0	50	"\","		10	callingPartyUnicodeLoginUserID	Latin1_General_CI_AS
11	SQLCHAR		0	12	","		11	origCause_location	""
12	SQLCHAR		0	12	","		12	origCause_value	""
13	SQLCHAR		0	12	","		13	origPrecedenceLevel	""
14	SQLCHAR		0	12	","		14	origMediaTransportAddress_IP	""
15	SQLCHAR		0	12	","		15	origMediaTransportAddress_Port	""
16	SQLCHAR		0	12	","		16	origMediaCap_payloadCapability	""
17	SQLCHAR		0	12	","		17	origMediaCap_maxFramesPerPacket	""
18	SQLCHAR		0	12	","		18	origMediaCap_g723BitRate	""
19	SQLCHAR		0	12	","		19	origVideoCap_Codec	""
20	SQLCHAR		0	12	","		20	origVideoCap_Bandwidth	""
21	SQLCHAR		0	12	","		21	origVideoCap_Resolution	""
22	SQLCHAR		0	12	","		22	origVideoTransportAddress_IP	""
23	SQLCHAR		0	12	",\""		23	origVideoTransportAddress_Port	""
24	SQLCHAR		0	64	"\",\""		24	origRSVPAudioStat	Latin1_General_CI_AS
25	SQLCHAR		0	64	"\","		25	origRSVPVideoStat	Latin1_General_CI_AS
26	SQLCHAR		0	12	","		26	destLegIdentifier	""
27	SQLCHAR		0	12	","		27	destNodeId	""
28	SQLCHAR		0	12	","		28	destSpan	""
29	SQLCHAR		0	12	",\""		29	destIpAddr	""
30	SQLCHAR		0	50	"\",\""		30	originalCalledPartyNumber	Latin1_General_CI_AS
31	SQLCHAR		0	50	"\",\""		31	finalCalledPartyNumber	Latin1_General_CI_AS
32	SQLCHAR		0	50	"\","		32	finalCalledPartyUnicodeLoginUserID	Latin1_General_CI_AS
33	SQLCHAR		0	12	","		33	destCause_location	""
34	SQLCHAR		0	12	","		34	destCause_value	""
35	SQLCHAR		0	12	","		35	destPrecedenceLevel	""
36	SQLCHAR		0	12	","		36	destMediaTransportAddress_IP	""
37	SQLCHAR		0	12	","		37	destMediaTransportAddress_Port	""
38	SQLCHAR		0	12	","		38	destMediaCap_payloadCapability	""
39	SQLCHAR		0	12	","		39	destMediaCap_maxFramesPerPacket	""
40	SQLCHAR		0	12	","		40	destMediaCap_g723BitRate	""
41	SQLCHAR		0	12	","		41	destVideoCap_Codec	""
42	SQLCHAR		0	12	","		42	destVideoCap_Bandwidth	""
43	SQLCHAR		0	12	","		43	destVideoCap_Resolution	""
44	SQLCHAR		0	12	","		44	destVideoTransportAddress_IP	""
45	SQLCHAR		0	12	",\""		45	destVideoTransportAddress_Port	""
46	SQLCHAR		0	64	"\",\""		46	destRSVPAudioStat	Latin1_General_CI_AS
47	SQLCHAR		0	64	"\","		47	destRSVPVideoStat	Latin1_General_CI_AS
48	SQLCHAR		0	12	","		48	dateTimeConnect	""
49	SQLCHAR		0	12	",\""		49	dateTimeDisconnect	""
50	SQLCHAR		0	50	"\",\""		50	lastRedirectDn	Latin1_General_CI_AS
51	SQLCHAR		0	50	"\",\""		51	pkid	Latin1_General_CI_AS
52	SQLCHAR		0	50	"\",\""		52	originalCalledPartyNumberPartition	Latin1_General_CI_AS
53	SQLCHAR		0	50	"\",\""		53	callingPartyNumberPartition	Latin1_General_CI_AS
54	SQLCHAR		0	50	"\",\""		54	finalCalledPartyNumberPartition	Latin1_General_CI_AS
55	SQLCHAR		0	50	"\","		55	lastRedirectDnPartition	Latin1_General_CI_AS
56	SQLCHAR		0	12	",\""		56	duration	""
57	SQLCHAR		0	129	"\",\""		57	origDeviceName	Latin1_General_CI_AS
58	SQLCHAR		0	129	"\","		58	destDeviceName	Latin1_General_CI_AS
59	SQLCHAR		0	12	","		59	origCallTerminationOnBehalfOf	""
60	SQLCHAR		0	12	","		60	destCallTerminationOnBehalfOf	""
61	SQLCHAR		0	12	","		61	origCalledPartyRedirectOnBehalfOf	""
62	SQLCHAR		0	12	","		62	lastRedirectRedirectOnBehalfOf	""
63	SQLCHAR		0	12	","		63	origCalledPartyRedirectReason	""
64	SQLCHAR		0	12	","		64	lastRedirectRedirectReason	""
65	SQLCHAR		0	12	",\""		65	destConversationId	""
66	SQLCHAR		0	50	"\","		66	globalCallId_ClusterID	Latin1_General_CI_AS
67	SQLCHAR		0	12	",\""		67	joinOnBehalfOf	""
68	SQLCHAR		0	2048	"\",\""		68	comment	Latin1_General_CI_AS
69	SQLCHAR		0	50	"\","		69	authCodeDescription	Latin1_General_CI_AS
70	SQLCHAR		0	12	",\""		70	authorizationLevel	""
71	SQLCHAR		0	32	"\","		71	clientMatterCode	Latin1_General_CI_AS
72	SQLCHAR		0	12	","		72	origDTMFMethod	""
73	SQLCHAR		0	12	","		73	destDTMFMethod	""
74	SQLCHAR		0	12	","		74	callSecuredStatus	""
75	SQLCHAR		0	12	","		75	origConversationId	""
76	SQLCHAR		0	12	","		76	origMediaCap_Bandwidth	""
77	SQLCHAR		0	12	",\""		77	destMediaCap_Bandwidth	""
78	SQLCHAR		0	32	"\n"		78	authorizationCodeValue	Latin1_General_CI_AS

Open in new window


DB Tables & Stored Procedures creation script(s)
CDR contains the actual call reports.
CREATE TABLE [dbo].[CDR](
   [cdrRecordType] [int] NOT NULL,
   [globalCallID_callManagerId] [int] NOT NULL,
   [globalCallID_callId] [int] NOT NULL,
   [origLegCallIdentifier] [int] NOT NULL,
   [dateTimeOrigination] [int] NOT NULL,
   [origNodeId] [int] NOT NULL,
   [origSpan] [int] NOT NULL,
   [origIpAddr] [int] NOT NULL,
   [callingPartyNumber] [varchar](50) NULL,
   [callingPartyUnicodeLoginUserID] [varchar](50) NULL,
   [origCause_location] [int] NOT NULL,
   [origCause_value] [int] NOT NULL,
   [origPrecedenceLevel] [int] NOT NULL,
   [origMediaTransportAddress_IP] [int] NOT NULL,
   [origMediaTransportAddress_Port] [int] NOT NULL,
   [origMediaCap_payloadCapability] [int] NOT NULL,
   [origMediaCap_maxFramesPerPacket] [int] NOT NULL,
   [origMediaCap_g723BitRate] [int] NOT NULL,
   [origVideoCap_Codec] [int] NOT NULL,
   [origVideoCap_Bandwidth] [int] NOT NULL,
   [origVideoCap_Resolution] [int] NOT NULL,
   [origVideoTransportAddress_IP] [int] NOT NULL,
   [origVideoTransportAddress_Port] [int] NOT NULL,
   [origRSVPAudioStat] [varchar](64) NULL,
   [origRSVPVideoStat] [varchar](64) NULL,
   [destLegIdentifier] [int] NOT NULL,
   [destNodeId] [int] NOT NULL,
   [destSpan] [int] NOT NULL,
   [destIpAddr] [int] NOT NULL,
   [originalCalledPartyNumber] [varchar](50) NULL,
   [finalCalledPartyNumber] [varchar](50) NULL,
   [finalCalledPartyUnicodeLoginUserID] [varchar](50) NULL,
   [destCause_location] [int] NOT NULL,
   [destCause_value] [int] NOT NULL,
   [destPrecedenceLevel] [int] NOT NULL,
   [destMediaTransportAddress_IP] [int] NOT NULL,
   [destMediaTransportAddress_Port] [int] NOT NULL,
   [destMediaCap_payloadCapability] [int] NOT NULL,
   [destMediaCap_maxFramesPerPacket] [int] NOT NULL,
   [destMediaCap_g723BitRate] [int] NOT NULL,
   [destVideoCap_Codec] [int] NOT NULL,
   [destVideoCap_Bandwidth] [int] NOT NULL,
   [destVideoCap_Resolution] [int] NOT NULL,
   [destVideoTransportAddress_IP] [int] NOT NULL,
   [destVideoTransportAddress_Port] [int] NOT NULL,
   [destRSVPAudioStat] [varchar](64) NULL,
   [destRSVPVideoStat] [varchar](64) NULL,
   [dateTimeConnect] [int] NOT NULL,
   [dateTimeDisconnect] [int] NOT NULL,
   [lastRedirectDn] [varchar](50) NULL,
   [pkid] [uniqueidentifier] NOT NULL,
   [originalCalledPartyNumberPartition] [varchar](50) NULL,
   [callingPartyNumberPartition] [varchar](50) NULL,
   [finalCalledPartyNumberPartition] [varchar](50) NULL,
   [lastRedirectDnPartition] [varchar](50) NULL,
   [duration] [int] NOT NULL,
   [origDeviceName] [varchar](129) NULL,
   [destDeviceName] [varchar](129) NULL,
   [origCallTerminationOnBehalfOf] [int] NOT NULL,
   [destCallTerminationOnBehalfOf] [int] NOT NULL,
   [origCalledPartyRedirectOnBehalfOf] [int] NOT NULL,
   [lastRedirectRedirectOnBehalfOf] [int] NOT NULL,
   [origCalledPartyRedirectReason] [int] NOT NULL,
   [lastRedirectRedirectReason] [int] NOT NULL,
   [destConversationId] [int] NOT NULL,
   [globalCallId_ClusterID] [varchar](50) NULL,
   [joinOnBehalfOf] [int] NOT NULL,
   [comment] [varchar](2048) NULL,
   [authCodeDescription] [varchar](50) NULL,
   [authorizationLevel] [int] NOT NULL,
   [clientMatterCode] [varchar](32) NULL,
   [origDTMFMethod] [int] NOT NULL,
   [destDTMFMethod] [int] NOT NULL,
   [callSecuredStatus] [int] NOT NULL,
   [origConversationId] [int] NOT NULL,
   [origMediaCap_Bandwidth] [int] NOT NULL,
   [destMediaCap_Bandwidth] [int] NOT NULL,
   [authorizationCodeValue] [varchar](32) NULL,
 CONSTRAINT [PK_CDR] PRIMARY KEY CLUSTERED 
(
   [pkid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


Files contains a record for each successfully imported CDR file.
CREATE TABLE [dbo].[Files](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [Source_Id] [int] NOT NULL,
   [filename] [nvarchar](50) NULL,
   [dts] [timestamp] NOT NULL,
 CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


Source contains the UNC path of the FTP accounts home directory mentioned above.
CREATE TABLE [dbo].[Source](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[unc_path] [nvarchar](255) NULL,
	[name] [nvarchar](50) NULL,
 CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


The table "Source" needs to populated with the UNC path of the FTP directory as described above.
INSERT INTO [CDR].[dbo].[Source]
   ([unc_path]
   ,[name])
VALUES
   ('\\server\share'
   ,'ftpserver1')

Open in new window


Call reports (CDR) also requires two Stored procedures - "sp_list_files" and "usp_import_data".
CREATE PROCEDURE [dbo].[sp_ListFiles]
   @PCWrite varchar(2000),
   @DBTable varchar(100)= NULL,
   @PCIntra varchar(100)= NULL,
   @PCExtra varchar(100)= NULL,
   @DBUltra bit = 0
AS
SET NOCOUNT ON
 
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
 
SET @Status = 0
 
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Wish varchar(2000)

SET @Work = 'DIR ' + '"' + @PCWrite + '"'
 
CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))
 
INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work
 
SET @Retain = @@ERROR
 
IF @Status = 0 SET @Status = @Retain
IF @Status = 0 SET @Status = @Return
 
IF (SELECT COUNT(*) FROM #DBAZ) < 4
   BEGIN

   SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
 
   IF @Wish IS NULL
      BEGIN
 
      RAISERROR ('General error [%d]',16,1,@Status)

      END
   ELSE
      BEGIN
 
      RAISERROR (@Wish,16,1)
 
      END
    END
ELSE
   BEGIN
   
   SET DATEFORMAT dmy
   DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING
(Name,40,1) = '.' OR Name LIKE '%.lnk'
 
   IF @DBTable IS NULL
     BEGIN
 
       SELECT SUBSTRING(Name,40,100) AS Files
       FROM #DBAZ
       WHERE 0 = 0
         AND (@DBUltra = 0 OR Name LIKE '%<DIR>%')
         AND (@DBUltra != 0 OR Name NOT LIKE '%<DIR>%')
         AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)
         AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)
       ORDER BY 1
     END
   ELSE
     BEGIN
 
     SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))
               + ' SELECT SUBSTRING(Name,40,100) AS Files'
               + '   FROM #DBAZ'
               + '  WHERE 0 = 0'
               + CASE WHEN @DBUltra = 0 THEN '' 
                      ELSE ' AND Name LIKE ' + CHAR(39) + '%<DIR>%' + CHAR(39) 
                 END
               + CASE WHEN @DBUltra != 0 THEN '' 
                      ELSE ' AND Name NOT LIKE ' + CHAR(39) + '%<DIR>%' + CHAR(39) 
                 END
               + CASE WHEN @PCIntra IS NULL THEN '' 
                      ELSE ' AND SUBSTRING(Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) 
                 END
               + CASE WHEN @PCExtra IS NULL THEN '' 
                      ELSE ' AND SUBSTRING(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) 
                 END
               + ' ORDER BY 1'
 
     IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
 
     IF @Status = 0 SET @Status = @Return
 
     END 
END
 
DROP TABLE #DBAZ
 
SET NOCOUNT OFF
 
RETURN (@Status)

Open in new window

CREATE PROCEDURE [dbo].[usp_importdata]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @pathId int, @path nvarchar(255)
DECLARE @bulk varchar(255)
DECLARE @file varchar(255)
DECLARE source_cursor CURSOR
   FOR select id as pathId, unc_path as [path] from Source
OPEN source_cursor
--iterate through paths
   FETCH NEXT FROM source_cursor into @pathId, @path
   WHILE @@FETCH_STATUS = 0
      BEGIN
      --get list of files in path
         CREATE TABLE #Files (MyFile varchar(200))
         EXECUTE sp_ListFiles @path,'#Files','%',NULL,0
         DECLARE file_cursor CURSOR
            FOR Select MyFile as [file] from #Files 
               Open file_cursor
                  FETCH NEXT FROM file_cursor into @file
                     WHILE @@FETCH_STATUS = 0
                        BEGIN
                           --import csv data in @path\@file
                           IF ( SELECT count(*) FROM Files where [filename]=@file and Source_Id=@pathId )  = 0
                           BEGIN
                              SET @Bulk = 'BULK INSERT CDR FROM ''' + @path + '\' + @file + ''' WITH (FORMATFILE=''h:\sqlfiles\cdrimport.fmt'', FIRSTROW=3) '
                              print @Bulk
                              EXEC ( @Bulk )
                                 if @@error = 0
                                    BEGIN
                                       --insert into log, so that we don't import the same file twice
                                       INSERT INTO Files ([filename], [source_Id]) 
                                       VALUES (@file,@pathId)
--                                       DECLARE @work varchar(255)
--                                       SELECT @work = 'move ' + @path + '\' + @file + ' ' + @path + '\backup\' + @file 
--                                        EXECUTE master.dbo.xp_cmdshell  @work
                                    END
                           END
                           FETCH NEXT FROM file_cursor into @file
                        END
               CLOSE file_cursor
         DEALLOCATE file_cursor
         DROP TABLE #Files
         FETCH NEXT FROM source_cursor INTO @pathId, @path
      END
CLOSE source_cursor
DEALLOCATE source_cursor

END

Open in new window


Step 2 - Call manager configuration

Log into Call Manager Serviceability
Under "Tools", Select "CDR Management"
Click on "Add New"
Populate with your FTP server details and credentials for the CDR user (as per requirements)
Click on "Add"

Wait for a few minutes and check the FTP server, you should have CDR waiting to be imported.

Step 3 - Test

Run usp_importdata on the CDR database.
Query the CDR table and ensure we have data.
select 
   callingPartyNumber, --who called
   finalCalledPartyNumber, -- where the call ended up
   originalCalledPartyNumber, -- where they tried to call
   lastRedirectDn , -- who triggered the last forward
   dateadd(second, dateTimeConnect + 36000 ,'1970-01-01') dtConnect,
   dateadd(second, dateTimeDisconnect + 36000,'1970-01-01') dtDisconnect,
   datetimedisconnect - datetimeconnect as duration
from CDR

Open in new window


Step 4 - Schedule SQL Agent job

Create a new job and schedule in the SQL Agent to execute usp_importdata every x minutes (i run mine every 5 minutes).


You're done! I hope I've helped someone. This is the first article I've written for EE, let me know what needs improving!
I'm assuming a reasonable knowledge of both SQL server and Call Manager for this, and because I didn't write the reports that point at this data, I can't include them.

10
Comment
Author:ludo_friend
  • 3
  • 3
  • 2
  • +9
20 Comments
 

Expert Comment

by:jaimebennett47
Has anyone updated this code for CCM v7.0?
0
 
LVL 8

Author Comment

by:ludo_friend
only very minor changes need to be made,  two columns are added.

outpulsedCallingPartyNumber  Text String
 This field comprises an alphanumeric string of up to 50 characters.
The calling party number gets outpulsed from the device. This field gets populated only when normalization or localization takes place at the device.
Default: Empty string "" or null.
 
outpulsedCalledPartyNumber  Text String
 This field comprises an alphanumeric string of up to 50 characters.
The called party number gets outpulsed from the device. This field gets populated only when normalization or localization takes place at the device.
Default: Empty string "" or null.
 
http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/7_0_1/cdr-defs/cdrfdes.html

it's not much additional effort, I'm happy to do it but I'm a little busy today.
0
 

Expert Comment

by:Srinivas_GT
Can you please expain what should be the paramaters values for SP's?
0
Reclaim your office - Try the MB 660 headset now!

High level of background noise often makes it difficult for employees to concentrate fully on their jobs – or to communicate clearly on calls. The MB 660 headset helps you create a disruption free workspace.  

 
LVL 8

Author Comment

by:ludo_friend
here you go...
sp_listfiles

   @PCWrite varchar(2000),

parameter is a path to a directory. The path must be accessible to SQL Server (the
service account or the proxy account).

   @DBTable varchar(100)= NULL,

parameter is a table name in which to insert the file/folder names. It can be a normal user
table or a temporary table. If no table name is provided, the list is returned
as a result set.

   @PCIntra varchar(100)= NULL,

parameter is a filter for including certain names. Each name is compared to
the filter using a LIKE operator, so wildcards are acceptable. For example, the
value "%.doc" would include all Word documents.

   @PCExtra varchar(100)= NULL,

parameter is a filter for excluding certain names. Each name is compared to
the filter using a NOT LIKE operator, so wildcards are acceptable.

   @DBUltra bit = 0

parameter determines whether files or folders are listed. A value of zero (0)
returns files and a value of one (1) returns folders.
0
 

Expert Comment

by:Srinivas_GT
Thanks for explanation!

I had inserted \\FTPServer  into Source table

INSERT INTO [CDR].[dbo].[Source]
   ([unc_path]
   ,[name])
VALUES
   ('\\FTPServer ’
   ,'ftpserver')


Here is the UNC path \\FTPServer\d$\ROOT\abc and file type “cdr_abc-xyz_01_201009161841_10456”  (Format is file)

In usp_importdata.

SET @Bulk = 'BULK INSERT CDR FROM ''' + @path + '\' + @file + ''' WITH (FORMATFILE=''d:\ROOT\abc\cdr_abc-xyz_01_201009161841_10456'', FIRSTROW=3) '

In this Scenario both FTP Server and SQL instance is same machine. Correct me if I am doing any wrong, I am confussed :(.
0
 

Expert Comment

by:Srinivas_GT
Never mind I achieved my goal using simple SSIS package. Thanks for your help.
0
 

Expert Comment

by:Donald21
Do you know if there is updates required CCM v8.5
0
 

Expert Comment

by:zakwithu2012
I have updated the script for  CCM v8.5
 but i faced one issue. the first row after the header and datatype rows is always been skipped.
i'm trying to fix it
0
 

Expert Comment

by:kjmamounj
i face one thing . it stop import from 13th of April  to 30 of April , then it start from 1st of may tell today (V 6.1.2) , i could not find the error because it is not show any thing ,  i compare between the content of the files that imported and file that not , i could not find any different
what  you think the different .
0
 

Expert Comment

by:kjmamounj
i found that the code is only import between 1st of month to end of 12 of month
but from where it take this option i can't find.
0
 

Expert Comment

by:YatteYattah
zakwithu2012 did you manage to work around the first row being skipped?

I have also updated the the scripts and queries to 8.5, all works well except the first row being skipped is annoying me.

Any ideas anyone?
0
 

Expert Comment

by:zakwithu2012
Yes, I create a script to delete the first line before it been processed. then you need to modify line 28  to start reading the file from the first line instead of the 3rd line, like below:

SET @Bulk = 'BULK INSERT CDR FROM ''' + @path + '\' + @file + ''' WITH (FORMATFILE=''h:\sqlfiles\cdrimport.fmt'', FIRSTROW=1) '

Open in new window

0
 

Expert Comment

by:YatteYattah
Thanks zakwithu2012, great idea. Did you use SQL to delete the first line? Any chance you can share the code?
0
 
LVL 3

Expert Comment

by:dtrance
I am using 8.5 and can not get this to work.
CREATE TABLE [dbo].[cdr](
	[cdrRecordType] [int] NULL,
	[globalCallID_callManagerId] [int] NULL,
	[globalCallID_callId] [int] NULL,
	[origLegCallIdentifier] [int] NULL,
	[dateTimeOrigination] [int] NULL,
	[origNodeId] [int] NULL,
	[origSpan] [int] NULL,
	[origIpAddr] [int] NULL,
	[callingPartyNumber] [varchar](50) NULL,
	[callingPartyUnicodeLoginUserID] [varchar](128) NULL,
	[origCause_location] [int] NULL,
	[origCause_value] [int] NULL,
	[origPrecedenceLevel] [int] NULL,
	[origMediaTransportAddress_IP] [int] NULL,
	[origMediaTransportAddress_Port] [int] NULL,
	[origMediaCap_payloadCapability] [int] NULL,
	[origMediaCap_maxFramesPerPacket] [int] NULL,
	[origMediaCap_g723BitRate] [int] NULL,
	[origVideoCap_Codec] [int] NULL,
	[origVideoCap_Bandwidth] [int] NULL,
	[origVideoCap_Resolution] [int] NULL,
	[origVideoTransportAddress_IP] [int] NULL,
	[origVideoTransportAddress_Port] [int] NULL,
	[origRSVPAudioStat] [varchar](64) NULL,
	[origRSVPVideoStat] [varchar](64) NULL,
	[destLegIdentifier] [int] NULL,
	[destNodeId] [int] NULL,
	[destSpan] [int] NULL,
	[destIpAddr] [int] NULL,
	[originalCalledPartyNumber] [varchar](50) NULL,
	[finalCalledPartyNumber] [varchar](50) NULL,
	[finalCalledPartyUnicodeLoginUserID] [varchar](128) NULL,
	[destCause_location] [int] NULL,
	[destCause_value] [int] NULL,
	[destPrecedenceLevel] [int] NULL,
	[destMediaTransportAddress_IP] [int] NULL,
	[destMediaTransportAddress_Port] [int] NULL,
	[destMediaCap_payloadCapability] [int] NULL,
	[destMediaCap_maxFramesPerPacket] [int] NULL,
	[destMediaCap_g723BitRate] [int] NULL,
	[destVideoCap_Codec] [int] NULL,
	[destVideoCap_Bandwidth] [int] NULL,
	[destVideoCap_Resolution] [int] NULL,
	[destVideoTransportAddress_IP] [int] NULL,
	[destVideoTransportAddress_Port] [int] NULL,
	[destRSVPAudioStat] [varchar](64) NULL,
	[destRSVPVideoStat] [varchar](64) NULL,
	[dateTimeConnect] [int] NULL,
	[dateTimeDisconnect] [int] NULL,
	[lastRedirectDn] [varchar](50) NULL,
	[pkid] [uniqueidentifier] NULL,
	[originalCalledPartyNumberPartition] [varchar](50) NULL,
	[callingPartyNumberPartition] [varchar](50) NULL,
	[finalCalledPartyNumberPartition] [varchar](50) NULL,
	[lastRedirectDnPartition] [varchar](50) NULL,
	[duration] [int] NULL,
	[origDeviceName] [varchar](129) NULL,
	[destDeviceName] [varchar](129) NULL,
	[origCallTerminationOnBehalfOf] [int] NULL,
	[destCallTerminationOnBehalfOf] [int] NULL,
	[origCalledPartyRedirectOnBehalfOf] [int] NULL,
	[lastRedirectRedirectOnBehalfOf] [int] NULL,
	[origCalledPartyRedirectReason] [int] NULL,
	[lastRedirectRedirectReason] [int] NULL,
	[destConversationId] [int] NULL,
	[globalCallId_ClusterID] [varchar](50) NULL,
	[joinOnBehalfOf] [int] NULL,
	[comment] [varchar](2048) NULL,
	[authCodeDescription] [varchar](50) NULL,
	[authorizationLevel] [int] NULL,
	[clientMatterCode] [varchar](32) NULL,
	[origDTMFMethod] [int] NULL,
	[destDTMFMethod] [int] NULL,
	[callSecuredStatus] [int] NULL,
	[origConversationId] [int] NULL,
	[origMediaCap_Bandwidth] [int] NULL,
	[destMediaCap_Bandwidth] [int] NULL,
	[authorizationCodeValue] [varchar](32) NULL,
	[outpulsedCallingPartyNumber] [varchar](50) NULL,
	[outpulsedCalledPartyNumber] [varchar](50) NULL,
	[origIpv4v6Addr] [varchar](64) NULL,
	[destIpv4v6Addr] [varchar](64) NULL,
	[origVideoCap_Codec_Channel2] [int] NULL,
	[origVideoCap_Bandwidth_Channel2] [int] NULL,
	[origVideoCap_Resolution_Channel2] [int] NULL,
	[origVideoTransportAddress_IP_Channel2] [int] NULL,
	[origVideoTransportAddress_Port_Channel2] [int] NULL,
	[origVideoChannel_Role_Channel2] [int] NULL,
	[destVideoCap_Codec_Channel2] [int] NULL,
	[destVideoCap_Bandwidth_Channel2] [int] NULL,
	[destVideoCap_Resolution_Channel2] [int] NULL,
	[destVideoTransportAddress_IP_Channel2] [int] NULL,
	[destVideoTransportAddress_Port_Channel2] [int] NULL,
	[destVideoChannel_Role_Channel2] [int] NULL,
	[IncomingProtocolID] [int] NULL,
	[IncomingProtocolCallRef] [varchar](32) NULL,
	[OutgoingProtocolID] [int] NULL,
	[OutgoingProtocolCallRef] [varchar](32) NULL,
	[currentRoutingReason] [int] NULL,
	[origRoutingReason] [int] NULL,
	[lastRedirectingRoutingReason] [int] NULL,
	[huntPilotPartition] [varchar](50) NULL,
	[huntPilotDN] [varchar](50) NULL,
	[calledPartyPatternUsage] [int] NULL
)

Open in new window


fmt file generated using bcp

10.0
104
1       SQLCHAR             0       12      "\t"     1     cdrRecordType                                                                      ""
2       SQLCHAR             0       12      "\t"     2     globalCallID_callManagerId                                                         ""
3       SQLCHAR             0       12      "\t"     3     globalCallID_callId                                                                ""
4       SQLCHAR             0       12      "\t"     4     origLegCallIdentifier                                                              ""
5       SQLCHAR             0       12      "\t"     5     dateTimeOrigination                                                                ""
6       SQLCHAR             0       12      "\t"     6     origNodeId                                                                         ""
7       SQLCHAR             0       12      "\t"     7     origSpan                                                                           ""
8       SQLCHAR             0       12      "\t"     8     origIpAddr                                                                         ""
9       SQLCHAR             0       50      "\t"     9     callingPartyNumber                                                                 SQL_Latin1_General_CP1_CI_AS
10      SQLCHAR             0       128     "\t"     10    callingPartyUnicodeLoginUserID                                                     SQL_Latin1_General_CP1_CI_AS
11      SQLCHAR             0       12      "\t"     11    origCause_location                                                                 ""
12      SQLCHAR             0       12      "\t"     12    origCause_value                                                                    ""
13      SQLCHAR             0       12      "\t"     13    origPrecedenceLevel                                                                ""
14      SQLCHAR             0       12      "\t"     14    origMediaTransportAddress_IP                                                       ""
15      SQLCHAR             0       12      "\t"     15    origMediaTransportAddress_Port                                                     ""
16      SQLCHAR             0       12      "\t"     16    origMediaCap_payloadCapability                                                     ""
17      SQLCHAR             0       12      "\t"     17    origMediaCap_maxFramesPerPacket                                                    ""
18      SQLCHAR             0       12      "\t"     18    origMediaCap_g723BitRate                                                           ""
19      SQLCHAR             0       12      "\t"     19    origVideoCap_Codec                                                                 ""
20      SQLCHAR             0       12      "\t"     20    origVideoCap_Bandwidth                                                             ""
21      SQLCHAR             0       12      "\t"     21    origVideoCap_Resolution                                                            ""
22      SQLCHAR             0       12      "\t"     22    origVideoTransportAddress_IP                                                       ""
23      SQLCHAR             0       12      "\t"     23    origVideoTransportAddress_Port                                                     ""
24      SQLCHAR             0       64      "\t"     24    origRSVPAudioStat                                                                  SQL_Latin1_General_CP1_CI_AS
25      SQLCHAR             0       64      "\t"     25    origRSVPVideoStat                                                                  SQL_Latin1_General_CP1_CI_AS
26      SQLCHAR             0       12      "\t"     26    destLegIdentifier                                                                  ""
27      SQLCHAR             0       12      "\t"     27    destNodeId                                                                         ""
28      SQLCHAR             0       12      "\t"     28    destSpan                                                                           ""
29      SQLCHAR             0       12      "\t"     29    destIpAddr                                                                         ""
30      SQLCHAR             0       50      "\t"     30    originalCalledPartyNumber                                                          SQL_Latin1_General_CP1_CI_AS
31      SQLCHAR             0       50      "\t"     31    finalCalledPartyNumber                                                             SQL_Latin1_General_CP1_CI_AS
32      SQLCHAR             0       128     "\t"     32    finalCalledPartyUnicodeLoginUserID                                                 SQL_Latin1_General_CP1_CI_AS
33      SQLCHAR             0       12      "\t"     33    destCause_location                                                                 ""
34      SQLCHAR             0       12      "\t"     34    destCause_value                                                                    ""
35      SQLCHAR             0       12      "\t"     35    destPrecedenceLevel                                                                ""
36      SQLCHAR             0       12      "\t"     36    destMediaTransportAddress_IP                                                       ""
37      SQLCHAR             0       12      "\t"     37    destMediaTransportAddress_Port                                                     ""
38      SQLCHAR             0       12      "\t"     38    destMediaCap_payloadCapability                                                     ""
39      SQLCHAR             0       12      "\t"     39    destMediaCap_maxFramesPerPacket                                                    ""
40      SQLCHAR             0       12      "\t"     40    destMediaCap_g723BitRate                                                           ""
41      SQLCHAR             0       12      "\t"     41    destVideoCap_Codec                                                                 ""
42      SQLCHAR             0       12      "\t"     42    destVideoCap_Bandwidth                                                             ""
43      SQLCHAR             0       12      "\t"     43    destVideoCap_Resolution                                                            ""
44      SQLCHAR             0       12      "\t"     44    destVideoTransportAddress_IP                                                       ""
45      SQLCHAR             0       12      "\t"     45    destVideoTransportAddress_Port                                                     ""
46      SQLCHAR             0       64      "\t"     46    destRSVPAudioStat                                                                  SQL_Latin1_General_CP1_CI_AS
47      SQLCHAR             0       64      "\t"     47    destRSVPVideoStat                                                                  SQL_Latin1_General_CP1_CI_AS
48      SQLCHAR             0       12      "\t"     48    dateTimeConnect                                                                    ""
49      SQLCHAR             0       12      "\t"     49    dateTimeDisconnect                                                                 ""
50      SQLCHAR             0       50      "\t"     50    lastRedirectDn                                                                     SQL_Latin1_General_CP1_CI_AS
51      SQLCHAR             0       37      "\t"     51    pkid                                                                               ""
52      SQLCHAR             0       50      "\t"     52    originalCalledPartyNumberPartition                                                 SQL_Latin1_General_CP1_CI_AS
53      SQLCHAR             0       50      "\t"     53    callingPartyNumberPartition                                                        SQL_Latin1_General_CP1_CI_AS
54      SQLCHAR             0       50      "\t"     54    finalCalledPartyNumberPartition                                                    SQL_Latin1_General_CP1_CI_AS
55      SQLCHAR             0       50      "\t"     55    lastRedirectDnPartition                                                            SQL_Latin1_General_CP1_CI_AS
56      SQLCHAR             0       12      "\t"     56    duration                                                                           ""
57      SQLCHAR             0       129     "\t"     57    origDeviceName                                                                     SQL_Latin1_General_CP1_CI_AS
58      SQLCHAR             0       129     "\t"     58    destDeviceName                                                                     SQL_Latin1_General_CP1_CI_AS
59      SQLCHAR             0       12      "\t"     59    origCallTerminationOnBehalfOf                                                      ""
60      SQLCHAR             0       12      "\t"     60    destCallTerminationOnBehalfOf                                                      ""
61      SQLCHAR             0       12      "\t"     61    origCalledPartyRedirectOnBehalfOf                                                  ""
62      SQLCHAR             0       12      "\t"     62    lastRedirectRedirectOnBehalfOf                                                     ""
63      SQLCHAR             0       12      "\t"     63    origCalledPartyRedirectReason                                                      ""
64      SQLCHAR             0       12      "\t"     64    lastRedirectRedirectReason                                                         ""
65      SQLCHAR             0       12      "\t"     65    destConversationId                                                                 ""
66      SQLCHAR             0       50      "\t"     66    globalCallId_ClusterID                                                             SQL_Latin1_General_CP1_CI_AS
67      SQLCHAR             0       12      "\t"     67    joinOnBehalfOf                                                                     ""
68      SQLCHAR             0       2048    "\t"     68    comment                                                                            SQL_Latin1_General_CP1_CI_AS
69      SQLCHAR             0       50      "\t"     69    authCodeDescription                                                                SQL_Latin1_General_CP1_CI_AS
70      SQLCHAR             0       12      "\t"     70    authorizationLevel                                                                 ""
71      SQLCHAR             0       32      "\t"     71    clientMatterCode                                                                   SQL_Latin1_General_CP1_CI_AS
72      SQLCHAR             0       12      "\t"     72    origDTMFMethod                                                                     ""
73      SQLCHAR             0       12      "\t"     73    destDTMFMethod                                                                     ""
74      SQLCHAR             0       12      "\t"     74    callSecuredStatus                                                                  ""
75      SQLCHAR             0       12      "\t"     75    origConversationId                                                                 ""
76      SQLCHAR             0       12      "\t"     76    origMediaCap_Bandwidth                                                             ""
77      SQLCHAR             0       12      "\t"     77    destMediaCap_Bandwidth                                                             ""
78      SQLCHAR             0       32      "\t"     78    authorizationCodeValue                                                             SQL_Latin1_General_CP1_CI_AS
79      SQLCHAR             0       50      "\t"     79    outpulsedCallingPartyNumber                                                        SQL_Latin1_General_CP1_CI_AS
80      SQLCHAR             0       50      "\t"     80    outpulsedCalledPartyNumber                                                         SQL_Latin1_General_CP1_CI_AS
81      SQLCHAR             0       64      "\t"     81    origIpv4v6Addr                                                                     SQL_Latin1_General_CP1_CI_AS
82      SQLCHAR             0       64      "\t"     82    destIpv4v6Addr                                                                     SQL_Latin1_General_CP1_CI_AS
83      SQLCHAR             0       12      "\t"     83    origVideoCap_Codec_Channel2                                                        ""
84      SQLCHAR             0       12      "\t"     84    origVideoCap_Bandwidth_Channel2                                                    ""
85      SQLCHAR             0       12      "\t"     85    origVideoCap_Resolution_Channel2                                                   ""
86      SQLCHAR             0       12      "\t"     86    origVideoTransportAddress_IP_Channel2                                              ""
87      SQLCHAR             0       12      "\t"     87    origVideoTransportAddress_Port_Channel2                                            ""
88      SQLCHAR             0       12      "\t"     88    origVideoChannel_Role_Channel2                                                     ""
89      SQLCHAR             0       12      "\t"     89    destVideoCap_Codec_Channel2                                                        ""
90      SQLCHAR             0       12      "\t"     90    destVideoCap_Bandwidth_Channel2                                                    ""
91      SQLCHAR             0       12      "\t"     91    destVideoCap_Resolution_Channel2                                                   ""
92      SQLCHAR             0       12      "\t"     92    destVideoTransportAddress_IP_Channel2                                              ""
93      SQLCHAR             0       12      "\t"     93    destVideoTransportAddress_Port_Channel2                                            ""
94      SQLCHAR             0       12      "\t"     94    destVideoChannel_Role_Channel2                                                     ""
95      SQLCHAR             0       12      "\t"     95    IncomingProtocolID                                                                 ""
96      SQLCHAR             0       32      "\t"     96    IncomingProtocolCallRef                                                            SQL_Latin1_General_CP1_CI_AS
97      SQLCHAR             0       12      "\t"     97    OutgoingProtocolID                                                                 ""
98      SQLCHAR             0       32      "\t"     98    OutgoingProtocolCallRef                                                            SQL_Latin1_General_CP1_CI_AS
99      SQLCHAR             0       12      "\t"     99    currentRoutingReason                                                               ""
100     SQLCHAR             0       12      "\t"     100   origRoutingReason                                                                  ""
101     SQLCHAR             0       12      "\t"     101   lastRedirectingRoutingReason                                                       ""
102     SQLCHAR             0       50      "\t"     102   huntPilotPartition                                                                 SQL_Latin1_General_CP1_CI_AS
103     SQLCHAR             0       50      "\t"     103   huntPilotDN                                                                        SQL_Latin1_General_CP1_CI_AS
104     SQLCHAR             0       12      "\r\n"   104   calledPartyPatternUsage                                                            ""

Open in new window


I have the logs coming in fine and the files table populates with all the logs but I get no data in the cdr table.
0
 
LVL 1

Expert Comment

by:saifb
please note that will impact the performance of the call manager,

if you're getting data each minute, these requests will cause high AXL requests causing call manager to crash due to high CPU and memory utilization,

alternatively, you can use the CDR to send to a billing server.
0
 
LVL 13

Expert Comment

by:Bryant Schaper
I stumbled on this recently, and I cannot get it to work either, we are running the 9, and I used BCP to create the formatfile but I noticed that everything is SQLCHAR and the CDR file specifies int, VARCHAR and such which is how I built my table.

This the error:

Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 9. Verify that the field terminator and row terminator are specified correctly.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".


Running SQL 2014 Express
0
 

Expert Comment

by:dwilliamhouston
I obviously have a permissions issue but cant figure out if its a SQL Server issue writing to the temp table or if its an issue opening up my UNC fileshare.

Msg 50000, Level 16, State 1, Procedure sp_ListFiles, Line 45
Access is denied.

(1 row(s) affected)

Any assistance would be appreciated.
0
 

Expert Comment

by:dwilliamhouston
I have narrowed the Access Denied to this line

SELECT @Wish = Name FROM #DBAZ WHERE Work = 1

I am running this on SQL Server 2014 if that helps.
0
 

Expert Comment

by:dwilliamhouston
I have found the issue was access denied to my file share.
0
 

Expert Comment

by:simple72
HI,
my cdr files are without header so what are the modifications i have to do with above code,

and i am using my pc as server so how i have to give unc path and its name please guide me??
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Join & Write a Comment

Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month