donderdag, januari 07, 2010

Calculate AD-accountexpires in SQL

For a customer i work for, we needed to read the Active Directory from SQL, specifically the moment an account expires. We knew we had to use ADSI OpenQuery, but had problems converting the big numeric value that AD returns (accountexpires is a large integer value that tracks time via the number of nanoseconds that have expired since January 1, 1601), to something you can use, like the number of days until an account expires.

I found the solution here (in one of the many comments), and the SQL (i used it in SQL 2000) code is:

ROUND((CAST(accountexpires AS BigInt)
/ 10000000 - 11644560000 - CAST(DATEDIFF(SECOND, '19700101', DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) AS VARCHAR(64))) / 60 / 60 / 24, 0)
AS accountexpires_in_x_days

You get a negative number if the account has already expired. And you can use this to manipulate further.

Comments in the mentioned post also contain a VBS-script for importing the AD in a table (so you don't have to query the AD all the time using a view, and getting around the limit of 1000 records), and solutions for getting around the limit of 1000 records (we decided to read every alphabet-letter by using "WHERE objectCategory=''person'' and objectClass = ''user''
and samaccountname > ''A'' AND samaccountname < ''C''
" in the OpenQuery-statement); that worked for us (yes, that means you union all these parts 26 times for every letter or so ;-)).

And wait, the mentioned post contains more: how to query all AD groups and how to query who is member of what group!

If you're looking for a VB script to read the AD, have a look at this post by ScriptingGuy.

ImageSource: Emerson HelpMagic.

1 opmerking: