Lastlogon and Lastlogontimestamp attribute value in CSVDE output

Convert lastlogon timestamp to readable format using W32TM and Excel

Most of the active directory admin have received a request to extract the last logon time for the list of users and computers from AD, we can use the CSVDE command to extract the lastLogon attribute value however from CSVDE output the lost logon attribute value would not be the readable format or usuable date/time format, and you can’t understand the format because it’s a UTC format

You can convert lastlogontimestamp to readable date format using W32TM command

Syntax:

w32tm /ntte (lastLogon attribute value)

Example:

C:>w32tm /ntte 127281844863301000

147316 22:48:06.3301000 – 5/5/2004 4:18:06 AM (local time)

It’s very difficult to use this command for bulk extract, we can convert this in Excel itself using below procedure.

You can use below formula to convert UTC format to normal readable format in Excel.

=IF(A1>0, DATE(1601,1,1) +A1 /600000000/1440,””)

A1 is the cell that contains the Timestamp.

Yes, in Excel itself you can convert lastlogon and lastlogontimestamp to a normal readable format no need for additional script and command, we can use this simple procedure to extract lastlogon timestamp from active directory without using a script, more about CSVDE

You can also use our online windows timestamp converter

Related Articles

How to extract bulk object from AD with the specific attributes (Part 1)

How to extract the user who has the option Password never expires in the OU

Force active directory replication

More useful commands