SuPump when used on disabled accounts

We had an issue crop up in one of our favorite sites using the Sumatra Pump.

Calendar item insertion jobs were hanging and the error logs were showing things like this:

GetUserFromAD-ERROR: Failed while reading AD: (employeeId=F112ZHW); err: Object reference not set to an instance of an object.
GetUserFromAD-ERROR: Failed while reading AD: (employeeId=F112ZHY); err: Object reference not set to an instance of an object.
'ERROR: Failed while reading AD.(employeeId=F112ZHT)
ERROR: Failed while reading AD.(employeeId=33460A)

Simple to diagnose: The accounts causing the problem were DISABLED accounts.

To deal with it exclude the disabled accounts.
      Patient: "Doctor, it hurts when I do this."  
      Doctor: "Don't do that!"

WHAT (NOT) TO DO

An easy fix: add this criteria to exclude disabled accounts to the LDAP string in the _config.xml file:

(!userAccountControl:1.2.840.113556.1.4.803:=2)

Thus,  your LDAP4USER setting should look something like this  

LDAP://DC=YOUR_DC,DC=COMPANY,DC=COM;(&(mailNickName=*)(employeeID=*)(!userAccountControl:1.2.840.113556.1.4.803:=2))

What the heck do those strange numbers mean????

It’s a bitwise AND filter for the UAC.  

For more info on the UAC please see:

How to use the UserAccountControl flags to manipulate user account properties

Inserting SQL data into Microsoft Exchange

When we say SQL data being inserted into Microsoft Exchange or Office 365, it's through our soda straw view of the world: calendar items, contacts, and tasks.

You can of course write one-off code in Exchange Web Services to accomplish a specific task – but why reinvent the wheel?  Sumatra has been  inserting calendar, contact, and task items into Exchange from legacy systems for the last fourteen years.  We’ve created a tool-set that allows you to read data from your SQL databases and insert them into Exchange. 

Most of our users of this feature are using Oracle as the SQL of choice, but there's nothing specific to that vendor.

We call this the Sumatra Calendar Pump or just the "Pump" if you hear us referring to it in casual conversation.

Migrating iPlanet Calendar to Exchange via SuPump

Over the last five years of doing calendar migrations we've heard of pretty much every legacy calendar system out there. One that comes up now and again is iPlanet, once bundled with Netscape (remember them?).

iPlanet has a tool: csexport.

The file extension of the OUTPUT file (.ics or .xml) determines the format it will export to: ither iCalendar or XML

So since suPump.exe takes an XML file as input for incorporation into Exchange, this gives a quick way of server-side migration.

You use it as follows:

csexport -c jsmithcal calendar jsmith.xml

then

suPump.exe /k /AT:UserID /u:jsmith /DT:A /file:"jsmith.xml"

Will add appointment data for a UserID where the XML data is in file “calendar.xml”. This command does not clear the calendar calendar before inserting data.

Sumatra Pump Contact Schema

SuPump.exe uses the following fields for inserting contacts. Size limits are up to available limits in Outlook.


ContactID: alpha numeric identifier (can be left blank)
ContactNum: Contact Number
OwnerUserID: UserID or mailboxAlias
FirstName:
LastName:
Title:
Dept:
Room:
Company:
Address:
City:
State:
Zip: XXXXX-XXXX
Country:
Email:
HomePhone: XXXXXXXXXX
WorkPhone: XXXXXXXXXX x XXXXX
Fax: XXXXXXXXXX
MobilePhone: XXXXXXXXXX
Pager: XXXXXXXXXX
OtherPhone1: XXXXXXXXXX
OtherPhone2: XXXXXXXXXX
OtherPhone3: XXXXXXXXXX
Custom1
Custom2
Custom3
Custom4
NoteInfo :additional information

Sample Syntax for Exchange Insertion with the Sumatra Pump

To read and insert a file contacts.xml into “Sumatra.com”. Tags the items with the default keyword
supump.exe /dn:Sumatra.com /file:contacts.xml /k

Insert one appointment for USERID=37238L. Remove data prior to insertion. Read source data from http (oracle server).
supump.exe /UNDO /k /AT:userID /u:37238L /DT:A /http:"http://oracle-dev.site.edu/dart/harpo/" /PKG:"EXCHANGESYNC_PKG.run_calendar?userid="

Appends one appointment for ExchangeAlias T_L to existing data (missing /undo)/ Read data from http.
supump.exe /k /AT:Exchange /u:T_L /DT:A /http:"http://oracle-dev.site.edu/dart/harpo/" /PKG:"EXCHANGESYNC_PKG.run_calendar?userid="

Remove user’s 37238L’s Appointment data that is tagged with the default keyword
supump.exe /dn:Sumatra.com /undo /k /u:37238L

Insert appointments for USERIDs in a text file. Remove appointment data prior to insertion. Read data from http.
suPump.exe /UNDO /k /AT:UserID /in:"F:\Sumatra\Clients\UserIds.txt" /DT:A /http:"http://oracle-dev.site.edu/dart/harpo/" /PKG:"EXCHANGESYNC_PKG.run_calendar?userid="

Insert appointments for ALL exchange users with USERIDs in a text file. Read data from http. Remove appointment data prior to insertion
suPump.exe /UNDO /k /AT:Exchange /ALL /DT:A /http:"http://oracle-dev.site.edu/dart/harpo/" /PKG:"EXCHANGESYNC_PKG.run_calendar?userid="

Add appointment data for one UserID where XML data is in file “calendar.xml”. Clear calendar before inserting data.
suPump.exe /undo /k /AT:UserID /u:37238L /DT:A /file:"F:\Sumatra\Clients\calendar.xml"

Insert contact data for ALL User IDs. Clear contacts before insertion. Read data from http.
supump.exe /UNDO /k /AT:Exchange /ALL /DT:C /http:"http://oracle-dev.site.edu/dart/harpo/" /PKG:"EXCHANGESYNC_PKG.run_contact?userid="

Insert Task data for User IDs in a file. Clear task data before insertion. Read data from http.
supump.exe /UNDO /k /AT:UserID /in:"F:\Sumatra\Clients\UserIds.txt" /DT:T /http:"http://oracle-dev.site.edu/dart/harpo/" /PKG:"EXCHANGESYNC_PKG.run_task?userid="

Insert all data into the domain name “lab.sumatra.com” for aliases defined in the file IT_Test.txt. Obtain the data from the web, and tag it with keyword testrun4
Suxml /dn:lab.sumatra.com /k:testrun /u:37238L /k:testrun4
/HTTP:"http://oracle-dev.site.edu/dart/harpo”
/pkg:"EXCHANGESYNC_PKG.run_calendar”

Syntax for SuPump.exe

The Sumatra Pump (which on occasion we pronounce "SumPump") uses the following syntax (which will be familiar to all of you using the Sumatra Utilities):

suPump /dn:domain /sn:servername /k:kwd /all /u:usr /in:infile /at:acct_type /dt:data_type
/out:outfile /undo /v:file /http:url /pkg:p
where:

/dn:domainname =Run on this "domain name"

/sn:serverName =The server name

User
/at:acct_type=Account type (acct_type=Specific or Exchange) default is Exchange alias.
/all=Retrieve ALL Aliases with SpecificIDs (employeeID)
/in:infile=user INput file, one Exchange Alias or SpecificID per record (default infile.txt)
/u:user =process this Alias or SpecificID User (supersedes /in:)
SpecificID in this case means anything you want to define, an account number or employee ID for example.

/dt:data_type=Data type = Activities, Tasks, Contacts (default is Activities)

/k:kwdstem = Assigns a category during an insert. E.g., /k=holiday2006; Also limits mtgs/appts/contacts/todos tagged with Keyword stem e.g. /k:ca finds ca,car,cat (/k default:mmconv102659080256Z).

/out:outfile=Output file (default outfile.txt)

/undo=remove items from calendar (tagged w/ keyword). Items to be removed is a function of the /dt

Data Location
/file:filestring=passes an XML file (path included) to be processed
/HTTP:URL=Obtain a file to be processed from HTTP address using this URL root. Note, the URL must be enclosed in quotes
/PKG:Pkgname=defines the package name to extract the data. The package will be appended to the URL root to retrieve the data. The package name must be enclosed in quotes.

Sumatra Pump: The Third Way to get Calendar Data into specific accounts

In Exchange there are two ways to get data into a user calendar:

1. Invite the user to a meeting which they accept
2. The user or their delegate types an entry themselves

This was too restrictive for a major graduate business school in the Northeast. There was no way for users to "subscribe" to calendar events relevant to their needs, such as course schedules and assignments, on-campus recruiting, and then have those events appear on their calendars.

They asked Sumatra to develop an Exchange server-based tool we refer to as the Sumatra Pump, suPump.exe.

A bit of background

This school built a web-based calendaring solution that offers its students, faculty, and staff a single source to access all academic calendars and schedules including the academic calendar, class schedules, club events, and one-on-one meetings such as the interview calendar. The web-based solution also provided users with a list of contacts and tasks. Users can personalize the solution by setting preferences to select the calendar data they wish to receive. End users wanted the calendar data in Outlook, so the school built an Outlook add-in that allowed users to merge selected calendar data into their Outlook calendar.

Fine and good, but this was manual -- the user had to initiate the command sequence. The school’s IT department wanted a solution that could be scheduled each night to push the requested data into Outlook.

Sumatra automated the calendar data merge process by implementing a server-based “Push” product on Exchange. The goal of this Push product is to deliver calendar, task, and contact updates to Outlook end users on both a scheduled basis and an on-demand basis.
This solution had the following results:

• Consolidated scheduling data delivery at the Exchange server level;
• Reduced system maintenance and management costs;
• Minimized the negative productivity impact of requiring users to initiate the calendar merge sequence, or by re-keying calendar data into Outlook;
• Extended support to all Exchange clients on-site not served by the client-side VB macro then in use (including, for example, OWA and Entourage users);
  

The suPump takes as input an XML file. For Activities the file has the following structure:

ActivityID: alpha numeric identifier (can be left blank)
UserID: SchoolID or mailboxAlias
Name: Activity name
Location:Activity location
Banner:True/False - Is this an all day event
Private:True/False - Should this be marked as private
Label:What color label (0-11) corresponds to Outlook labels
Duration:Duration in minutes
ActivityTime:Activity start date and time
LastTime:Activity end date and time
TimeZone:timezone (see below)
FreeBusy:"Free", "Busy", "Tent","OOF"
RecurType: Not implemented for this client
Ongoing:Not implemented for this client
DaysMask:Not implemented for this client
WeeksMask:Not implemented for this client
DaysFromEndOfMonth:Not implemented for this client
Period:Not implemented for this client
Exceptions: Not implemented for this client
Reminder: number of minutes
NoteInfo: additional information

Contacts and Tasks have similar formats.

We've been thinking of using this for simple calendar / contact / task migrations for formats we do not currently handle (iPlanet calendar migration is one of the cases occasionally requested).

This is also something we've looked at as part of a co-existence strategy for alternate calendar servers and Exchange.