Follow our news:

Follow canonburysvcs on Twitter Follow Canonbury Services on Facebook Follow Canonbury Services' news by RSS Follow Canonbury Services' news by Atom Follow Canonbury Services' news by email


How to use GM logs for forensics

04/04/2012 – in SQL queries

The GoldMine user logs (Tools>Logs>GoldMine Logs>User Login Logs) give you a quick and easy way of seeing when current users logged in and out. However, the list of users doesn’t include users who have been deleted.

However, as long as you haven’t pruned the logs themselves, which is good practice to do now and again to stop the logs growing and taking over the world, then you can still get at the information by writing a SQL query:

select * from userlog where userid='fred' order by login desc

Just replace ‘fred’ with the user that you’re interested in.

There’s more that you can do though. Anyone who knows anything about GoldMine will know that it’s easy to write a query to see someone’s history items:

select * from conthist where userid='fred' order by ondate desc

but you can also have a look at the record changes that a user made. This query is a bit more complicated:

select * from contact2, conttlog where tableid='''' and frecid=recid and userid like '%fred' order by logstamp desc

(that’s four single quotes in the middle)

This query uses one of the TLog tables. Conttlog logs every change made to the contact tables in order to keep track of things for synchronisation but unfortunately it’s not very user friendly. We can use it to bring up the records that Fred changed, in descending order by date and time, but unfortunately there’s no easy way from within the query to see what he did or when it was. But there is a way!

From the SQL query just click back onto the contact record tab. The record that you’re on will correspond to whichever one you have highlighted in the SQL results pane. Now go to Tools>Synchronise>Sync Spy to reveal the changes that were made, when and by whom.