This is the re-usable SQL / JDBC reporting data source presented by Devin Rosenbauer of IDW at the 2023 SailPoint Developer Days.

Building

Adding the IIQ library

You will need to provide your own copy of identityiq.jar at lib/iiq.

If you miss this step, you will receive this error on build:

* What went wrong:
A problem occurred evaluating root project 'jdbc-reporting'.
> MISSING IIQ: You must put your own copy of identityiq.jar at lib/iiq/ before you can run the build

Executing the build

To build, simply run: ./gradlew build or ./gradlew.bat build. This should download any appropriate libraries, including Gradle itself. The resulting JAR file will be at build/libs.

Usage

Deploying the Library

To use this data source, you will need to place the built JAR in your SailPoint environment at WEB-INF/lib so that it's on the base IIQ classpath. You can do this as part of your usual build tool (e.g., SSB) or simply drop it in there in Tomcat.

Using in a report

Your report must use the following DataSource syntax:

<DataSource dataSourceClass="com.identityworksllc.iiq.jdbcreporting.JDBCDataSource" type="Java">

The Query and QueryScript sections work identically to HQL reports, as documented in the IIQ official documentation. Bind variables use the syntax :name, where name is the variable you've included as an Argument.

ReportColumnConfigs

The most important note here is the distinction between property and _field.

• The field is required and must be unique in your report. It can be any string and simply names the column in the rendered report.
• The property must reference a column or column alias in your SQL ResultSet. It does not need to be unique so that you can re-use the same ResultSet column more than once.

If a property is not specified, then field will be used as a fallback.

You may specify a valueClass, which can be one of (for this demo):

• xml: the string value will be treated as raw SailPoint XML and parsed into an arbitrary object
• date: the string value will be treated as a millisecond timestamp and converted to a `Date
• Any SailPoint class name (e.g., Identity): the value will be interpreted as the ID of an object of the named type and looked up

You may specify a RenderScript or RenderRule, which work identically to the HQL report type. The value to render will be passed as value and the full row as row. Note that the valueClass will already have been processed, so your script can expect a "real" object and not a string if you've specified one.

Examples

There is a very simple example at config/example.

Future enhancements?

The following could be added to this data source at some point. I have already implemented them in my "big" version of this data source, but they would need some work to convert to this one.

• Support ifEmpty: if this column has no value, use this one instead.
• Support object dot-walking (e.g., value.manager.assignedRoles.name).
• Parse a custom column as XML and extract an XPath from it.
• Extract something from a custom JSON column.
• Beanshell pre-processing of row data: useful to modify output in a way that's difficult to do in SQL, exclude rows entirely, etc.
• Use the input locale and time zones.
• Automatically translate extension attributes: detect and convert a token like "(department)" to "extended1".