CVP Reporting - Troubleshoot Duplicate Schedules

Introduction

This document describes a troubleshoot process of duplicate schedules for Cisco Customer Voice Portal (CVP) reporting feature.

Contributed by Mohamed Mohasseb, Aleksey Yankovskyy and Alexander Levichev, Cisco TAC Engineers.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

• Microsoft Windows Server
• Cisco CVP
• Informix DB Access tool 

Components Used

The information in this document is based on CVP server version 11.0, but it is also applicable for the previous versions.

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Troubleshoot Duplicate Schedules in CVP Reporting

In ciscoadmin database agg_schedule table controls how often each aggregation is run. In some cases after an upgrade the table is reloaded with a new schedule however the old one still exists. This results in each aggregation being performed twice. This will adversely impact the accuracy of the summary tables as each one will have twice the number of rows, and hence counts, inserted into it.

Diagnose

Verify if there are duplicates in agg_schedule table.

Login to CVP Reporting server with cvp_dbadmin user.

Start Windows CMD tool. Type dbaccess command. Select Connection tab > Connect. Select cvp database server and press enter. You will be prompted to type in the credentials. Use cvp_dbadmin account.

Select ciscoadmin@cvp database. Select Query-language > New. Run this command. 

UNLOAD TO schedule.txt SELECT * FROM agg_schedule

Open schedule.txt file. Depending on CVP version it might be located in either C:\Users\Administrator or C:\db\Informix\etc\sysadmin folders.

It should have unique CVP data schedule entries. For example call_15 or applicationsummary_daily as depicted in this picture.

Ensure that there are no duplicate types. If duplicates are found then implement the workaround.

Workaround

Remove Duplicate Schedule

Disable the summary job (CVPSummary) in Windows Task Scheduler.

Open schedule.txt file and remove all duplicate lines. There should remain only the first 10 entries.

Connect to ciscoadmin database as described before and run this query. This command deletes all entries from agg_schedule table. 

• DELETE FROM agg_schedule WHERE 1=1;

Load the new values from schedule.txt file that does not contain any duplicates to agg_schedule table.

• LOAD FROM schedule.txt INSERT INTO agg_schedule;

Ensure there are no duplicates present in agg_schedule table. The output of schedule1.txt file should contain only 10 entries. 

• UNLOAD TO schedule1.txt SELECT * FROM agg_schedule;

Remove Duplicate Entries from Tables

Since 15-minute table is the base from which all other tables are populated it needs to be fixed first.

Connect to cvp_data database.

Run these commands for call_15 table.

SELECT distinct * FROM call_15 into temp t1 with no log;
TRUNCATE table call_15;
INSERT into call_15 select * from t1;
DROP table t1;

Repeat the same steps for applicationsummary_15 table.

SELECT distinct * from applicationsummary_15 into temp t1 with no log;
TRUNCATE table applicationsummary_15;
INSERT into applicationsummary_15 select * from t1;
DROP table t1;

Note: If the problem is started more than 60 days ago repeat the above steps for daily, weekly and monthly tables.

Reset Recent Executions

Reset lastrun field for 15-minute tables.

Find out the time when 15-minute tables were updated. Run these commands against cvp_data database. 

SELECT max(dbdatetime) FROM applicationsummary_15;
SELECT max(dbdatetime) FROM call_15;

Write down the number of days since the last update.  

Run this query to reset the last execution for 15-minute tables on ciscoadmin DB. In this example 15-minute tables were updated 17 days ago last time.

UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 17 units day) WHERE dst_tabname LIKE 'call_15';
UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 17 units day) WHERE dst_tabname LIKE 'applicationsummary_15';

Note: Replace "17"  from the command with the number of days you got from the  step for each of these two tables.

After that step 15-minute tables will be corrected. 

Since 15-minute tables retain data for the last 60 days, reset the lastrun value for daily, weekly and monthly tables and delete all data 60 days back for these tables. This way ensures that next time when aggregation.bat process start, it will populate the data in daily, weekly, monthly tables with the correct values.

Reset the lastrun for daily, weekly and monthly tables with these commands executed against ciscoadmin database. 

UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 60 units day) WHERE dst_tabname LIKE 'call_daily';
UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 60 units day) WHERE dst_tabname LIKE 'call_weekly';
UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 60 units day) WHERE dst_tabname LIKE 'call_monthly;
UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 60 units day) WHERE dst_tabname LIKE 'applicationsummary_daily;
UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 60 units day) WHERE dst_tabname LIKE 'applicationsummary_weekly;
UPDATE ciscoadmin:agg_schedule SET lastrun = (current year to day - 60 units day) WHERE dst_tabname LIKE 'applicationsummary_monthly;

Delete all data in daily, weekly and monthly tables for 60 days back. 

DELETE FROM cvp_data:call_daily WHERE dbdatetime > (current - 60 units day);
DELETE FROM cvp_data:call_weekly WHERE dbdatetime > (current - 60 units day);
DELETE FROM cvp_data:call_monthly WHERE dbdatetime > (current - 60 units day);

DELETE FROM cvp_data:applicationsummary_daily WHERE dbdatetime > (current - 60 units day);
DELETE FROM cvp_data:applicationsummary_weekly WHERE dbdatetime > (current - 60 units day);
DELETE FROM cvp_data:applicationsummary_monthly WHERE dbdatetime > (current - 60 units day);

Enable the summary job CVPSummary in task scheduler for the aggregation process to start again.

Verify 

Use this section in order to confirm that your configuration works properly.

Ensure the summary tables are being updated with these commands executed against cvp_db database. 

SELECT MAX(dbdatetime) FROM applicationsummary_15;
SELECT max(dbdatetime) FROM applicationsummary_daily;
SELECT max(dbdatetime) FROM applicationsummary_weekly;
SELECT max(dbdatetime) FROM applicationsummary_monthly;

SELECT MAX(dbdatetime) FROM call_15;
SELECT MAX(dbdatetime) FROM call_daily;
SELECT MAX(dbdatetime) FROM call_weekly;
SELECT MAX(dbdatetime) FROM call_monthly;

Related Information

• Integrate Squirrel SQL Client with CVP Reporting Configuration Example
• Technical Support & Documentation