Lookup transform table values

[chellanikaps3 (Kapil C)]

Hi All,

I am working on a lookup transform and wanted to understand that if the values passed in the table can be passed through any other way on the runtime instead of passing them manually like key-value pair.

For Example in the lookup table i am passing key value as:

Userid( XXYZ) : yes

I need to pass this userid to the table at runtime. Is there any Script or API call can be made. Usually i paste the table value manually but need to automate it.

Thanks in advance

ISC - Workflows, Rules, & Transforms - AMS Identity Security Cloud (ISC) > ISC Discussion and Questions

[iamnithesh (Nithesh Rao)]

Can you explain your requirement in detail please?

[Pankaj_IAM_SailPoint (Pankaj Kumar)]

Hello Kapil,

You can use Poweshell Script to Update SailPoint Lookup Transform:
Step 1: Define Variables
$tenant = “your-tenant.api.identitynow.com”
$clientId = “your-client-id”
$clientSecret = “your-client-secret”
$transformId = “your-transform-id”
Step 2: Get Access Token
$tokenBody = @{
grant_type = “client_credentials”
client_id = $clientId
client_secret = $clientSecret
}

$tokenResponse = Invoke-RestMethod -Method Post -Uri "https://$tenant/oauth/token"
-Body $tokenBody

$accessToken = $tokenResponse.access_token

Step 3: Update Lookup Table

$headers = @{
Authorization = “Bearer $accessToken”
“Content-Type” = “application/json”
}

$body = @{
type = “lookup”
attributes = @{
table = @{
“XXYZ” = “yes”
“ABCD” = “yes”
“PQRS” = “yes”
}
}
} | ConvertTo-Json -Depth 5

Step 4: Call SailPoint Transform API
Invoke-RestMethod -Method Put -Uri "https://$tenant/v3/transforms/$transformId"
-Headers $headers `
-Body $body

This script will Update the Lookup Transform table automatically.

[kallajayaram (Jaya Ram Kalla)]

Hi @chellanikaps3

In SailPoint Identity Security Cloud, the Lookup Transform table is static and cannot be dynamically populated at runtime through scripts or APIs. The values must be configured manually in the transform definition. If you need dynamic values such as UserID, it is better to fetch them from an identity attribute or populate them through a workflow or rule and then reference that attribute in the transform.

Could you please share your exact requirement

Example:

If userid =123 return →YES

If userid= ABC return → No

{

“name”: “UserIDLookup”,

“type”: “lookup”,

“attributes”: {

"input": {

  "type": "identityAttribute",

  "attributes": {

    "name": "uid"

  }

},

"table": {

  "123": "YES",

  "ABC": "NO"

},

"default": "NO"

}

}

[sup3rmark (Mark Corsillo)]

The way a lookup transform works is that you pass in a value, and that value is checked against the defined table. If the value exists in the table, the corresponding response value is returned; otherwise, the default case is returned. You’d have to define the values to search for as well as the responses for each case; there is no way to check an external source for the key-value pairs dynamically.

[chellanikaps3 (Kapil C)]

Thanks Pankaj for the prompt response, this will update the lookup table values from csv or database column as well ?

[chellanikaps3 (Kapil C)]

Thanks for the update. Can we user a rule transform for this purpose.

[chellanikaps3 (Kapil C)]

For the lookup transform to be executed, you need to provide the key-value pair in the input and set a default value.

So I have a requirement where the input values to the transform can be passed through automation instead of manually.

For example : I have 100 input key- value pair and the default is set to no.

XYZ = yes

RST=no

POM=yes

TCU=no.

I need to insert these input values through automation from a csv file or some DB tables.

[chellanikaps3 (Kapil C)]

Thanks Jaya for the response. Below is the requirement

For the lookup transform to be executed, you need to provide the key-value pair in the input and set a default value.

So I have a requirement where the input values to the transform can be passed through automation instead of manually.

For example : I have 100 input key- value pair and the default is set to no.

XYZ = yes

RST=no

POM=yes

TCU=no.

I need to insert these input values through automation from a csv file or some DB tables.

@chellanikaps3

For the lookup transform to be executed, you need to provide the key-value pair in the input and set a default value.

So I have a requirement where the input values to the transform can be passed through automation instead of manually.

For example : I have 100 input key- value pair and the default is set to no.

XYZ = yes

RST=no

POM=yes

TCU=no.

I need to insert these input values through automation from a csv file or some DB tables.

[David_Norris (Terry Yau)]

@chellanikaps3

I need to insert these input values through automation from a csv file or some DB tables.

You might want to take a look at this, to brainstorm:

Custom entities "Non-Identites" in ISC (Location/Site/Building/Facilities) - supported approach

Please be sure you’ve read the docs and API specs before asking for help. Also, please be sure you’ve searched the forum for your answer before you create a new topic. Hi everyone, A quick question about ISC. In IdentityIQ, we could use CustomObject to store custom data types… In ISC, is there any supported equivalent concept to store/manage a custom entity (non-identity domain), for example, “Location/Facilities” (site/building/floor) physical access (e.g. lockers, visitors, park…

…and yes, you can update the lookup transform via API (e.g. PowerShell SDK)…but you’ll need somewhere to store the script, the DB credential, the PAT and run the script.

Create Transform API:

create-transform | SailPoint Developer Community

Update Transform API:

update-transform | SailPoint Developer Community

[iamnithesh (Nithesh Rao)]

@chellanikaps3

I need to insert these input values through automation from a csv file or some DB tables.

Is this going to be one time effort or do you want to update the transform regularly by reading the CSV? like daily or once every few hours?

If you are thinking of reading from CSV in real time while identity attributes are calculated, then that’s not possible with Lookup transforms. What @kallajayaram has explained in this response is exactly what they are designed to do.

I am seeing that the responses are based on your original post on how to update a Lookup transform and these might not be leading you in the right direction. If you explain your requirement in pure business language rather than in terms of how to design the solution using a specific type of transform, you might receive better suggestions.

[kallajayaram (Jaya Ram Kalla)]

Hi @chellanikaps3

Yes, a Rule Transform can be used for this requirement.

If you have a large number of key-value mappings (for example 100+), maintaining them directly inside a Lookup Transform can become difficult. In such cases, the mappings can be maintained in an external source such as a CSV file or a database table, and the rule can dynamically read those values during execution.

A possible approach would be:

1. Store the key-value mappings in a CSV file or database table.

2. In the rule, read the data and load it into a Map structure.

3. Compare the incoming input value with the keys in the map.

4. Return the mapped value (for example yes/no). If the key is not found, return the default value.

This approach improves scalability and maintainability, as updates can be made directly in the external data source without modifying the transform each time.

Another alternative is to automate the Lookup Transform using the Transforms API, where a script reads the CSV or database entries and updates the lookup table dynamically in the transform JSON.

Both approaches help avoid manually maintaining large lookup tables within ISC and make the solution easier to manage in the long term.

[iamnithesh (Nithesh Rao)]

@kallajayaram

In the rule, read the data and load it into a Map structure.

Not sure if I understand this part. Where would the file be stored? And would you be able to read a CSV file from cloud rule which is the rule type for calculating the identity attributes

[kallajayaram (Jaya Ram Kalla)]

Hi @iamnithesh

please correct me if I am wrong

In ISC cloud rules we cannot directly read external files like CSV or access databases because cloud rules run in a restricted SailPoint environment.

A more practical approach is to maintain the key-value mappings in an external CSV file or database and use an automation script (for example Python or PowerShell) to convert the mappings into the lookup table JSON format.

The script can then call the SailPoint Transforms API to update the Lookup Transform automatically. This way the mappings can be maintained externally while the transform is updated programmatically.

This approach works well when there are 100+ mappings and avoids manually maintaining large lookup tables in the transform configuration.

[David_Norris (Terry Yau)]

You sound like an AI…

…saw you replacing that first line to “please correct me if I am wrong”

[kallajayaram (Jaya Ram Kalla)]

Hi @David_Norris

Thanks for pointing that out. I’m still learning ISC and exploring different approaches, so I appreciate the feedback.

My understanding was that since cloud rules in ISC cannot directly access external files like CSVs or databases, one possible approach could be to maintain the mappings externally and update the Lookup Transform programmatically using the Transforms API.

I’m definitely open to corrections and would really appreciate learning the recommended approach from the community for handling larger lookup mappings in ISC.

Thanks again for your input

[David_Norris (Terry Yau)]

FYI: AI Usage Policy - SailPoint Developer Community

You need to state your response is AI-assisted if that’s the case. (and even that sounded like an AI response)

[iamnithesh (Nithesh Rao)]

While I agree with you, I do not think it’s necessary as you can manually convert a csv file to json “key”:”value” format simply using the formula in Excel and copy/paste them into your transform using VSCode

Whether you use script or do it manually, end result is the same.

I have used lookup transforms with 300+ (or may be more) key/value pairs and never noticed any issue with performance

[chellanikaps3 (Kapil C)]

@kallajayaram

ped value (for example yes/no). If the key is not found, return the default value.

This approach improves scalability and maintainability, as updates can be made directly in the external data source without modifying the transform each time.

Thanks for the response.