Notes tone unturned: About recording user database activity tracking in IBM Lotus Notes/Domino 8.x

Saturday, November 28, 2009

About recording user database activity tracking in IBM Lotus Notes/Domino 8.x

Who did what to the important contents of my database, when, and where? All good questions if you own, sponsor, administer or make design changes to that database.

This post was inspired by reading Vincenzo Capponcelli’s article in his blog, at Database-User Activity: da domino 8.x maggior dettaglio per inserimenti, cancellazioni, aggiornamenti where he says he’s just noticed extended capability of Notes 8.x databases, those with on-disk structure (ODS) version 51, to separately record database Adds, Updates and Deletes where in previous ODS versions only “Writes” were recorded.

I tried to post a comment on the blog but, at the moment anyhow, it rejecting attempted comments with error message “Comment has been disallowed by the Spam Filter” – so, Vincenzo, you need to fix this.

Luckily, I had saved my comments offline (I’ve been caught before by bugs and annoyances like this, on various web sites that should work far better than they do).
So instead of losing them when the error page returned me to a blank comments section, below is the knowledge contribution that I tried to make at Vincenzo’s blog.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Ciao Vincenzo, da Down Under!
Yes, it is definitely an improvement to record the Adds, Updates and Deletes separately. However, when you say it answers the question "chi ha rubato la marmellata?" [who stole the jam?]

I don’t think it really helps a great deal. All that it answers, unfortunately, is "who MIGHT have stolen the jam?" and not "who ACTUALLY stole the jam?"

This is better than before, but not the complete story, since it still doesn't tell you what was deleted or changed, exactly when each event occurred, and so on. You might get the desired information from the Statlog server task, but this doesn't help for database actions performed while the user is disconnected from any server.

If you want a full audit trail, you need to write some code, perhaps using examples and tips from various web sites and blogs, or a couple of the tools available from OpenNTF.org to save you some effort.

There are some commercial offerings that go much further. For example, over the last ten years or so, I developed and enhanced a commercial developer toolkit (SDK) that I call NotesTracker, which records a lot of details about users who perform CRUD actions (Create, Read, Update, Delete).

And a range of other things, too. Continuing along the lines of Vincenzo’s question, this is sort of like whether the jam was marmalade, raspberry, strawberry, plum …

The tracking of document deletions proved the hardest, because of the way that deletion works in Notes/Domino, and this is explained in the NotesTracker Guide (free to download, from http://asiapac.com.au/UsageTracker_Download.htm#Documentation or http://notestracker.com/UsageTracker_Download.htm#Documentation ).

I've had some people ask me questions like "Can I determine who deleted certain important documents from my database right back in 2007?" (or quite a few months ago, anyway).

But they become very disappointed when I have to give them the bad news. That is, you must have previously switched on either the built-in Notes tracking (the "Record activity" option in the database properties) or in your roll-your-own tracking tool or a commercial one like NotesTracker.

And it must be configured properly to track deletions. For example, this is a user-definable setting in NotesTracker. See the snapshots here or here to understand what I mean.

(Click to view a larger image)

Naturally enough, they will not be able to discover the culprit before any such tracking was activated. Certainly not three or four years back, as one person asked me about, for sure!

According to the Admin Help database (under the topic "Managing database activity recording in databases") apparently only 64K is allocated to the native activity tracking, so you cannot even rely on it to keep more than a smallish amount of history. I would guess the built-in history that must fit into this 64K space will go back at most only a few weeks or months, depending on how busy that particular database is. The roll-your-own or commercial tools should enable you to retain tracking history as far back as you like, limited only by available disk space (certainly NotesTracker works this way).

But you'll get this storing of user activity only while the tracking was activated, of course. … Just like one of those crime movies where police find they can’t see who stole the Crown Jewels or the Mona Lisa because the video recording was switched off at the time the event happened!