No old or new data in reports after upgrade to 3.7.14 +
Article ID: 199034
Updated On:
Products
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.
Feedback
