I am working on the integration of multiple applications in SailPoint IdentityIQ (IIQ). All of these applications are the same in functionality but have different names. I would like to know if it is feasible to add a variable in the JDBC connector that allows configuring the application identifier from the configuration form, instead of having to manually modify the SQL queries every time a new application is added.
Specifically, I want to know if I can use the variable in the WHERE clause of the SQL statement instead of a static value, so that instead of:
WHERE 1=1
AND p.aplica_pk = âApplicationNameâ
I could use:
WHERE 1=1
AND p.aplica_pk = < variable >
Does anyone have experience with this or can confirm if it is possible to do this in IIQ?
Thank you for your suggestion regarding the use of ${applicationName}. Iâm considering how to dynamically extract part of this variable for my query.
Is it possible to subdivide ${applicationName} to get only the specific part I need (for example, extracting âExampleValueâ from âApplication_ExampleValueâ) within the SQL query in SailPoint IdentityIQ?
I tried to use SUBSTRING and other similar functions to extract part of this variable, but I encountered the following error during the preview:
[ ConnectorException ] [ Error details ] The server encountered an unexpected error while contacting target system. Please check the logs. Non supported SQL92 token at position: 494.
It seems like this is trying to solve a problem that doesnât exist. Each new application is different from another but doesnât change day by day. If you export application A to XML, then you can just do a search and replace on the application name, save under a new name, and then import it. Thatâs what a lot of these âapplication duplicatorsâ do.
I have a plugin that allows you to clone your AD application into a hundred, a thousand applications, based on the AD Group collections that represent each application. Thatâs an example of doing that.
I also have some code that allows you to update the SQL before each aggregation, I use that for delta aggregations when clients donât want to do the suggested delta aggregation setup and just want to use a date stamp in the data.
I understand. In this case, what I need is the substring of ${applicationName} since p.aplika_pk has the correct value, and itâs the application name that has something extra beyond the correct value. I will try replacing p.aplika_pk with ${applicationName} in that query line to see if it works.
Another option would be to include the module in the description, so thereâs no need to do a substring, for example, $applicationDescription if that exists.
Hi @mercury , I understand what youâre saying and that itâs not really a problem. What Iâm proposing is based on a clientâs requirementâthey would like to replicate each similar application without having to modify the SQL statement directly. Instead, by using the application name, which will have a global part + the module name (which goes into the query), they want to be able to perform the aggregation query
Hi @phodgdon , thanks for your response. Iâve never used tokens via SSB before, are you referring to something like this? https://community.sailpoint.com/t5/IdentityIQ-Forum/Tokenize-object-id-in-SSB/m-p/244253#M184131. How could this help me? Because what I need is to insert a variable in the SQL statement of the JDBC connector, and I canât imagine how to use those tokens there. Apologies for my lack of knowledge
I tried using p.aplica_pk = SUBSTRING(${applicationName}, INSTR(${applicationName}, '_') + 1), but this generates the error:
Exception during aggregation of Object Type account on Application Alhambra_ESCRITORIOCOMERCIAL. Reason: Unable to create iterator sailpoint.connector.ConnectorException: [ConnectorException] [Error details] The server encountered an unexpected error while contacting the target system. Please check the logs. Non supported SQL92 token at position: 505.
This is not part of the connector code. Only $identity is translated. I do like the unix ${xx} notation but it was never conceived so itâs not part of the connectorâs functionality. And @phodgdon what they want is to have a single connector SQL that works on 100 applications, so tokenizing isnât going to help them.