Standardize on ModifiedBy column -> Enable audit trail

This is a forum to suggest new features for mojoPortal. 

This thread is closed to new posts. You must sign in to post in the forums.
9/28/2012 11:59:17 AM
Gravatar
Total Posts 58

Standardize on ModifiedBy column -> Enable audit trail

One of our clients has been experiencing a recurring problem that has re-awoken something that I've been mulling over for a while.

The view permissions on the home page keep changing so that only admins can see it.  This means that non-admin users get redirected to a login page - not terribly friendly for a large church!

We've been round the loop of checking that only the appropriate staff have admin rights etc and they've all promise me that they haven't done anything silly.  The problem seemed to have disappeared but today it happened again and they called me as soon as they spotted it.  The obvious thing for me to do was for me to check the last modified date and time on the mp_Pages table, but to my surprise although this table has a LastModifiedUTC column it doesn't have a column saying who modified it!

 

I will put a post in the bugs forum just in case anyone else has spotted a similar issue, but it raises a bigger issue - the lack of an audit trail.  

Frustratingly mojoPortal already has much of what's needed to create a trigger-based audit trailing system, and I would gladly put some development work in to get it going, but before it can work correctly we need to standardize on certain column names.

Many of the Mojo tables already collect information about when a table row was last modified and who by but it's not consistent.  If you run the following query you'll get back a list of all the modification related columns:

SELECT o.name as 'table',c.name as 'column'
FROM sys.all_columns c
left join sys.all_objects o on c.object_id=o.object_id
WHERE c.name like '%Mod%'and o.type='U'
and c.name not like '%Module%' and c.name not like '%mode%'
order by o.name

Here's a selection:

Table                    Column
mp_Pages           LastModifiedUTC
mp_RssFeeds    LastModUserGuid
mp_RssFeeds    LastModUtc
mp_SavedQuery LastModUtc
mp_SavedQuery LastModBy
mp_Tag                ModifiedBy
mp_Tag                ModifiedUtc

If we could standardize on the name of the columns - e.g. "LastModUTC" and "LastModBy" it would be fairly straightforward to write a trigger that could -

  • Check in an audit trail config table whether this table has auditing enabled, and which column values are to be stored.
  • Write the before (and possibly after) values of the selected columns into a separate audit trail table.

So, please could we have a "LastModBy" column added to mp_Pages, and then have a process of standardizing on the name of the "modifiedby" columns as the next version of Mojo are released and new features are rolled-out?

Thanks

9/28/2012 2:10:03 PM
Gravatar
Total Posts 18439

Re: Standardize on ModifiedBy column -> Enable audit trail

I've got a to do item in our project tracker to add fields for those purposes to page settings.

Note that the purpose of the existing field was not really for tracking who modified the page settings, it was for tracking which pages have new content, but since pages don't directly have content attached to the page object but only via features loaded on the page, that timestamp could be updated by any feature upon editing the feature content ie the html content feature whenever its own content is updated would also update the timestamp on the page that contains it.

So I will be adding 2 new fields to actually keep track of change to page settings. I totally agree we should have those fields.

Best,

Joe

9/28/2012 6:32:13 PM
Gravatar
Total Posts 58

Re: Standardize on ModifiedBy column -> Enable audit trail

Thanks Joe - that's excellent news.

Ian

10/8/2012 2:33:43 PM
Gravatar
Total Posts 18439

Re: Standardize on ModifiedBy column -> Enable audit trail

fyi, I just completed these changes so it will be in the coming release. Only problem is that the created date will not be correct for existing pages, only for newly created ones. In addition to tracking the user that modified page settings it will also track the ip address.

Best,

Joe

10/8/2012 6:31:13 PM
Gravatar
Total Posts 58

Re: Standardize on ModifiedBy column -> Enable audit trail

That's excellent news - I had no expectation of if happening so soon - thanks Joe!

I suppose I'll have to think about coming good on my promise of a trigger based audit trail now!

Thanks again - I'll let me client know - I'm sure they'll be very pleased.

Ian

 

You must sign in to post in the forums. This thread is closed to new posts.