Transform to check 30 days past End Date Check

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

I am creating a Transform to determine Life Cycle State based on End Date.

Following is the validation I have in Transform:

  • Checking End Date is Past Date - must be treated as Terminated
  • Compare End Date and Current date - If the current date is 30 days passed End date - then treated as Terminated, if the current data is not passed 30 days from End Date then treated as Inactive
  • Else Identity is considered as Active

For this I have created Transform:

{
  "name": "LifecycleStateTransfrom_30daypastenddatecheck",
  "type": "static",
  "attributes": {
    "inPast": {
      "type": "dateCompare",
      "attributes": {
        "firstDate": {
          "type": "dateFormat",
          "attributes": {
            "input": {
              "type": "dateMath",
              "attributes": {
                "expression": "now/d"
              }
            },
            "inputFormat": "yyyy-MM-dd",
            "outputFormat": "ISO8601"
          }
        },
        "secondDate": {
          "type": "dateFormat",
          "attributes": {
            "input": {
              "type": "accountAttribute",
              "attributes": {
                "sourceName": "A Test HR Source",
                "attributeName": "end_date"
              }
            },
            "inputFormat": "MM/d/yyyy K:m:s a z",
            "outputFormat": "ISO8601"
          }
        },
        "operator": "gte",
        "positiveCondition": "true",
        "negativeCondition": "false"
      }
    },
    "thirtyDayPastEndDateCheck": {
      "type": "dateCompare",
      "attributes": {
        "firstDate": {
          "type": "dateMath",
          "attributes": {
            "expression": "now/d"
          }
        },
        "secondDate": {
          "type": "dateMath",
          "attributes": {
            "input": {
              "type": "dateFormat",
              "attributes": {
                "input": {
                  "type": "accountAttribute",
                  "attributes": {
                    "sourceName": "A Test HR Source",
                    "attributeName": "end_date"
                  }
                },
                "inputFormat": "MM/d/yyyy K:m:s a z",
                "outputFormat": "ISO8601"
              }
            },
            "expression": "+30d"
          }
        },
        "operator": "gte",
        "positiveCondition": "true",
        "negativeCondition": "false"
      }
    },
    "value": "#if($inPast=='true' && $thirtyDayPastEndDateCheck == 'false')inactive#elseif($thirtyDayPastEndDateCheck == 'true')terminated#{else}active#end"
  }
  }

Have applied this transform to Cloud LifeCycle State attribute in Identity Profile and observing following error when previewed to test the transform

There was an exception while calculating the value for this attribute. Error during transformation for attribute: cloudLifecycleState (Transform ID: LifecycleStateTransfrom_30daypastenddatecheck) Cause: DateCompare failed: both firstDate and secondDate must be provided. Current values: firstDate[2025-09-21T00:00Z], secondDate[null]

For user the End date format displayed on the UI is:

image
image1919×924 85.9 KB

Our HR Source is JDBC Asset and date format is:

image
image1317×139 122 KB

Please suggest me what is the change to be made in transform to fix the error

2

Hi @ChandrakalaS

The problem happens if the end_date field is missing or not in the expected format—this causes the second date in the comparison to be null, which makes the transform fail.

Try this:

Wrap every use of the end_date attribute in a firstValid transform. This will make sure that SailPoint always sends a value (even if the HR record has a blank or null end date), which stops the error and allows the Logic to work as expected. Here’s the exact JSON you need to use where you reference the formatted end_date:

{
  "type": "firstValid",
  "attributes": {
    "values": [
      {
        "type": "dateFormat",
        "attributes": {
          "input": {
            "type": "accountAttribute",
            "attributes": {
              "sourceName": "A Test HR Source",
              "attributeName": "end_date"
            }
          },
          "inputFormat": "MM/d/yyyy K:m:s a z",
          "outputFormat": "ISO8601"
        }
      },
      ""
    ]
  }
}

Replace any place in your transform where you fetch or format the end date with this block (as the input to dateMath, dateCompare, etc). This way, if the end_date is missing, the transform will get an empty string instead of null, and SailPoint won’t throw an error.

3

@selvasanthosh As suggested by you, I made the transform changes as below. Still, seeing the same error.

{
    "name": "LifecycleStateTransfrom_30daypastenddatecheck1",
    "type": "static",
    "attributes": {
        "inPast": {
            "type": "dateCompare",
            "attributes": {
                "firstDate": {
                    "type": "dateFormat",
                    "attributes": {
                        "input": {
                            "type": "dateMath",
                            "attributes": {
                                "expression": "now/d"
                            }
                        },
                        "inputFormat": "yyyy-MM-dd",
                        "outputFormat": "ISO8601"
                    }
                },
                "secondDate": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "dateFormat",
                                "attributes": {
                                    "input": {
                                        "type": "accountAttribute",
                                        "attributes": {
                                            "sourceName": "A Test HR Source",
                                            "attributeName": "end_date"
                                        }
                                    },
                                    "inputFormat": "MM/dd/yyyy K:m:s a z",
                                    "outputFormat": "ISO8601"
                                }
                            },
                            ""
                        ]
                    }
                },
                "operator": "gte",
                "positiveCondition": "true",
                "negativeCondition": "false"
            }
        },
        "thirtyDayPastEndDateCheck": {
            "type": "dateCompare",
            "attributes": {
                "firstDate": {
                    "type": "dateMath",
                    "attributes": {
                        "expression": "now/d"
                    }
                },
                "secondDate": {
                    "type": "dateMath",
                    "attributes": {
                        "input": {
                            "type": "firstValid",
                            "attributes": {
                                "values": [
                                    {
                                        "type": "dateFormat",
                                        "attributes": {
                                            "input": {
                                                "type": "accountAttribute",
                                                "attributes": {
                                                    "sourceName": "A Test HR Source",
                                                    "attributeName": "end_date"
                                                }
                                            },
                                            "inputFormat": "MM/dd/yyyy K:m:s a z",
                                            "outputFormat": "ISO8601"
                                        }
                                    },
                                    ""
                                ]
                            }
                        },
                        "expression": "+30d"
                    }
                },
                "operator": "gte",
                "positiveCondition": "true",
                "negativeCondition": "false"
            }
        },
        "value": "#if($inPast=='true' && $thirtyDayPastEndDateCheck == 'false')inactive#elseif($thirtyDayPastEndDateCheck == 'true')terminated#{else}active#end"
    }
}

Please suggest me the change to be made in the transform

4 
{
  "name": "LifecycleStateTransfrom_30daypastenddatecheck",
  "type": "static",
  "attributes": {
    "endDatePast": {
      "type": "dateCompare",
      "attributes": {
        "firstDate": {
          "type": "firstValid",
          "attributes": {
            "values": [
              {
                "type": "dateFormat",
                "attributes": {
                  "input": {
                    "type": "accountAttribute",
                    "attributes": {
                      "sourceName": "A Test HR Source",
                      "attributeName": "end_date"
                    }
                  },
                  "inputFormat": "MM/dd/yyyy K:m:s a z",
                  "outputFormat": "ISO8601"
                }
              },
              "2050-12-31T00:00:00.000Z"
            ]
          }
        },
        "secondDate": {
          "type": "dateMath",
          "attributes": {
            "expression": "now/d"
          }
        },
        "operator": "lt",
        "positiveCondition": "true",
        "negativeCondition": "false"
      }
    },
    "thirtyDaysPastEndDate": {
      "type": "dateCompare",
      "attributes": {
        "firstDate": {
          "type": "dateMath",
          "attributes": {
            "expression": "now/d"
          }
        },
        "secondDate": {
          "type": "firstValid",
          "attributes": {
            "values": [
              {
                "type": "dateMath",
                "attributes": {
                  "input": {
                    "type": "dateFormat",
                    "attributes": {
                      "input": {
                        "type": "accountAttribute",
                        "attributes": {
                          "sourceName": "A Test HR Source",
                          "attributeName": "end_date"
                        }
                      },
                      "inputFormat": "MM/dd/yyyy K:m:s a z",
                      "outputFormat": "ISO8601"
                    }
                  },
                  "expression": "+30d"
                }
              },
              "2050-12-31T00:00:00.000Z"
            ]
          }
        },
        "operator": "gte",
        "positiveCondition": "true",
        "negativeCondition": "false"
      }
    },
    "value": "#if($endDatePast=='true')#if($thirtyDaysPastEndDate=='true')terminated#{else}inactive#end#{else}active#end"
  }
}

@ChandrakalaS can you try this one

The fallback value "2050-12-31T00:00:00.000Z" serves two purposes:

  1. Prevents Null Failures: If the end_date attribute is missing or blank, firstValid will return this non-null ISO8601 date, ensuring that the dateCompare and dateMath operations always have a valid timestamp to work with. Otherwise, comparisons on null or empty strings would throw “both firstDate and secondDate must be provided” errors.

  2. Logical Neutral Value: By choosing a date far in the future—beyond any realistic end date—you ensure it never satisfies the “past” or “30 days past” conditions:

    • For the past-date check (end_date < now), a future date will yield false, correctly routing to the “active” branch.

    • For the 30-day check (now >= end_date + 30d), a future date will also yield false, which under the nested logic avoids marking someone inactive or terminated erroneously.