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
stringtype… 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 anintegerobject, 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
- Of course, things can’t be that simple… integers have a max value of 2147483647. The values returned by
EPOCH_TIME_WIN32andEPOCH_TIME_JAVAare 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$dto make theifstatement 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”
- AD lastLogon/lastLogonTimestamp:
- 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
}
