1. Junior Member
Join Date
Apr 2014
Posts
4

Here's the use case: Scan an Active Directory OU and email the user that their password expires within 5 days. -- I have the AD input working fine the trick I ran into is the format of the pwdLastSet attribute which is the nanoseconds from 1601 format. I know the equation to convert it to a normal date, I have this working in excel as such (pwdLastSet/10000000/3600/24)-109205 this gets you the days since 1/1/1900 then you add it to 1/1/1900 in date format you get your date. Example (130402530063387000/10000000/3600/24)-109205 = X then 1/1/1900 + X = 3/25/2014. How do I do this in Kettle. I have tried JavaScript but I keep getting 12/31/1969 for all my results. Or is there a better built in way to convert Microsoft nanosecond date storage in Active Directory?

2. Senior Member
Join Date
Nov 2008
Posts
777
You are encountering three different time formats, which are roughly:
1. AD is using the number of 100-nanosecond intervals since 1/1/1601
2. Excel is using the number of days since 1/1/1900
3. Kettle (Java) is using epoch time which is the number of milliseconds since 1/1/1970

To convert AD time to epoch time you thus have to divide the AD time by 10000 to get milliseconds and then apply the offset from 1/1/1601 to 1/1/1970 in milliseconds. Here is some JavaScript code that seems to do just that:
Code:
```calendar = java.util.Calendar.getInstance();

calendar.setTime(new Date("1/1/1601"));
base_1601_time = calendar.getTimeInMillis();

calendar.setTime(new Date("1/1/1970"));
base_1970_time = calendar.getTimeInMillis();

ms_offset = base_1970_time - base_1601_time;

Last edited by darrell.nelson; 04-22-2014 at 05:44 PM.

3. Senior Member
Join Date
Nov 2008
Posts
777
I found another way to handle the conversion! I knew someone had already done the math on this but I didn't find it on my first cut. I'll post a sample using this class tomorrow if someone hasn't done so already.

http://poi.apache.org/apidocs/org/ap...hpsf/Util.html

4. Senior Member
Join Date
Nov 2008
Posts
777
Yep, here's an even easier and more efficient way to convert Active Directory time values to Kettle/Java Date values. Since Apache POI is already an integral part of Kettle, a User Defined Java Expression step with this code fragment as the 'Java expression' will do the conversion:
Code:
`org.apache.poi.hpsf.Util.filetimeToDate(AD_time)`

5. Junior Member
Join Date
Apr 2014
Posts
4
I got it working with Javascript conversions using the math equations.

Thanks.

6. Senior Member
Join Date
Nov 2008
Posts
777
Even the JavaScript conversion can be vastly simplified (down to a one-liner!) with that Apache POI Util class:
Code:
`var converted_AD_time = org.apache.poi.hpsf.Util.filetimeToDate(AD_time);`

7. Senior Member
Join Date
Jun 2012
Posts
5,534
Thanks, Darrell, I wasn't aware of the POI Util class.

While I would prefer to do the math over using WinBase.FILETIME (JNA), I second your recommendation of the POI class, mainly for two reasons: Simplicity (due to a single long parameter) and Maintanability (1 LOC).

I prefer the UDJE step over JavaScript, though.

8. Senior Member
Join Date
Nov 2008
Posts
777
Originally Posted by marabu
I prefer the UDJE step over JavaScript, though
i prefer it as well. I gave that option a few posts ago but the caller still seemed to want JavaScript.