Saturday, August 30, 2008

Retrieve data from Lotus Notes with PowerShell

Last week when I was playing with PowerShell I tried to connect to my Lotus Notes (LN) mailbox and retrieve some data to console. After some minutes I'v just had an idea to try something more useful then read emails in text form. Let me describe the scenario.

In IT we use database where we store all data about our hardware. About it's assignments, lease time, specification, etc. I'd like to check all notebooks which are after their date of warranty.

First of all it's necessary to check names of LN field we will use. In this case it's Category, Type, Owner, End of warranty. If LN designer are good then it's easy to find it - in my case, I was lucky because names of fields were self-descriptive. I used accessing through COM and very helpful was Lotus Domino Designer Help.

Connect to the database and open the View

# Create LN Object
$DomSession = New-Object -ComObject Lotus.NotesSession

# Initialize LN Object
# You'll be asking for LN password to your id
$DomSession.Initialize()


# Connect to Server, select db and display the name
$DomDatabase = $DomSession.GetDatabase("LN007","IT\HW.nsf")
Write-Host "Database open : " $DomDatabase.Title

# Open specific View (By Serial Number)
$DomView = $DomDatabase.GetView('Serial Number')
Write-Host "View read : " $DomView.Name


# Show number of documents
$DomNumOfDocs = $DomView.AllEntries.Count
Write-Host "Num of Docs : " $DomNumOfDocs


# Get First Document in the View
$DomDoc = $DomView.GetFirstDocument()


So, we connected to the mentioned database (hw.nsf) which is located on the LN007 server. Then we found the right view, counted all documents in it and assigned first document into variable $DomDoc. Now let's have a look how to see the data.

Read fields for current document
Let's first see the script and then comment it

$i = 0
while ($DomDoc -ne $null) {
$item = [string] $DomDoc.GetItemValue("fldPurchaseDateEnd")

if ( $item.Trim().Length -ne 0 ) {
$tMonth,$tDay,$tYear = $item.split("/")
$tDate = "$tYear$tMonth$tDay"

if ( ( $currDate -gt $tdate ) -and ( $DomDoc.GetItemValue("fldHWCategory") -eq "Notebook" ) ) {
Write-host $DomDoc.GetItemValue("fldHardwareType")":" $DomDoc.GetItemValue("fldCurrentUser") ":" $item.substring(0,10)
$i++
} #if - date, HWcategory
} #if - length 0


$DomDoc = $DomView.GetNextDocument($DomDoc)
} #while

Write-Host "Out of leasing : " $i


I used while loop to go thru all documents in the view. GetItemValue is used for getting value of specific field. The rest is easy - if the length of fldPurchaseDateEnd is not zero (e.g. date exists) check the date and compare it with current (variable $currDate created at the beginning of the script this way:

$Date = [string] (Get-Date)
$Day,$Month,$Year,$Hour,$Minute,$Second = $Date.split(" .:/")
$currDate = "$Year$Month$Day"


this was necessary because of different interpretation of date in LN and Windows). If the date is older then today and HW Category is notebook then the type and current user are displayed. At the end of script is shown how many notebooks are out of warranty.

Useful links

2 comments:

Aaron Ooi said...

Hi David, thank you for your sharing that im able to connect into the Lotus Notes via Power Shell.

My goal is to search for a document and then delete that document. Believe me that i have tried my very best before forced to comments into your blog lol.

Below are my $DomView View that i wanted to search for that documents:
PS C:\Windows\system32> $DomView


Aliases : {serversbyname}
AllEntries : System.__ComObject
AutoUpdate : True
BackgroundColor : 1
ColumnCount : 5
ColumnNames : {System Name, OS, Model, Type...}
Columns : {System.__ComObject, System.__ComObject, System.__ComObject, System.__ComObject...}
Created : 20/8/2002 5:02:38 AM
HeaderLines : 1
HttpURL :
IsCalendar : False
IsCategorized : False
IsConflict : True
IsDefaultView : False
IsFolder : False
IsHierarchical : True
IsModified : True
IsPrivate : False
LastModified : 6/9/2019 9:54:07 AM
Name : System Summary\by Name
NotesURL : notes://MY-PEN-DomDev1@PEN@D@NIC/__4825846D000A7191.nsf/57078A608984176086256C1A00739933?OpenView
Parent : System.__ComObject
ProtectReaders : False
Readers : {}
RowLines : 1
SPACING : 0
TopLevelEntryCount : 4888
UniversalID : 57078A608984176086256C1A00739933
IsProhibitDesignRefresh : False
SelectionFormula : SELECT @Contains(Form;"System")
EntryCount : 4889
LockHolders : {}
ViewInheritedName :

How do i continue to use this view to search for example this document where the ColumnValue has "IN-BAN-FW-edge02-bld2"?

Your update is very much appreciated :)

Aaron Ooi said...

If possible is to searched that documents then execute delete