Migrate data from Service Manager Configuration Items to Cireson Hardware Assets

Michael_McAllister

If anyone is curious, I thought I’d share this in case it’s useful for any other customers migrating from using Service Manager Configuration Items to Cireson Hardware Assets.

 

Here’s the SQL code I used to get the Notes, Serial Number and Asset Status ENUM fields (though these may be different in your implementation). It's not really optomized, I just got it working and then moved on as this was a one time import for us.

 

SELECT [ServiceManager].[dbo].[MT_Computer].[Notes_5CFC0E2A_AB82_5830_D4BB_0596CBED1984] AS Notes

      ,[ServiceManager].[dbo].[MT_Microsoft$SystemCenter$ConfigurationManager$DeployedComputer].[SerialNumber_485D02FB_4C40_C079_C157_E228FCE33763] AS SerialNumber

         ,[ServiceManager].[dbo].[EnumType].EnumTypeName AS AssetEnum

  FROM [ServiceManager].[dbo].[MT_Microsoft$SystemCenter$ConfigurationManager$DeployedComputer]

Join Relationship ON [ServiceManager].[dbo].[MT_Microsoft$SystemCenter$ConfigurationManager$DeployedComputer].[BaseManagedEntityId] = Relationship.SourceEntityId

Join RelationshipType ON Relationship.RelationshipTypeId = RelationshipType.RelationshipTypeId

join [ServiceManager].[dbo].[MT_Computer] ON [ServiceManager].[dbo].[MT_Computer].BaseManagedEntityId = Relationship.TargetEntityId

join [ServiceManager].[dbo].[EnumType] ON [ServiceManager].[dbo].[EnumType].EnumTypeId = [ServiceManager].[dbo].[MT_Computer].[AssetStatus_B6E7674B_684A_040D_30B8_D1B42CCB3BC6]

WHERE [ServiceManager].[dbo].[MT_Computer].[ObjectStatus_4AE3E5FE_BC03_1336_0A45_80BF58DEE57B] = 'ACDCEDB7-100C-8C91-D664-4629A218BD94' and

RelationshipType.RelationshipTypeName = 'Microsoft.SystemCenter.ConfigurationManager.DeployedComputerRunsWindowsComputer' and

[ServiceManager].[dbo].[MT_Microsoft$SystemCenter$ConfigurationManager$DeployedComputer].[SerialNumber_485D02FB_4C40_C079_C157_E228FCE33763] is not null

 

Unfortunately the enums weren’t the same for the Cireson Hardware Asset objects, so I had to export it into a csv and replace all the items with the correct prefix for the enum. For example “System.ConfigItem.AssetStatusEnum.Retired” to “Cireson.AssetManagement.HardwareAssetStatusEnum.Retired”. I then used the Import Connect to bring this csv into the system. Additionally the Notes column needed to be mapped to the Description field on the Hardware Asset class instead of the notes field there. 

After that everything seemed to work great! I'm sure it would be fairly trivial to extend this to additional fields if needed. The big problem was finding all the individual tables holding the various pieces of info and the relationship tables and pulling it all together.