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