# Transform - Find Users' Last Activity Date (find latest date in array) **URL:** \ **Category:** SHF Community Knowledge Base\ **Tags:** identity-security-cloud, transforms\ **Created:** [March 11, 2024, 11:10pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841 "2024-03-11T23:10:48Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![sup3rmark](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/sup3rmark/32/36229_2.png) [@sup3rmark](https://developer.sailpoint.com/discuss/u/sup3rmark)\ **Post date:** [March 11, 2024, 11:10pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/1 "2024-03-11T23:10:48Z")
Iterating on [a post I made about 6 weeks ago](https://developer.sailpoint.com/discuss/t/transform-to-get-the-latest-value-from-4-date-fields/28231) with a different way to achieve the same goal! I wanted to expand on my previous transform (which looks at the AD `LastLogon` and `LastLogonTimestamp` attributes, as well as the AzureAD/EntraID `lastSignInDateTime` and `lastNonInteractiveSignInDateTime` attributes), to also pull in the AD `pwdLastSet` attribute as well as the AD `whenCreated` attribute. Because comparing 6 values using `if` statements would be cumbersome and even uglier than comparing 4 values was, I decided to dive back into Velocity to try and figure out a better way. Now, through the magic of arrays and `foreach` loops, I’ve found one! I’ve simplified my previous transform a bit (by removing the essentially duplicated values to maintain the to-the-minute accuracy) while also adding the two additional attributes, and am now returning `null` values rather than `error` when a value couldn’t be determined. The transform: ```json { "name": "mc-LastActiveDate", "type": "firstValid", "attributes": { "values": [ { "type": "dateFormat", "attributes": { "input": { "type": "static", "attributes": { "adLastLogon": { "type": "dateFormat", "attributes": { "input": { "type": "firstValid", "attributes": { "values": [ { "type": "accountAttribute", "attributes": { "attributeName": "lastLogon", "sourceName": "Active Directory" } }, { "attributes": { "value": "125911584000000000" }, "type": "static" } ] } }, "inputFormat": "EPOCH_TIME_WIN32", "outputFormat": "yyMMddHH" } }, "adLastLogonTimestamp": { "type": "dateFormat", "attributes": { "input": { "type": "firstValid", "attributes": { "values": [ { "type": "accountAttribute", "attributes": { "attributeName": "lastLogonTimestamp", "sourceName": "Active Directory" } }, { "attributes": { "value": "125911584000000000" }, "type": "static" } ] } }, "inputFormat": "EPOCH_TIME_WIN32", "outputFormat": "yyMMddHH" } }, "adPwdLastSet": { "type": "dateFormat", "attributes": { "input": { "type": "firstValid", "attributes": { "values": [ { "type": "accountAttribute", "attributes": { "attributeName": "pwdLastSet", "sourceName": "Active Directory" } }, { "attributes": { "value": "125911584000000000" }, "type": "static" } ] } }, "inputFormat": "EPOCH_TIME_WIN32", "outputFormat": "yyMMddHH" } }, "adWhenCreated": { "type": "dateFormat", "attributes": { "input": { "type": "firstValid", "attributes": { "values": [ { "type": "accountAttribute", "attributes": { "attributeName": "whenCreated", "sourceName": "Active Directory" } }, { "attributes": { "value": "20000101010000.0Z" }, "type": "static" } ] } }, "inputFormat": "yyyyMMddHHmmss.S'Z'", "outputFormat": "yyMMddHH" } }, "azureLastInteractive": { "type": "dateFormat", "attributes": { "input": { "type": "firstValid", "attributes": { "values": [ { "type": "accountAttribute", "attributes": { "attributeName": "lastSignInDateTime", "sourceName": "Azure Active Directory" } }, { "attributes": { "value": "2000-01-01T00:00:00Z" }, "type": "static" } ] } }, "inputFormat": "yyyy-MM-dd'T'HH:mm:ss'Z'", "outputFormat": "yyMMddHH" } }, "azureLastNonInteractive": { "type": "dateFormat", "attributes": { "input": { "type": "firstValid", "attributes": { "values": [ { "type": "accountAttribute", "attributes": { "attributeName": "lastNonInteractiveSignInDateTime", "sourceName": "Azure Active Directory" } }, { "attributes": { "value": "2000-01-01T00:00:00Z" }, "type": "static" } ] } }, "inputFormat": "yyyy-MM-dd'T'HH:mm:ss'Z'", "outputFormat": "yyMMddHH" } }, "value": "#set($max=-10)#set($array = [$max.parseInt($adLastLogon),$max.parseInt($adLastLogonTimestamp),$max.parseInt($adPwdLastSet),$max.parseInt($adWhenCreated),$max.parseInt($azureLastInteractive),$max.parseInt($azureLastNonInteractive)])#foreach($val in $array)#if($val > $max)#set($max = $val)#end#end#if($max > 0)$max#end" } }, "inputFormat": "yyMMddHH", "outputFormat": "ISO8601" } }, null ] }, "internal": false } ``` The main logic can be broken down as follows. Remember we have to parse the dates as integers in order for the comparisons to work: ```apache #set($max=-10) ## Instantiate a variable as an int; this will be our base for comparison as well as an int that will help us parse the dates as integers so we can compare them #set($array = [$max.parseInt($adLastLogon),$max.parseInt($adLastLogonTimestamp),$max.parseInt($adPwdLastSet),$max.parseInt($adWhenCreated),$max.parseInt($azureLastInteractive),$max.parseInt($azureLastNonInteractive)]) ## Build an array of integers representing our dates #foreach($val in $array) ## Iterate through the array #if($val > $max) ## Compare each value in the array against the current max value (which started at -10) #set($max = $val) ## If the current value is greater than the max, that value becomes the new max #end #end #if($max > 0) ## If the max value is greater than 0, return the max value $max #end ``` Shout-out to @tyler_mairose and @brennenscott for various bits of inspiration! > [@Display multiple account id using transform](https://developer.sailpoint.com/discuss/t/display-multiple-account-id-using-transform/19162/13): > > I missed a method on the $identity object, this can actually be done in a more simple way. There is a method getLinksByAppIdOrName(String appId, String appName) that as it states returns accounts given an id or name of an application. Including this results in the following transform: { "attributes": { "value": "#foreach($link in $identity.getLinksByAppIdOrName(\"2c918088814e6a610181686b56977fa8\",null))#if($foreach.index + 1 != $identity.getLinks().size())$link.getNativeIdentity(… > [@Transform to return NULL](https://developer.sailpoint.com/discuss/t/transform-to-return-null/30233/7): > > This definitely returns the STRING VALUE of “NULL”, but not the desired NULL value that is desired. ---
**Author:** ![tyler\_mairose](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/tyler_mairose/32/30112_2.png) [@tyler\_mairose](https://developer.sailpoint.com/discuss/u/tyler_mairose)\ **Post date:** [March 12, 2024, 2:41am UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/2 "2024-03-12T02:41:23Z")
@sup3rmark I’ve been playing around with Apache velocity myself and this is a pretty great use case of what it possible! Good stuff! ---
**Author:** ![mcheek](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/mcheek/32/23705_2.png) [@mcheek](https://developer.sailpoint.com/discuss/u/mcheek)\ **Post date:** [March 12, 2024, 12:45pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/3 "2024-03-12T12:45:10Z")
Is any of this going to be used on Azure guest accounts? I’ve had a hard time relying on lastNonInteractiveSignInDateTime to indicate actual activity. ---
**Author:** ![sup3rmark](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/sup3rmark/32/36229_2.png) [@sup3rmark](https://developer.sailpoint.com/discuss/u/sup3rmark)\ **Post date:** [March 12, 2024, 1:35pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/4 "2024-03-12T13:35:44Z")
This is more of an example of using some Velocity functionality that’s not included in the transform documentation, like arrays and foreach and a way to compare multiple dates. That said, I’m only planning on using this as a way to generate a list of accounts for our operations team to look into. Eventually, we may use it to automatically trigger a recertification, but in the short term they’ll be reaching out to managers to verify whether the folks are still around. ---
**Author:** ![mcheek](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/mcheek/32/23705_2.png) [@mcheek](https://developer.sailpoint.com/discuss/u/mcheek)\ **Post date:** [March 12, 2024, 1:41pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/5 "2024-03-12T13:41:36Z")
Yeah, I was more asking outside the technical implementation of this. We have a hard time validating whether or not guest accounts in Azure are still needed or even active, and have found that using the lastNonInteractiveSignInDateTime is not a reliable way of determining that. For a guest account, just populating their tenant picker in an application can trigger an update of that field, which is frustrating. ---
**Author:** ![sup3rmark](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/sup3rmark/32/36229_2.png) [@sup3rmark](https://developer.sailpoint.com/discuss/u/sup3rmark)\ **Post date:** [March 12, 2024, 1:44pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/6 "2024-03-12T13:44:01Z")
Ah, good to know! I don’t believe we’re using guest accounts, so shouldn’t have that issue, but at least this is erring on the side of showing more recent activity rather than less recent activity? 😛 ---
**Author:** ![edmarks](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/edmarks/32/2896_2.png) [@edmarks](https://developer.sailpoint.com/discuss/u/edmarks)\ **Post date:** [April 17, 2024, 12:24pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/7 "2024-04-17T12:24:02Z")
Totally non-technical, but about “Compliance” - If we assume the IGA system is “managing identities and associated accounts to a ‘desired’ state”, then the last login becomes a bit irrelevant in the majority of cases I’ve seen where people want this value. The most prevalent use case is “Disable inactive accounts”, but this is generally in conflict with “desired state” which provides the account in the first place. This begs the questions of - Is the desired state correctly configured? Is someone not using the account that they should be using? etc. ---
**Author:** ![sup3rmark](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/sup3rmark/32/36229_2.png) [@sup3rmark](https://developer.sailpoint.com/discuss/u/sup3rmark)\ **Post date:** [April 17, 2024, 2:06pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/8 "2024-04-17T14:06:15Z")
@edmarks while I don’t disagree with you, it’s important to remember that different orgs and industries have different use cases. While there’s obviously an ideal world in which the HRIS platform is our Source of Truth and always has accurate and up-to-date information, and our non-employees are properly managed in a system where their access is properly shut down by managers when their engagements/contracts/terms end, the world in which we live and work is far from ideal 🙂 To be clear, I’m _not_ suggesting automation to trigger deprovisioning when a user’s last activity date is outside some specific threshold - I’m using this particular bit of logic just to identify users who likely haven’t been active in a while, and our operations team will then reach out to the managers/HR to verify that the users should still be active. This will help to catch cases where managers didn’t properly enter terms into Workday or work with HR to do so, or didn’t end contracts for contingent workers who are no longer on our projects. From a security perspective, of course, this still leaves a huge gap, as it wouldn’t alert us to folks who _are_ still actively logging in even after they shouldn’t be, so it’s not a full solution. That said, there’s plenty of cases where it’s encouraged to unlicense folks from certain applications when they’re no longer using said applications, or remove their access when it’s clearly no longer being used. There’s entire companies whose products exist specifically for this sort of functionality, and ISC offers this sort of functionality itself as well as part of the Access Insights, so the last activity date data is not wholly irrelevant in an IGA platform. But again, this post isn’t meant to be a debate on using the last activity date or its relevance to SailPoint/IGA in general. This is simply a Show And Tell post showing a way to leverage Velocity in a way that was otherwise not documented elsewhere in SailPoint docs or the forum, to effectively compare mulitple dates, and hopefully inspire folks to look into other ways to extend the documented functionality we can get from Transforms by diving deeper into the Velocity scripting language. ---
**Author:** ![edmarks](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/edmarks/32/2896_2.png) [@edmarks](https://developer.sailpoint.com/discuss/u/edmarks)\ **Post date:** [April 17, 2024, 2:39pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/9 "2024-04-17T14:39:40Z")
@sup3rmark We’re aligned and you’re spot on that different orgs have different requirements and intentions on how to use the data collected. ---
**Author:** ![MattUribe](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/matturibe/32/20300_2.png) [@MattUribe](https://developer.sailpoint.com/discuss/u/MattUribe)\ **Post date:** [August 16, 2024, 8:31pm UTC](https://developer.sailpoint.com/discuss/t/transform-find-users-last-activity-date-find-latest-date-in-array/34841/10 "2024-08-16T20:31:36Z")
This is a fantastic example of what can be done with Velocity. Thanks for sharing!