Importing Cisco UC540 Data into SQL

23 Oct

This is quite a long post, but basically I wanted to get the Cisco call information into a SQL database so that I could perform some regular analysis, the next stage is to create a nice .net front end to display all this information with graphs and that.

This is only V1, is omits any transfers after the original call, it basically displays the same as you would find on http://uc540.ip.address/ccme.html

uc540_1

This is the outline of what’s happening here:

  • FTP CDR records from UC540 to FTP Server
  • Stored Procedure then does the following:
  • Gets a list of all raw files
  • Imports these into a single table
  • Cleans that data
  • Exports that to a CSV
  • Imports that into a secondary temporary table
  • Grab the relevant (Internal, Inbound, Outbound) data and import that into a final log table

Firstly then, from the UC540 navigate to Cisco Configuration Assistant (CCA) and navigate to Application > General Settings > Call Accounting and in there set the following:

uc540_2

(Note: FTP URL should be similar to: ‘ftp://x.x.x.x/’)

I then installed FileZilla FTP server on an internal server and set the home directory to c:\CiscoLogs\files

I could then see CDR files (in the format ‘.UC_540.10_MM_YYYY_HH_mm_SS.MS’) coming into the FTS server but the odd thing was that the UC540 was writing the same files name with different information every few minutes, a new files was created each hour but by this point I would have lost the information from earlier that hour. My SQL later on rectifies this issue.

All of the rest of this is taken care of by SQL. The code is not the neatest, and probably not the most efficient but for now, and V1.0, it seems to work.

It’s worth noting that the CDR files are CSV files but they aren’t always that neat and so a bit of cleaning up was required. There are also 129 fields for each row and each call can have anywhere from 1 to X number of lines, so there’s quite a bit of information to process.

The CRR data appears to be in NTP format, thanks to Pete Yates for the SQL to convert this to a useful DateTime format:

Declare @string varchar(32)
set @string = '09:59:25.837 GMT Fri Sep 26 2014'
select (convert(datetime, substring(@string, 22, 11))+convert(datetime, substring(@string,0 , 13)))

See here for the Cisco explanation of all 129 fields in the CSV.

So, into SQL then and the first thing was to create the tables I needed

UC540_log_calldirection

This basically lists internal, inbound and outbound

CREATE TABLE [dbo].[uc540_log_calldirection](
[in_out] [int] NULL,
[description] [varchar](20) NULL
) ON [PRIMARY]

UC540_files

This is where all the file names are stored

CREATE TABLE [dbo].[uc540_files](
[filename] [varchar](255) NULL
) ON [PRIMARY]

UC540_log

This is the final log table where my clean data will sit

CREATE TABLE [dbo].[uc540_log](
[in_out] [int] NULL,
[call_id] [bigint] NULL,
[alert_time] [datetime] NULL,
[connect_time] [datetime] NULL,
[disconnect_time] [datetime] NULL,
[externally_dialed] [varchar](30) NULL,
[call_from] [varchar](30) NULL,
[call_to] [varchar](30) NULL
) ON [PRIMARY]

UC540_log_import

This is the table that will import the raw data from the CDR files

CREATE TABLE [dbo].[uc540_log_import](
[data] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

UC540_log_temp

This is the table with all 129 fields

CREATE TABLE [dbo].[uc540_log_temp](
[unix_time] [varchar](255) NULL,
[call-id] [varchar](255) NULL,
[cdr-type] [varchar](255) NULL,
[leg-type] [varchar](255) NULL,
[h323-conf-id] [varchar](255) NULL,
[peer-address] [varchar](255) NULL,
[peer-sub-address] [varchar](255) NULL,
[h323-setup-time] [varchar](255) NULL,
[alert-time] [varchar](255) NULL,
[h323-connect-time] [varchar](255) NULL,
[h323-disconnect-time] [varchar](255) NULL,
[h323-disconnect-cause] [varchar](255) NULL,
[disconnect-text] [varchar](255) NULL,
[h323-call-origin] [varchar](255) NULL,
[charged-units] [varchar](255) NULL,
[info-type] [varchar](255) NULL,
[paks-out] [varchar](255) NULL,
[bytes-out] [varchar](255) NULL,
[paks-in] [varchar](255) NULL,
[bytes-in] [varchar](255) NULL,
[username] [varchar](255) NULL,
[clid] [varchar](255) NULL,
[dnis] [varchar](255) NULL,
[gtd-orig-cic] [varchar](255) NULL,
[gtd-term-cic] [varchar](255) NULL,
[tx-duration] [varchar](255) NULL,
[peer-id] [varchar](255) NULL,
[peer-if-index] [varchar](255) NULL,
[logical-if-index] [varchar](255) NULL,
[acom-level] [varchar](255) NULL,
[noise-level] [varchar](255) NULL,
[voice-tx-duration] [varchar](255) NULL,
[account-code] [varchar](255) NULL,
[codec-bytes] [varchar](255) NULL,
[codec-type-rate] [varchar](255) NULL,
[ontime-rv-playout] [varchar](255) NULL,
[remote-udp-port] [varchar](255) NULL,
[remote-media-udp-port] [varchar](255) NULL,
[vad-enable] [varchar](255) NULL,
[receive-delay] [varchar](255) NULL,
[round-trip-delay] [varchar](255) NULL,
[hiwater-playout-delay] [varchar](255) NULL,
[lowater-playout-delay] [varchar](255) NULL,
[gapfill-with-interpolation] [varchar](255) NULL,
[gapfill-with-redundancy] [varchar](255) NULL,
[gapfill-with-silence] [varchar](255) NULL,
[gapfill-with-prediction] [varchar](255) NULL,
[early-packets] [varchar](255) NULL,
[late-packets] [varchar](255) NULL,
[lost-packets] [varchar](255) NULL,
[max-bitrate] [varchar](255) NULL,
[faxrelay-start-time] [varchar](255) NULL,
[faxrelay-stop-time] [varchar](255) NULL,
[faxrelay-max-jit-buf-depth] [varchar](255) NULL,
[faxrelay-jit-buf-ovflow] [varchar](255) NULL,
[faxrelay-init-hs-mod] [varchar](255) NULL,
[faxrelay-mr-hs-mod] [varchar](255) NULL,
[faxrelay-num-pages] [varchar](255) NULL,
[faxrelay-tx-packets] [varchar](255) NULL,
[faxrelay-rx-packets] [varchar](255) NULL,
[faxrelay-direction] [varchar](255) NULL,
[faxrelay-pkt-conceal] [varchar](255) NULL,
[faxrelay-ecm-status] [varchar](255) NULL,
[faxrelay-encap-protocol] [varchar](255) NULL,
[faxrelay-nsf-country-code] [varchar](255) NULL,
[faxrelay-nsf-manuf-code] [varchar](255) NULL,
[faxrelay-fax-success] [varchar](255) NULL,
[override-session-time] [varchar](255) NULL,
[h323-ivr-out] [varchar](255) NULL,
[internal-error-code] [varchar](255) NULL,
[h323-voice-quality] [varchar](255) NULL,
[remote-media-address] [varchar](255) NULL,
[remote-media-id] [varchar](255) NULL,
[carrier-id] [varchar](255) NULL,
[calling-party-category] [varchar](255) NULL,
[originating-line-info] [varchar](255) NULL,
[charge-number] [varchar](255) NULL,
[transmission-medium-req] [varchar](255) NULL,
[service-descriptor] [varchar](255) NULL,
[outgoing-area] [varchar](255) NULL,
[incoming-area] [varchar](255) NULL,
[out-trunkgroup-label] [varchar](255) NULL,
[out-carrier-id] [varchar](255) NULL,
[dsp-id] [varchar](255) NULL,
[in-trunkgroup-label] [varchar](255) NULL,
[in-carrier-id] [varchar](255) NULL,
[cust-biz-grp-id] [varchar](255) NULL,
[supp-svc-xfer-by] [varchar](255) NULL,
[voice-feature] [varchar](255) NULL,
[feature-operation] [varchar](255) NULL,
[feature-op-status] [varchar](255) NULL,
[feature-op-time] [varchar](255) NULL,
[feature-id] [varchar](255) NULL,
[gw-rxd-cdn] [varchar](255) NULL,
[gw-rxd-cgn] [varchar](255) NULL,
[gtd-gw-rxd-ocn] [varchar](255) NULL,
[gtd-gw-rxd-cnn] [varchar](255) NULL,
[gw-rxd-rdn] [varchar](255) NULL,
[gw-final-xlated-cdn] [varchar](255) NULL,
[gw-final-xlated-cgn] [varchar](255) NULL,
[gw-final-xlated-rdn] [varchar](255) NULL,
[gk-xlated-cdn] [varchar](255) NULL,
[gk-xlated-cgn] [varchar](255) NULL,
[gw-collected-cdn] [varchar](255) NULL,
[ip-hop] [varchar](255) NULL,
[redirected-station] [varchar](255) NULL,
[subscriber] [varchar](255) NULL,
[in-intrfc-desc] [varchar](255) NULL,
[out-intrfc-desc] [varchar](255) NULL,
[session-protocol] [varchar](255) NULL,
[local-hostname] [varchar](255) NULL,
[backward-call-id] [varchar](255) NULL,
[feature-id_field1] [varchar](255) NULL,
[feature-id_field2] [varchar](255) NULL,
[feature-id_field3] [varchar](255) NULL,
[feature-id_field4] [varchar](255) NULL,
[feature-id_field5] [varchar](255) NULL,
[feature-id_field6] [varchar](255) NULL,
[feature-id_field7] [varchar](255) NULL,
[feature-id_field8] [varchar](255) NULL,
[feature-id_field9] [varchar](255) NULL,
[feature-id_field10] [varchar](255) NULL,
[feature-id_field11] [varchar](255) NULL,
[feature-id_field12] [varchar](255) NULL,
[ip-phone-info] [varchar](255) NULL,
[ip-pbx-mode] [varchar](255) NULL,
[in-lpcor-group] [varchar](255) NULL,
[out-lpcor-group] [varchar](255) NULL,
[fac-digit] [varchar](255) NULL,
[fac-status] [varchar](255) NULL
) ON [PRIMARY]

The data for UC540_log_calldirection is as follows:

  • 0, Outbound
  • 1, Inbound
  • 2, Internal

I’ve also created a view but that calls in a users table with their extension and won’t be of any use to anyone else.

With the tables created I then have a SQL job that runs every minute and it calls a single stored procedure, this was where most of my time went. All of this is commented, so I’ll let that provide the explanation here.

CREATE procedure [dbo].[sp_importcalllogs]

AS

--Clear files from list of raw files temporary table
Delete from uc540_files

--Set some variables
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@cmd varchar(1000)

--Import list of raw files into SQL
SET @path = 'C:\CiscoLogs\files\'
SET @cmd = 'dir ' + @path + '*.* /b'
INSERT INTO uc540_files("filename")
EXEC Master..xp_cmdShell @cmd

--Delete the archive folder entry from this table so it doesnt try to process that and error
delete from uc540_files where "filename" = 'archive'

--Empty the current temporary log import files table
delete from uc540_log_import

--Loop through each raw file and import the information into the log table
declare c1 cursor for SELECT "filename" FROM uc540_files

open c1

fetch next from c1 into @filename
While @@fetch_status <> -1
begin

--bulk insert won't take a variable name, so make a sql and execute it instead:
set @sql = 'BULK INSERT uc540_log_import FROM ''' + @path + @filename + ''' '
+ ' WITH (
ROWTERMINATOR = ''\n''
) '
print @sql
exec (@sql)

--Set some time and date variables to use
DECLARE
@TodayDate as varchar(40),
@TodayHour as varchar(40),
@TodayMinu as varchar(40)

SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayMinu = DATEPART(mi,GETDATE())

--Now move that file to the archive folder so that it doesnt get processed again. Also rename it as there could be a file later on with different information the we need
--(cisco has files named every hour, but they are moved more frequently, so this file could appear again with completely different information!)

--The 'if' stament checks if its already been renamed (for testing I was moving the files back and they kept getting renamed and bcame very long!)
SET @cmd = 'move ' + @path + @filename + ' ' + @path + '\archive'
if (left(@filename,1) = '.')
begin
--if its not already renamed then add the date and time prefix to it, and name it as a .log also
SET @cmd = 'move ' + @path + @filename + ' ' + @path + '\archive\' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '_' + @filename + '.log'
EXEC Master..xp_cmdShell @cmd
end

EXEC Master..xp_cmdShell @cmd

fetch next from c1 into @filename
end
close c1
deallocate c1

--Now remove any entries from the import table that are not correct (not sure why cisco does this mind, a value of 600 seemed to work well here)
delete from uc540_log_import where len(data) <= 600 or data IS null --Now that the data is 'clean' let's move it to the temp table and split it by comma's --I couldn't find a better way in sql to achieve this, so im exporting it to a single text file to later re-import into SQL select @cmd = 'bcp "select * from DATABASENAME.dbo.uc540_log_import" queryout "C:\CiscoLogs\temp\output.csv" -T -c -t' exec master..xp_cmdshell @cmd --Clear out the temp table delete from uc540_log_temp --Now all the data needs to be split by comma and imported to the temp table BULK INSERT uc540_log_temp FROM 'c:\ciscologs\temp\output.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) --Now that the data is split, let's copy the relevant unique data to the live log table (being sure not to copy over any pre-existing items as we would end up with duplicate entries) --The column in_out: 0 = out, 1 = In, 2 = Internal (including voicemail) --For some reason some of the connect and disconnect date/times start with ". and others just " no idea why this is but I strip the dots out anyway --Firstly import the inbound calls insert into uc540_log select distinct '1' as "in_out", "call-id" as 'call_id', convert(datetime, substring(replace(replace("h323-setup-time", '".', ''), '"', ''), 22, 11))+convert(datetime, substring(replace(replace("h323-setup-time", '".', ''), '"', ''),0 , 13)) as 'alert_time', convert(datetime, substring( replace(replace("h323-connect-time", '".', ''), '"', '') , 22, 11))+convert(datetime, substring( replace(replace("h323-connect-time", '".', ''), '"', '') ,0 , 13)) as 'connect_time', convert(datetime, substring( replace(replace("h323-disconnect-time", '".', ''), '"', '') , 22, 11))+convert(datetime, substring( replace(replace("h323-disconnect-time", '".', ''), '"', '') ,0 , 13)) as 'disconnect_time', replace(dnis, '"', '') as 'externally_dialed', replace(username, '"', '') as 'call_from', replace(case when (select top 1 "fac-digit" as 'answered by' from uc540_log_temp as t where "fac-digit" like '"2%"' and t.[h323-conf-id] = m.[h323-conf-id] and [feature-id_field7] = '"CFNA"' order by "unix_time" desc) is null then (select top 1 dnis as 'answered by' from uc540_log_temp as t where t.[h323-conf-id] = m.[h323-conf-id] and [h323-call-origin] = '"originate"' order by "unix_time" desc) ELSE (select top 1 "fac-digit" as 'answered by' from uc540_log_temp as t where "fac-digit" like '"2%"' and t.[h323-conf-id] = m.[h323-conf-id] and [feature-id_field7] = '"CFNA"' order by "unix_time" desc) end, '"', '') as 'call_to' from uc540_log_temp as m where len("username") > 5 and len(case when (select top 1 "fac-digit" as 'answered by' from uc540_log_temp as t where "fac-digit" like '"2%"' and t.[h323-conf-id] = m.[h323-conf-id] and [feature-id_field7] = '"CFNA"' order by "unix_time" desc) is null
then (select top 1 dnis as 'answered by' from uc540_log_temp as t where t.[h323-conf-id] = m.[h323-conf-id] and [h323-call-origin] = '"originate"' order by "unix_time" desc)
ELSE (select top 1 "fac-digit" as 'answered by' from uc540_log_temp as t where "fac-digit" like '"2%"' and t.[h323-conf-id] = m.[h323-conf-id] and [feature-id_field7] = '"CFNA"' order by "unix_time" desc)
end) = 5
and [feature-id_field7] in ( '"TWC"', '""') and [h323-connect-time] <> [h323-disconnect-time] and "fac-digit" <> '0' and [h323-call-origin] = '"answer"'
and "call-id" not in (select distinct "call_id" from uc540_log)

--Secondly import the outbound calls
insert into uc540_log
select distinct
'0' as "in_out",
"call-id" as 'call_id',
null as 'alert-time',
convert(datetime, substring( replace(replace("h323-connect-time", '".', ''), '"', '') , 22, 11))+convert(datetime, substring( replace(replace("h323-connect-time", '".', ''), '"', '') ,0 , 13)) as 'connect_time',
convert(datetime, substring( replace(replace("h323-disconnect-time", '".', ''), '"', '') , 22, 11))+convert(datetime, substring( replace(replace("h323-disconnect-time", '".', ''), '"', '') ,0 , 13)) as 'disconnect_time',
--no externally dialed here
null as 'externally_dialed',
replace(replace( "gw-final-xlated-cdn", '#:', ''), '"', '') as 'call_from',
replace(dnis, '"', '') as 'call_to'
from uc540_log_temp
where username = '"08456800142"' and "alert-time" = '""' and [feature-id_field9] = '""' and [feature-id_field11] = '"TWC"' and [h323-connect-time] <> [h323-disconnect-time]
and "call-id" not in (select distinct "call_id" from uc540_log)

--Thirdly import the internal calls
insert into uc540_log
select distinct
'2' as "in_out",
"call-id" as 'call_id',
convert(datetime, substring(replace(replace("h323-setup-time", '".', ''), '"', ''), 22, 11))+convert(datetime, substring(replace(replace("h323-setup-time", '".', ''), '"', ''),0 , 13)) as 'alert_time',
convert(datetime, substring( replace(replace("h323-connect-time", '".', ''), '"', '') , 22, 11))+convert(datetime, substring( replace(replace("h323-connect-time", '".', ''), '"', '') ,0 , 13)) as 'connect_time',
convert(datetime, substring( replace(replace("h323-disconnect-time", '".', ''), '"', '') , 22, 11))+convert(datetime, substring( replace(replace("h323-disconnect-time", '".', ''), '"', '') ,0 , 13)) as 'disconnect_time',
--no externally dialed here
null as 'externally_dialed',
replace(username, '"', '') as 'call_from',
replace(dnis, '"', '') as 'call_to'
from uc540_log_temp
where "username" like '"2%' and len("dnis") = 5 and ([feature-id_field9] = '"TWC"' OR [feature-id_field11] = '"TWC"' ) AND [h323-connect-time] <> [h323-disconnect-time]
and "call-id" not in (select distinct "call_id" from uc540_log)

--That's it
GO

And that’s it, pretty simple really. Let me know what you think and where any improvements can be made for V2.0.

PS, it’s worth noting that to enable xp_cmdshell you need to run the following:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

I also had some useful feedback from Pete to change the code by adding in my own custom functions to tidy the code and also making the sql as seperate downloadable files. I haven’t had time for this yet, I’ll add this for V2.0!

I used Visual Studio .net and a pretty simple data grid, I pulled in data from SQL, AD and created an area codes table as well as a well known numbers tables (for clients, mobiles etc) and this now displays pretty well:

davecalllog