If you ever want to store the Active Directory timestamps you've probably seen they are an 18-digit number.
The 18-digit Active Directory timestamps, also named 'Windows NT time format','Win32 FILETIME or SYSTEMTIME' or NTFS file time.
These are used in Microsoft Active Directory for pwdLastSet, accountExpires, LastLogon, LastLogonTimestamp and LastPwdSet. The timestamp is the number of 100-nanoseconds intervals (1 nanosecond = one billionth of a second) since Jan 1, 1601 UTC. I wanted to store the last logon time (this is a unique value PER domain controller by the way, so repeat for each dc if you want all the data) and create a relationship from the
LDAP user (l:Ldapaccount)-[r:LAST_AUTHENTICATED_ON]->(dc:Ldapaccount) to the domain controller.
I used apoc.load.ldap and apoc.date.format to get the job done. The arithmetic is: take the LastLogon value and divide by 10000 to get converted to milliseconds. Then subtract 11644473600000 (number of milliseconds between 1/1/1601 and 1/1/1970) to give us UNIX Posix stamp.
Then for this use-case I converted to the local time zone in human readable format.
Here's the Cypher:
call apoc.load.ldap({ldapHost:'dc.mydomain.com',loginDN:'cn=_queryuser,cn=Managed Service Accounts,DC=mydomain,DC=com',loginPW:'Passw0rd'},
{searchBase:'ou=Hosted,dc=mydomain,dc=com',searchScope:'SCOPE_SUB',attributes:['userPrincipalName','LastLogon'],
searchFilter:'(&(samAccountType=805306368))'})
yield entry as user
MATCH (l:Ldapaccount {upn:user.userPrincipalName}),(dc:Ldapaccount {fqdn:'dc.mydomain.com'})
where user.LastLogon is not null
MERGE (l)-[r:LAST_AUTHENTICATED_ON]->(dc)
set r.lastauth=apoc.date.format((toInt(user.LastLogon)/10000)-11644473600000,'ms','yyyy-MM-dd HH:mm:ss.sss', 'CST')
