Novell is now a part of Micro Focus

Synchronizing Log Time with Actual Time

Novell Cool Solutions: Tip

Digg This - Slashdot This

Posted: 22 Nov 2004

Recently, an Novell Audit customer commented, "We are having a problem: the log time is way behind ... or ahead of the actual time."

Here is some timely advice on the issue from several of our Forum experts:

Log timestamps are in GMT, not local time. The time/date stamps are stored as a count of seconds since 12:00 AM Jan 1, 1970, GMT time zone.

If you are using Novell Audit 1.0.2, the local time setting in iManager works correctly, and lreport also handles the adjustment. Other software, such as MS Access or Crystal Reports, must have programming included to adjust the time zone.

Here are some code blocks for Crystal Reports and MS Access. Be sure to adjust the GMT offsets from Eastern to your local timezone. Note that these GMT-to-local time conversions are off by an hour between April 1 and the start of Daylight Savings, and again from the end of Daylight Savings to October 31. If anyone can produce a more accurate adjustment for Crystal 8.5 or MS Access, let us know!

Crystal Reports code

//Add this code in a formula field
//replace <TIMEFIELD> with the Novell Audit time field
datetimevar tempdate;
numbervar tempmonth;
//Convert from UNIX epoch (1/1/1970) to Crystal (1/1/1900)
tempdate:=dateadd('s',{<TIMEFIELD>},#Jan 1, 1970#);
//Handle Daylight Savings
if (tempmonth in (4.0 to 10.0)) then
//Next line is EDT
//Next line is EST

MS Access VBA

' Add this function to a module and call from a query
' Query example: fnSecDateConv(log.ClientTimestamp) AS ClientTime
Function fnSecDateConv(dSecTime As Double) As Date
     ' Convert Novell Audit stored date to readable form
     ' Input is a count in seconds from 1/1/1970 12:00 AM
     ' Output is an Access date value
     Dim dtTempDate As Date
     Dim iTempMonth As Integer

     dtTempDate = DateAdd("s", dSecTime, "1/1/1970")
     iTempMonth = Month(dtTempDate)
     If (4 <= iTempMonth) And (iTempMonth <= 10) Then
         ' Next line adjusts for EDT
         dtTempDate = DateAdd("h", -4, dtTempDate)
         ' Next line adjusts for EST
         dtTempDate = DateAdd("h", -5, dtTempDate)
     End If
     fnSecDateConv = dtTempDate
End Function

Java Report Writer

A Java report writer is available from a vendor called Elixir. Here is a conversion routine for that product:

= ( date-add (to-date "01-jan-1970" "dd-MMM-yyyy") [ClientTimestamp] "s")

This takes 1/1/1970 as the base and adds the timestamp to it.

Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions.

© Copyright Micro Focus or one of its affiliates