4.47 SetupSupplementalDB

Use this Knowledge Script to verify that Communications Manager is properly configured for the collection of Call Detail Records (CDRs). This script creates a CiscoCM supplemental database, plus the tables and stored procedures needed to store CDRs. In addition, this script creates a SQL job that removes old records from the supplemental database.

4.47.1 Understanding the CiscoCM Supplemental Database

The CiscoCM supplemental database is a SQL Server database you create on the proxy agent computer. The supplemental database fulfills three functions:

  • Storage for CDRs. The Unified Communications Manager server pushes CDRs, which are flat files, to a folder on the proxy agent computer. From there, the CDRs are saved to tables in the CiscoCM supplemental database, from which the CDR_CallFailures, CDR_CallQuality, and CDR_Query Knowledge Scripts can easily monitor and retrieve data.

    When you create the supplemental database, you specify how long data is retained before being deleted and archived. AppManager automatically archives any flat files older than the retention age you specify. That way, no time or CPU is wasted by transferring to the supplemental database any files that will be immediately slated for deletion.

  • Data source for Call Data Analysis module. By creating a supplemental database in which to store CDRs, you establish a means of using the AppManager for Call Data Analysis module for analyzing call activity for Unified Communications Manager. The Call Data Analysis module was designed to analyze CDRs that are pushed to a supplemental database. The CDR_RetrieveConfigData script retrieves the Unified Communications Manager configuration information Call Data Analysis requires and stores it in the supplemental database.

    When using AppManager for Call Data Analysis, simply identify the CiscoCM supplemental database as a Data Source when you run the CallDataAnalysis_AddDataSource_CiscoCM Knowledge Script.

  • Storage for phone deregistration data. The PhoneDeregistrations Knowledge Script uses AXL queries to create a list of unregistered phones and to identify when they reregister. The script stores the deregistration data in an audit table in the CiscoCM supplemental database, from which it is easily accessed for reporting. The CDR_RetrieveConfigData script retrieves the Communications Manager configuration information the Report scripts need to accommodate your grouping choices and stores it in the supplemental database.

To use the supplemental database:

  1. Create the database. Use the SetupSupplementalDB Knowledge Script to create one CiscoCM supplemental database per Unified Communications Manager cluster you are monitoring.

  2. Populate the database. Use CDR_RetrieveConfigData to retrieve configuration data from Unified Communications Manager and save it to the CiscoCM supplemental database. Then run CDR_RetrieveCallRecords to retrieve the CDR flat files from the folder into which they were pushed (using FTP) by the primary Communications Manager. This folder is located on the proxy agent computer; you configure the primary Communications Manager to send CDRs to this location.

    Although the PhoneDeregistrations script does not monitor data in the supplemental database, it does populate the audit table in the database with phone deregistration data, which is subsequently used by the Report_PhoneDeregAudit and Report_PhoneDeregWatchList Report scripts.

  3. Monitor the data in the database. Depending on your monitoring objectives, use the following scripts to analyze the data in the database.

    • CDR_CallFailures monitors CDRs for calls that ended with an abnormal termination code.

    • CDR_CallQuality monitors CDRs for jitter, latency, lost data, and MOS.

    • CDR_Query searches CDRs based on query filters you select.

    • PhoneDeregistrations monitors phone deregistrations and maintains a history of phone deregistrations in the supplemental database

  4. Run the Report Knowledge Scripts. The Report_PhoneDeregAudit and Report_PhoneDeregWatchList scripts organize and display the information in the CiscoCM supplemental database. The reporting function requires the netiqmc service on the Report agent computer to be running as an account that has permissions on the supplemental database.

4.47.2 Resource Object

CiscoCM _CDRMgmt

4.47.3 Default Schedule

By default, this script runs once.

4.47.4 Setting Parameter Values

Set the following parameters as needed:

Parameter

How to Set It

General Settings

Job Failure Notification

Event severity when job fails

Set the event severity level, from 1 to 40, to indicate the importance of the failure of the SetupSupplementalDB job. The default is 5.

Raise event if database setup succeeds?

Select Yes to raise an event if the setup of the CiscoCM supplemental database is successful. The default is unselected.

Event severity when database setup succeeds

Set the event severity level, from 1 to 40, to indicate the importance of the success of the setup of the CiscoCM supplemental database. The default is 25.

Raise event if database setup fails?

Select Yes to raise an event if the setup of the CiscoCM supplemental database is unsuccessful. The default is selected.

Event severity when database setup fails

Set the event severity level, from 1 to 40, to indicate the importance of the failure of the setup of the CiscoCM supplemental database. The default is 5.

Phone Deregistration Parameters

Number of days to keep phone deregistration audit entries

Specify the number of days’ worth of phone deregistration audit entries you want to keep in the CiscoCM supplemental database. Any data older than what you specify is discarded. The default is 180 days.

CDR Parameters

Full path to call detail records

Specify the full path to the location of the CDRs on the proxy agent computer. The TreeView cluster name must appear in the path. Use the same TreeView cluster name you used when configuring the proxy agent computer as a billing server. The default is blank.

For example, if you entered CCM80-01\ as the host name of the primary server when you configured the billing server and your FTP server is installed in the default location, then enter the following:

c:\inetpub\ftproot\CCM80-01

For more information, see Appendix A, “Monitoring Deregistration for Communications Manager 4.x.

Number of days to keep call detail records

Specify the number of days’ worth of call detail records you want to keep in the CiscoCM supplemental database. Any data older than what you specify is discarded. The default is 7 days.

SQL Server Information

Local SQL Server Instance name

Specify the name of the local SQL Server instance (on the proxy agent computer) in which you want to create the new CiscoCM supplemental database. Leave this parameter blank to accept the default name.