Cisco Call Manager 6 CDR -> SQL server

Published:
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
17,910 Views

Comments (20)

CERTIFIED EXPERT

Commented:
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
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.
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.
I have found the issue was access denied to my file share.

Commented:
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??

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.