Saturday, September 12, 2015

Active Directory / Oracle - time stamp handling dilema

I recently received another last minute development request.  I'm going to be a little bit vague on some details on purpose - some things can't be shared.

The general problem to solve is:  On a particular administrative action, a user must complete a specific activity within a particular time frame.  If that activity isn't completed then some data is manipulated to force the user to complete the activity in a timely fashion.  If the activity was completed then flags are cleared regarding the condition.

On first pass through prototyping a possible solution, I recognized it isn't quite as straight forward as hoped.

In this case, we handle several pieces of information from different sources (Oracle and Active Directory). One item is an Oracle date/time (sysdate) generated by the administrative transaction.  We save that "transaction timestamp" in Oracle along with a "to be done by" date which is also stored in Oracle. At that same time, an Active Directory field is indirectly updated to the equivalent of "now". This part of the process works ok and there are no real alternatives available at this time.

At this point in the process, an integration runs which looks for administrative transactions that passed or are at the "to be done by" date and therefore should be checked against the user activity to verify they completed their activity.  This involved comparing an Oracle date/time stored in Oracle against Oracle sysdate - which works well enough and has no issue.

Next we get the user specific last transaction timestamp from Active directory which is represented in Active Directory as a "100ns increment from midnight Jan. 1, 1601".  We then normalize the Oracle transaction timestamp to the same representation as the Active Directory timestamp.  Now in a perfect world, if the Active Directory timestamp is newer than the Oracle transactions timestamp - then the user completed what was required and we clear flags and complete the transaction.Otherwise, we flag the user to complete their task.

The are 2 basic problems though; (1) there is an inherent difference between the Oracle date/time and the Active Directory timestamp - possibly due to one or more causes [activities are only semi-coordinated across servers, possible differences in available precision between Oracle date/time and AD timestamp].  (2) There can be minor differences between system times even when using NTP.

The result of these issues is that in some common circumstances, a user is determined to have completed the transaction just because of the time differences occurring because activities are serialized across different systems but using each systems time.  This I can easily see in the test data I generated.  I have not knowingly run into an issue with differences in the actual clocks in this current situation but we have had previous problems with clocks being out-of-sync.

The "cost" in this situation is significantly higher than desirable if users are determined to be "incomplete" when they actually are "complete".  On that same note, for other reasons it is in the organizations best interest to be as accurate as reasonable. 

[edit 2015/10/03]
Sourcing the time stamps only from AD isn't possible - initially I thought maybe it could be. The final solution isn't too hard to implement.  First I had to determine how close my times had to be to meet business needs.  In this case, I determined that the one use case affected would be fine with 10 seconds of accuracy.  The way I implemented that was to take the transaction time and activity time and subtract them.  If you take the absolute value of that and compare it against 10 seconds, I know whether the user met the timing requirement.  Problem solved.  If the 10 second value is externally configurable, I can easily update the behavior as business requirements change.





No comments:

Post a Comment