Issues with Custom report on Custom object

IdentityIQ (IIQ) IIQ Discussion and Questions
,
1

Which IIQ version are you inquiring about?

8.4

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

I created a custom object which takes advantage of the Custom class’s Attributes map, allowing me to save a record when users complete a form when they create an access request.

The record looks something like this in the Attributes map:

<entry key="userId (multiple users can submit these forms, so each of these entries represents a user">
<value>
<List>
<Map>
<entry key="dateTime">
<value>
<Date>epoch bigint</Date>
</value>
</entry>
<entry key="name" value="NAME">
<entry key="managed attribute display name" value="VALUE">
<entry key="managed attribute value" value="VALUE">
</Map>
<Map>
<entry key="dateTime 2 (user can submit multiple forms across numerous access requests)">
<value>
<Date>epoch bigint</Date>
</value>
</entry>
<entry key="name" value="NAME">
<entry key="managed attribute display name" value="VALUE">
<entry key="managed attribute value" value="VALUE">
</Map>
</List>
</value>
</entry>

I have successfully created a SQL query that can display the XML data in a SQL table, but I have not managed to present that data in an identityiq report.

The SQL query:

SELECT
    entry_parent.value(‘@key’, ‘nvarchar(255)’) AS userId,
    [entry].value(‘(entry[@key=“completedOn”]/value/Date)[1]’, ‘bigint’) AS completedOn,
    [entry].value(‘(entry[@key=“fullName”]/@value)[1]’, ‘nvarchar(255)’) AS fullName,
    [entry].value(‘(entry[@key=“managedAttributeDisplayName”]/@value)[1]’, ‘nvarchar(255)’) AS managedAttributeDisplayName,
    [entry].value(‘(entry[@key=“managedAttributeValue”]/@value)[1]’, ‘nvarchar(255)’) AS managedAttributeValue
FROM (
    SELECT CAST(attributes AS XML) as attributes_xml
        from spt_custom where name = ‘CUSTOM ENTRY NAME HERE’) as src
CROSS APPLY attributes_xml.nodes(‘/Attributes/Map/entry’) AS parent(entry_parent)
CROSS APPLY entry_parent.nodes(‘value/List/Map’) AS t([entry])
order by completedOn Desc

When I tried to create the report myself, I have tried to use the Filter-type datasource to use the OptionsQuery and try to write the SQL query from scratch, but that didn’t work. I also tried to use the Hql-type datasource to use the Query object, keeping only the FROM and CROSS APPLY pieces of my query, but identityiq threw errors on the first open parenthesis “(“ of the FROM section.

How do I create a custom report that can show numerous entries of data like this, 1 form submission per row?

2

@michael_mcewan You have to use sql script based report. check this thread :

Custom Reporting in IdentityIQ - Content / Community Blog - SailPoint Developer Community

Hope this will help

thanks,

Pravin

3

Hi @michael_mcewan ,

If your form submission does create an access request, you can use the out-of-the-box “Access Request Status Report” to retrieve the necessary data.

4

I will take a look at this and return with my results. Thank you for linking this post.

5

Thank you for pointing out that report, but it’s actually the access request that creates the form. And it’s the saved form response data that I need to create my report from.

6

Custom objects are not intended to be used to store large amounts of data (like per-user request data). To quote the JavaDocs for sailpoint.object.Custom:

The intent is that there not be very many of these so they can be considered “exportable” classes. Do not use it for things like a custom audit log where you can have thousands of instances.

Unfortunately, I’m not familiar enough with access request processes to suggest an alternative approach.

If you decide to go with Custom objects, I expect you will eventually run into performance problems and potentially issues with the size of the attributes map. I’ve personally encountered at least one scenario in which I was unable to persist a Custom object that had a large amount of data in it.

7

We have an open source library that will let you do this and you can just specify your attributes CLOB in the report and it can parse whatever values you want.

We also did a developer days presentation on it a couple years ago.