I was helping out a fellow consultant with some less-than-obvious T-SQL for some custom reporting recently.  One of the needs was to get the Last Logon time for an Active Directory user.

ConfigMgr Active Directory User Discovery was pulling in the correct attributes, but we hit a snag in getting a good report of the user data.  The same issue would apply to an AD computer object; this is not user specific.

There are a hundred places around the web that have posted the formula to calculate a SQL datetime from and Active Directory timestamp.  This is an example:

Cast((lastLogon0 / 864000000000.0 - 109207) AS DATETIME) as [LastLogon]

We were using this code but SQL was still throwing the error

Arithmetic overflow error converting expression to data type datetime.

Hmm… interesting.  We found some internet chatter about the SQL datatype in our table possibly being a string (varchar / nvarchar); however, we confirmed that ConfigMgr created the columns as bigint, so there was no need to cast the string as a numeric data type of any sort.

I started digging into the data and eventually ended up with this query to test the value ranges:

Select count(*) from v_R_User where lastLogon0 is NULL --determine if any NULLs exist
Select Min(lastLogon0), Max(lastLogon0) from v_R_User where lastLogon0 is NOT NULL --determine the lowest and highest values that are not NULL

The result confirmed a suspicion that NULLs may have been the root cause.  But another value popped up which I had not expected.  The Min(lastLogon0) was 0 (zero).  That would definitely cause an arithmetic problem since we are doing some division then subtraction.  The result would be a negative date.  Hmm.. I don’t think we can have a logon timestamp during BC (i.e. Before Christ).

I tweaked the value test query to filter out zeros just like NULLs

Select count(*) from v_R_User where lastLogon0 is NULL
Select Min(lastLogon0), Max(lastLogon0) from v_R_User lastLogon0 is NOT NULL and lastLogon0 > 0

Now the Min and Max had acceptable values of really big numbers.

Putting it all together, we ended up with a Case statement for each datetime value that needed conversion.  The statement handles both the NULL and 0 exception cases in addition to the expected time values.  We substituted January 1, 1980 for the invalid values, but any other date could be used.

Transact-SQL
Select SD.AD_Site_Name0 [AD Site]
, SD.User_Name0 [User ID]
, USR.Full_User_Name0 [User Full Name]
, SD.Full_Domain_Name0 [User FQDN]
, [User Last Logon] = Case
      When USR.lastLogon0 IS NULL Then '1/1/1980'
      When USR.lastLogon0 = 0 Then '1/1/1980'
      Else Cast((USR.lastLogon0 / 864000000000.0 - 109207) AS DATETIME)
      End
, [User Last Logon Time Stamp] = Case
      When USR.lastLogonTimestamp0 IS NULL Then '1/1/1980'
      When USR.lastLogonTimestamp0 = 0 Then '1/1/1980'
      Else Cast((USR.lastLogonTimestamp0 / 864000000000.0 - 109207) AS DATETIME)
      End
, USR.employeeID0 [Employee ID]
, USR.Mail0 [Email]
from v_R_System AS SD 
inner join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID 
inner join v_Collection COL on FCM.CollectionID = COL.CollectionID 
inner join v_R_User USR on SD.User_Name0 = USR.User_Name0  
where COL.Name = 'All Systems'
order by USR.Full_User_Name0