How to parse and filter JSON String in Sailpoint Workflows?

Identity Security Cloud (ISC) ISC Discussion and Questions
,
1

Hi everyone,

I’m working with a Workflow in SailPoint where I’ve set up a trigger to fire on identity attribute change. My goal is to parse an attribute newValue that contains a complex JSON string representing various events related to a user’s profile.

Here’s an example of the JSON structure I’m dealing with:
{ “Actions”: [{ “ActionTypeCode” : “02”, “ActionTypeName”: “Organizational Change”, “ReasonCode” : “01”, “ReasonTypeText”: “Transfer without change of pay”, “ActionStartDate” : “2022-09-01”, “ActionEndDate” : “9999-12-31”, “EmployeeNumber” : “01000014” } ,{ “ActionTypeCode” : “90”, “ActionTypeName”: “Migration”, “ReasonCode” : “”, “ReasonTypeText”: “”, “ActionStartDate” : “2018-04-01”, “ActionEndDate” : “2022-08-31”, “EmployeeNumber” : “01000014” } ] }

In the beautified view:

{
  "Actions": [
    {
      "ActionTypeCode": "02",
      "ActionTypeName": "Organizational Change",
      "ReasonCode": "01",
      "ReasonTypeText": "Transfer without change of pay",
      "ActionStartDate": "2022-09-01",
      "ActionEndDate": "9999-12-31",
      "EmployeeNumber": "01000014"
    },
    {
      "ActionTypeCode": "90",
      "ActionTypeName": "Migration",
      "ReasonCode": "",
      "ReasonTypeText": "",
      "ActionStartDate": "2018-04-01",
      "ActionEndDate": "2022-08-31",
      "EmployeeNumber": "01000014"
    }
  ]
}

The challenge is that SailPoint treats this entire JSON data as a string stored in the identity attribute, which makes it difficult to perform complex parsing and filtering.

image
image634×659 24 KB

For example, I need to extract the ActionStartDate of specific events based on criteria like ActionTypeCode and compare these dates with the current date to determine further actions (e.g., sending emails to the responsible manager).

Any guidance or examples on how to achieve this would be greatly appreciated. Thanks in advance for your help!

2

@Blazelip - Below is the escaped JSON for your reference

{\r\n  \"Actions\": [\r\n    {\r\n      \"ActionTypeCode\": \"02\",\r\n      \"ActionTypeName\": \"Organizational Change\",\r\n      \"ReasonCode\": \"01\",\r\n      \"ReasonTypeText\": \"Transfer without change of pay\",\r\n      \"ActionStartDate\": \"2022-09-01\",\r\n      \"ActionEndDate\": \"9999-12-31\",\r\n      \"EmployeeNumber\": \"01000014\"\r\n    },\r\n    {\r\n      \"ActionTypeCode\": \"90\",\r\n      \"ActionTypeName\": \"Migration\",\r\n      \"ReasonCode\": \"\",\r\n      \"ReasonTypeText\": \"\",\r\n      \"ActionStartDate\": \"2018-04-01\",\r\n      \"ActionEndDate\": \"2022-08-31\",\r\n      \"EmployeeNumber\": \"01000014\"\r\n    }\r\n  ]\r\n}

You can use below website for your JSON escape and unescape purpose -

Thank you

1 Like
3

Thank u for such a quick reply @officialamitguptaa ,

As far as I understood doing JSON escape string that lets us to use this as Worklow Testing Input. But how I can parse it afterwards? Since it’s still a string and as I said I need a complex parsing of it (item filtering, etc)

Now we test the workflow with mock data.
Btw, will the auto escape-string come into game during the real “game”?

image
image660×449 19.5 KB

4

Hi @Blazelip,

Have you tried using something like this.?

$.changes[*].oldValue.Actions[?(@.ActionTypeCode == "02")].ActionStartDate

If you can have a define variable operator and use the above filter, you should be getting the ActionStartDate, that can be used in you subsequent operations.

5

image
image1313×562 32.6 KB

I’ve tried, since the oldValue value is represented as a JSON like string, not a JSON, therefore JSONPath editor throws an error and Sailpoint doesn’t dive deeper than oldValue key-pair.

6

Hi @Blazelip,

Looking at your incoming data (Identity attribute), you may need to convert this attribute first into a different format using a transform (Probably a replace transform).

The transformed value can then be called in the workflow so that it can be correctly parsed.

1 Like
7

Thank u Jesvin for a timely reply,
I thought of this, but previously I wanted to make sure that there is no such complex parsing feature by SP workflows.