23 Nov 2021

How to convert datetime <= to => Active Directory LDAP Win32 Filetime

In: Dataweave, Mulesoft, Anypoint Studio, Post By: ayman-lahrim

blog-image

At some point, while developing a Mule application for Active Directory, it's almost inevitable that you'll need to manipulate Win32 FILETIME. In this article, we will see how to read/convert datetime to Active Directory timestamps and what is all about!

Getting started

Active Directory timestamps 18-digit:

Also known as 'Windows NT time format', 'Win32 FILETIME or SYSTEMTIME', or NTFS file time(wikipedia).

  • Used in Microsoft Active Directory for pwdLastSet, accountExpires, LastLogon, LastLogonTimestamp, and LastPwdSet.
  • The timestamp is the number of 100-nanosecond intervals (1 nanosecond = one billionth of a second =>1 Second = 1000000000 nanoseconds) since Jan 1, 1601, UTC.

FILETIME: 01/01/1601 UTC, One unit 1 correspond to 100ns (nanoSeconds)

Mulesoft use Unix/Epoch time 

Also known as Epoch time, Posix time, seconds since the Epoch or UNIX Epoch time(wikipedia).

  • It is the number of seconds that have elapsed since the Unix epoch, excluding leap seconds. The Unix epoch is 00:00:00 UTC on 1 January 1970 (an arbitrary date)
  • This value is increased by a value of one second.

Unix time : 01/01/1970 UTC Units in Seconds => 0000000000t Epoch time = 116444736000000000 FILETIME

We will use this value as a constant in our DataWeave Transformation.

Steps 

In order to convert these two types, we will follow a three steps process :

  1. Convert equivalent Filetime of 0000000000t Epoch time to nanoseconds (Epoch Time)
  2. Convert DateTime to Seconds (Epoch time)
  3. Compute the Filetime

In human date, we will use 2021-11-20 at 11:44:07 as our example date to convert.

 

1 - Convert equivalent FILETIME of 0000000000t Epoch time to nanoseconds (Epoch Time) :

  • A - to nanoSeconds = 116444736000000000 * 100 = 11644473600000000000
  • B - to seconds = 11644473600000000000 / 1000000000 = 11644473600

 

Date

Epoch time

Seconds

Filetime

01/01/1970

0000000000t

11644473600

116444736000000000

11644473600 seconds will be our 0000000000t Epoch time.

We have to add this value to any date over 01/01/1970.

 

B - Convert DateTime to Seconds (Epoch time)

    Value in seconds since 01/01/1970 till 2021-11-20T11:44:07 :

    "2021-11-20T11:44:07" as DateTime as Number is 1637408647

C - Compute Filetime 

Results of Step (A + B) to nanoSeconds / 100 (FileTime)

((1637408647 + 11644473600)* 1000000000)/100

Result => 132818822470000000

DataWeave Implementation 

 

%dw 2.0
output application/json
/*
- Win32 FILETIME (1=100nanoseconde) begin : 01/01/1601
- Epoch Time begin : 01/01/1970
- Win32 FILETIME from 01/01/1601 to 01/01/1970 = 116444736000000000
- convert to seconds
   - (116444736000000000*100) => to nanoseconde
   - (116444736000000000100 / 1000000000 ) => to seconds
0t Epoch Time = 11644473600 used as constant
*/
fun dateToFileTime(date) =
((date as DateTime as Number)+ 11644473600)* 10000000
//(date in Seconds + 0t epochTime in Seconds) *(1000000000/100)
---
{
a : dateToFileTime("2021-11-20T11:44:07"),
b : dateToFileTime(now())
}
result=>
{
 "a": 132818822470000000,
 "b": 132820569110000000
}

 

Written by Ayman Lahrim, API integration engineer & MuleSoft Mentor