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

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

  • Comments 4
  • Likes

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hi Travis - I am trying this with just modifying the time zone for the user in the spreadsheet to see what I get:

    Creating new CSVImporter

    Data Filename: C:\Temp\ImportUsersToolkit\users1.csv

    Format Filename: C:\Temp\ImportUsersToolkit\Users.xml

    Validating against XSD schema...

    Validation completed.

    Matched property User Name of class System.Domain.User in format file with class property display name: User Name. Display names may not be unique. Consider using actual property name UserName.

    Warning: possible error in row on line 1 of CSV file C:\Temp\ImportUsersToolkit\users1.csv. The following message was logged:

    --> Unable to parse property (type name: System.UserPreference.Localization, property type: int, property display name: Locale ID, property name: LocaleID, property value: Preference Locale ID)

    Unable to commit CSV row (data file: C:\Temp\ImportUsersToolkit\users1.csv, line number: 2). Commit failed with the following exception A discovery data item was rejected because the item is already bound to another Membership relationship..

    CSV Import Summary Statistics for file C:\Temp\ImportUsersToolkit\users1.csv

    Instances created in memory: 2

    Instances failed in memory: 0

    Instances committed to database: 1

    Instances rejected by database: 1

    Not sure how to proceed.

  • @Rob -

    Please send me your .xml and .csv flie

    twright @ youknowwhere.com

  • Good day, Travis.

    We are using SCSM 2012 SP1.

    I am trying to set LocaleID property to all users in my domain.

    The one user is used for tests.

    After import I see this attribute by powershell:

    PS C:\Windows\system32> Get-SCClass -Name 'System.UserPreference.Localization' | Get-SCClassInstance -filter "DisplayName like %Pref%"

    __EnterpriseManagementObject : pref.ru_a.pushkin.locale

    __RelationshipAliases        : {}

    DisplayName                  : pref.ru_a.pushkin.locale

    Id                           : pref.ru_a.pushkin.locale

    LocaleID                     : 1049

    Timezone                     :

    #Name                        : pref.ru_a.pushkin.locale

    #Path                        :

    #FullName                    : System.UserPreference.Localization:pref.ru_a.pushkin.locale

    #LastModified                : 22.10.2013 9:47:04

    #TimeAdded                   : 22.10.2013 9:47:04

    #LastModifiedBy              : 7431e155-3d9e-4724-895e-c03ba951a352

    EnterpriseManagementObject   : pref.ru_a.pushkin.locale

    RelationshipAliases          : {}

    PS C:\Windows\system32> $user.GetRelatedObjectsWhereSource($userpreferenceclass).EnterpriseManagementObject

    ClassName                                     DisplayName                                   LastModified          

    ---------                                     -----------                                   ------------          

    System.UserPreference.Localization            pref.ru_a.pushkin.locale                      22.10.2013 9:47:04    

    System.Notification.Endpoint                  RU_a.pushkin_SMTP                             11.10.2013 15:09:56  

    System.Notification.Endpoint                  RU_a.pushkin_SIP                              11.10.2013 15:09:56  

    But if I try to open user with set attribute  in console I get the error and console closes:

    first window:

    Application: Service Manager

    Application Version: 7.5.2905.0

    Severity: Error

    Message: An error was encountered while running the Service Manager Console. The console will now close.

    second window:

    Description:

     Stopped working

    Problem signature:

     Problem Event Name: CLR20r3

     Problem Signature 01: MIISDOYLP4JANZLHYCZWCU3FX1QY0RNK

     Problem Signature 02: 7.0.5000.0

     Problem Signature 03: 50acb084

     Problem Signature 04: UMWL5F1JEP3RGRKSUPHVGWX3YM44UGDU

     Problem Signature 05: 7.0.5000.0

     Problem Signature 06: 50acb074

     Problem Signature 07: 7

     Problem Signature 08: 9e

     Problem Signature 09: System.NullReferenceException

     OS Version: 6.2.9200.2.0.0.272.7

     Locale ID: 1049

    I would be grateful for any advice.

    BR,

    Vladislav.

  • Sorry for disturbing!

    Stefan Allansson helped me.

    I didn't import TimeZone and this is the reason.

    Thank You in any case!

    BR,

    Vladislav.