Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Help with converting AD dates to readable formats

  1. #1

    Default Help with converting AD dates to readable formats

    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. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    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;
    
    calendar.setTimeInMillis(AD_time / 10000 - ms_offset);
    var converted_AD_time = calendar.getTime();
    Name:  AD_time.png
Views: 1866
Size:  20.0 KB
    Last edited by darrell.nelson; 04-22-2014 at 05:44 PM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Nov 2008
    Posts
    777

    Default

    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
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    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)
    Name:  AD_time2.png
Views: 1807
Size:  21.5 KB
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5

    Default

    I got it working with Javascript conversions using the math equations.

    Thanks.

  6. #6
    Join Date
    Nov 2008
    Posts
    777

    Default

    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);
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  7. #7
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    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.

    Name:  UDJE.jpg
Views: 1885
Size:  22.8 KB
    So long, and thanks for all the fish.

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by marabu View Post
    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.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.