Query Failed error appears in CA Performance Manager (NetOps Portal) Dashboards instead of data

Products

• CA Performance Management - Usage and Administration
• DX NetOps

Issue/Introduction

When viewing various dashboard views in NetOps Portal,  "Query Failed" errors are generated instead of data:
 

Environment

CAPM 3.x

Cause

From the following errors in the Vertica logs, we can see that the problem is caused by the existence of duplicates in the poll_item table;
 

Caused by: java.sql.SQLIntegrityConstraintViolationException: [Vertica][VJDBC](3149) ERROR: Duplicate primary/unique key detected in join [(dauser.avail_rate x dauser.poll_item) using avail_rate_super_seg_b0 and poll_item_is_filtered_in_order_by_node0001 (PATH ID: 21)]; value [2343921]

These duplicates need to be deleted as they're causing conflict issues when NetOps Portal queries the DB.

Resolution

1. First, log into the DR as the DR admin user and run the following to connect to the Vertica DB:
   
   /opt/vertica/bin/vsql -U <DA_USER_VERTICA_NAME> -w <DA_USER_PASSWORD> 
   
   The username and password will be what you set up for the DA to access the DR when you first installed it.  
   
   
   
2. At the SQL command prompt, run the following to identify any duplicates;
   
   select item_id,device_item_id,count(*) from poll_item group by 1,2 having count(*) > 1; 
   
   If there are indeed duplicates, then you'll see output similar to;
   
   dradmin=> select item_id,device_item_id,count(*) from poll_item group by 1,2 having count(*) > 1;
   item_id  | device_item_id | count
   ---------+----------------+-------
     428399 |         408902 |     2
     686928 |         408417 |     2
   (2 rows)
   
   
   
3. For each itemid, we need to request poll_item and ask for "epoch" also.
   
   select item_id, device_item_id, epoch from poll_item where item_id = X and device_item_id = Y;
   
   For example:
   
   dradmin=> select item_id, device_item_id, epoch from poll_item where item_id = 428399 and device_item_id = 408902;
   item_id  |  epoch  
   ---------+---------
    428399  | 6614331
    428399  | 4984032
   (2 rows)
   
   
   
4. For each itemid/epoch, we want to:
   
   delete from poll_item where item_id = X and device_item_id = Y and epoch = NEWEST_EPOCH;
   
   For example:
   
   dradmin=> delete from poll_item where item_id = 428399 and device_item_id = 408902 and epoch = 6614331;
   
   
   
5. Repeat this process for the item items seen in step 2 until the query in step 2 returns an empty set:
   
   dradmin=> select item_id,device_item_id,count(*) from poll_item group by 1,2 having count(*) > 1;
   item_id  | device_item_id | count
   ---------+----------------+-------
   (0 rows)
   
   
   
6. Once those are all cleaned up, we need to check for duplicate items where device_item_id is different.
   
   select item_id, device_item_id, epoch from poll_item where item_id in (select item_id from poll_item group by 1 having count(*) > 1); 
   
   
   
7. This should give us a list of items/device and epoch added. We need to determine which of the 2+ devices the component should be on and remove the incorrect one.  This will likely end up being the oldest epoch but needs to be determined on a case by case basis.
   
   
   
8. After all updates are completed make sure you type COMMIT; to commit all changes to the database