Manage Learn to apply best practices and optimize your operations.

Gather usage statistics within a database

Track the activity in a database, who is using it and what exactly is being used.

These steps describe how to build usage statistics within a Notes database. This will work on local replicas as well as the server replica. Track who is using it, when and how long they are using it and what features of the database are being used. All users must have at least 'Author' access to the database for this to work. Add the following design elements and enhancements to any database. Watch out - this will add record volume to your application. The performance hit is negligible.

This results in one document per user, per database session to track every form and view(at least the ones you are tracking) that was accessed while the user was in the database. Summarized in the view it allows for easy tracking to determine if a database is being used and what elements are of value to the users based on actual usage. The subroutine call can also be added to agents to track actions that the users have performed while in the database.


1. Create a form (fmUsageProfile) with a numeric field for every design element you want to track usage. This document will be used as a profile document. (Since this is a profile document you don't actually have to physically have a design element, but it is helpful to keep track of the field names you use.)

Fields to include
User - text or names
OpenDate - date/time
CloseDate - date/time
View1 - numeric
Form1 - numeric ...

2. Create a form (fmUsageSummary) with all of the same fields as the profile document. This form will be used to display the statistics in a view.

3. Create a script library with one subroutine with the following code:

Sub UpdateUsageProfile(sFieldname As String)
     Dim session As New notessession
     Dim db As notesdatabase
     Dim docUsageProfile As notesdocument
     Dim iSum As Integer
     Dim varSum As Variant
     
     Set db = session.currentdatabase
     Set docUsageProfile = db.GetProfileDocument("fmUsageProfile", session.username)
     
     varSum = docUsageProfile.GetItemValue(sFieldName)
     
     iSum = varSum(0) + 1
     
     Call docUsageProfile.replaceitemvalue(sFieldname,iSum)
     
     docUsageProfile.save True,True
End Sub

4. In the Database Script element:
- Create a subroutine that initializes all of the tallying fields on the profile document. . This will be called by the Initialize and QueryClose events.

Sub SetProfile(docUsageProfile As notesdocument)
docUsageProfile.View1= 0
docUsageProfile.Form1 = 0
docUsageProfile.OpenDate = ""  
End Sub

- In the Initialize event, create the profile document and call the initialize routine and set the opendate with the current time.

Dim session As New notessession
Dim db As notesdatabase
Dim docUsageProfile As notesdocument
Dim dt As New notesdatetime( "")
Set db = session.currentdatabase
Call dt.SETNOW
Set docUsageProfile = db.GetProfileDocument("fmUsageProfile", session.username)
Call SetProfile(docUsageProfile)
docUsageProfile.User = session.commonusername
Set docUsageProfile.OpenDate = dt
docUsageProfile.save True,True

- In the QueryClose event set the CloseDate and copy the profile document fields to the notes form created in step 2.

Dim session As New notessession
Dim db As notesdatabase
Dim doc As notesdocument
Dim dt As New notesdatetime( "")
Call dt.SETNOW
Dim docUsageProfile As notesdocument
Set db = session.currentdatabase
Set docUsageProfile = db.GetProfileDocument("fmUsageProfile",
session.username)
Set docUsageProfile.CloseDate = dt

Set doc = db.CreateDocument
doc.Form = "fmUsageSummary"
Call docUsageProfile.CopyAllItems (doc, True)
doc.Save True,True
Call SetProfile(docUsageProfile)  '//optional,  it is also cleared on the
initialize
docUsageProfile.Save True,True

5. Create a view with a selection formula to include all "fmUsageSummary" documents. Categorize this view by User (@name([CN];User)), by @Month(OpenDate) Columns Include
OpenDate
# of Times Opened - (enter a constant value of 1 and total the column)
Duration - (CloseDate - Opendate) / 60
Create a column for each tally field that you want to total or create a column that will add similar design element tally fields together to get a total (i.e. add all the fields that are associated with a particular view category like customer). Be sure total the columns by category.

6. In each design element you want to track, in the 'Options' event include the script library that was built in step 3. In the PostOpen or QueryClose event of the design element call the subroutine passing the field name you want to increment.
(i.e. In the PostOpen of 1. Main View - Call UpdateUsageProfile("View1"))

Dig Deeper on Domino Resources - Part 4

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchWindowsServer

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchDataCenter

SearchContentManagement

Close