A couple of weeks ago I wrote this post because I had just found out that a group of security ‘professionals’ and ‘consultants’ (not from our company) that were assigned to one of my projects did not have the technical ability to download user records, or any other records, from Active Directory (AD) and perform the appropriate ID management analysis. While I am senior management, I do take the pride of being able to do about anything required, including the technical work that is necessary to figure out via Google, MS Support sites, or any other resource – a skill that my Japanese counter-parts don’t seem to possess. Donald Trump’s “Your fired!” expressions cross my mind every time I look at one of these so-called professionals that says, “I don’t know how….”
In this blog posting I will briefly outline what this involves, include the necessary reference links, and provide any insights.
First the reasoning and approach. The reason we download all these records from the Active Directory is to perform an analysis of certain information in order to find dormant accounts that need to be disabled or deleted, finds account with excessive logins to determine if brute force access has been attempted and may have been successful, identify accounts that belong to multiple groups that would breach internal controls, and identify accounts that were created but went unused for an extended period of time. I will only explain last login date analysis in this post, since that is a bit tricky and will set any one attempting this for the first time off to a good start.

In order to download the AD contents, this Microsoft support page outlines the LDIFDE command. While there are advantages to this file format for importing and exporting AD contents, there are more advantages for importing when it comes to this command. The output from the LDIFDE command is a simple record-by-record vertical listing with no de-limitation. Needless to say, not a good format for data analysis, so after racking my brain over how to convert this file into CSV or some other format, I came across the CSVDE command on this Microsoft support page. Both of these commands are installed by default in WIndows 2003 Server in the system32 folder on sysroot. To download the whole contents of AD (recommended for a thorough analysis), simply type the following command at the prompt on the AD Server main node.
csvde -f c:\filename.csv
For larger environments with thousands of users, please do not execute this command during main production hours. Definitely perform this function after working hours!! I warned the client, and they went ahead and executed the command at 10 a.m. on a Tuesday morning – crazy to say the least.

The next step is to filter out all disabled accounts, since analyzing disabled accounts should most likely be limited to troubleshooting something instead of looking for accounts that are active that should be disabled or deleted. In many secure environments, accounts are never deleted for audit purposes and usually disabled instead. The trick here is that AD uses codes to identify account status, and even Microsoft does not supply a comprehensive listing of these codes. That’s where this site comes in very handy because it is probably to most comprehensive listing. The record to look for is “userAccountControl”, and just filter for all the non-disabled codes, then you will end up with all active accounts. The key term here being ALL – including service, machine, group, management, admin, etc. So our next step is to filter for ‘user’ in the “objectType” record in order to analyze users only. Eventually, we will get down to other accounts for deeper analysis.
Now we have all active user accounts, which in my case is about 8,500 records; something that is manageable in Excel. All of the work up to this point was performed in Access, since the CSVDE command produced a file with 167,000 records and weighed in at about 200 MB. Furthermore, for filtering and getting down to the target records, Access allows direct SQL queries, so this makes the process much faster once the filtering records are identified.
Windows records dates in AD in integer8 format. To quote from this site:
“Many attributes in Active Directory have a data type (syntax) called Integer8. These 64-bit numbers (8 bytes) usually represent time in 100-nanosecond intervals. If the Integer8 attribute is a date, the value represents the number of 100-nanosecond intervals since 12:00 AM January 1, 1601.”
Now, how many 100 nanosecond interval till…… well, this is where Excel formulas work hard for us. Next to the “lastLogon” field in Excel add a column and type in the following formula that I adopted from this resource. But I did the proof by calculating backwards and verifying the number of 100 nanosecond in an hour, day, year to gain reasonable confidence that this formula is close enough.
=RefCell/10,000,000/3600/24 – 109207
After performing the calculation make sure to select the column and change the field formatting to date, since numbers will just appear if you don’t.
Now, that will give you a listing of all active users and their  last logon dates – the information you need to perform a dormant account analysis. Separate by 30 days, 60 days, 90 days, 180 days, and greater. I was surprised by what we discovered. We found that a lot of accounts were being created for development and testing, but those accounts were remaining in applications (poor secure programming) and remaining active AD but going unused.
73…. enjoy and please comment with ideas or rants.