While on site with a client, we noticed that some Windows AD attributes we were receiving came in an interesting format. For example, the accountExpires attribute is stored in what is called Windows/LDAP timestamp. It measures time that has passed since Jan. 1, 1601 (weird) in 100s of nanoseconds (more weird). Seeing as a multitude of our clients and partners use AD and may leverage these fields, I wanted to share a function we used to convert that into timestamps we can use in MySQL, and consequentially, Securonix.

The function can be found here:

DELIMITER $$ CREATE FUNCTION LDAP2UNIX (ldap double) RETURNS double BEGIN DECLARE linux double; SET linux =((ldap/10000000)-((1970-1601)365 - 3 + ((1970-1601)/4))86400); RETURN linux; END$$

Pardon the weird math. Once this is created in the backend, you can put a SQL query such as this in to the postprocessor of the import:

UPDATE users SET terminationdate = (SELECT FROM_UNIXTIME(LDAP2UNIX(customfield21)));

No extra files needed, and it works pretty quickly against a data set. Please share any comments and questions regarding this function or functions in general that you may have!

asked 08 Feb '16, 09:33

Anthony%20Herbert's gravatar image

Anthony Herbert
accept rate: 0%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 08 Feb '16, 09:33

Seen: 716 times

Last updated: 08 Feb '16, 09:33