How to convert AD filetime (last logon value eg:131624069521116676) to Date Time format

[Shawn Horley]

Greetings Folks!

So I get errors from my LDAP OU Definitions where it fails to format the Last Logon value to a Date/Time format (MM-dd-yyyy HH:mm:ss) through my transform maps. I've learned that the value coming from AD is an integer value eg: 131624069521116676, and the error states: com.glide.db.impex.transformer.TransformCoercionException: Unable to format 131624069521116676 using format string MM-dd-yyyy HH:mm:ss for field last_login

Do I have any options that will convert this to usable data?

I've attached the full error that I get in my Logs

Appreciate any suggestions!

Cheers

Shawn

[Jon Barnes]

Normally, I think you should be able to use setNumericValue to get a glideDateTime, but I don't think that number you gave above is the type of number expected. that number is generally meant to be milliseconds since 1970-01-01, but this number is too big for that.

var gdt = new GlideDateTime();
gdt.setNumericValue(131624069521116676);

The key will be to find out what in AD they base this MS from. There should be a base date, and it would be the MS from that date. But GlideDateTime expects it to be MS from 1970-01-01, so you can def calculate this once you know how AD calculates it.

var ms = ADMS - DIFF;

var gdt = new GlideDateTime();

gdt.setNumericValue(ms);

where ADMS is the number you get from AD and DIFF is the difference between ADs base date in MS and 1970-01-01 in MS

[Shawn Horley]

Hi John

Thanks for your response!

Apparently Microsoft (in their questionable wisdom) decided to start measuring Epoch seconds from a start date of 1601 or thereabouts rather than anything closer to the 20th century. 

[Jon Barnes]

interesting. thanks for letting me know. so if I use that date, and run this script, it should give me the actual date, but the date ends up being roughly 600 years in the future. Do you know what the actual last login date is supposed to be for that specific case you posted?

var tm = 131624069521116676;
var adBase = new GlideDateTime();
adBase.setValue('1601-01-01');
gs.print(adBase);
var gdt = new GlideDateTime();
gdt.setNumericValue(tm + adBase.getNumericValue());
gs.print(gdt);

this returns: 2601-09-21 13:38:36

[Shawn Horley]

Hi Jon

The date time value from AD is: Tuesday, February 6, 2018 9:09:12 AM GMT-07:00

Cheers

I know that the epoch calculation for the lastlogon appears to be based upon:  Jan 1, 1601 UTC