Error in prov. transaction report

[abhishek_chowdhury]

Which IIQ version are you inquiring about?

Version 8.3

Please share any images or screenshots, if relevant.

Share all details related to your problem, including any error messages you may have received.

In out of the box provisioning transaction report when selecting more than one application in application channel, we are getting following error, the report works fine with one application channel

sailpoint.tools.GeneralException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at sailpoint.persistence.HibernatePersistenceManager.countObjects(HibernatePersistenceManager.java:2758)
at sailpoint.persistence.ClassPersistenceManager.countObjects(ClassPersistenceManager.java:358)
at sailpoint.server.InternalContext.countObjects(InternalContext.java:918)
at sailpoint.reporting.datasource.ProjectionDataSource.getSizeEstimate(ProjectionDataSource.java:61)
at sailpoint.reporting.JasperCsvWriter.write(JasperCsvWriter.java:96)
at sailpoint.reporting.LiveReportExecutor.execute(LiveReportExecutor.java:275)
at sailpoint.api.TaskManager.runSync(TaskManager.java:981)
at sailpoint.api.TaskManager.runSync(TaskManager.java:764)
at sailpoint.scheduler.JobAdapter.execute(JobAdapter.java:128)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1602)
at sailpoint.persistence.HibernatePersistenceManager.countObjects(HibernatePersistenceManager.java:2745)
… 10 more
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2304)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2057)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2019)
at org.hibernate.loader.Loader.doQuery(Loader.java:948)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.doList(Loader.java:2832)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2664)
at org.hibernate.loader.Loader.list(Loader.java:2659)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1414)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1625)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1593)
… 11 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘,’.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:648)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:567)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7675)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4137)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:485)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
… 26 more

[iamksatish (Satish Kurasala)]

Is this error specific to any app as part of selection, When you select only Azure AD and only ServiceNow, is the report working fine?

[abhishek_chowdhury]

nope this error is just when there are more than 1 app, it could be any app

[patrickboston]

Seems like a bug. I was able to re-produce on 8.4p1 as well.

Because this is a Filter type report, you can easily change the TaskDefinition to fix the bug. I did a quick change on the integration Query Parameter script to perform a Filter.in instead of a Filter.like as it was originally.

Other option is to report to SailPoint via an official support ticket. Not sure what their response will be though.

[abhishek_chowdhury]

thanks I will try this and and get back. I will also open a case

[abhishek_chowdhury]

yes updating the filter works as expected, i will also open a case.

[ipobeidi (Ivan Obeidi)]

Same happened in here, and had the same solution.