JasperReport shows no data for specific Alarm types when used in a filter for alert summary Report
Article ID: 278622
Updated On:
Products
Issue/Introduction
Cannot generate a Alert summary report from Spectrum Jasper 7.9.1 for specific alert type for all discovered devices, (Critical/Major/Minor syslog Alerts)
Critical Syslog Event has occurred(0x21001c)
Major Syslog Event has occurred(0x21001b)
Minor Syslog Event has occurred(0x21001a)
These are included in an XML filter file. While this file is shown in the Jasper input parameters inclusion field, no data is returned when it is run;
Environment
DX NetOps Spectrum 22.2.x to 23.3.6
JasperServer 7.9.x
Cause
Analysis of the error in the logs shows this to be an issue with the SQL query syntax and MySql 8.x version being used. The syntax needs to be changed slightly for the MySql version:
2024-01-10T19:00:47,835 ERROR AsyncJasperPrintAccessor,pool-7-thread-36:321 - Error during report executionnet.sf.jasperreports.engine.JRException: Error executing SQL statement for: Event_Log_Details.
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:357)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:181)
at com.jaspersoft.commons.util.JSControlledJdbcQueryExecuter.createDatasource(JSControlledJdbcQueryExecuter.java:115)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1257)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:726)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:457)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:584)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:974)
at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:181)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:925)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1872)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1177)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1118)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:996)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
... 17 more
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null
INNER JOIN v_security_string_accessibility_by_landscape s ON s.security_st' at line 11
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1599)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1461)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1424)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:240)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:216)
... 22 more
Resolution
Fixed in DX NetOps Spectrum release 23.3.7
Feedback
