CDR/CMR files on CallManager 7.x using CLI commands

[jays_jara]

Is there a way to view the size and contents of the CDR files on CallManager 7.x using CLI commands? I’m trying to figure out which files to look at and where they would be located. I’d like to open the file(s) and determine how many CDR records, on average, exist and how large file(s) actually is/are.

[jking2685]

The quickest method is just to send them to a billing server, this can be a basic FTP server or an accounting package. The Publisher will send the CDR's and CMR's (If enabled) every 1 min (The default).

Typically the files are a few kb each, depending on the amount of traffic on the UCM.

You can also export a specified period of calls as a CSV file and process the data through our accounting package if you are interested, we offer free trials via www.tri-line.com/cisco

Our products auto configure all the users, UDP's, handsets, depts, gateways etc based on the UCM configuration.

TIM Plus overview:

[jking2685]

Just found this post which has some CLI examples you might be interested in http://www.tek-tips.com/viewthread.cfm?qid=1524902

go to the CLI and issue the following commands:

admin:file list activelog cm/cdr_repository/car/

The output of the above command will be the following

admin:file list activelog cm/cdr_repository/car/

20081218 20081219 20081220 20081221 20081222 20081223 20081224 20081225 20081226 20081227 20081228 20081229 20081230 20081231 20090101 20090102 20090103 20090104 20090105 20090106 20090107 20090108 20090109 20090110 20090111 20090112 20090113 20090114 20090115 20090116 20090117 20090118

dir count = 32, file count = 0

admin:

As we can see above each directory represents a day of collection, now we need to know

if we have files inside each directory, if indeed CDR is collecting information, we should

see files inside each directory, a file inside each folder will represent about 1 or 2 min of collection.

to do this will issue the following command:

For example we will take a look at date 20090117

admin:file list activelog cm/cdr_repository/car/20090117

The output will be the following:

admin:file list activelog cm/cdr_repository/car/20090117

cdr_PUBLISHERNAME-Cluster_01_200901170000_6812

cdr_PUBLISHERNAME-Cluster_01_200901170001_6821

cdr_PUBLISHERNAME-Cluster_01_200901170002_6790

cmr_PUBLISHERNAME-Cluster_01_200901170000_6713

cmr_PUBLISHERNAME-Cluster_01_200901170001_6714

cmr_PUBLISHERNAME-Cluster_01_200901170002_6715

cmr_PUBLISHERNAME-Cluster_01_200901170003_6716

cmr_PUBLISHERNAME-Cluster_01_200901170004_6717

cmr_PUBLISHERNAME-Cluster_01_200901170005_6718

cmr_PUBLISHERNAME-Cluster_01_200901170006_6719

As you can see above, CDR is indeed collecting data starting at 0000 hours on date 20090117

so far we know that data is being collected but we are unable to see it via the car gui,

at this point we could either get a file exported to an sftp server or we can actually see it

raw in the cli, to see the contents of a file we need to use the following command.

For example lets take a look at the following file which represents collection date 20090117 and

time 1333 hours

admin:file view activelog cm/cdr_repository/car/20090117/cmr_PUBLISHERNAME_03_200901171333_4040

The output will be the following:

admin:file view activelog cm/cdr_repository/car/20090117/cmr_PUBLISHERNAME-Cluster_03_200901171333_4040

"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","nodeId","directoryNum","callIdentifier","dateTimeStamp","numberPacketsSent","numberOctetsSent","numberPacketsReceived","numberOctetsReceived","numberPacketsLost","jitter","latency","pkid","directoryNumPartition","globalCallId_ClusterID","deviceName","varVQMetrics"

INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,UNIQUEIDENTIFIER,VARCHAR(50),VARCHAR(50),VARCHAR(129),VARCHAR(600)

2,3,39739,3,"2755",61245505,1232199149,5282,908504,5278,907816,0,1,0,"d371996a-aee3-41f1-9d7a-6574bd7bc4ac","ANY-Internal_PT","PUBLISHERNAME-Cluster","SEP0015FA1CBC12","MLQK=4.5000;MLQKav=4.5000;MLQKmn=4.5000;MLQKmx=4.5000;MLQKvr=0.95;CCR=0.0000;ICR=0.0000;ICRmx=0.0000;CS=0;SCS=0"

end of the file reached

options: q=quit, n=next, p=prev, b=begin, e=end (lines 1 - 3 of 3)

admin:

Ok we have now confirmed that cdr is collecting data as suppose to, next we will check the CAR preferences, and look for

the min date max date, etc

To do this, issue the following command

admin:run sql select * from car:tbl_system_preferences

The output will be the following

admin:run sql select * from car:tbl_system_preferences

param_name param_value

========================= ========================================

MIN_DATE 01/01/1970

MAX_DATE 01/01/1970

CDR_MIN_DATE 01/01/1970

CDR_MAX_DATE 01/01/1970

MAX_CDR_NUMBER 2000000

MAX_ERROR_RECORD_ID 1

COMPANY_NAME

YOUR COMPANY

NAME

TOLL_FREE 1800,1855,1866,1877,1888

CHARGELIMIT 200.00

GOOD 20.00

POOR 30.00

DEFAULT_CAR_USER _unspecifieduser

LOADER_STATUS 1

CONTINUOUS_LOADING_24_7 1

LOAD_CDR_ONLY 1

MANUAL_PURGE_STATUS 0

LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30

PURGE_LOW_WATER_MARK 80

PURGE_HIGH_WATER_MARK 90

MIN_CAR_DATABASE_AGE 30

MAX_CAR_DATABASE_AGE 60

LAST_PROCESSING_DIRECTORY

LAST_PROCESSING_FILE

LAST_PROCESSING_DATA_ROW

UPDATE_STATISTICS_DATE 01/17/2009,0

LOADER_BATCH 600,600,2500,3000

INSTALLATION_DATE 1/16/2009

admin:

As you can see above the MIN_DATE, MAX_DATE, CDR_MIN_DATE, CDR_MAX_DATE are set to 01/01/1970

First and forth most we will change the company name to the name of our company , to do this

we need to issue the following command (changinge the name of the company is not a necessary troubleshooting step

I just wanted to show you that any of the parameters can be modified)

admin:run sql update car:tbl_system_preferences set param_value='COLEMAN

TECHNOLOGIES'

where param_name='COMPANY_NAME'

the output will be the following

admin:run sql update car:tbl_system_preferences set param_value='COLEMAN TECHNOLOGIES' where param_name='COMPANY_NAME'

Rows: 1

admin:

Now we will issue the following command to verify our changes

admin:run sql select * from car:tbl_system_preferences

The output is the following

admin:run sql select * from car:tbl_system_preferences

param_name param_value

========================= ========================================

MIN_DATE 01/01/1970

MAX_DATE 01/01/1970

CDR_MIN_DATE 01/01/1970

CDR_MAX_DATE 01/01/1970

MAX_CDR_NUMBER 2000000

MAX_ERROR_RECORD_ID 1

COMPANY_NAME COLEMAN TECHNOLOGIES

TOLL_FREE 1800,1855,1866,1877,1888

CHARGELIMIT 200.00

GOOD 20.00

POOR 30.00

DEFAULT_CAR_USER _unspecifieduser

LOADER_STATUS 1

CONTINUOUS_LOADING_24_7 1

LOAD_CDR_ONLY 1

MANUAL_PURGE_STATUS 0

LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30

PURGE_LOW_WATER_MARK 80

PURGE_HIGH_WATER_MARK 90

MIN_CAR_DATABASE_AGE 30

MAX_CAR_DATABASE_AGE 60

LAST_PROCESSING_DIRECTORY

LAST_PROCESSING_FILE

LAST_PROCESSING_DATA_ROW

UPDATE_STATISTICS_DATE 01/17/2009,0

LOADER_BATCH 600,600,2500,3000

INSTALLATION_DATE 1/16/2009

admin:

Ok now we need to fix our dates, because otherwise we would never be able to see any cdr data

via the car gui.

We will change the dates in order, starting from MIN_DATE to CDR_MAX_DATE

admin:run sql update car:tbl_system_preferences set param_value='01/01/2009' where param_name='MIN_DATE'

Rows: 1

admin:

To verify our changes we will issue the following command

admin:run sql select * from car:tbl_system_preferences

param_name param_value

========================= ========================================

MIN_DATE 01/01/2009 <--------

MAX_DATE 01/01/1970

CDR_MIN_DATE 01/01/1970

CDR_MAX_DATE 01/01/1970

MAX_CDR_NUMBER 2000000

MAX_ERROR_RECORD_ID 1

COMPANY_NAME COLEMAN TECHNOLOGIES

TOLL_FREE 1800,1855,1866,1877,1888

CHARGELIMIT 200.00

GOOD 20.00

POOR 30.00

DEFAULT_CAR_USER _unspecifieduser

LOADER_STATUS 1

CONTINUOUS_LOADING_24_7 1

LOAD_CDR_ONLY 1

MANUAL_PURGE_STATUS 0

LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30

PURGE_LOW_WATER_MARK 80

PURGE_HIGH_WATER_MARK 90

MIN_CAR_DATABASE_AGE 30

MAX_CAR_DATABASE_AGE 60

LAST_PROCESSING_DIRECTORY

LAST_PROCESSING_FILE

LAST_PROCESSING_DATA_ROW

UPDATE_STATISTICS_DATE 01/17/2009,0

LOADER_BATCH 600,600,2500,3000

INSTALLATION_DATE 1/16/2009

admin:

Now we will proceed to change the MAX_DATE,CDR_MIN_DATE, CDR_MAX_DATE respectively

admin:run sql update car:tbl_system_preferences set param_value='01/01/2012' where param_name='MAX_DATE'

Rows: 1

admin:run sql update car:tbl_system_preferences set param_value='01/01/2009' where param_name='CDR_MIN_DATE'

Rows: 1

admin:run sql update car:tbl_system_preferences set param_value='01/01/2012' where param_name='CDR_MAX_DATE'

Rows: 1

admin:

Now we will verify our changes

admin:run sql select * from car:tbl_system_preferences

param_name param_value

========================= ========================================

MIN_DATE 01/01/2009 <---------------

MAX_DATE 01/01/2012 <---------------

CDR_MIN_DATE 01/01/2009 <---------------

CDR_MAX_DATE 01/01/2012 <---------------

MAX_CDR_NUMBER 2000000

MAX_ERROR_RECORD_ID 1

COMPANY_NAME COLEMAN TECHNOLOGIES

TOLL_FREE 1800,1855,1866,1877,1888

CHARGELIMIT 200.00

GOOD 20.00

POOR 30.00

DEFAULT_CAR_USER _unspecifieduser

LOADER_STATUS 1

CONTINUOUS_LOADING_24_7 1

LOAD_CDR_ONLY 1

MANUAL_PURGE_STATUS 0

LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30

PURGE_LOW_WATER_MARK 80

PURGE_HIGH_WATER_MARK 90

MIN_CAR_DATABASE_AGE 30

MAX_CAR_DATABASE_AGE 60

LAST_PROCESSING_DIRECTORY

LAST_PROCESSING_FILE

LAST_PROCESSING_DATA_ROW

UPDATE_STATISTICS_DATE 01/17/2009,0

LOADER_BATCH 600,600,2500,3000

INSTALLATION_DATE 1/16/2009

admin:

Before running another query via the car gui, we need to do the following

-Restart the following network services in the following order

-Wait until 01:00hrs the next day.

Cisco Database Layer Monitor

Cisco CDR Repository Manager

Cisco CDR Agent

Cisco CAR Scheduler

This document was created by Cesar Fiestas go to the CLI
and issue the following commands

admin:file list activelog cm/cdr_repository/car/

The output of the above command will be the following

admin:file list activelog cm/cdr_repository/car/

20081218
20081219
20081220
20081221
20081222
20081223
20081224
20081225
20081226
20081227
20081228
20081229
20081230
20081231
20090101
20090102
20090103
20090104
20090105
20090106
20090107
20090108
20090109
20090110
20090111
20090112
20090113
20090114
20090115
20090116
20090117
20090118
dir count = 32, file count = 0
admin:

As we can see above each directory represents a day of collection, now we need to know
if we have files inside each directory, if indeed CDR is collecting information, we should
see files inside each directory, a file inside each folder will represent about 1 or 2 min of collection.
to do this will issue the following command:

For example we will take a look at date 20090117

admin:file list activelog cm/cdr_repository/car/20090117

The output will be the following:

admin:file list activelog cm/cdr_repository/car/20090117
cdr_PUBLISHERNAME-Cluster_01_200901170000_6812
cdr_PUBLISHERNAME-Cluster_01_200901170001_6821
cdr_PUBLISHERNAME-Cluster_01_200901170002_6790
cmr_PUBLISHERNAME-Cluster_01_200901170000_6713
cmr_PUBLISHERNAME-Cluster_01_200901170001_6714
cmr_PUBLISHERNAME-Cluster_01_200901170002_6715
cmr_PUBLISHERNAME-Cluster_01_200901170003_6716
cmr_PUBLISHERNAME-Cluster_01_200901170004_6717
cmr_PUBLISHERNAME-Cluster_01_200901170005_6718
cmr_PUBLISHERNAME-Cluster_01_200901170006_6719

As you can see above, CDR is indeed collecting data starting at 0000 hours on date 20090117
so far we know that data is being collected but we are unable to see it via the car gui,
at this point we could either get a file exported to an sftp server or we can actually see it
raw in the cli, to see the contents of a file we need to use the following command.

For example lets take a look at the following file which represents collection date 20090117 and
time 1333 hours

admin:file view activelog cm/cdr_repository/car/20090117/cmr_PUBLISHERNAME_03_200901171333_4040

The output will be the following:

admin:file view activelog cm/cdr_repository/car/20090117/cmr_PUBLISHERNAME-Cluster_03_200901171333_4040

"cdrRecordType","globalCallID_callManagerId","globalCallID_callId","nodeId","directoryNum","callIdentifier","dateTimeStamp","numberPacketsSent","numberOctetsSent","numberPacketsReceived","numberOctetsReceived","numberPacketsLost","jitter","latency","pkid","directoryNumPartition","globalCallId_ClusterID","deviceName","varVQMetrics"
INTEGER,INTEGER,INTEGER,INTEGER,VARCHAR(50),INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,INTEGER,UNIQUEIDENTIFIER,VARCHAR(50),VARCHAR(50),VARCHAR(129),VARCHAR(600)
2,3,39739,3,"2755",61245505,1232199149,5282,908504,5278,907816,0,1,0,"d371996a-aee3-41f1-9d7a-6574bd7bc4ac","ANY-Internal_PT","PUBLISHERNAME-Cluster","SEP0015FA1CBC12","MLQK=4.5000;MLQKav=4.5000;MLQKmn=4.5000;MLQKmx=4.5000;MLQKvr=0.95;CCR=0.0000;ICR=0.0000;ICRmx=0.0000;CS=0;SCS=0"

end of the file reached
options: q=quit, n=next, p=prev, b=begin, e=end (lines 1 - 3 of 3)
admin:

Ok we have now confirmed that cdr is collecting data as suppose to, next we will check the CAR preferences, and look for
the min date max date, etc

To do this, issue the following command

admin:run sql select * from car:tbl_system_preferences

The output will be the following

admin:run sql select * from car:tbl_system_preferences
param_name param_value
========================= ========================================
MIN_DATE 01/01/1970
MAX_DATE 01/01/1970
CDR_MIN_DATE 01/01/1970
CDR_MAX_DATE 01/01/1970
MAX_CDR_NUMBER 2000000
MAX_ERROR_RECORD_ID 1
COMPANY_NAME YOUR COMPANY NAME
TOLL_FREE 1800,1855,1866,1877,1888
CHARGELIMIT 200.00
GOOD 20.00
POOR 30.00
DEFAULT_CAR_USER _unspecifieduser
LOADER_STATUS 1
CONTINUOUS_LOADING_24_7 1
LOAD_CDR_ONLY 1
MANUAL_PURGE_STATUS 0
LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30
PURGE_LOW_WATER_MARK 80
PURGE_HIGH_WATER_MARK 90
MIN_CAR_DATABASE_AGE 30
MAX_CAR_DATABASE_AGE 60
LAST_PROCESSING_DIRECTORY
LAST_PROCESSING_FILE
LAST_PROCESSING_DATA_ROW
UPDATE_STATISTICS_DATE 01/17/2009,0
LOADER_BATCH 600,600,2500,3000
INSTALLATION_DATE 1/16/2009
admin:

As you can see above the MIN_DATE, MAX_DATE, CDR_MIN_DATE, CDR_MAX_DATE are set to 01/01/1970
First and forth most we will change the company name to the name of our company , to do this
we need to issue the following command (changinge the name of the company is not a necessary troubleshooting step
I just wanted to show you that any of the parameters can be modified)

admin:run sql update car:tbl_system_preferences set param_value='COLEMAN TECHNOLOGIES' where param_name='COMPANY_NAME'

the output will be the following

admin:run sql update car:tbl_system_preferences set param_value='COLEMAN TECHNOLOGIES' where param_name='COMPANY_NAME'
Rows: 1
admin:

Now we will issue the following command to verify our changes

admin:run sql select * from car:tbl_system_preferences

The output is the following

admin:run sql select * from car:tbl_system_preferences
param_name param_value
========================= ========================================
MIN_DATE 01/01/1970
MAX_DATE 01/01/1970
CDR_MIN_DATE 01/01/1970
CDR_MAX_DATE 01/01/1970
MAX_CDR_NUMBER 2000000
MAX_ERROR_RECORD_ID 1
COMPANY_NAME COLEMAN TECHNOLOGIES
TOLL_FREE 1800,1855,1866,1877,1888
CHARGELIMIT 200.00
GOOD 20.00
POOR 30.00
DEFAULT_CAR_USER _unspecifieduser
LOADER_STATUS 1
CONTINUOUS_LOADING_24_7 1
LOAD_CDR_ONLY 1
MANUAL_PURGE_STATUS 0
LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30
PURGE_LOW_WATER_MARK 80
PURGE_HIGH_WATER_MARK 90
MIN_CAR_DATABASE_AGE 30
MAX_CAR_DATABASE_AGE 60
LAST_PROCESSING_DIRECTORY
LAST_PROCESSING_FILE
LAST_PROCESSING_DATA_ROW
UPDATE_STATISTICS_DATE 01/17/2009,0
LOADER_BATCH 600,600,2500,3000
INSTALLATION_DATE 1/16/2009
admin:

Ok now we need to fix our dates, because otherwise we would never be able to see any cdr data
via the car gui.

We will change the dates in order, starting from MIN_DATE to CDR_MAX_DATE

admin:run sql update car:tbl_system_preferences set param_value='01/01/2009' where param_name='MIN_DATE'
Rows: 1
admin:

To verify our changes we will issue the following command

admin:run sql select * from car:tbl_system_preferences
param_name param_value
========================= ========================================
MIN_DATE 01/01/2009 <--------
MAX_DATE 01/01/1970
CDR_MIN_DATE 01/01/1970
CDR_MAX_DATE 01/01/1970
MAX_CDR_NUMBER 2000000
MAX_ERROR_RECORD_ID 1
COMPANY_NAME COLEMAN TECHNOLOGIES
TOLL_FREE 1800,1855,1866,1877,1888
CHARGELIMIT 200.00
GOOD 20.00
POOR 30.00
DEFAULT_CAR_USER _unspecifieduser
LOADER_STATUS 1
CONTINUOUS_LOADING_24_7 1
LOAD_CDR_ONLY 1
MANUAL_PURGE_STATUS 0
LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30
PURGE_LOW_WATER_MARK 80
PURGE_HIGH_WATER_MARK 90
MIN_CAR_DATABASE_AGE 30
MAX_CAR_DATABASE_AGE 60
LAST_PROCESSING_DIRECTORY
LAST_PROCESSING_FILE
LAST_PROCESSING_DATA_ROW
UPDATE_STATISTICS_DATE 01/17/2009,0
LOADER_BATCH 600,600,2500,3000
INSTALLATION_DATE 1/16/2009
admin:

Now we will proceed to change the MAX_DATE,CDR_MIN_DATE, CDR_MAX_DATE respectively

admin:run sql update car:tbl_system_preferences set param_value='01/01/2012' where param_name='MAX_DATE'
Rows: 1
admin:run sql update car:tbl_system_preferences set param_value='01/01/2009' where param_name='CDR_MIN_DATE'
Rows: 1
admin:run sql update car:tbl_system_preferences set param_value='01/01/2012' where param_name='CDR_MAX_DATE'
Rows: 1
admin:

Now we will verify our changes

admin:run sql select * from car:tbl_system_preferences
param_name param_value
========================= ========================================
MIN_DATE 01/01/2009 <---------------
MAX_DATE 01/01/2012 <---------------
CDR_MIN_DATE 01/01/2009 <---------------
CDR_MAX_DATE 01/01/2012 <---------------
MAX_CDR_NUMBER 2000000
MAX_ERROR_RECORD_ID 1
COMPANY_NAME COLEMAN TECHNOLOGIES
TOLL_FREE 1800,1855,1866,1877,1888
CHARGELIMIT 200.00
GOOD 20.00
POOR 30.00
DEFAULT_CAR_USER _unspecifieduser
LOADER_STATUS 1
CONTINUOUS_LOADING_24_7 1
LOAD_CDR_ONLY 1
MANUAL_PURGE_STATUS 0
LOADER_SCHEDULE_BACKUP DailyCdrLoad,L,1440,-1,00:00:00,0,300,30
PURGE_LOW_WATER_MARK 80
PURGE_HIGH_WATER_MARK 90
MIN_CAR_DATABASE_AGE 30
MAX_CAR_DATABASE_AGE 60
LAST_PROCESSING_DIRECTORY
LAST_PROCESSING_FILE
LAST_PROCESSING_DATA_ROW
UPDATE_STATISTICS_DATE 01/17/2009,0
LOADER_BATCH 600,600,2500,3000
INSTALLATION_DATE 1/16/2009
admin:

Before running another query via the car gui, we need to do the following

-Restart the following network services in the following order

-Wait until 01:00hrs the next day.

Cisco Database Layer Monitor
Cisco CDR Repository Manager
Cisco CDR Agent
Cisco CAR Scheduler

This document was created by Cesar Fiestas