ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

No old or new data in reports after upgrade to 3.7.14 +

book

Article ID: 199034

calendar_today

Updated On:

Products

CA Performance Management - Usage and Administration DX NetOps

Issue/Introduction

After upgrading to 3.7.14 + there is no data in any reports. This effects old data and new data. The following error is seen in the PCService.log 

ERROR | qtp1658746569-22         | 2020-08-14 20:57:18,015 | com.ca.im.portal.plugins.rib.models.RIBTableModel                
      | RIB query failed
          Model ID/Type: {1000063/RIBTableModel}
          Result status: {ERROR_UNSUPPORTED_QUERY_SYNTAX}
          Query ID: {5227a364-0de0-46e6-8fca-f111c9ff56f5}
          RIB source URL: {http://localhost:8581}
          Query: {SELECT .PollItem.ID, .PollItem.DevName, .Item.Name, .Item.Description, .LmHistoryAvailStatsForwardAvail.SumRate, .LmHistoryAvailStatsBackwardAvail.SumRate, .LmHistoryAvailStatsForwardAvail.Sum, .LmHistoryAvailStatsBackwardAvail.Sum, .LmHistoryAvailStatsForwardAvail.AvgRate, .LmHistoryAvailStatsBackwardAvail.AvgRate, .Resolution.Returned FROM CA.IM.DA.MF.MEF36LmHistoryAvailStats.SOAM_LM_HISTORY_AVAIL_STATS WHERE .PollItem.DeviceID = 68694 AND .EndTime(60) > 1597435020 AND .EndTime(60) <= 1597438620 GROUPBY .PollItem.ID, .PollItem.ID ORDERBY .PollItem.DevName DESC, .PollItem.ID ASC LIMIT 10}
          Reason: {
Error occurred while running a RIB query on Data Aggregator RIB Source. Query ID: RIBQuery_5227a364_0de0_46e6_8fca_f111c9ff56f5
  Exception thrown: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: [Vertica][VJDBC](2640) ERROR: Column "p4.device_name" must appear in the GROUP BY clause or be used in an aggregate function
[Vertica][VJDBC](2640) ERROR: Column "p4.device_name" must appear in the GROUP BY clause or be used in an aggregate function
[Vertica][VJDBC](2640) ERROR: Column "p4.device_name" must appear in the GROUP BY clause or be used in an aggregate function
  Possible cause: RIBQuery passed in was semantically incorrect.
  Query:
{

Cause

In 3.7.14 we changed all reports to query the ETL tables for performance. Due to duplicates in Vertica tables the ETL job was failing and not populating the tables resulting in all reports having no data. 

Resolution

The only way to resolve this if the upgrade has completed is to open a defect with support to clean up the system. To verify if the if the install has this issue please run the attached script. 

Additional Information

To verify if you have this issue download the etlHealth.sh script from the following KB Article to the Data Repository server:

https://knowledge.broadcom.com/external/article?articleId=199748

Then run the script to verify if this is the issue you are encountering:

./etlHealth.sh dauser dapass

It will either return "PASS", which means you have a different issue, or "FAIL", in which case we need to clean up the IREP.

The "FAIL" message includes instructions to collect the IREP, Then open an ticket with support and upload the IREP. 

https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/dx-netops/20-2/Performance-Monitoring-with-DX-Performance-Management/upgrading/upgrade-the-data-aggregator.html#concept.dita_1dad79026dbd1614b8719b8055007f55d1c96487_VerifyETLHealth

https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/dx-netops/20-2/Performance-Monitoring-with-DX-Performance-Management/troubleshooting/ETL-Failures.html