Importing Users via CSV and Setting Email Address, Locale, and Time Zone Information

The easiest way to get information about users into the Service Manager CMDB is to use the Active Directory connector.  Not everybody uses AD for storing user data though so in this blog post I’ll show you how you can quickly insert user information using the CSV import tool.

For background you might want to read this post:

http://blogs.technet.com/servicemanager/archive/2009/05/26/using-the-csv-import-feature.aspx

In particular though, I want to point out two special properties – Time Zone and Locale.  All date/time property values are stored in the database in UTC/GMT format.  Since users want to see these values in their notifications in their own time zone and formatted according to their locale we need to store this information about each user.  Unfortunately, there is no easy way to find this information about each user – it is not stored in Active Directory.  So – one easy way of setting these properties for each user is to use the CSV import tool.  Especially if you combine this with the blog post I wrote up previously about exporting data from views to Excel it becomes fairly easy to set these properties for all your users.  You could for example export the data to Excel, sort by Office location or by Country and use that information to quickly fill in the appropriate time zone and locale information for users by using “Fill Down” or even functions in Excel.  Then save the data file as a .csv and import!

The other thing I want to point out is that a user’s email address is not stored on the user object itself.  It is stored on a related object since we allow a user to have more than one email address.  In order for a user to receive email notifications from Service Manager, the user must have a related notification object with an email address specified.

Let’s take a look at the model and format file first.

First of all, most of the properties that we are dealing with are found on the System.User and System.DomainUser classes like this:

image

System.Domain.User is the first non-abstract (aka “concrete”) class in the model so we will be creating objects of that class when we do this CSV import.  The System.Domain.User class inherits all the properties from System.User, System.ConfigItem, and System.Entity.  System.Domain.User is one of the very few classes in the model that has a compound key – Domain and UserName.  Both properties must be provided to create an object of the System.Domain.User class and the combination of the two must always be unique.

System.User has two relationships that we care about in this situation – System.UserHasPreference and System.UserManagesUser

image

Classes which derive from System.UserPreference store user preferences – in this case the user’s preferred Time Zone and Locale and a list of notification addresses – email, SIP for instant messaging, etc.

We have a type projection provided out of the box that covers all of these properties and these relationships.  It looks like this:

image

So – our format file ends up looking like this:

image

A few notes on this:

  • All of the properties of System.Domain.User are string properties
  • The Domain and UserName properties of System.Domain.User and the Manager are required.
  • If the Domain and UserName property values of a user match an existing user object in the database the data in the data file will overwrite the data in the database (except for blank values).  If the Domain and UserName combination don’t already exist in the database a new user object will be created.
  • The System.Notification.Endpoint ID property must be unique in the whole system.  It’s never shown anywhere in the console so I recommend using a GUID or a naming scheme like ‘<domain>_<username>_<some number>’ to try to keep things unique.  The same is true of the System.UserPreference.Localization ID property.
  • The ChannelName property value should typically be SMTP (for email) or SIP (for instant messaging).  If it is not ‘SMTP’ it cannot be used by the notification system in Service Manager 2010.
  • The TargetAddress property is where you store the email or SIP address – for example twright@contoso.com
  • The Timezone property should be the standard time zone name as defined Windows.  I’ve provided a list of those in the .zip file linked to below.  You can also run the provided GetSystemTimeZone.exe to get the list of system time zones defined on your computer.  It outputs a file in C:\TimeZoneInfo.  You don’t need to worry about Daylight Savings Time.  The system will take care of that for you.
  • The LocaleID property value should be the value from the Decimal Value column of this table: http://msdn.microsoft.com/en-us/library/0h88fahh(VS.85).aspx

A couple of things to remember:

  • Before importing, don’t forget to remove the column header row  if you have one in Excel!  The CSV importer will treat it as a data row!
  • You don’t need to fill in values for every cell.  If there are some fields you don’t want to fill in just hide the column in Excel.  Don’t delete the column though unless you also make the corresponding change to the format file! Otherwise data will end up in the wrong property.
  • Whatever data is in the spreadsheet will overwrite whatever data is in the database so make sure that you only import the data that you actually need to insert/update!
  • Be careful with CSV import.  There is no undo button!  I recommend testing it out on one record first to make sure it is doing what you want first before doing large data inserts.

You can download a sample .csv data file, sample .xml formatting file, the standard time zone .txt file, and the GetSystemTimeZone.exe application from here:

http://cid-17faa48294add53f.skydrive.live.com/self.aspx/.Public/Tools/ImportUsersToolkit.zip