Convert AD time to real time.

I recently had a command, from on high, to find the last login times of users from multiple separate Active Directory (AD) domains and other login info.

So I Google around and find a dsquery command below. Then I find out later there userAccountControl chunk in the limits it pretty much to some attribute like built-in users or something like that. Use this one instead to get all your users and times:

dsquery * -filter "(&(objectCategory=user)(objectClass=user))" -limit 0 -attr distinguishedName sAMAccountName lastLogon > \\192.168.1.1\LogonData\%COMPUTERNAME%_logontimes.txt

Select all Open in new window

This gets you the last login time and dumps it to a file on a server with the computer's name from a DOS prompt.  Take off the Greater Than sign (>) and the rest to see it on screen.

Now Microsoft, in it's infinite wisdom, decided to store the AD time as milliseconds since 01/01/1753. I'm guessing the the days are the ideas of Babbage or the oldest living relative of Bill Gates.

So now I'm seeing the number in 129956570270092500 as the last login time. That is about useless to the average human. So some more Googling and I come up with the formula to get it to normal human dating.  Since I am probably going to to be tasked with building this report on a regular basis I decided to build a function for use in MS SQL Server DB that can convert it on a regular basis.

Here's the function and sample tests:

if object_id('[dbo].[fn_GetLastLogonDt]') is not null
                      begin
                      	drop function [dbo].[fn_GetLastLogonDt]
                      end
                      
                      go
                      
                      create function [dbo].[fn_GetLastLogonDt](@LastLogonStamp bigint)
                      returns datetime
                      as
                      /************************************************************
                      *
                      *    Author:		Jim P.
                      *    Last modified:	02/07/2013
                      *    Purpose:		Converts the last logon microsecond 
                      *					from Active Directory into a regular
                      *					date value
                      *    
                      *
                      *************************************************************/
                      begin
                      
                      declare @LastLogin		as		bigint
                      declare	@ReturnDt		as		datetime
                      
                      
                      --	If the timestamp is too large, or small you get an error.
                      --	so I'm putting in an error handling routine to give an
                      --	obviously false date to handle that.
                      
                      if	@LastLogonStamp < 47967800000000000 or
                      	@LastLogonStamp > 2650468000000000000
                      	begin
                      		select @ReturnDt =  case 
                      				when	@LastLogonStamp > 2650000000000000000	then	cast('9999-12-31 23:59:59:59' as datetime) --@ReturnDt =
                      				when	@LastLogonStamp < 47967800000000000		then	cast('1753-01-01 00:00:00.000' as datetime)  -- 1753-01-01 00:00:00.000
                      			end	
                      	end
                      else
                      	begin
                      		set @LastLogin = @LastLogonStamp
                      		set @LastLogin = @LastLogin / (60 * 10000000)
                      		set @LastLogin = @LastLogin / 1440
                      		set @LastLogin = @LastLogin - (datediff(dd, '1/1/1900', dateadd(yy, 299, '1/1/1900')))
                      		select  @ReturnDt = dateadd(day,@lastlogin,'1/1/1900')
                      	end
                      
                      return  @ReturnDt
                      end
                      
                      go
                      
                      select	[dbo].[fn_GetLastLogonDt](129956570270092500)
                      select	[dbo].[fn_GetLastLogonDt](4000000000000000)
                      select	[dbo].[fn_GetLastLogonDt](46000000000000000)
                      select	[dbo].[fn_GetLastLogonDt](2750000000000000000)
                      select	[dbo].[fn_GetLastLogonDt](1650468000000000000)

Select all Open in new window

Note that SQL Server can handle date as a date value from 01/01/1753 to 12/31/9999.

I figured this is a good way to pass on the knowledge to anyone who is searching for it. This function was built on SQL Server 2005 Express, but I can't see a problem with it working with any version from 2005 onward.

As a further information, this DSQuery is run along with the following query:

dsquery user -limit 0 | dsget user -dn -samid -ln -fn -display -mustchpwd -canchpwd -pwdneverexpires -disabled -acctexpires  > \\192.168.1.1\LogonData\%COMPUTERNAME%_logonnames.txt

Select all Open in new window

This gets the rest of the information on the users, such as disabled and expiration. I built a VBA routine, with the help of other exports, to import the data into Access on the way to the SQL DB.