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.

5

Hi @selvasanthosh, Transform sample you suggested is getting the lifecycle state as ACTIVE for all identities (identities that are having end date - some have end date 30 days past end date, and some are within 30 days)

Can you pls suggest correct velocity logic for evaluating the state correctly.

6

Hi @ChandrakalaS

The issue is that using a far future date "2050-12-31T00:00:00.000Z" as a fallback in firstValid makes ALL users without end dates evaluate as “active” because future dates will never satisfy your past-date conditions. This is why everyone shows as “ACTIVE”.

Try this one that properly handles missing/null end dates:

{
  "name": "LifecycleStateTransform_Fixed",
  "type": "static",
  "attributes": {
    "hasEndDate": {
      "type": "conditional",
      "attributes": {
        "expression": "$endDateRaw eq null",
        "positiveCondition": "false",
        "negativeCondition": "true",
        "endDateRaw": {
          "type": "accountAttribute",
          "attributes": {
            "sourceName": "A Test HR Source",
            "attributeName": "end_date"
          }
        }
      }
    },
    "endDatePast": {
      "type": "dateCompare",
      "attributes": {
        "firstDate": {
          "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"
          }
        },
        "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": "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"
          }
        },
        "operator": "gte",
        "positiveCondition": "true",
        "negativeCondition": "false"
      }
    },
    "value": "#if($hasEndDate=='false')active#elseif($endDatePast=='true' && $thirtyDaysPastEndDate=='true')terminated#elseif($endDatePast=='true')inactive#{else}active#end"
  }
}

This will check
First check if end_date exists using a conditional transform

  • Only perform date comparisons when end date actually exists

  • This prevents the “always active” issue caused by future date fallbacks

This should correctly identify users with past end dates and apply the proper lifecycle states.

7

@selvasanthosh For the Transform that you suggested is giving this error when previewed:

There was an exception while calculating the value for this attribute. Error during transformation for attribute: cloudLifecycleState (Transform ID: LifecycleStateTransform_Fixed) Cause: Error rendering template: $endDateRaw eq null

Please suggest me the correction to be made.

Thanks in advance.

8

Hi @ChandrakalaS ,

Try this one and check

{
  "name": "LifecycleStateTransform_Final_Working",
  "type": "static",
  "attributes": {
    "value": "#set($endDateRaw = '')#set($endDateFormatted = '')#foreach($attr in $identity.getAccounts('A Test HR Source'))#if($attr.getAttribute('end_date'))#set($endDateRaw = $attr.getAttribute('end_date'))#set($endDateFormatted = $date.format('yyyy-MM-dd', $date.toDate('MM/dd/yyyy K:m:s a z', $endDateRaw)))#break#end#end#if($endDateRaw == '' || !$endDateRaw)active#else#set($today = $date.format('yyyy-MM-dd', $date))#set($endDatePlusThirty = $date.format('yyyy-MM-dd', $date.add($date.toDate('yyyy-MM-dd', $endDateFormatted), 30)))#if($endDateFormatted < $today)#if($today >= $endDatePlusThirty)terminated#{else}inactive#end#{else}active#end#end"
  }
}

If it’s still throwing an error here’s an Alternative simpler approach using only nested transforms (no velocity variables):

{
  "name": "LifecycleStateTransform_Working",
  "type": "firstValid",
  "attributes": {
    "values": [
      {
        "type": "conditional",
        "attributes": {
          "expression": {
            "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": "past",
              "negativeCondition": "future"
            }
          },
          "positiveCondition": {
            "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/dd/yyyy K:m:s a z",
                      "outputFormat": "ISO8601"
                    }
                  },
                  "expression": "+30d"
                }
              },
              "operator": "gte",
              "positiveCondition": "terminated",
              "negativeCondition": "inactive"
            }
          },
          "negativeCondition": "active"
        }
      },
      "active"
    ]
  }
}

This approach avoids variable references entirely and uses nested conditional logic that SailPoint can properly parse and execute.

9

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