GoldMine uses two tables to keep track of all the changes to the database, primarily for synchronisation (for more information on the mechanics of the sync process, go to the technical documents section of the GoldMine downloads on this website). They can also be useful for us when we’re tracking down who has done what in the database – the only trouble being that the TLog tables, as they’re called, aren’t particularly user-friendly.

The biggest problem is reading the field that records the date and time of whatever activity is being logged; the information is encoded in a fiendish manner that means that it’s essentially unreadable. All we can do is say that, if we sort by the logstamp field, it will order the items in the correct order.

Which is why I was very pleased to find a post on the FrontRange Forum by Steven Graff in which he showed a way of translating the logstamp into a readable date. You can find the post here.

Steven’s technique is to create a function called Recid2Date into which you pass the logstamp (or indeed any recid field). Here is the SQL code which will create the function:


USE your_database_name
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Recid2Date]') and OBJECTPROPERTY(id, N'IsTableFunction') = 1)
drop function dbo.[Recid2Date]
GO

create function [dbo].[Recid2Date] (@recid varchar(20))
returns varchar(50)

begin
declare @xdate datetime, @offset int
set @offset = (select DATEDIFF(ss,GETUTCDATE(),GETDATE())) /************* Number of seconds offset from GMT ************/
set @xdate =
(
select dateadd(ss, @offset +

(POWER(cast(36 as bigint),6) *
case
when (select isnumeric(substring(@recid,1,1))) = 0
then (select ascii(substring(@recid,1,1))) - 55
else (select ascii(substring(@recid,1,1))) - 48
End
+
POWER(cast(36 as bigint),5) *
case
when (select isnumeric(substring(@recid,2,1))) = 0
then (select ascii(substring(@recid,2,1))) - 55
else (select ascii(substring(@recid,2,1))) - 48
End
+
POWER(cast(36 as bigint),4) *
case
when (select isnumeric(substring(@recid,3,1))) = 0
then (select ascii(substring(@recid,3,1))) - 55
else (select ascii(substring(@recid,3,1))) - 48
End
+
POWER(cast(36 as bigint),3) *
case
when (select isnumeric(substring(@recid,4,1))) = 0
then (select ascii(substring(@recid,4,1))) - 55
else (select ascii(substring(@recid,4,1))) - 48
End
+
POWER(cast(36 as bigint),2) *
case
when (select isnumeric(substring(@recid,5,1))) = 0
then (select ascii(substring(@recid,5,1))) - 55
else (select ascii(substring(@recid,5,1))) - 48
End
+
POWER(cast(36 as bigint),1) *
case
when (select isnumeric(substring(@recid,6,1))) = 0
then (select ascii(substring(@recid,6,1))) - 55
else (select ascii(substring(@recid,6,1))) - 48
End
+
POWER(cast(36 as bigint),0) *
case
when (select isnumeric(substring(@recid,7,1))) = 0
then (select ascii(substring(@recid,7,1))) - 55
else (select ascii(substring(@recid,7,1))) - 48
End
)
/50
,'1/1/1990')
)
return (@xdate)
end

GO


I wrote a query, using that function, to open up the TLogs for inspection:


select
log_stamp, sync_stamp,

isnull(contact1.contact,'')+
isnull(c2.contact,'')+
isnull(c3.contact,'')+
isnull(c4.contact,'')+
isnull(c5.contact,'')+
isnull(c6.contact,'')+
isnull(c7.contact,'') as contact,

tablename, fieldname, description,

isnull(conthist.srectype,'')+
isnull(contsupp.rectype,'')+
isnull(cal.rectype,'')+
isnull(opmgr.rectype,'') as rectype,

isnull(conthist.ref,'')+
isnull(contsupp.contsupref,'')+
isnull(cal.ref,'')+
isnull(mailbox.mailref,'')+
isnull(opmgr.stage,'') as ref,

location, username,

isnull(contact1.accountno,'')+
isnull(contact2.accountno,'')+
isnull(conthist.accountno,'')+
isnull(contsupp.accountno,'')+
isnull(cal.accountno,'')+
isnull(mailbox.accountno,'')+
isnull(opmgr.accountno,'') as accountno,

frecid as recid

from

(select *, dbo.recid2date(logstamp) as log_stamp, dbo.recid2date(syncstamp) as sync_stamp,
case
when fieldname='zzNew' then 'New'
when fieldname='zsNew' then 'New & synced'
when fieldname='zzzDelComp' then 'Completed'
when action='u' then 'Update'
when action='d' then 'Delete'
else action
end as description,
case
when tableid='&' then 'Contact1'
when tableid='''' then 'Contact2'
when tableid='*' then 'ContGrps'
when tableid=')' then 'ContHist'
when tableid='(' then 'ContSupp'
when tableid='%' then 'Conttlog'
when tableid='0' then 'ContUDef'
else tableid
end as tablename,
case
when userid like '~%' then 'Local'
else 'Remote'
end as location,
case
when userid like '~%' then substring(userid, 2, 8)
else userid
end as username
from conttlog
union
select *, dbo.recid2date(logstamp) as log_stamp, dbo.recid2date(syncstamp) as sync_stamp,
case
when fieldname='zzNew' then 'New'
when fieldname='zsNew' then 'New & synced'
when fieldname='zzzDelComp' then 'Completed'
when action='u' then 'Update'
when action='d' then 'Delete'
else action
end as description,
case
when tableid='"' then 'Cal'
when tableid='6' then 'Fields5'
when tableid='+' then 'Filters'
when tableid='#' then 'Forms'
when tableid='8' then 'FormsFld'
when tableid='J' then 'FormsQue'
when tableid='$' then 'GMTLog'
when tableid='I' then 'GSLogs'
when tableid='H' then 'GSServer'
when tableid='G' then 'GSSites'
when tableid='2' then 'ImpExp'
when tableid='3' then 'InfoMine'
when tableid='D' then 'LeadDbfs'
when tableid='!' then 'LookUp'
when tableid=';' then 'MailBox'
when tableid='B' then 'OpMgr'
when tableid='C' then 'OpMgrFld'
when tableid='/' then 'PerPhone'
when tableid=':' then 'Report32'
when tableid='1' then 'ResItems'
when tableid='4' then 'ScriptsW'
when tableid='-' then 'SpFiles'
when tableid='=' then 'SysLog'
when tableid='7' then 'Tracks'
when tableid='9' then 'UserLog'
when tableid='.' then 'Users'
when tableid=',' then 'DataDict'
when tableid='A' then 'SyncLock'
when tableid='@' then 'SyncTask'
else tableid
end as tablename,
case
when userid like '~%' then 'Local'
else 'Remote'
end as location,
case
when userid like '~%' then substring(userid, 2, 8)
else userid
end as username
from gmtlog
) as tlog

left outer join contact1
on frecid=contact1.recid
left outer join contact2
on frecid=contact2.recid
left outer join conthist
on frecid=conthist.recid
left outer join contsupp
on frecid=contsupp.recid
left outer join cal
on frecid=cal.recid
left outer join mailbox
on frecid=mailbox.recid
left outer join opmgr
on frecid=opmgr.recid

left outer join contact1 as c2
on contact2.accountno=c2.accountno
left outer join contact1 as c3
on conthist.accountno=c3.accountno
left outer join contact1 as c4
on contsupp.accountno=c4.accountno
left outer join contact1 as c5
on cal.accountno=c5.accountno
left outer join contact1 as c6
on mailbox.accountno=c6.accountno
left outer join contact1 as c7
on opmgr.accountno=c7.accountno

where
isnull(contact1.contact,'')+
isnull(c2.contact,'')+
isnull(c3.contact,'')+
isnull(c4.contact,'')+
isnull(c5.contact,'')+
isnull(c6.contact,'')+
isnull(c7.contact,'') like 'fred bloggs'
and
tablename like 'contsupp'

order by logstamp desc


As well as using Steven’s function to translate the logstamp and syncstamp, it also translates the hieroglyphics which GoldMine uses to identify the tables, and distinguishes between changes that were made locally and those which were made by remote (ie syncing) users. It combines both log tables into the one query.

It’s written so that you can filter by table name or, if you want to see all of the log entries, then just say: tablename like ‘%’.

You can also limit the query to a single record. In the example this is Fred Bloggs. Again, if you want all records, just replace the name with a percent sign.

The script will run fine in GoldMine’s own SQL window, although it might time out. If it does, just add the following to the [GoldMine] section of gm.ini:

SQLQueryTimeout = 2
LongSQLQueryTimeout = 2

The numbers represent minutes, so set them to whatever you think is reasonable.

Of course you can achieve something similar in GoldMine by going to Tools > Logs > GoldMine Logs but, personally, I prefer to be one step closer to the underlying data.

I recommend that you have a look at the Tlogs Mechanics document in the downloads area. This will give you the background to what the script is doing. One oddity is that, for most tables, a fieldname of ‘zzNew’ means that a record has never been synchronised, and this is changed to ‘zsNew’ when that record has been included in at least one transfer set. OpMgr records, though, are set to ‘zsNew’ right from the start, which is why the script interprets them as having been synchronised. This just seems to be a quirk or, as GoldMine themselves have just told me, an ‘undocumented feature’!