The
reports that are generated are stored in the Reports.Nsf database or they can
be mailed to the desired recipients.
The
purpose of this article is not so much to teach you how to use these reports
but more to show you how to generate other reports that might be more useful.
For example, the "Top 25 Users by Count" is nice but what about the
other hundreds or thousands of users that you may have? How can you tell how
many emails the 26th highest user by count may have had? There is no way, with
the built in reports, to report on this kind of information.
You
may run into other issues when running these reports. If you have hundreds, or even thousands of
users, and if you keep the Message Tracking information in the MTSTORE database
for long periods of time you will accumulate very large amounts of tracking
data. In these situations, it can take a very long time to generate some of
these reports, especially if you want the report to include all of the data for
something like a Message Volume Summary. In that case, you very likely will not
be able to generate the report you have selected or at a minimum it will take a
very long time to run the report.
What
we want to discuss here is the layout of the records in the MTSTORE.NSF and how
to write a LotusScript program that can read those records and store the data
in such a way that views can be created that give meaningful reports. Of course, other programs could be written to
generate the reports in a more visually pleasing format or, if you wish, you
could easily export the information and generate the reports in something like
a Lotus Symphony or other spreadsheet program.
The MTSTORE.NSF Database
The MTSTORE.NSF Database probably
contains far fewer actual records than you might expect. However, each record
can contain a tremendous amount of data. The database does not store a single
record for each email that passes through the system. Instead, each record is a
table which contains information on many emails. The information is arranged so
that it can be read by using an index method that links the various pieces of
information for each email. Exactly what information is recorded for a
particular email? It depends on what you
are recording. You get the name of the sender, the name of the recipient, the
date and time the document was delivered, the size of the document, the names
of each server the email passed through before being delivered, and, if you have selected to record the
information, you will have the subject line of the email. Other information,
including but not limited to such things as as the Universal Document ID, the
Message Type, and the document Originator are also collected.
All of this information is kept in the
MTSTORE.NSF database and can be used to generate whatever reports you may need.
In this article I will show samples of
the headers in the records contained in the MTSTORE.NSF database but I will not
show the actual data since that information might be sensitive on my production
servers. I suggest you open your own MTSTORE.NSF file and look around. CAUTION: Do not attempt to edit the MTSTORE.NSF
records directly. Unpredictable and Undesirable results WILL occur.
The records in the MTSTORE.NSF files are
made up of tables. Those tables have cells defined which contain the
information about each of the email documents.
Once the tables in a record have recorded information for up to 200
email documents, a new record will be generated. It is possible that a record
will contain less than 200 entries in situations where the message tracking
server has been restarted. On a server shutdown, the information that has been
collected since the last interval expiration will be automatically written to
the database and the record will be closed to further updates. Here is a list
of the information contained in each table and cell:
Table 1:
DISPSTAT - 1=unknown 2=inqueue
4=delivered 8=delivery_failed 16=transferred 32=transfer_failed
64=group_expanded 1073741824=deleted
DISPTM - Time this message was
dispatched
MSGTM - Time this message entered the
tracking system.
MSGSZ - Message Size
MSGTYPE
- 1 =unknown 2=memo 4=deliveryreport 8=nondeliveryreport
16=returnreceipt 32=phonemessage 64=tracereport 128=notice
Table 2:
MTA
- Current mail server.
PREMTA - Mail server from which this
message was transferred.
NEXTMTA - Mail server to which this
message will be transferred.
Table 3:
UNQMSGID - Universal message id that
does not change for this document.
INMSGID -
OUTMSGID
Table 4:
INORG
- Document originator.
OUTORG - Usually the same as INORG.
INRCP - Document recipient.
OUTRCP - Usually the same as INRCP
Table 5:
SUBJ - Subject line of the message.
NODELREA
-
When
looking at these tables it is useful to know the legend that accompanies this
database:
Legend:
Magenta
= meta data Gray =
message data Blue =
recipient data
DISPSTAT:
1=unknown 2=inqueue 4=delivered 8=delivery_failed 16=transferred 32=transfer_failed
64=group_expanded 1073741824=deleted
MSGTYPE:
1=unknown 2=memo 4=deliveryreport 8=nondeliveryreport 16=returnreceipt
32=phonemessage 64=tracereport 128=notice
So we have five tables that contain
information about our email. We see that information in each table is linked to
the previous table and the next table by the location into which the
information is stored. For example, the first entry in each cell represents the
collection of information for the first email. For demonstration purposes I
have created five tables here with the same header names in each cell that you will find in the MTSTORE.NSF database. I
have generated "test" data to illustrate the content of each cell:
|
DISPSTAT
|
DISPTM
|
MSGTM
|
MSGSZ
|
MSGTYPE
|
|
|
|
|
|
|
|
16
|
10/31/2010 05:05:00 AM
|
10/31/2010 05:05:00 AM
|
34260
|
2
|
|
MTA
|
PREMTA
|
NEXTMTA
|
|
|
|
|
|
MYMAILServer/MyDomain
|
YOURMAILServer/MyDomain
|
OutBoundSMTPServer/MyDomain
|
|
UNQMSGID
|
INMSGID
|
OUTMSGID
|
|
|
|
|
|
OFFFA44C71:F41BB47F
ON442577CD:00368A20
|
OFFFA44C71.F41BB47F-ON442577CD.00368A20-442577CD.00367063
|
OFFFA44C71.F41BB47F-ON442577CD.00368A20-442577CD.00367063
|
|
INORG
|
OUTORG
|
INRCP
|
OUTRCP
|
|
|
|
|
|
|
Test User/MyDomain
|
Test User/MyDomain
|
internetuser@inter.net
|
internetuser@inter.net
|
|
SUBJ
|
NODELREA
|
|
|
|
|
First Test Message
|
`z
|
Here is some sample code to read the MTSTORE.NSF database:
{Code;}<
Sub Initialize
REM This
routine is designed to read the MTSTORE database and then use
REM that
information to create an individual record for each email that
REM is
recorded. The records I create will contain the sender, the
REM recipient,
the delivery timestamp, and the message size. Keep in
REM mind that
when you are figuring out how many emails you are
REM
processing, you will find that it will be the number of NSF
REM documents
in the MTSTORE database multiplied by 200. So be prepared
REM for a lot
of records. In my testing, I have included this agent in
REM a database
with several other agents. I wanted to know how many
REM documents
each of my users has sent and received so I wrote an
REM agent that
reads the view where this agent stores all the documents
REM and that
second agent counts to see how many each user has sent and
REM received
and then a summary record containing that count is built
REM for each
user. Remember that when you read the sender and recipient
REM names, you may be seeing the
normal Notes Hierarchical name or you
REM might see
the name in an internet format such as,
REM
jdoe@mydomain.com. If the name is of the internet format, I chose
REM to write
code in another agent that converts the internet format
REM into the associated Notes
Hieracrhical name. I can then generate a
REM report that has totals for all email
showing up under the correct
REM name.
Dim s As New NotesSession
Dim db As New NotesDatabase( "", "" )
Dim data As NotesDatabase 'This will be
where we store the docs
Dim newdoc As NotesDocument
Dim dc As
NotesDocumentCollection
Dim doc As NotesDocument
Dim view As NotesView
Set data =
s.CurrentDatabase 'Notice that our agents run from here
'I have copy
of the mtstore.nsf on my local machine
Call db.Open( "", "messagetracking\\mtstore1031.nsf" )
reccount
=1
Set
dc=db.AllDocuments 'Go get all
the documents at the beginning
Set
view=db.GetView("MailTracking Data")
If Not(view Is Nothing) Then
Set
doc=view.GetFirstDocument
While Not(doc Is Nothing)
For i=0 To Ubound(doc.inorg)
Print "Working on document number "+reccount+" and
index= "+Cstr(i)
Set newdoc =New
Notesdocument(data)
'If Instr returns
True the inorg field does
'not contain a name, but
contains a number which can
'be extracted and
used to find the name of the
'sender. What
this says is that the correct sender
'name is the
value at the index indicated by that
'number. This
technique cuts down on the amount of
‘data that actually has to be stored in
the database.
‘Usually, the index will point to the last entry.
If Instr(doc.inorg(i),"`") Then
first=Strleft(Strright(doc.inorg(i),"`"),"`")
counter=Cint(first)
Newdoc.sender=doc.inorg(counter)
Else
Newdoc.sender=doc.inorg(i)
End If
If Instr(doc.inrcp(i),"`") Then
first=Strleft(Strright(doc.inrcp(i),"`"),"`")
counter=Cint(first)
Newdoc.Recipient=doc.inrcp(counter)
Else
Newdoc.Recipient=doc.inrcp(i)
End If
Newdoc.TimeStamp=doc.msgtm(i)
Newdoc.MessageSize=doc.msgsz(i)
Call newdoc.Save(True, False)
Next
Set doc =
view.GetNextDocument(doc)
reccount=reccount+1
Wend
End If
End Sub
>{code}
Be
aware that to run this agent you must take several steps first:
- Create a new database with a blank template.
- Create a form for you records. This form must
contain the fields indicated in this program that are being written in the
Newdoc document.
- Create views to be able to see the documents.
- You may want to create other forms for summary
data.
- You may want to create a wide assortment of
views to be able to view the data from different perspectives.
- Include this agent and you may, of course, wish
to write other agents that can perform actions such as creating summary
data, or creating other records that suit your individual needs.
.
After
the agent has run, the database will contain a single record for each email
that has been either sent or received. In my test I had over 400 documents in
the MTSTORE.NSF database which, multiplied by 200, means I had over 80,000
records. After creating those records I
then needed to summarize those records into one record for each user. That one
record, maybe for John Doe, would contain the number of email documents sent
and the number received for Mr. Doe. I could then create views in my database
to allow a visual report of how much email each person is sending and receiving
for a given time period. It is important to note that email sent or received by
a particular individual may show up with the hierarchical name or a variety of
internet address formats. As a simple example consider the following:
John
Doe/MyDomain
jdoe@mydomain.com
Both
of these addresses refer to the same person. If you want your reports to be
accurate you may find that you need to write some programs to collect the data
together and to figure out just how many different formats a person's name may
take.
The information
contained in this document is not guaranteed to be totally accurate and if you
run the code given herein, you do so at your own risk. Please review each line
to make sure it applies correctly to your environment.