Infrastructure

ConfigMgr reporting of AD timestamps

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:

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

Arithmetic overflow error converting expression to data type datetime.

image

Apologies for the poor image.

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:

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

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.

 

Leave a Reply