<

Convert AD time to real time.

Published on
10,183 Points
3,283 Views
4 Endorsements
Last Modified:
Approved
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

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)

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

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.
4
Comment
Author:Jim P.
2 Comments
 

Administrative Comment

by:Eric AKA Netminder
jimpen,

Congratulations! Your article has been published. I hope others find this tip as handy as I did.

ericpete
Page Editor
0
LVL 61

Expert Comment

by:Kevin Cross
Nice article, Jim!

For those looking for additional information on the topic, including a TechNet article, please consider reading my Converting Active Directory Timestamps in Microsoft SQL Server article.
0

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month