Tom Watson at myITforum.com

lastLogonTimestamp in Active Directory User Discovery

This is something you might find a use for if you have Active Directory User Discovery enabled.  Active Directory User Discovery might be something you have enabled on your central site, but not your child sites, purely for reporting purposes.

One word of warning, before I delve into this.  lastLogonTimestamp may not actually display the last logon for a user, as its purpose is more to find inactive accounts, as opposed to the exact time a particular user last logged in:-

http://blogs.technet.com/askds/archive/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works.aspx

Remember the purpose of the lastLogontimeStamp attribute is locate inactive accounts not provide real-time logon information.

So anyway, this is still useful if that’s the type of information you are looking for.  To configure this on your ConfigMgr site is simply a case of adding an attribute to your discovery like so:-

Once Active Directory User Discovery runs, it will populate with a lastLogonTimestamp column.

The second part of this is that the information is actually stored in Interger8 format.  This basically means it is stored as the number of 100-nanosecond intervals since 12:00 AM January 1, 1601.  For example “129085607775156538” basically means 12908560777515653800 nano-seconds since 12:00 AM January 1, 1601.  One way to convert is to use w32tm.exe /ntte.:-

w32tm /ntte <NT time epoch>
  Convert a NT system time, in (10^-7)s intervals from 0h 1-Jan 1601, into a readable format.

C:\ w32tm.exe /ntte 129085607775156538
149404 15:19:37.5156538 - 21/01/2010 15:19:37 (local time)

To do this in T-SQL, I borrowed a bit of code from http://myitforum.com/cs2/blogs/jnelson/archive/2009/08/25/140938.aspx and came up with the following report:-

SELECT User_Name0,
       Name0,
       DATEADD(mi,(lastLogontimeStamp0  / 600000000) - 157258080,0) AS [Last Logon Time Stamp (UTC)]
FROM   dbo.v_R_User
WHERE  User_Name0 LIKE @variable

This gives you an output like so:-