F:\>csvde -r "(objectClass=user)" -f output.csv
* note.. all that is on one line. I wrapped it because blogger was cutting it off.
That worked great, but excel choked on the dates. It turns out this is because the dates are not "normal" timestamp dates. "Normal" timestamps are based on the number of seconds since midnight on 1/1/1970. The timestamps in Active Directory in UTC format, a 64 bit number based on the number of nanosecond since 1/1/1601 divided by 100! For even more complexity, Excel's dates are based on the number of days since 1/1/1900. Arrgh.
As theoretical knowledge goes, that is all fine and good.. but how do we use it in excel?
The formula to convert from Active Directory LastLogon or LastLogonTimestamp is:
=IF(C2>0,C2/(8.64*10^11) - 109205,"")
- C2 is the cell that contains the Timestamp.
- The If() statement hides the value if the user has not logged in.
- (8.64*10^11) is the number of nanoseconds in a day divided by 100.
- 109205 is the number of days, including leap days, between 1601 and 1900. (Remember, 1900 is when excel dates "start")
That's it. Paste in your formula and format it as a date, or date/time. The times returned are in GMT. All of the other solutions I saw in my google searches pointed to a vbScript solution. Please leave a comment if this helps you.
P.s. If you want it in Central US time (GMT-6), subtract 0.25 (That is 6 hours divided by 24 hours in a day). For Eastern time (GMT-5), subtract 0.208333333 .. (5/24).