NetOps Operational Reports fails immediately for any report - Query failed

Products

• Spectrum
• Network Observability
• CA Performance Management

Issue/Introduction

After successfully installing the NetOps Operational Reports and enabling it in the Performance Management Portal, Operational Reports (such as Asset, Alarm, or Availability reports) fail to display data.

Users may see a "Query failed" message in the Portal UI. The following error is found in the PCService.log on the Performance Management Portal server:

text

ERROR | Model-Invoker-36 | com.ca.im.portal.plugins.rib.models.RIBTableModel | RIB query failedReason: {{org.apache.cxf.binding.soap.SoapFault: StatementCallback; bad SQL grammar [SET @is_security_enabled := IS_SECURITY_ENABLED();]}}Caused by: {StatementCallback; bad SQL grammar [SET @is_security_enabled := IS_SECURITY_ENABLED();]}

Environment

DX NetOps Performance Management: 24.3.x

DX NetOps Spectrum: 24.3.x

Cause

The MySQL user account (typically SRM_user) used by the NetOps Report Manager Service to query the reporting database lacks the EXECUTE privilege. This privilege is required to run stored routines and functions (like IS_SECURITY_ENABLED()) used in the reporting queries.

Resolution

To resolve this issue, grant the necessary permissions to the SRM_user in the MySQL reporting database:

1. Log into the MySQL instance on the Spectrum Report Manager (SRM) server as a user with administrative privileges (e.g., root).
2. Run the following command to get the grants query for the user:
   sql

   SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user;

3. Run the following command to verify the current grants for the user:
   sql

   SHOW GRANTS FOR 'SRM_user'@'<RMS_hostname>';

4. Execute the following commands to grant the required SELECT and EXECUTE privileges on the reporting database:
   sql

   GRANT SELECT, EXECUTE ON reporting.* TO 'SRM_user'@'<RMS_hostname>';
   FLUSH PRIVILEGES;

5. Restart the SRM-RIB service on the SRM server to ensure the changes take effect.
   

   systemctl restart srm-rib.service

6. Log back into the NetOps Portal and verify that the reports now display data correctly.