Transform to get the latest value from 4 date fields

I wanted to find the latest of the AD LastLogon, AD LastLogonTimestamp, AzureAD (EntraID) LastSignInDateTime, and AzureAD (EntraID) LastNonInteractiveSignInDateTime. Because I’m looking to compare four values, if I used @tyler_mairose solution from this thread, I would need something like 12 DateCompare transforms, and I’d still need a boatload of if statements, so I wanted to find another way. I ran into a lot of issues, but wanted to share my findings with the class. There’s probably room for improvement/optimization, but this seems to work so far.

  • Basically, the values we’re working with when we populate a variable via a Static transform are of string type… and you can’t use <=> operators with a string, even if it’s a date or a number. This means we need to get our values to be integers… but how can we do that?
  • Because transforms use Apache Velocity, which in turn is built on Java, we can use some Java tricks. We can use the parseInt() method that’s part of an integer object, but first we need an integer object! Turns out, this is as simple as… creating one: #set($i=0) will do that for us.
  • Now, to compare one value ($a) to another value ($b), we can do #if( $i.parseInt($a) > $i.parseInt($b)).
  • But wait… we’re working with dates, aren’t we? Where did integers come into this? Well, I wasn’t able to find anything out-of-the-box that would let us parse dates in-line to compare, so… guess we’re stuck with integers :stuck_out_tongue:
  • Of course, things can’t be that simple… integers have a max value of 2147483647. The values returned by EPOCH_TIME_WIN32 and EPOCH_TIME_JAVA are both way beyond that. We need something much, much smaller… but we can’t just create our own epoch time from a different year that we specify. Because I’m only trying to find the last time an account was logged in so that I can determine whether it’s been inactive long enough to justify disabling it, I settled on just looking at year-month-date-hour. I lose a little bit of precision here, but it’s good enough for this purpose.
  • In order to convert my dates to ints, then, I need to do a dateFormat transform where I specify my own output format (I called these variables $a, $b, $c, and $d to make the if statement easier to read). Here are the formats for the different attributes I’m using:
    • AD lastLogon/lastLogonTimestamp:
      • “inputFormat”: “EPOCH_TIME_WIN32”
      • “outputFormat”: “yyMMddHH”
    • AzureAD/EntraID lastSignInDateTime/lastNonInteractiveSignInDateTime:
      • “inputFormat”: “yyyy-MM-dd’T’HH:mm:ss’Z’”
      • “outputFormat”: “yyMMddHH”
  • I ultimately want to return the value as ISO8601, so each of the attributes also gets converted as necessary and stored in a separate variable. I gave these regular, meaningful names that reflect what they really are to make it easier to understand.

Here’s the full transform:

{
    "type": "static",
    "attributes": {
        "a": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "accountAttribute",
                                "attributes": {
                                    "attributeName": "lastLogon",
                                    "sourceName": "AD Dev"
                                }
                            },
                            {
                                "attributes": {
                                    "value": "125911584000000000"
                                },
                                "type": "static"
                            }
                        ]
                    }
                },
                "inputFormat": "EPOCH_TIME_WIN32",
                "outputFormat": "yyMMddHH"
            }
        },
        "adLastLogon": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "accountAttribute",
                    "attributes": {
                        "attributeName": "lastLogon",
                        "sourceName": "AD Dev"
                    }
                },
                "inputFormat": "EPOCH_TIME_WIN32",
                "outputFormat": "ISO8601"
            }
        },
        "b": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "accountAttribute",
                                "attributes": {
                                    "attributeName": "lastLogonTimestamp",
                                    "sourceName": "AD Dev"
                                }
                            },
                            {
                                "attributes": {
                                    "value": "125911584000000000"
                                },
                                "type": "static"
                            }
                        ]
                    }
                },
                "inputFormat": "EPOCH_TIME_WIN32",
                "outputFormat": "yyMMddHH"
            }
        },
        "adLastLogonTimestamp": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "accountAttribute",
                    "attributes": {
                        "attributeName": "lastLogonTimestamp",
                        "sourceName": "AD Dev"
                    }
                },
                "inputFormat": "EPOCH_TIME_WIN32",
                "outputFormat": "ISO8601"
            }
        },
        "c": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "accountAttribute",
                                "attributes": {
                                    "attributeName": "lastSignInDateTime",
                                    "sourceName": "Azure AD"
                                }
                            },
                            {
                                "attributes": {
                                    "value": "2000-01-01T00:00:00Z"
                                },
                                "type": "static"
                            }
                        ]
                    }
                },
                "inputFormat": "yyyy-MM-dd'T'HH:mm:ss'Z'",
                "outputFormat": "yyMMddHH"
            }
        },
        "aadLastInteractive": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "accountAttribute",
                    "attributes": {
                        "attributeName": "lastSignInDateTime",
                        "sourceName": "Azure AD"
                    }
                },
                "inputFormat": "yyyy-MM-dd'T'HH:mm:ss'Z'",
                "outputFormat": "ISO8601"
            }
        },
        "d": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "firstValid",
                    "attributes": {
                        "values": [
                            {
                                "type": "accountAttribute",
                                "attributes": {
                                    "attributeName": "lastNonInteractiveSignInDateTime",
                                    "sourceName": "Azure AD"
                                }
                            },
                            {
                                "attributes": {
                                    "value": "2000-01-01T00:00:00Z"
                                },
                                "type": "static"
                            }
                        ]
                    }
                },
                "inputFormat": "yyyy-MM-dd'T'HH:mm:ss'Z'",
                "outputFormat": "yyMMddHH"
            }
        },
        "aadLastNonInteractive": {
            "type": "dateFormat",
            "attributes": {
                "input": {
                    "type": "accountAttribute",
                    "attributes": {
                        "attributeName": "lastNonInteractiveSignInDateTime",
                        "sourceName": "Azure AD"
                    }
                },
                "inputFormat": "yyyy-MM-dd'T'HH:mm:ss'Z'",
                "outputFormat": "ISO8601"
            }
        },
        "value": "#set($i=0)#if(($i.parseInt($a) gt $i.parseInt($b)) && ($i.parseInt($a) gt $i.parseInt($c)) && ($i.parseInt($a) gt $i.parseInt($d)))$adLastLogon#elseif(($i.parseInt($b) gt $i.parseInt($a)) && ($i.parseInt($b) gt $i.parseInt($c)) && ($i.parseInt($b) gt $i.parseInt($d)))$adLastLogonTimestamp#elseif(($i.parseInt($c) gt $i.parseInt($a)) && ($i.parseInt($c) gt $i.parseInt($b)) && ($i.parseInt($c) gt $i.parseInt($d)))$aadLastInteractive#elseif(($i.parseInt($d) gt $i.parseInt($a)) && ($i.parseInt($d) gt $i.parseInt($b)) && ($i.parseInt($d) gt $i.parseInt($c)))$aadLastNonInteractive#{else}error#end"
    },
    "internal": false
}
2 Likes

I love the way you are thinking @sup3rmark

Indeed it will be a lot of child transforms required for this. And to use Apache velocity script in static Transform, I don’t think it supports all the operations/logics in velocity script. Scratching brain to implement the logic.

I had a lot of challenges in giving solution for this requirement: Remove duplicate value from transform - IdentityNow (IDN) / IDN Discussion and Questions - SailPoint Developer Community Forum not because I don’t know, because it is not supporting.

How many of you are thinking that, "Oh God, I wish that there is a way to write actual programming like Java, hell with this low code platform".

I wish SailPoint allow us to write piece of Java/Beanshell for complex scenarios in future.

Anyway thanks for publishing your work, really appreciate your efforts :slight_smile:

Thanks
Krish

You can always right a Generic Rule in BeanShell and then call that from your transform.

1 Like

Really curious about the underlying use case and I assume it’s similar to a number of cases where I’ve seen organizations trying to detect unused accounts. If so, then from a purist sense this isn’t the responsibility of the IGA/IAM solution. The IAM solution manages accounts from a “Rule Based” (role membership in IDN) or “Ad Hoc” (access request in IDN) basis. I know it’s a pretty “ideal” scenario, but just throwing it out there for discussion.

1 Like

Thanks Ed! Yeah, trying to detect unused accounts.

I’m not entirely sure I agree that this isn’t the responsibility of the IGA/IAM solution, but I guess it depends on the goals and objectives of the org, and where ownership of the platform lives. In my org, IGA is part of Security, and active but unused accounts do pose a security risk, and since IdentityNow already has the attributes here, it seems fair to offer the ability to report (and possibly automate) around it. An account that hasn’t been used in months, realistically, doesn’t need any of the access assigned to it, so this could be a useful metric in determining whether the account should remain active or if it’s entitlements should be revoked/recertified.

Thanks @MVKR7T! I enjoyed diving into the capabilities/possibilities of Velocity and trying different things to see what would work and what wouldn’t work in a transform. It definitely doesn’t support everything, but it’s nice to see that it does allow some things that aren’t explicitly spelled out in the documentation, and I’m definitely curious about other things we can try that aren’t exposed in the docs!

@sup3rmark It’s truly a philosophical debate that goes along with the maturity curve of the organization. At a high maturity level where things are heavily rule based then it’s likely either a misconfigured rule that provides the account not being used or an improper classification of the identity matching a rule it shouldn’t.

In a low maturity level where things aren’t highly controlled and are more ad-hoc then you definitely get more of these types of scenarios with unused accounts based on “same as” security or perpetued rights as a person moves within the organization.

At the “purest” philosophical level though the IAM solution should be rule based and controlling what identities get what accounts with what access and for how long. By definition of the “rules” they get things whether they use them or not which isn’t the responsibility of the IAM team, but the people in the organization responsible for the rule definition.

YES - I’m splitting hairs, but having been down this path with many clients I’ve worked with over the years wanted to clarify “ideal” vs. “real” states and not have people just implement functionality because it’s always been there.

And I COMPLETELY agree that stale accounts should be addressed via some kind of policy and related processes.

1 Like

If you’re just looking for unused accounts, couldn’t you just independently check each of the four dates against your threshold (now-90d or something like that)? Then if all are less than that date they’re unused.

You could also get the max date with 3 separate transforms:
Transform A: dateCompare date 1 greater than date 2. positive condition: date 1, negative condition: date 2
Transform B: dateCompare date 3 greater than date 4. positive condition: date 3, negative condition: date 4
Transform C: dateCompare Transform A greater than Transform B. positive condition:Transform A, negative condition: Transform B

1 Like

Yes, but this coalesces the four different values into the only one we actually care about: the latest of the 4. I also didn’t want to have to add that multiple separate transforms/identity attributes.

There’s more than one way to cook an egg, definitely. I’m just showing one possibility, and in particular, one that uses underlying Java/Apache Velocity functionality that isn’t mentioned in the transform documentation.