Writing custom reports in IIQ becomes trivial as querying a database with our JDBC Reporting utilities, part of the IIQCommon suite of tools.
The following code snippet is derived from a real example of a report used at a real IIQ installation.
As you can see, it’s just a basic wrapper around an actual SQL query, which can be as powerful as your whole database. You don’t need to know anything about IIQ, or Hibernate, or the vagaries of IIQ Filters. It’s just plain SQL.
Processing outputs is automatic and trivial, too, with a variety of options for formatting and display.
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE TaskDefinition PUBLIC "sailpoint.dtd" "sailpoint.dtd">
<TaskDefinition executor="sailpoint.reporting.LiveReportExecutor" name="Demo - Failed Tasks Report" progressMode="Percentage" resultAction="Rename" subType="Demo" template="true" type="LiveReport">
<Attributes>
<Map>
<entry key="report">
<value>
<LiveReport title="Demo - Failed Tasks Report">
<DataSource dataSourceClass="com.identityworksllc.iiq.common.reporting.JDBCDataSource" type="Java">
<Query>
<![CDATA[
select distinct tr.id, tr.name, tr.created, tr.completed, partition_errors.text as errors
from (
select
spt_task_result.*,
case when (attributes is null or dbms_lob.substr(attributes, 1, 1) = ' ') then to_clob('<Attributes/>') else attributes end attr
from spt_task_result
where
completed is not null and
schedule is not null and
(name like '%Daily%' or name like '%Process%') and
completion_status = 'Error' and
TO_DATE('19700101','yyyymmdd') + ((created/1000)/24/60/60) > sysdate - 4
order by created desc
) tr
outer apply xmltable(
'/Attributes//TaskResult/Messages/Message/Parameters/Message/Parameters/String'
passing xmltype(tr.attr)
columns text VARCHAR(4000) PATH 'text()'
) partition_errors
order by completed desc
]]>
</Query>
</DataSource>
<Columns>
<ReportColumnConfig field="name" header="Task Result Name" property="name" width="110"/>
<ReportColumnConfig field="created" header="Start Time" property="created:timestamp:yyyy-MM-dd HH:mm:ss z" width="150"/>
<ReportColumnConfig field="completed" header="Completion Time" property="completed:timestamp:yyyy-MM-dd HH:mm:ss z" width="150"/>
<ReportColumnConfig field="errors" header="Error Message(s)" property="errors" width="110"/>
</Columns>
</LiveReport>
</value>
</entry>
</Map>
</Attributes>
<Description>Returns the list of all failed tasks</Description>
<RequiredRights>
<Reference class="sailpoint.object.SPRight" name="Reporter_People"/>
</RequiredRights>
</TaskDefinition>
The JDBCDataSource is just one of several powerful reports in IIQCommon. Contact us for more information using the link below!
NOTE: While the full version of the reporting framework is not part of the open-source subset of IIQCommon, you can find a simplified open-source demo in our repository, which we demonstrated at SailPoint’s 2023 Developer Days conference.