Managing the Cisco CRA Historical Report Databases

Table of Contents

Managing the Cisco CRA Historical Report Databases
About the CRA System Databases
Configuring the Database Server
Synchronizing Data on the Local and Historical Report Databases
Configuring Users for Historical Reporting
Purging Historical Data
Truncating Database Transaction Log Files
Importing Historical Data from Data Files

Managing the Cisco CRA Historical Report Databases


The Cisco CRA system historical databases can reside either on the CRA server machine or on the Historical Report Database server machine.

Managing the CRA Historical Report databases involves:

  • Configuring maximum number of client and scheduler connections to the historical databases. If the Historical Report Database server is being used to store historical reporting data, the IP address of the database server must also be configured.
  • Configuring users for historical reports.
  • Purging historical data when the size of the databases approaches maximum capacity.
  • Synchronizing data on the CRA server databases and Historical Report Database Server database, if the Historical Report Database server is being used to store historical reporting data.
  • Importing historical data from data files. These data files are generated if the historical database is down and the number of new historical records exceeds the cache.

You must be logged in to the CRA Administration in order to manage the CRA historical reports database.

This chapter contains the following topics:

About the CRA System Databases

This section describes the CRA system databases, including:

Historical Database Names

When you install the Cisco CRA system, the installation process creates two databases that the system uses to store data for historical reports. These databases are described in Table 9-1.

Table 9-1   CRA System Historical Databases

Database Name Description
Db_cra

Contains most information for historical reports, including information about resource groups, skills, and resources.

Db_cra_ccdr

Contains information about each call that the system processes.

If you install the Historical Report Database server, the installation process creates these databases on the Historical Report Database server. The CRA system writes historical data directly to the Historical Report Database server databases and only writes historical data to the local databases if the connection to the Historical Report Database server cannot be established.

Historical Database Sizing

You can configure the CRA system to store historical data in local databases or in databases on the Historical Report Database server, which is installed on a separate machine from the CRA server. You can use the Historical Report Databases server regardless of whether you install the CRA Engine on the same machine as Cisco CallManager or on its own machine.

Microsoft SQL Desktop Edition (MSDE) databases are used to store historical data on the CRA server if historical data is stored locally.

If you install the CRA Engine on the same server as Cisco CallManager and historical data is stored locally, the CRA system uses the MS SQL Server databases on that server to store historical data.

On the Historical Report Database server (if it is installed), you can use either MS SQL Server databases or MSDE databases. MSDE supports up to 15 concurrent historical reporting clients. If you want to support more historical reporting clients, use MS SQL Server.

The CRA system determines the size of the historical reports databases, as described in Table 9-2.

Table 9-2   Database Sizing

Database Type Database Size
MSDE database

If free disk space on the server is greater than 8 GB, the maximum size of each database is 2 GB. If free disk space on the server is less than 8 GB, the maximum size of each database is one-fourth of the free disk space. For example, if free disk space on the server is 4 GB, the size of each database will be 1 GB

MS SQL Server database

The default size of each database is 256 MB.


Caution   If you change the default size of an MS SQL database, make sure that the space allocated and the maximum file size are the same. If these values are not the same, purging will not function properly.

The CRA system limits the number of records in a report to 500,000 records from CCDR (Contact Call Detail Record) by all stored procedures.

To change or disable this limit, run the setHistMaxRecCount.bat file from the wfavvid install directory on the server used to store historical data, with the following usage: setHistMaxRecCount "dbUserName" and "dbPassword" MaxRecordCount. (Make sure that you enclose dbUserName and dbPassword in double quotes [" "].)

The following are examples of change and disable limit syntax:

  • Change limit—setHistMaxRecCount "sa" "" 400000.
  • Disable limit—setHistMaxRecCount "sa" "" 0.

  • Note   If you increase or disable the records limit, you will affect your CRA system performance.

Configuring the Database Server

You can configure the maximum number of client and reporting sessions that can access the historical reporting databases.

If you are using the Historical Report Database server to store historical data, you also need to specify the IP address of the Historical Report Database server.


Note   If you specify the IP address of the Historical Report Database server, you cannot change the IP address back to the local CRA server.

Before configuring the database server, you must stop the CRA Engine. Restart the CRA Engine after you have updated the configuration for the changes to take effect.

To configure the database server, complete the following steps.,

Procedure

Step 1   From the CRA Administration menu bar, choose System > Engine.

The Engine web page opens, displaying the Engine Status page.

Step 2   Click Stop Engine.

Step 3   From the CRA Administration menu bar, choose Tools > Historical Reporting.

The Historical Reporting Configuration web page opens, displaying the Database Server Configuration area.

Step 4   If you have installed the Historical Report Database Server, enter the IP address of the server on which the historical database is installed in the Historical Report Database Server field.

The default value is the local IP address of the CRA server.

Step 5   Enter the maximum number of historical reporting client connections and scheduler connections in the Maximum DB Connections for Report Client Sessions field.

Step 6   Click Update.

Step 7   From the CRA Administration menu bar, choose System > Engine.

The Engine web page opens, displaying the Engine Status page.

Step 8   Click Start Engine. The configuration changes take effect.





Synchronizing Data on the Local and Historical Report Databases

If the Historical Report Database server is installed, the CRA server records data as follows:

  • Historical data is written directly to the Historical Database server
  • ICD configuration data is written directly to the local CRA server. A copy of the ICD configuration data is stored on the Historical Report Database server. Updates to configuration data on the local server trigger updates to the Historical Report Database server.

If the connection between the CRA server and the Historical Report Database server cannot be established (for example, if the network connection goes down), the CRA server writes historical data to the local databases and flags new or changed ICD configuration database records to be synchronized when the connection is reestablished.

When the connection between the CRA server and Historical Report Database server is reestablished, the data on the Historical Report Database server must be synchronized with the data on the CRA server.

You can enable and schedule automatic synchronization of the data on the CRA server databases and Historical Report Database server databases. You can also synchronize the data manually at any time.


Note   The historical records are removed from the CRA server after the data has been updated on the Historical Report Database server.

This section describes:

Synchronizing Data Automatically

Automatic historical and ICD configuration data synchronization ensures proper system functioning.

You can enable automatic synchronization if it is currently disabled. If the connection between the CRA server and Historical Reports Database server fails and is reestablished, the Historical Reports Database server checks whether any data is out of sync between the CRA server local databases and the Historical Reports Database server databases. If data is out of sync, the Historical Reports Database server automatically updates the data according to the schedule that you specify.

To configure automatic historical and configuration data synchronization, complete the following steps.

Procedure

Step 1   From the CRA Administration menu bar, choose Tools > Historical Reporting.

The Historical Reporting Configuration web page opens, displaying the Database Server Configuration area.

Step 2   Look in the Database Server Status area to ensure that the Historical Reports Database server status is "Available". You can configure automatic synchronization only if the status is Available.

Step 3   Click the Synchronize Data hyperlink on the Historical Reporting Configuration navigation bar.

Step 4   Check the Enable Historical Data Synchronizer check box to enable automatic historical data synchronization.

Step 5   Select a time of day at which to synchronize the historical data from the drop-down menu.

Because historical data synchronization can be a lengthy process, especially if you are upgrading from a previous version of Cisco CRA, synchronize data at an off-peak time or a time at which the system is not receiving calls.

Step 6   Check the Enable Configuration Data Synchronizer check box to enable automatic configuration data synchronization.

Step 7   Select the interval, in minutes, at which to synchronize the configuration data.

The default value is 5 minutes.

Step 8   Click Update to apply the synchronization schedule changes.





Synchronizing Data Manually

You can manually synchronize the historical and configuration data in the CRA server local databases and Historical Reports Database server databases at any time. This action will not affect the automatic synchronization schedule. However, you cannot manually synchronize data while the automatic synchronization is executing.

To synchronize historical and configuration data manually, complete the following steps.

Procedure

Step 1   From the CRA Administration menu bar, choose Tools > Historical Reporting.

The Historical Reporting Configuration web page opens, displaying the Database Server Configuration area.

Step 2   Click the Synchronize Data hyperlink on the Historical Reporting Configuration navigation bar.

The Synchronize Data area opens.

Step 3   Check that atheistically Data Synchronizer Status and Configuration Data Synchronizer Status, listed in the Synchronize Now area, are both "Not Executing". If either are executing, you cannot manually synchronize data. You can only synchronize data manually when neither are executing.

Step 4   Click Synchronize Now.

If automatic synchronization currently is executing, a message appears instructing you to manually synchronize at another time. Otherwise, the historical and configuration data is synchronized.





Configuring Users for Historical Reporting

You can enable users configured on the Cisco CallManager to use installed CRA historical reporting packages.

To configure users for historical reporting, complete the following steps.

Procedure

Step 1   From the CRA Administration menu bar, choose Tools > Historical Reporting.

The Historical Reporting Configuration web page opens, displaying the Database Server Configuration area.

Step 2   Click the User Configuration hyperlink on the Historical Reporting Configuration navigation bar.

The User Configuration area opens, listing a maximum of 75 Cisco CallManager users.

Step 3   Choose the user for which you want to configure historical reporting by either:

  • Entering search criteria (such as a user name) in the Enter Search Criteria text field and then choosing the appropriate user from the drop-down list. If more than 75 users are configured on Cisco CallManager, you can use search to find user who are not displayed.
  • Choosing the user from the drop-down list.

Step 4   Click Next.

The User configuration for user area opens.

Step 5   In the Installed Reporting Packages list, choose the reporting package for which you want to configure this user, and click >.

Step 6   If you want to remove reporting packages from this user, choose the reporting package that you want to remove in the Selected Reporting Packages list, and click < .

Step 7   Click Update to apply the changes.

The User Configuration area opens.

Step 8   Repeat steps 3 through 7 to configure additional users.





Purging Historical Data

As the Cisco CRA Engine runs, it collects information about the status and performance of the CRA system. Historical information is stored in databases that can then be accessed to provide reports. (Refer to the Cisco Customer Response Applications Historical Reports User Guide for more information about Cisco CRA Historical Reports.)

When either of the historical reports databases approaches its maximum size, some or all of the data in it must be removed. Removing data from a database is called purging.

When the system purges data, it removes historical data from both the Db_cra_ccdr database and Db_cra database.

When the system purges data from the historical reports databases, it determines what information to purge based on the number of months you specify and on the current date. For example, if you instruct the system to purge data older than 12 months, a purge on January 15 will purge data older than January 15 of the previous year.


Note   When you purge data, you permanently delete it. If you want to keep data that will be purged, back up the databases.

Cisco CRA Administration provides the following features for purging historical reports databases:

  • Daily comparison of the size of the databases to a user-specified maximum size
  • User-specified time at which the system purges data
  • Automatic purging of databases when they exceed the user-specified maximum sizes
  • Automatic purging of databases based on user-specified parameters
  • Manual purging of databases
  • Automatic e-mail notification of purging activities
  • Automatic SNMP (Simple Network Management Protocol) traps and Syslog messages describing purging activities

This section describes:

Configuring Automatic Purging

The CRA Engine performs automatic purging each day at a preset time. To help keep your system running most efficiently, Cisco recommends that you schedule automatic purging to run when your system is least busy. By default, daily purges are scheduled to run at 4:00 a.m. (0400), but you can change this time.

The system bases its purging activities on a variety of parameters. You can change the default value for any parameter as needed.

A log file, PurgeProcess.log, stores information about the automatic purging activities that the system performs. The PurgeProcess.log file has a maximum size of 2 MB. When this file reaches its maximum size, the system copies it to a backup file named PurgeProcess.bak. The system maintains one such backup file. Each time the PurgeProcess.log file reaches 2 MB in size, the system moves that information to the existing backup file, overwriting the information in the existing backup file. This log file is stored on the CRA server in the <CRAinstallationDirectory>\wfavvid\log\Purge_Sch directory. (By default, the system installs in the Program Files directory.) You can view this log file by opening the file in a text editor.

This section contains the following procedures:

Managing the Purging Scheduler Service

The Cisco Purging Scheduler Service must be running on the CRA server for automatic purging to occur. By default, this service is always running. If you turn off this service, make sure to monitor the sizes of the databases and purge them manually as needed.

If you change the server system clock, you must stop and restart the Cisco Purging Scheduler Service for automatic purging to occur at the correct time.

To verify that the Cisco Purging Scheduler Service is running, and to restart it or stop it, if necessary, perform the following steps:

To start or stop the Purge Scheduler Service, complete the following steps.

Procedure

Step 1   From the Windows desktop, choose Start > Settings > Control Panel.

The Control Panel window opens.

Step 2   Double-click Administrative Tools.

The Administrative Tools window opens.

Step 3   Double-click Services.

The Services window opens.

Step 4   Look at the Status field of the Cisco Purge  Scheduler Service. If this field displays "Started," the Purging Scheduler Service is running. If this field is blank, the Purge Scheduler Service is not running.

  • To start the Purging Scheduler Service, right-click Cisco Purging Scheduler Service in the Services Window, and then choose Start from the Services menu.
  • To stop the Purging Scheduler Service, right-click Cisco Purging Scheduler Service in the Services Window, and then choose Stop from the Services menu.




Setting the Daily Purge Schedule

You can change the time of day that the system assesses the need to purge data and the age of data to purge.

To set the purge schedule, complete the following steps.

Procedure

Step 1   From the CRA Administration menu bar, choose Tools > Historical Reporting.

The Historical Reporting Configuration web page opens, displaying the User Configuration area.

Step 2   Click the Purge Schedule hyperlink on the Historical Reporting Configuration navigation bar.

The Purge Schedule area opens.

Step 3   From the Daily Purge At drop-down menu, choose a time of day at which the system determines if purging is necessary.

Step 4   From the Purge Data Older Than N Months drop-down menu, choose a number of months.

If the system determines that purging is necessary, it will purge both databases of data that is older than the number of months specified in this field.

Step 5   Click Update.

The new purge schedule configuration is added to the CRA system.





Configuring Purge Notification Parameters

When data is purged, the CRA system sends one of the following messages, depending on the activity that occurs:

  • Database purged—Announces a purge has taken place, and includes an explanation of the purging activity.
  • Database approaching maximum size—Sends an alert that the database is approaching its maximum size.

The system can send notifications through the following three methods:

  • E-mail
  • Syslog (system log)
  • SNMP traps

For e-mail notification, the eMail subsystem must be configured on the CRA server. For information about configuring the eMail subsystem, see the "Provision the eMail Subsystem" section.

For Syslog and SNMP trap notification, the Cisco AVVID (Architecture for Voice, Video, and Integrated Data) Alarm Service must be running, and Syslog and SNMP must be properly configured. For more information about Cisco AVVID Alarm Service, Syslog, and SNMP, see the Cisco Customer Response Applications Serviceability Guide.

To configure the parameters of e-mail purge notification messages, complete the following steps.

Procedure

Step 1   From the CRA Administration menu bar, choose Tools > Historical Reporting.

The Historical Reporting Configuration web page opens.

Step 2   To change the purge notification parameters, click the Purge Notification hyperlink on the Historical Reporting Configuration navigation bar.

The Purge Notification area opens.

Step 3   In the Send Email Notifications To field, enter the e-mail address to which you want the notification sent.

Step 4   From the Send Email Notification When Database Size Exceeds N % of maximum DB size drop-down menu, accept the default of 85, or choose another number.

Step 5   From the Initiate an Automatic Purge When Database Size Exceeds N % of maximum DB size drop-down menu, accept the default of 95, or choose another number.

Step 6   From the Auto Purge Data for the Oldest N Days drop-down menu, accept the default of 7, or choose another number.

Step 7   Click Update.

The Purge Notification area refreshes, and the CRA system is configured with the new values.





Purging Manually

You can manually purge the databases at any time. This action will not affect the automatic purging schedule.

A log file, Jvm.stdout, stores information about the manual purge activities that you perform. The Jvm.stdout file has no maximum size; when new information is generated, the information is appended to the existing jvm.stdout file. This log file is stored on the CRA server in the <CRAinstallationDirectory>\wfavvid\log\Purge_Sch directory. (By default, the system installs in the Program Files directory. You can view this log file by opening the file in a text editor.

To manually purge historical data, complete the following steps.

Procedure

Step 1   From the CRA Administration menu bar, choose Tools > Historical Reporting.

The Historical Reporting Configuration web page opens.

Step 2   Click the Purge Now hyperlink on the Historical Reporting Configuration navigation bar.

The Purge Now area opens.

Step 3   From the Purge Data Older Than N Months drop-down menu, accept the default number of 13, or choose a different number.

The system will purge both databases of data that is older than the number of months that you specify.

Step 4   Click Purge Now.

The databases are purged, and the Purge Now area refreshes.





Truncating Database Transaction Log Files

The db_cra_log.ldf and dba_cra_ccdr_log.ldf database transaction log files can grow large. These log files are stored in the <CRAinstallationDirectory>\wfavvid directory on the CRA server, and also on the Historical Reports Database server if it is installed.

This Cisco CRA command line tool named runTruncateHistDBLogs can truncate either or both of these files to the size that you specify. You can run this command from a Microsoft Windows command window.

The process of truncating transaction log files can take up to 10 minutes.


Note   The runTruncateHistDBLogs command can spend up to 5 minutes per database log file performing the truncation. If you are substantially truncating a log file, this time limit may not allow the command to truncate the log file to the size that you specified. In this case, rerun the runTruncateHistDBLogs command to truncate the log files to the desired size.

To ensure that the truncated log files are updated with current information, perform a complete backup of the CRA databases and log files after executing the runTruncateHistDBLogs command.

The syntax for the runTruncateHistDBLogs command is:

runTruncateHistDBLogs "dbUserName" "dbPassword" {"db_cra" | "db_cra_ccdr | "db_cra_all"} size

Table 9-3 lists the options and parameters for the runTruncateHistDBLogs command.

Table 9-3   Options and Parameters for runTruncateHistDBLogs command

Option or Parameter Description

"dbUserName"

User name of the database.

"dbPassword"

User password for the user name.

"db_cra"

Truncates the db_cra_log.ldf database transaction log file.

"db_cra_ccdr"

Truncates the db_cra_ccdr_log.ldf database transaction log file.

"db_cra_all"

Truncates the db_cra_log.ldf and the db_cra_ccdr_log.ldf database transaction log files.

size

Size in MB to which the file or files will be truncated.

It is recommended that, for the ldb_cra_log.ldf file, you specify a size no less than one-fourth the size of the ldb_cra.mdf database. It is recommended that, for the db_cra_ccdr_log.ldf log file, you specify a size no less than one-fourth the size of the db_cra_ccdr.mdf database.

Examples:

The following command truncates the db_cra_log.ldf database transaction log file to 250 MB:

runTruncateHistDBLogs "sa" "sa_password" "db_cra" 250

The following command truncates the db_cra_log.ldf and the db_cra_ccdr_log.ldf database transaction log files to 300 MB:

runTruncateHistDBLogs "sa" "sa_password" "db_cra_all" 300

Importing Historical Data from Data Files

If the local historical database on the CRA server is down, historical records are cached to be written to the database. If the number of records exceeds the cache maximum, the additional records are written to files stored in the wfaavid\SQLdata directory. These data files are also generated if the historical record queue is exceeded when the local historical databases on the Historical Reports Database server and CRA server are down.

You can import the data from these files into the historical database by running the osql command for all data files in the wfavvid\SQLData directory.

The syntax for the osql command is:

osql -UuserId -Ppassword -idata_file_name

Table 9-4 lists the options and parameters for the osql command on the CRA server.

Table 9-4   Options and Parameters for osql command

Option or Parameter Description

userId

User id for the database.

password

User password for the user id.

data_file_name

Name of the file to be imported to database.

Example:

If the userId and password are "sample" and the file name is sampleFile.data, the command is:

osql -Usample -Psample -isampleFile.data