How do I translate or read the date and time stamps in the AppManager Repository and AppManager log? (NETIQKB2498)

How do I translate or read the date and time stamps in the AppManager Repository and AppManager log files?
What format are dates and times stored in the Repository?
What utility can I use to translate date and time stamps into readable format?

Dates and times in the AppManager Repository (for example, the Time column of the Data table) are stored as integers using the Universal Coordinated Time (UTC) format. These dates and times are also used in AppManager log files.  The UTC format is loosely defined as the current date and time of day in Greenwich, England.

NetIQ provides a utility called prttime to translate date and time stamps into a readable format. You can find the prttime.exe program in the Extras folder in the AppManager installation kit.

To translate a specific date and time in UTC format to a readable date and time format for AppManager log files:

1. Run the utility from the CD, or copy the prttime.exe file to a directory in the system path and launch a command prompt to execute the utility.
2. Copy the date and time in UTC format from an AppManager log file (for example, 1023460132).
3. Paste the date and time in UTC format in the command prompt using the Paste menu option, or type the date and time in UTC format at the following line:
   
   Please enter a time ==>
   
4. Press Enter.  This will return the UTC date and time (for example,1023460132)in a readable format similar to the following:

Fri Jun 07 07:28:52 2002

To convert dates and times stored in UTC format on the AppManager Repository to a readable format:

1. Open SQL Server Query Analyzer.
2. Select the Repository (QDB).
3. Use this SQL statement as a guide to build other SQL queries to convert the dates and times stored in UTC format into a readable format. Using this exact SQL query will return all dates and times for all data in the Data table in readable format.

Note: If the name of the AppManager Repository is renamed from the default (QDB), replace QDB with the appropriate name.

USE QDB
DECLARE @t int
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias',
@param = @t OUT
SELECT @t = @t  60 / convert to seconds */
SELECT dateadd(second, Time-(@t), '1/1/70 0:0:0')
FROM Data

Run the SQL query by pressing F5 or clicking the green arrow.

Additional Example:

This SQL query returns Event information adjusted to Eastern Standard Time (EST). The JobID will need to be adjusted accordingly.

SELECT
Convert(char(20),Event.MachineName) "Machine",
Convert(char(20),Event.JobID) "JobID",
Convert(char(20),Event.EventID) "EventID",
Convert(char(20),Event.Severity) "Severity",
Convert(char(50),EventDetail.AgentMsgShort) "Short Agent Message",
Convert(char(50),EventDetail.AgentMsgLong) "Long Agent Message", RealTime =
Convert(char(20),dateadd(Second,EventDetail.
.FirstOccurTime,'12/31/69 20:00:00'))
FROM Event, EventDetail
WHERE Event.EventID=EventDetail.EventID AND Event.JobID =61999
ORDER BY Event.MachineName, EventDetail.FirstOccurTime

Formerly known as NETIQKB2498

WARNING: The above SQL queries directly accesses your database and update/delete data. If the queries are performed incorrectly it can cause irreparable harm to the database and may result in loss of data.  If you are unfamiliar with SQL or how to run a SQL query please contact NetIQ Technical Support directly.

You can use the following SQL Query to input a specific calendar date to have AppManager return UTC time format:

select DATEDIFF(second,'1/1/1970',GETUTCDATE())

where '1/1/1970' should the date you wish to convert.

This query returns the following result:

1112377497