Mail Event Log Analyze Through Excel

Mail log analyze.

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

This was first published in November 2000

Dig deeper on Domino Resources - Part 7

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWinIT

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 ...

SearchEnterpriseLinux

SearchVirtualDataCentre.co.UK

Close