• Replies 3 replies
  • Subscribers 338 subscribers
  • Views 53 views
  • Users 0 members are here
Related

System Table Audit Script

Levi Sauerbrei
$organization

Something like this may already be out there in the world, but I didn't find it when I looked.

After a trouble ticket last week was traced back to a System Table change made by a user, I wanted an easy way to see what values had changed in all system tables within a date range.

After a little monkeying around and a trip back to Dynamic SQL school, I came up with something that gives me exactly that.

The script parses T_REFERENCE_TABLES.  Anything it finds that has a 'last_updated_dt' column registered gets polled and added to the result.

What I end up with is a table with the System Table name, the record ID and the audit information.  Not perfect yet but it will give me a quick way to see if someone has been messing around in an area that is suddenly having troubles.

I've attached the script to my profile if anyone is interested.

Parents Reply Children
  • $organization in reply to Sheila Kearney Miller
    Hi Sheila, I'm replying to this as I believe Levi no longer is part of the Tessitura family - a big loss! I've used this script before - and have made a couple of modifications. My modifications were necessary due to the changes in the realm of System Tables. The two things I had to change were: 1. to deal with System Tables that have a name longer than 30 characters such as TX_CONTACT_POINT_CATEGORY_PURPOSE 2. to deal with the fact that not all STs have an id column - such as TX_MACHINE_LOCATION Whilst this script tells me which tables were updated during the previous 7 days, it only shows the date and user of the last update - so if there were several in the period between running the script, you don't see all updates. Also, it doesn't show you the "before and after" data. Does this address the problems you're having? Martin
  • $organization in reply to Martin Keen (Past Member)
    Thanks, Martin-- I will try that! Cheers, Sheila