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

No comments: