<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Convert AD time to real time.

Published on
10,240 Points
3,340 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
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

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month