Migrate data from Service Manager Configuration Items to Cireson Hardware Assets

Michael_McAllisterMichael_McAllister Customer IT Monkey ✭
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.
Sign In or Register to comment.