Have you ever needed to query data directly from your IIQ database for reporting? Have you ever wanted to test a SailPoint filter or HQL query before you used it in a role membership rule? Have you ever wondered why your IIQ Filters behave the way they do? Have you ever needed a Filter String when you have a CompositeFilter XML, or vice versa?
The open-source Instrumental ID Query Plugin can help you with all of these things.
Querying
The plugin provides an input area where you can enter a SQL script, an HQL query, or a SailPoint filter (in either Filter String or XML format).
It then executes your query and provides the results in a table in the browser. The results can be further filtered dynamically in the table or exported to a CSV file for offline processing.
Filter translation
The Query Plugin can also invoke SailPoint’s Filter compilation classes to retrieve the HQL and SQL rendition of a Filter. This is commonly used for a few purposes:
- You can debug why certain Filters produce unintuitive results.
- You can also use the resulting SQL query to tune a common Filter by applying indexes to your tables.
- You can translate a Filter String directly to a CompositeFilter XML, which can be copied into the Role Membership screen or an XML artifact in your build. This is very useful in combination with Advanced Analytics.
The following screenshot shows the translation of a Filter on first name and department.
