Adding a Variable to SQL Queries in JDBC Connector

IdentityIQ (IIQ) IIQ Discussion and Questions
,
1

Hello everyone,

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 in advance for your help.

Best regards,
Antonio

2

Hi @AntonioGvtt,

you can use $ statment like $(identity) to return the identityAttribute account(account name).

3

Hi @AntonioGvtt ,
try this

WHERE 1=1
AND p.aplica_pk = ${applicationName}
4

Hi @Zekkin ,

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.

I appreciate any guidance you can provide.

Thanks in advance!

Best regards,
Antonio

5

Try

SQL

SUBSTRING(p.aplica_pk, INSTR(p.aplica_pk, '_') + 1)

SQL Server

SUBSTRING(p.aplica_pk, CHARINDEX('_', p.aplica_pk) + 1, LEN(p.aplica_pk))
6

  1. p.aplica_pk:

    • This refers to the column aplica_pk from the table or alias p. This column contains the application name, such as "Application_ExampleValue".

  2. INSTR(p.aplica_pk, '_'):

    • INSTR is a string function that returns the position of the first occurrence of a specified substring within a string.
    • In this case, it looks for the underscore ('_') in the value of p.aplica_pk.
    • For example, if p.aplica_pk is "Application_ExampleValue", INSTR would return 11, which is the position of the underscore.

  3. INSTR(p.aplica_pk, '_') + 1:

    • This adds 1 to the position returned by INSTR, effectively moving the starting position to just after the underscore.
    • Continuing the previous example, this would yield 12, which is where the substring you want starts.

  4. SUBSTRING(p.aplica_pk, ...):

    • SUBSTRING is a function that extracts a substring from a given string starting from a specified position.
    • The syntax is usually SUBSTRING(string, start_position).
    • In this case, the starting position is calculated by the INSTR function plus one.
7

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.

8

Hi @Zekkin

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.

9

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

10

Why not just use tokens via the SSB?

11

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

12

Hello everyone,

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.

Do you have any suggestions?

Thanks!

13

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.

1 Like
15

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.