Importing Computers via CSV

Importing Computers via CSV

  • Comments 1
  • Likes

SCSM is capable of capturing detailed information about computers in the CMDB.  The model for a computer is actually pretty complicated involving many objects related together.  The physical computer object for example is separated from the Windows computer object because each physical computer can potentially run many Windows computers due to multi-boot configuration.  Each Windows computer can have an unknown number of logical drives, physical drives, network adapters, and processors.  There are relationships of computer to user such as who the custodian of the computer is and who the primary user is.

Getting computer data into SCSM’s CMDB is a slam dunk if you already have SCCM and/or SCOM managing your computers.  You can quickly set up a connector by going through a wizard.  Minutes later you will have a richly populated CMDB full of data about your computers.  Not everybody has SCCM or SCOM to discover their computers though.  Not all types of computers may be manageable by SCCM or SCOM.  You may know about a computer prior to it being discoverable such as while it is in transit after an order, being installed, or at other points in the asset lifecycle.

Importing computers through CSV can be pretty straightforward or very detailed depending on how much data you want to insert.  I’ve created a series of mapping files, data file templates, and test data files to make this much easier for you.

The main mapping/data template file is the ComputerFormatFile.xml and ComputerDataFile.xlsx.  This will allow you to import Windows computer objects, the Operating System object they host, the related Physical Computer, and the Custodian and Primary Users.

In each data template file there is a header to tell you what data to put where.

image

  • The first row (in this case orange) tells you which Class you are dealing with.  You can’t see it in this case because it is spanning multiple columns, but it is the Microsoft.Windows.Computer class.
  • The second row is the property name for the column.
  • The third row indicates whether or not the property is key/required.  FALSE means it is not required.
  • The fourth row indicates the data type of the property.
  • The fifth row indicates the max length (if appropriate).

A few important notes:

  1. For enumeration data type properties you can enter the value in any of these formats: GUID, Element ID, or Display String.  If you choose Display String as I did above with ‘Deployed’ you will get a warning in the console when importing the data but we will do our best to find the right one.  The problem comes in when you have multiple enumerations with the same Display String.  If they are all different then it should be fine to use the Display String.
  2. Make sure that after you are done filling in your data template file that you save the file as a .csv file and remove the header.
  3. Remember that a key=TRUE field must have a value and the value must be unique not only in the data set that you are going to import but also in the database (unless you want to update data instead of insert).  If an object with the same key property value already exists in the database the database object will be updated with the data from your CSV data file.
  4. You can’t import the data if you still have the data file open in Excel or some other editor.  Make sure you close those editors first before you import.

The other four mapping/data template files in the package are for:

  • Processor
  • Logical Disk
  • Physical Disk
  • Processor

You’ll need to import the computers first using the ComputerFormatFile.xml and ComputerDataFile.xlsx.  Then you can import their components.

You’ll map the data together by using the Principal Name (Fully Qualified Domain Name or FQDN).  For example in the sample data file for Physical Disk you can see the computer’s FQDN as the first column in the data file:

image

If a given computer has more than one component you need to create one line per component and just repeat the computer FQDN in the first column.  For example, above you can see that computer1 has two physical drives and computer2 only has one.

Keep in mind that you can import data in any of these ways:

  1. manually using the “wizard” in the Administration > Connectors view by clicking the ‘Import from CSV…’ task
  2. on a recurring schedule using the CSV Connector
  3. using the Import-SCSMInstance PowerShell cmdlet like this:

image

Hopefully that will make it easier for you to populate data in scenarios where you can’t get the data from SCOM or SCCM automatically.

The mapping/data files are attached to this blog post and I’ll include them in the upcoming “resource kit” too.

General information on CSV Import.

Attachment: ComputerCSVDataImport.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
  • Hello and thanks for the information, one thing i have not been able to find anywhere is how the heck do i remove the instances in all the classes in a easy way? I am missing a Remove-SCSMInstance -Datafile... anyone?