LotusScript agent to parse log.nsf on an SMTP server

This LotusScript agent reads log.nsf and creates a document for every unique IP address it finds that’s relaying off of the server.

Don’t put this in your log.nsf — I created a new copy of mine and parsed a month’s worth of data. Putting this in your production log.nsf is a no-no! :p

I’ll break this down and comment further as time permits.


   Agent scrapeMailHosts
   Created Feb 6, 2013 by Thom Rosario
   Description: parses log.nsf and creates one document for each unique relaying host

Option Public
Option Declare
   Dim db As NotesDatabase
   Dim mailLogView, hostsView As NotesView
   Dim ip, hostname, ipArray() As String

Sub Initialize
   Dim session As New NotesSession
   Dim mailLogDoc As NotesDocument

   Set db = session.CurrentDatabase  
   Set mailLogView = db.GetView ("MailRoutingEvents")
   Set hostsView = db.GetView ("SMTPHosts")
   Set mailLogDoc = mailLogView.GetFirstDocument
   Call getExistingIPs (mailLogDoc)
   Call Main (mailLogDoc)
End Sub

   Sub setIPAndHostname
   Description: sets the global variables ip and hostname
      Logs are formatted thusly:
      11/16/2012 10:52:19 PM  SMTP Server: connected
      or if there's a hostname, then they're formatted like:
      11/16/2012 10:52:28 PM  SMTP Server: hostname.foo.com ( connected  

Sub setIPAndHostname (logEntry As Variant, doc As NotesDocument)
   Dim server, hasParen, ipTemp, hostTemp As Variant
   Dim serverFormula, parenFormula, ipFormula, hostFormula As String

   serverFormula = |@Left (@Right ("| + logEntry + |"; "SMTP Server: "); " connected")|
   server = Evaluate (serverFormula, doc)
   'Now test for (
   parenFormula = |@Contains ("|+ server(0) +|"; "(")|
   hasParen = Evaluate (parenFormula, doc)
   If hasParen(0) Then
      'Has paren, so set ip AND hostname
      'IP address is stored inside the parens
      ipFormula = |@Left(@Right ("|+server(0)+|"; "("); ")")|
      ipTemp = Evaluate (ipFormula, doc)
      ip = ipTemp(0)
      'hostname = @Left of the first space
      hostFormula = |@Left ("|+server(0)+|"; " ")|
      hostTemp = Evaluate (hostFormula, doc)
      hostname = hostTemp(0)
      'There was no paren, so the only value we got was the IP address
      ip = server(0)
      hostname = ""
   End if
End Sub

   Function isPopularRelayServer
   Description: determines whether or not the passed IP address 
   is one of the popular relay servers.  This is really quick and dirty,
   but it works faster than searching the view ever will πŸ™‚

Function isPopularRelayServer (ip As variant) As boolean
   If ip = "" Or _
      ip = "" Or _
      ip = "" Or _
      ip = "" Or _ 
      ip = "" Then
   isPopularRelayServer = True
     isPopularRelayServer = False
   End If
End Function

   Sub addNewIP
   Description: add the newly found IP address to the global 
   array of known IP addresses

Sub addNewIP (ip As Variant)
  Dim newUbound As Integer
  newUbound = UBound (ipArray) + 1
  ReDim Preserve ipArray (newUbound)
  ipArray (newUbound) = ip
End Sub

   Sub getExistingIPs
   Description: pre-populates the view of IPs already scraped

Sub getExistingIPs (doc As NotesDocument)
   Dim ipList As Variant
   Dim dbCol As String
   Dim count As Integer
   count = 0
   dbCol = |@DbColumn (notes:nocache; "":""; "SMTPHosts"; 1)|
   ipList = Evaluate (dbCol, doc)
   ForAll server In ipList
      ReDim Preserve ipArray(count)
      ipArray(count) = server
      count = count + 1
   End ForAll
End Sub

   Sub markLogDocProcessed
   Description: mark the current log document as processed
   so that it disappears out of the view

Sub markLogDocProcessed (doc As NotesDocument)
   'Set the person who made the edits SDTech
   'Save the document
   doc.SDTech = "Thom Rosario"
   Call doc.Save (True, True)
End Sub

   Sub CreateHostDoc
   Description: creates a new document given the IP and hostname
Sub createHostDoc (ip As variant, hostname As Variant)
   Dim hostDoc As NotesDocument
   Set hostDoc = db.CreateDocument
   hostDoc.Form = "SMTP Host"
   hostDoc.IPAddress = ip
   hostDoc.Host = hostname
   Call hostDoc.Save (True, True)
End Sub

   Function isRelayLogEntry
   Description: this function takes a line of text and determines
   whether or not it's a log entry showing that a server is relaying 
   off of this server

Function isRelayLogEntry (logEntry As Variant, doc As NotesDocument) As Boolean
   'If the line has "SMTP Server:" and "connected", then we're interested 
   Dim hasServer, hasConnected As Variant
   Dim serverString, connectString, tempString As String

  'Get rid of single quotes in the string; they mess up rest of this function
  tempString = findReplace (logEntry, "'", "")
   'See if it's an SMTP server line
   serverString = |@Contains('| + tempString + |'; 'SMTP Server:')|
   hasServer = Evaluate (serverString, doc)

   If hasServer(0) Then
      'Now see if it's a server connecting
      connectString = |@Contains('| + tempString + |'; ' connected')|
      hasConnected = Evaluate (connectString, doc)
      'if has server and has connected then true
      If hasConnected(0) Then
         isRelayLogEntry = true
         isRelayLogEntry = false
      End If
   End If
End Function

  Sub Main
  Description: main logic loop

Sub Main (mailLogDoc As NotesDocument)
   Dim docToProcess As NotesDocument
   Dim logBody As Variant  
   While Not(mailLogDoc Is Nothing)
      If Not mailLogDoc.HasItem ("SDTech") Then   
         If mailLogDoc.HasItem("EventList") Then
            logBody = mailLogDoc.EventList
            ForAll logLine In logBody
            If isRelayLogEntry (logLine, mailLogDoc) Then
               Call setIPAndHostname (logLine, mailLogDoc)
               If Not isPopularRelayServer (ip) Then
                  If Not isAlreadyRecorded (ip) Then
                     Call addNewIP (ip)
                     Call createHostDoc (ip, hostname)
                  End If 'Already Recorded                      
               End If 'Is Popular
            End If 'Is a relay log entry
         End ForAll 'Done parsing all lines
      End If 'Has the EventList field
      Set docToProcess = mailLogDoc
   End If 'No SDTech field
   Set mailLogDoc = mailLogView.GetNextDocument(mailLogDoc)
   Call markLogDocProcessed (docToProcess)
   Wend 'Done w/ current doc
End Sub

  Function findReplace
  Description: takes a source string, a character to find,
  and a character to replace it with

Function findReplace (ByVal wholeString As Variant, find As String, ireplace As String) As String
   Dim checkString, saveLeft, leftString, rightString As String
   Dim n As Integer
   checkString = wholeString
   saveLeft = ""
   While InStr (1, checkString, find) <> 0
      n = InStr (1, checkString, find)
      leftString = Left (checkString, n-1)
      rightString = Right (checkString, Len (checkString) - n - Len (find) + 1)
      saveLeft = saveLeft + leftString + ireplace
      checkString = rightString
   findReplace = saveLeft + checkString
End Function

   Function isAlreadyRecorded
   Description: compares the passed IP address to the SMTP Hosts
   view's IP addresses and returns true if found.

Function isAlreadyRecorded (ip As Variant) As boolean
   Dim found As Boolean
   Dim i As Integer
   found = false

   For i = 0 To UBound(ipArray)
      If ip = ipArray(i) Then
      found = true
   End If
   isAlreadyRecorded = found
End Function

Send an HTML table in email via Powershell for Exchange 2007

I didn’t like sending a raw text table, so I put my data into an HTML table and it works great, but adds a lot of lines to your script.

No pain, no gain πŸ™‚


# Get an email ready 
$smtpServer  = "smtp.foo.com"
$msg         = new-object Net.Mail.MailMessage
$smtp        = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From    = "admins@foo.com"
$msg.Subject = "Hello world."
$msg.Body    = @() # Use the @ symbol for multi-line data

# Start off by creating the table and the header row
# Use the += operator to append
$html += "<table cellpadding='3'>

# This is where you'd loop through several data rows
If ($percent -gt .75) {
   $html += "<tr>"
   $html += "<td>" + $row.Owner + "</td>"
   $html += "<td>" + $row.Size + "</td>"
   $html += "<td>" + $row.Quota + "</td>"
   $html += "<td>" + $row.Percent + "</td>"
   $html += "<td>" + $row.Sent + "</td>"
   $html += "<td>" + $row.Calendar + "</td>"
   $html += "<td>" + $row.Contacts + "</td>"
   $html += "<td>" + $row.Deleted + "</td>"
   $html += "<td>" + $row.Drafts + "</td>"
   $html += "<td>" + $row.Notes + "</td>"
   $html += "<td>" + $row.Outbox + "</td>"
   $html += "<td>" + $row.Tasks + "</td>"
   $html += "</tr>"
}  # Done looping through everything
$html += "</table><br /><br />" # Close the table tag
# Send the file via email
$msg.IsBodyHTML = $true # Crucial for sending html mail
$msg.Body += "Here are the VIPs over 75% of their quota.<br /><br />" + $html

Launching Powershell

Interestingly enough, most of the sites I stumbled upon assumed a level of knowledge I didn’t yet have when I started on this trek. This post covers one of the small obstacles I ran into: launching the *right* Powershell.

In my case, I was interested in running an Exchange script, so I needed to launch the Exchange-specific shell. Click the link to see the rest of the post.

Login into an Exchange server and launch the Powershell console.

Launch Powershell

Startup screen.

Powershell Startup Window

Change directories

Change Directories

Run a script. Note that filename completion works here, and even adds the “.\” as shown below. I rolled my eyes when I saw “.\”. The whole slash-vs.-backslash is back.

Launch a script

Create a report of Exchange 2007 User Quotas via Powershell

I created this script to keep me informed of the mail quotas for a small subset of my current customer’s VIPs.

I’ll break down the different pieces of this script and explain them on different pages. View the full script after the break.


This script reads an input file w/ one email address per line and:
-- Checks to see if a matching mailbox can be found
-- Gathers some data about the mailbox
   -- Mailbox size
   -- Mailbox quota
   -- Percent full
-- Sends an HTML table via email.

Thom Rosario

-- Added the ability to send email results as an HTML table

-- Added the results as an attachment

# Initialize
$input   = "c:\Scripts\VIPQuotas\vip.csv"
$output  = "c:\Scripts\VIPQuotas\output.txt"

# Get an email ready 
$smtpServer = "smtp.foo.com"
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From = "admins@foo.com"
$msg.Subject = "VIP Quotas Report"
$msg.Body = @()
$html += "<table cellpadding='3'>

# Create a table object to hold the data
$table = New-Object system.Data.DataTable β€œtable”

$ownerCol    = New-Object system.Data.DataColumn Owner,([string])
$sizeCol     = New-Object system.Data.DataColumn Size,([decimal])
$quotaCol    = New-Object system.Data.DataColumn Quota,([decimal])
$percentCol  = New-Object system.Data.DataColumn Percent,([int])
$sentCol     = New-Object system.Data.DataColumn Sent,([int])
$calendarCol = New-Object system.Data.DataColumn Calendar,([int])
$contactsCol = New-Object system.Data.DataColumn Contacts,([int])
$deletedCol  = New-Object system.Data.DataColumn Deleted,([int])
$draftsCol   = New-Object system.Data.DataColumn Drafts,([int])
$notesCol    = New-Object system.Data.DataColumn Notes,([int])
$outboxCol   = New-Object system.Data.DataColumn Outbox,([int])
$tasksCol    = New-Object system.Data.DataColumn Tasks,([int])


# Loop through the addresses in the file
Import-CSV $input | ForEach-Object {
   $mail = $_.email
   Try {
      $mb = Get-Mailbox $mail

      # Get mailbox stats
      $mbstats = Get-MailboxStatistics $mail
      $size = $mbstats.TotalItemSize.Value.ToMB()
      $mailDB = Get-MailboxDatabase -Identity $mb.Database
      If ($mb.UseDatabaseQuotaDefaults -eq $true) {
         $quota = $mailDB.ProhibitSendQuota.Value.ToMB()
      } else { # Not using default quota
         Try {
            $quota = $mb.ProhibitSendQuota.Value.ToMB()
         Catch {
            $quota = 0 # No quota set
      If ($quota -ne 0) {
         $percent = ($size/$quota)
      } else {
         $percent = 0 # Avoid dividing by zero
      # Add a row full of data to the table
      $row         = $table.NewRow()
      $row.Owner   = $mb.DisplayName
      $row.Size    = $size
      $row.Quota   = $quota
      $row.Percent = [Math]::Floor($percent*100)
      # If they're above 75% quota, let's see where the space is being used
      If ($percent -gt .75) {
         $html += "<tr>"
         $html += "<td>" + $row.Owner + "</td>"
         $html += "<td>" + $row.Size + "</td>"
         $html += "<td>" + $row.Quota + "</td>"
         $html += "<td>" + $row.Percent + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope SentItems
         $row.Sent = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Sent + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Calendar
         $row.Calendar = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Calendar + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Contacts
         $row.Contacts = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Contacts + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope DeletedItems
         $row.Deleted = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Deleted + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Drafts
         $row.Drafts = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Drafts + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Notes
         $row.Notes = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Notes + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Outbox
         $row.Outbox = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Outbox + "</td>"
         $folder = Get-MailboxFolderStatistics $mail -FolderScope Tasks
         $row.Tasks = $folder.FolderSize.ToMB()
         $html += "<td>" + $row.Tasks + "</td>"
         $html += "</tr>"
      }   # End of percent > 75 if-then
   }   # End of the Try statement
   Catch [System.Exception] {
      echo "Something went wrong with $mail."
}  # Done looping through the csv
$html += "</table><br /><br />"

# Display the table
$outTable = $table | Sort-Object Percent -descending | format-table -AutoSize

# To see the output, uncomment the next line. Not useful when running as a
# scheduled task, so it's disabled in production :p

# Write to disk.
$outTable | Out-File $output  
Copy-Item -Path $output $remote

# Send the file via email
$attachment = new-object Net.Mail.Attachment($output)
$msg.IsBodyHTML = $true
$msg.Body += "Here are the VIPs over 75% of their quota.<br /><br />" + $html

Microsoft Powershell

I recently needed to write some Exchange Powershell to get some information about a small group of customers. This book will be where I talk about everything I learned, and my thoughts on the process.

The complete script is already posted as the last page of the book. You can navigate the full book via the links under the “Book Navigation” section, or at the bottom of every page.