<

Cisco Call Manager 6 CDR -> SQL server

Published on
23,883 Points
16,383 Views
10 Endorsements
Last Modified:
Approved
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
Author:ludo_friend
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free