Retrieving email from multiple databases

hcl-bot · September 7, 2007, 1:50pm

I just received a requirement from a client that I have never encountered before. As a result of a court order, they must scan every Notes mail database in their domain (approx 300), and produce a printout of every email received from a single individual.

Has anyone ever had that kind of a requirement before, and do you have any ideas how I would approach this requirement with some kind of an agent? One acceptable option we discussed was to write each email to a database, and print out all the documents manually.

BTW - did I mention this is on a pretty short fuse?

Anyway, any ideas would be greatly appreciated.

hcl-bot · September 7, 2007, 2:27pm

Subject: An idea maybe???

Just an idea…

Open every mail file and locate the documents (by mail file), creating a list below and then get Users to print’m and turn’m in somewhere…

Then you can compare the list below to the actual printouts for validation.

As I said, just an idea!!!

Partial code for a start…

Dim Session As New NotesSession

 Dim directory As notesdbdirectory

 Dim Db As NotesDatabase 

 Dim Doc As NotesDocument

 Dim View As NotesView     

 Dim Acl As NotesAcl

 Dim AclEntry As Notesaclentry   

 Dim dbACL As NotesACL

 Dim itemACL As NotesItem

 Dim ACLNames As NotesACLEntry

 Dim ACLLevel As String

 

 Set directory = New notesdbdirectory ( Inputbox$("What is the Server Name?"))

 Set db = directory.getfirstdatabase(database)

 x = 0

 While Not db Is Nothing 

      x = x+1

      On Error Goto FileOpenError

      Call db.open("","")

      On Error Goto 0

      

      

      Set mydb = session.CurrentDatabase

      Set doc = mydb.createdocument

      doc.Form = "Default Access Report"

      doc.dbtitle = Db.Title

      doc.DbCreatedDate = Db.Created

      

      doc.DBPath = Db.FilePath

      

      

      Set dbACL = db.ACL

code to get view and locate documentbykey goes here

     Continue:          

      Set db = directory.getnextdatabase

      

      

      Call Doc.Save(True,False)

      

 Wend

 

 

 Dim w As New NotesUIWorkspace

 Call w.ViewRefresh

 Exit Sub

FileOpenError:

 Doc.DBPath = Db.FilePath

 Doc.DBTitle = "++ Unable to Open Database ++"

 SaveFlag = Doc.Save(True,True)

 Resume continue

hcl-bot · September 7, 2007, 2:50pm

Subject: RE: An idea maybe???

Code looks interesting!

Two issues: because of the legal issues, client prefers users not be involved in the process. And we are leaning toward using an extra database to copy all emails into, and printing manually from there.

I will look more closely at the code, and get back to you.

hcl-bot · September 7, 2007, 2:41pm

Subject: Retrieving email from multiple databases

you could write something yourself or you can buy a copy of Power Tools which should do what you need. There’s a utility in there called Find User Mail Documents. It will create a record for each mail file that it finds documents in the match your search criteria and will create a doclink to each document. Then you can open each doclink and print the docs out. If it’s just for 1 person I would hope it wouldn’t find too many matches. You might be better off just writing something yourself. The only difficulty you might have in both cases is dealing with encrypted mail. You win’t be able to see the contents of the e-mail unless you have the user’s ID. You could code your own search agent as such:

Get all mail files (either cycle through the DD or build your own list)

cycle through each mail file and use the Search method to find all documents where the field From, InetFrom or SMTPOriginator contains the person’s e-mail address that you’re seraching for and create a document collection

then you could copy the document collection to a new database

when all done open the database and print them out

hcl-bot · September 7, 2007, 2:57pm

Subject: RE: Retrieving email from multiple databases

It looks like the best solution, all things considered, is to write my own code. Because of the short fuse, I was hoping someone had done something like this before. I can write it, I was just looking for short cuts. I have to believe someone has had a requirement, imposed by a court somewhere, to retrieve emails.

You analysis confirms mine…and I will proceed.

hcl-bot · September 7, 2007, 3:21pm

Subject: RE: Retrieving email from multiple databases

I’ve had to do something similar, fortunately your requirements are a lot easier than mine were:

http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedweb/aa54d181bdf860af85257274006d92ea?OpenDocument

I spent a lot of time with Lotus on the phone, and like you, I have to believe someone has either had a requirement, or at least that Lotus thought this through somewhere along the line - apparently they have not.

(not trying to be negative, just truthful)

Anyhow, at the ‘end’ of an almost year-long open PMR with Lotus, when it ended up getting closed, the entire thing boiled down to: (paraphrasing) “Next time you need to do something like this, try domain index search instead of Full Text search and see what happens”.

In your case, I think your two options boil down to:

Try something similar to what michael b suggested; open each database, read thru the all documents view looking for something by this particular individual, and copy each document to a separate database

or

Create a domain index, and search that; copying documents to a separate database. Not ever having done this, I don’t know the ins and outs.

You don’t say how many servers you have - I’ve got an agent that opens each mail file based on the user’s home server (from the NAB) I can post part of that code here if you need it; or anything else just ask.

hcl-bot · September 7, 2007, 3:33pm

Subject: RE: Retrieving email from multiple databases

Good News! Only one server…if you have some code, it would certainly be appreciated. Thanks!

hcl-bot · September 9, 2007, 2:19pm

Subject: RE: Retrieving email from multiple databases

Here is basically the code I used to search all my databases → http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/aa54d181bdf860af85257274006d92ea?OpenDocument

Some things to keep in mind -

If you try to create one agent, and run it from the server, it’ll time out (‘Max LotusScript/Java execution time:’ on Server document). I started to run mine as a local agent, but ‘CreateFTIndex’ works on Local Database only, so I ended up making this a RunOnServer agent.
If you’re going to do the search on the production server, I would NOT use the ‘maildb.FTSearch’ method. I’d instead open the all documents view, and read each document one at a time looking for the sender. (see below)
If you use ‘maildb.FTSearch’, you’ll probably want to create all the FTIs ahead of time; if you use an agent to create them, the process times out and returns an error to the agent - HOWEVER - the FTI process continues to run in the background, and eventually the combined threads will crash the server.
If you use ‘maildb.FTSearch’, you’ll probably find that various other server tasks (such as Updall, Update and especially CHRONOS) will cause the agent to crash (both tasks will try to lock the databases, one of them is going to bomb).
If you use ‘maildb.FTSearch’, you’ll possibly find that the agent will crash after a period of time anyway due to some memory ‘condition’, (it’s ‘not a leak’ per Lotus support, but the memory never gets de-allocated) and the only way to restart it is to restart the server.

When i ran my agent, I created a custom database, and copied some of the mail forms, etc over to it. If I were to do it again, I’d probably just create a new mail database.

Then I created two Forms, One contained some fields for notes, and a button to start the RunOnServer Agent:

Sub Click(Source As Button)

'11/30/2006 - MAT	Add 'RestartDB' field



Dim workspace As New NotesUIWorkspace

Dim uidoc As NotesUIDocument

Dim doc As notesdocument

Dim s As New NotesSession

Dim db As NotesDatabase

Dim logdoc As NotesDocument

Dim agent As NotesAgent



Set db = s.CurrentDatabase



If db.Server = "" Then

	Msgbox "This Agent should be run from a database on the Server"

End If



Set uidoc = workspace.CurrentDocument

Set doc = uidoc.document

REM Create document containing data to be passed

Set logdoc = New NotesDocument(db)

logdoc.Form = "SearchResults"

logdoc.ServerName = doc.ServerName

logdoc.Title = doc.Title

logdoc.RunDate = Format(Cstr(Now), "mm/dd/yyyy hh:mm AM/PM" )

logdoc.Filters = doc.Filters

logdoc.SearchLoc = doc.SearchLoc

logdoc.SpecificDB = doc.SpecificDB

logdoc.RestartDB = doc.RestartDB

Call logdoc.Save(True, False)

REM Start agent and pass note ID of document

Set agent = db.GetAgent("(Search Mail Databases)")

If agent.RunOnServer(logdoc.NoteID) = 0 Then

	Messagebox "Agent ran",, "Success"

Else

	Messagebox "Agent did not run",, "Failure"

End If



Call uidoc.close

Call workspace.ViewRefresh

End Sub

The other Form was the “SearchResults” form - as the name implies, it contained various fields and was populated by the 'RunOnServer button (code above) as well as the results (‘Results’ field) from the agent itself.

When the agent runs, it copies any documents that meet the search criteria to the database, and adds a 1 line ‘log’ to the ‘Results’ field.

The agent below is modified (and untested) but shows an alternate to the maildb.ftsearch method.

You’ll want to define ‘SearchString$’ - possibly just the user’s last name - depends on if you’re looking for an SMTP address, Notes address or either.

The sample code doesn’t check for mail send TO that particular user, so some other items you might want to check ‘Body’, ‘SendTo’, ‘CopyTo’, ‘BlindCopyTo’, etc.

Sub Initialize

'11/02/06	MAT	Search Mail Databases

'Declare all variables

Dim session As New NotesSession

Dim db As NotesDatabase

Dim MaildbDir As NotesDbDirectory	

Dim agent As NotesAgent

Dim Maildb As NotesDatabase 

Dim mailview As NotesView

Dim Maildoc As NotesDocument

Dim nextMaildoc As NotesDocument

Dim newDoc As NotesDocument

Dim doc As NotesDocument

Dim rtitem As NotesRichTextItem

Dim DateTime As NotesDateTime

'Set error checking

On Error Goto processError

'Set up variables

Set db = session.CurrentDatabase 

Set agent = session.CurrentAgent



Set doc = db.GetDocumentByID(agent.ParameterDocID)



Set rtitem = New NotesRichTextItem ( doc, "Results" )

'Find all databases in the specified folder and search for text

Set MaildbDir = New NotesDbDirectory( db.Server ) 

Set Maildb = MaildbDir.GetFirstDatabase( DATABASE ) 

Do Until Maildb Is Nothing

	If Not Lcase(Left(Maildb.FilePath, 5)) = "mail\" Then Goto nextdb

	

	Print "Searching 'Mail' file: " + Maildb.Title

	Call rtitem.AddNewLine( 1 )

	Call rtitem.AppendText( "Searching 'Mail' file: " + Maildb.Title )

	Call rtitem.AddNewLine( 2 )

	

	Gosub SearchDB

nextdb:

	Set Maildb = MaildbDir.GetNextDatabase

Loop



Goto TheEnd

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

SearchDB:

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Set mailview = Maildb.GetView("($All)")		



Set maildoc = mailview.GetFirstDocument

While Not maildoc Is Nothing		

	Set Nextmaildoc = mailview.GetNextDocument(maildoc)		

	

	If maildoc.HasItem("Principal") Then

		If Instr( 1, maildoc.principal(0) , SearchString$ , 5 ) > 0 Then

			Gosub logit

			Goto gotone

		End If

	End If

	

	If maildoc.HasItem("SMTPOriginator") Then

		If Instr( 1, maildoc.SMTPOriginator(0) , SearchString$ , 5 ) > 0 Then

			Gosub logit

			Goto gotone

		End If

	End If

	

	If maildoc.HasItem("From") Then

		If Instr( 1, maildoc.From(0) , SearchString$ , 5 ) > 0 Then

			Gosub logit

			Goto gotone

		End If

	End If

	

'More here....

gotone:

	Set maildoc = Nextmaildoc

Wend



Return

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

LogIt:

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Set newDoc = MailDoc.CopyToDatabase( db )	



If MailDoc.HasItem ("DeliveredDate" ) Then

	docDate$ = Cstr(MailDoc.DeliveredDate(0))

Elseif MailDoc.HasItem ("PostedDate" )Then	

	docDate$ = Cstr(MailDoc.PostedDate(0))

Else

	docDate$ = Cstr(MailDoc.Created)

End If



Set DateTime = session.CreateDateTime( docDate$)



MsgTxt$ = Maildb.Title + ",   Date:  " + DateTime.DateOnly + ",  Subject: " + MailDoc.Subject(0)

Call rtitem.AppendText( MsgTxt$ )

Call rtitem.AddNewLine( 1 )



Return

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

processError:

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

ErrTxt$ = "Error " & Err() & ": " & Error() & " at Line# " & Erl() 



Print "ERROR -> " + ErrTxt$



Call rtitem.AddNewLine( 1 )

Call rtitem.AppendText( ErrTxt$ )

Call rtitem.AddNewLine( 1 )



Call rtitem.AddNewLine( 1 )

Call rtitem.AppendText( "Fatal Error while processing Database: " + Maildb.FilePath )

Call rtitem.AddNewLine( 1 )



Resume TheEnd

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

TheEnd:

’ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

’ Call doc.Save(True, False, False)

Print "Agent Complete"

Call rtitem.AddNewLine( 1 )

Call rtitem.AppendText( "Agent Complete")



Call doc.Save(True, False, False)

End Sub