Manage
Learn to apply best practices and optimize your operations.
Mail Event Log Analyze Through Excel
The database stores the summaries and csv files created by the MailLogAnalyze
button in the"Mail Routing Events" view in the address book.(r46).
Installation.
Copy this database to a server,
Copy the "MailLogAnalyze" view action to your servers log.nsf, edit the script
and enter the location of this database ( now called "yourserver\maillog.nsf"
Copy the "Explode events" agent to yourservers log.nsf
Run.
Run the "Explode events" on one days documents in the Mail routing Event,
select the same documents as above and
Run the view agent "MailLogAnalyze" from the "Mail Routing Events" view of the
log.nsf.
What happens.
1. The Events filed is splitted into multivaluefields.
2. One record for each message is written to the csv file
3. The csv file is formatted and autofiltered.
4. The csv file is attached to the maillog "Summary" document.
5. Summary information from excel is stored in the maillog summary document.
6. You don't have to save the excel file as the csv is available from the
maillog.nsf.
7. The "View details" button in the summary form detaches and formattes the csv
file. The usage of csv as file type save a lot of diskspace.
Disclaimer.
Use the agents on you own risks.
Sub Click(Source As Button)
' detach file
Dim w As New Notesuiworkspace
Dim uidoc As notesuidocument
Dim doc As notesdocument
Dim item As notesrichtextitem
Set uidoc=w.Currentdocument
Set doc=uidoc.document
Dim attach As notesembeddedobject
Set attach=doc.Getattachment("mailevents.csv")
Call attach.Extractfile("C:\temp\mailevents.csv")
'format excel
Dim xlApp As Variant
Dim xlsheet As Variant
Set xlApp = CreateObject("Excel.Application")
xlApp.StatusBar = "Opening WorkSheet. Please be patient..."
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.ReferenceStyle = 2
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
xlsheet.Name = "Notes mail report"
xlApp.workbooks.Open("c:\temp\mailevents.csv")
xlapp.Rows("1:1").Select
xlapp.Selection.Font.Size=20
xlapp.Selection.Font.Bold = True
xlapp.Rows("3:3").Select
xlapp.Selection.Font.Bold = True
xlapp.Rows("3:10000").Select
xlapp.selection.Font.Name = "Arial"
xlapp.selection.Font.Size = 9
xlapp.Rows("3:10000").Select
xlapp.selection.Font.Size = 9
xlapp.selection.columns.Autofit
xlapp.selection.columns.Autofilter
xlapp.referencestyle = 1
xlapp.Range("A1").Select
xlApp.StatusBar = "Opening WorkSheet completed"
xlapp.Range("G2").value= "=sum(G3:G20000)"
xlapp.Range("H2").value= "=sum(H3:H20000)"
xlapp.Range("I2").value= "=sum(I3:I20000)"
xlapp.Range("J2").value= "=sum(J3:J20000)"
xlapp.Range("K2").value= "=sum(K3:K20000)"
xlapp.Range("A2").value= "=sum(H2:K2)/2"
xlapp.referencestyle = 1
xlapp.Range("A1").Select
End Sub
The code for the "Explode events" macro:
FIELD EVENTS:=@Explode(Events;@Char(0));SELECT @All
the code for the MailLogAnalyze view button:
Sub Click(Source As Button)
'
'
'replace yourserver with your servername
'
'
Dim s As New notessession
Dim view As notesview
Dim dc As notesdocumentcollection
Dim doc As notesdocument
Dim db As notesdatabase
Dim vlength,x,y,y1,y2,y3,y4,numberofmails,dd As Integer
Set db=s.currentdatabase
Set dc=db.unprocessedDocuments
Dim texts As Variant
numberofmails=0
dd=0
Set doc=dc.getfirstdocument
Open "c:\temp\mailevents.csv" For Output As #1
Print #1, "Mail routing report for selected documents"
Print #1, " "
Print #1, "Date;Hour;Message;ToServer;ForUser;FromUser;Size;ToInternal
;ToExternal;FromInternal;FromExternal"
While Not doc Is Nothing
' analyze doc
Dim item As NotesItem
Set item = doc.GetFirstItem( "Events" )
dd=dd+1
Forall v In item.Values
y=Instr(31,v,"Message")
y1=Instr(40,v,"transferred")
If y+y1=79 Then
y6=Instr(39,v,"to ")
y2=Instr(y6,v,"for")
y3=Instr(1,v,"from")
y4=Instr(1,v,"Size:")+6
y5=Instr(y4,v,"K")
Datestring$=Mid(v,1,10)
HourOfDay$=Mid(v,12,2)
Message$=Mid(v,39,8)
ToServer$=Mid(v,y6+2,y2-(y6+3))
ForUser$=Mid(v,y2+4,y3-(y2+5))
FromUser$=Mid(v,y3+4,(y4-7)-(y3+5))
Size$=Mid(v,y4,(y5-y4))
numberofmails=numberofmails+1
fx% = Instr(1,FromUser$,";") ' From
tx%= Instr(1,ForUser$,";") ' To
If fx% > 0 Then
FromUser$=Left(FromUser$,fx%) & Mid(FromUser$,fx%+1)
End If
