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:
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:
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
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:
So – our format file ends up looking like this:
A few notes on this:
A couple of things to remember:
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:
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...
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.
Please send me your .xml and .csv flie
twright @ youknowwhere.com