Monday 9 May 2011

Importing data into Service Manager 2010 using CSV imports

A question was asked by Peter the other day on the TechNet Gallery regarding importing data into the Asset Management Solution that I uploaded.
http://gallery.technet.microsoft.com/Asset-Management-d06aff1f/view/Discussions

It’s very easy to do, and hopefully this post will explain how for those that don’t know.

Imports can be performed using the “Import from CSV file…” task found in the console under Administration>Connectors.


To be able to import you need two files.

a)      An XML file that defines the columns in the CSV and the related class & properties

b)      A CSV file containing the data you want to import

For this example I’ll use the Network Infrastructure class as that was the one Peter asked about, but same procedure applies to any Service Manager class.

Let’s start with the CSV.

The Network Infrastructure class is built from inheriting the Asset Management Base class with the Network Infrastructure properties added on top:

Asset Management Base Class
Property ID
Type
Key
List Used
EquipmentType
enum
True
AssetManagementEquipmentTypes.Enum
Manufacturer
enum
False
AssetManagementManufacturerAndModel.Enum
Model
enum
False
AssetManagementManufacturerAndModel.Enum
SerialNumber
String
False

EquipmentCost
Decimal
False

OrderNumber
String
False

Supplier
enum
False
AssetManagementSuppliers.Enum
SupplierReferenceNumber
String
False

GoodsReceivedDate
datetime
False

WarrantyExpirationDate
datetime
False

InstallDeliveryDate
datetime
False

Department
enum
False
AssetManagementOrganisationalList.Enum
Directorate
enum
False
AssetManagementOrganisationalList.Enum
Section
String
False

Location
enum
False
AssetManagementLocations.Enum
ServiceDeskRef
string
False


 Network Infrastructure Class
Property ID
Type
Key
List Used
AssetTag
Int
True

MaintenanceContractReferenceNumber
String
False

MaintenanceExpiryDate
Datetime
False



In this example I’m going to import some data for:

Equipment Type (This is a Key so MUST have data in the CSV)
Asset Tag (This is a Key so MUST have data in the CSV)
Manufacturer
Model
Serial Number
Supplier
Maintenance Contract Reference Number
Maintenance Expiry Date

You could build the CSV file using the friendly display names of the equipment, but I prefer to use the enum values as this cuts down on the amount of false errors shown during import.

To get the enums, either open the xml of the management pack and search for them, or use the brilliant little tool ListGuids.exe by Rob Ford.http://gallery.technet.microsoft.com/Tool-to-list-Guids-of-e33ca89b

N.B. If you’re adding your own equipment types, manufactures, models etc, these must all be added, BEFORE you attempt to import.

For Equipment Type I’ll be using AssetManagementEquipmentTypes.NetworkInfrastructure.Switch.Enum as the six items are all switches.
For Manufacturer I’ll be using AssetManagementManufacturerAndModel.Cisco.Enum as the six items are made by Cisco.

For Model I’ll be using AssetManagementManufacturerAndModel.Cisco.Switch.WSC355048PSS.Enum and AssetManagementManufacturerAndModel.Cisco.Switch.WSC356024PSS.Enum as the six items are a mixture.

Serial Numbers are unique.

Supplier is using AssetManagementSuppliers.ComputaCentre.Enum

Maintenance Contract Reference Number and Maintenance Expiry Date are unique/random for the devices.

So, for this example the CSV would look something like this:



Next the XML file needs creating to define the columns used and the classes/properties being referenced.
<?xml version="1.0" encoding="utf-8"?>
<CSVImportFormat>
      <Class Type="NetworkInfrastructureAsset">
                <Property ID="EquipmentType" />
                <Property ID="AssetTag"/>
                <Property ID="Manufacturer" />
                <Property ID="Model" />
                <Property ID="SerialNumber"/>
                <Property ID="Supplier" />
                <Property ID="MaintenanceContractReferenceNumber" />
                <Property ID="MaintenanceExpiryDate" />
      </Class>
</CSVImportFormat>


The Class Type defines the class within Service Manager to target the import at, in this example the “Network Infrastructure Asset” class.
The Property ID’s then define what each of the columns in the CSV represent.

And it’s as simple as that!

Finally to import the data, click the “Import from CSV file…” task from connectors, and use the browse buttons to select the XML file and Data (CSV) file.

Hopefully, it should import them all just fine.


As I’ve already said, this can be used for any class/property, both OOB and extended.

You can also import things like asset custodians and other relationships by modifying the XML to include SEED properties, but I’ll save that for another post.
I've uploaded the CSV and XML to my skydrive for you to download and play with.
A list of classes and properties for the Asset Management solution can be found in the documentation included in the Asset Management zip file.

2 comments:

Martyn Burford said...

Hi,

Making real progress with your Asset Management MP. Really good product and is proving very useful.

We're stuck importing mobile phones. Using this guide and the documents provided, we can add the phone and the number, but do not know how to link them together. We thought it was the IMEI number (FK1), but adding this to the CSV is not playing ball.

Do you have any thoughts on this?

Martyn Burford said...

Hi,

Making real progress with your Asset Management MP. Really good product and is proving very useful.

We're stuck importing mobile phones. Using this guide and the documents provided, we can add the phone and the number, but do not know how to link them together. We thought it was the IMEI number (FK1), but adding this to the CSV is not playing ball.

Do you have any thoughts on this?