Asset Data Import Tool

From Hornbill
Jump to navigation Jump to search

About the Hornbill Database Asset Import Utility

The utility provides a simple, safe and secure way to create and update asset records on the Hornbill platform, by reading asset records from your asset discovery tool, or another database containing asset records. The tool is designed to run behind your corporate firewall, connect to your asset database server, query the required information, then transform and load the records into your Hornbill instance. The tool connects to your Hornbill instance in the cloud over HTTPS/SSL, so as long as you have standard internet access then you should be able to use the tool without the need to make any firewall configuration changes. The tool supports both the initial bulk import as well as incremental adds and updates. You can schedule the tool to run periodically to perform the import/update tasks as required.

Open Source

The Asset Import Utility is provided open source under the Hornbill Community Licence and can be found Here on GitHub

Installation Overview

Windows Installation

  • Download the ZIP archive relevant to your OS and architecture
  • Extract zip into a folder you would like the application to run from e.g. C:\Asset_Import\
  • Open conf_sccm_assetscomputer.json and add in the necessary configuration
  • Open a Command Line Prompt as Administrator
  • Change Directory to the folder containing the import files C:\Asset_Import\
  • Run the command:

For Windows Systems: goDBAssetImport.exe -dryrun=true -file=conf_sccm_assetscomputer.json

For Mac OSX and Linux Systems: ./goDBAssetImport -dryrun=true -file=conf_sccm_assetscomputer.json

Configuration Overview

A demonstration configuration file is provided within the package, which includes configuration for importing asset data from Microsoft SCCM 2007 or 2012 (conf_sccm_assetscomputer.json - see the Configuration Example section of this document). If a configuration file is not specified as a command line argument when executing the tool, then a default configuration file named conf.json, containing the correct JSON, must exist. The following configuration file contains the configuration elements required when importing Asset Types that belong to the AssetsComputer entity: 

 "APIKey": "",
 "InstanceId": "",
 "LogSizeBytes":1000000,
 "SQLConf": {
     "Driver": "",
     "Server": "",
     "Database": "",
     "Authentication": "",
     "UserName": "",
     "Password": "",
     "Port": 0,
     "Encrypt": false,
     "Query": ""
 },
 "AssetTypes": [{
         "AssetType": "Server",
         "Query": "",
         "AssetIdentifier": {
             "DBColumn": "SystemSerialNumber",
             "Entity": "AssetsComputer",
             "EntityColumn": "h_serial_number"
         }
     },
     {
         "AssetType": "Laptop",
         "Query": "",
         "AssetIdentifier": {
             "DBColumn": "MachineName",
             "Entity": "Asset",
             "EntityColumn": "h_name"
         }
     },
     {
         "AssetType": "Desktop",
         "Query": "",
         "AssetIdentifier": {
             "DBColumn": "MachineName",
             "Entity": "Asset",
             "EntityColumn": "h_name"
         }
     },
     {
         "AssetType": "Virtual Machine",
         "Query": "",
         "AssetIdentifier": {
             "DBColumn": "MachineName",
             "Entity": "Asset",
             "EntityColumn": "h_name"
         }
     }
 ],
 "AssetGenericFieldMapping":{
     "h_name":"",
     "h_site":"",
     "h_asset_tag":"",
     "h_acq_method":"",
     "h_actual_retired_date":"",
     "h_beneficiary":"",
     "h_building":"",
     "h_cost":"",
     "h_cost_center":"",
     "h_country":"",
     "h_created_date":"",
     "h_deprec_method":"",
     "h_deprec_start":"",
     "h_description":"",
     "h_disposal_price":"",
     "h_disposal_reason":"",
     "h_floor":"",
     "h_geo_location":"",
     "h_invoice_number":"",
     "h_location":"",
     "h_location_type":"",
     "h_maintenance_cost":"",
     "h_maintenance_ref":"",
     "h_notes":"",
     "h_operational_state":"",
     "h_order_date":"",
     "h_order_number":"",
     "h_owned_by":"",
     "h_owned_by_name":"",
     "h_product_id":"",
     "h_received_date":"",
     "h_residual_value":"",
     "h_room":"",
     "h_scheduled_retire_date":"",
     "h_supplier_id":"",
     "h_supported_by":"",
     "h_used_by":"",
     "h_used_by_name":"",
     "h_version":"",
     "h_warranty_expires":"",
     "h_warranty_start":""
 },
 "AssetTypeFieldMapping":{
     "h_name":"",
     "h_mac_address":"",
     "h_net_ip_address":"",
     "h_net_computer_name":"",
     "h_net_win_domain":"",
     "h_model":"",
     "h_manufacturer":"",
     "h_cpu_info":"",
     "h_description":"",
     "h_last_logged_on":"",
     "h_last_logged_on_user":"",
     "h_memory_info":"",
     "h_net_win_dom_role":"",
     "h_optical_drive":"",
     "h_os_description":"",
     "h_os_registered_to":"",
     "h_os_serial_number":"",
     "h_os_service_pack":"",
     "h_os_type":"",
     "h_os_version":"",
     "h_physical_disk_size":"",
     "h_serial_number":"",
     "h_cpu_clock_speed":"",
     "h_physical_cpus":"",
     "h_logical_cpus":"",
     "h_bios_name":"",
     "h_bios_manufacturer":"",
     "h_bios_serial_number":"",
     "h_bios_release_date":"",
     "h_bios_version":"",
     "h_max_memory_capacity":"",
     "h_number_memory_slots":"",
     "h_net_name":"",
     "h_subnet_mask":""
 }

InstanceConfig

  • "APIKey" - a Hornbill API key for a user account with the correct permissions to carry out all of the required API calls
  • "InstanceId" - Instance Id
  • "LogSizeBytes" - The maximum size that the generated Log Files should be, in bytes. Setting this value to 0 will cause the tool to create one log file only and not split the results between multiple logs.

SQLConfig

  • "Driver" the driver to use to connect to the database that holds the asset information:
    • mssql = Microsoft SQL Server (2005 or above)
    • mysql = MySQL Server
    • mysql320 = MySQL Server v3.2.0 to v4.0
    • swsql = Supportworks SQL (Core Services v3.x)
    • odbc = ODBC Data Source using SQL Server driver. When using ODBC as a data source, the `Database`, `UserName`, `Password` and `Query` parameters should be populated accordingly:
      • Database - this should be populated with the name of the ODBC connection on the PC that is running the tool
      • UserName - this should be the SQL authentication Username to connect to the Database
      • Password - this should be the password for the above username
      • Query - this should be the SQL query to retrieve the asset records
  • "Server" The address of the SQL server
  • "Authentication The type of authentication to use to connect to the SQL server. Can be either:
    • Windows - Windows Account authentication, uses the logged-in Windows account to authenticate, and not the UserName and Password fields (below)
    • SQL - uses SQL Server authentication, and requires the Username and Password parameters (below) to be populated
  • "UserName" The username for the SQL database - only used when Authentication is set to SQL: for Windows authentication this field can be left as an empty string
  • "Password" Password for above User Name - only used when Authentication is set to SQL: for Windows authentication this field can be left as an empty string
  • "Port" Integer value containing the port to use when communicating with the SQL server
  • "Encrypt" Boolean value to specify whether the connection between the script and the database should be encrypted. NOTE: There is a bug in SQL Server 2008 and below that causes the connection to fail if the connection is encrypted. Only set this to true if your SQL Server has been patched accordingly.
  • "Query" The basic SQL query to retrieve asset information from the data source. See AssetTypes below for further filtering

AssetTypes

  • An array of objects details the asset types to import:
    • AssetType - the Asset Type Name which needs to match a correct Asset Type Name in your Hornbill Instance
    • Query - additional SQL filter to be appended to the Query from SQLConf, to retrieve assets of that asset type.
    • AssetIdentifier - an object containing details to help in the identification of existing asset records in the Hornbill instance. If value in an imported records DBColumn matches the value in the EntityColumn of an asset in Hornbill (within the defined Entity), then the asset record will be updated rather than a new asset being created:
      • DBColumn - specifies the unique identifier column from the database query
      • Entity - the Hornbill entity where data is stored
      • EntityColumn - specifies the unique identifier column from the Hornbill entity

AssetGenericFieldMapping

  • Maps data in to the generic Asset record
  • Any value wrapped with [] will be populated with the corresponding response from the SQL Query
  • Providing a value of __clear__ will NULL that column for the record in the database when assets are being updated ONLY. This can either be hard-coded in the config, or sent as a string column within the SQL query resultset (SELECT '__clear__' AS clearColumn in the query and [clearColumn] in the mapping for example)
  • Any Other Value is treated as written examples below:
    • "h_name":"[MachineName]", - the value of MachineName is taken from the SQL output and populated within this field;
    • "h_description":"This is a description", - the value of "h_description" would be populated with "This is a description" for ALL imported assets;
    • "h_description":"[MachineName] ([SystemModel])]", - the value of "h_description" would be populated with the value of MachineName from the SQL output, followed by the SystemModel, surrounded by brackets;
    • "h_site":"[SiteName]", - When a string is passed to the h_site field, the script attempts to resolve the given site name against the Site entity, and populates this (and h_site_id) with the correct site information. If the site cannot be resolved, the site details are not populated for the Asset record being imported.
    • "h_owned_by":"[UserName]" - when a valid Hornbill User ID (for a Full or Basic User) is passed to this field, the user is verified on your Hornbill instance, and the tool will complete the h_owned_by and h_owned_by_name columns appropriately.
    • "h_used_by":"[UserName]" - when a valid Hornbill User ID (for a Full or Basic User) is passed to this field, the user is verified on your Hornbill instance, and the tool will complete the h_used_by and h_used_by_name columns appropriately.
    • "h_company_name":"[CompanyName]" - when a valid Hornbill Company group name is passed to this field, the company is verified against your Hornbill instance, and the tool will complete the h_company_id and h_company_name columns appropriately.

AssetTypeFieldMapping

  • Maps data in to the type-specific Asset record, so the same rules as AssetGenericFieldMapping
  • For the computer asset class:
    • "h_last_logged_on_user":"[UserName]" - when a valid Hornbill User ID (for a Full or Basic User) is passed to this field, the user is verified on your Hornbill instance, and the tool will complete the h_last_logged_on_user column with an appropriate URN value for the user.

Configuration Examples

SCCM 2007 & 2012

The following is an example of the SQLConf, AssetTypes and data mapping configuration that could be used to import computer-type assets from an SCCM 2007 or 2012 data source. NOTE: The configuration example is provided as-is, and may not be suitable to import your organisations SCCM asset data. We highly recommend that a DBA review the SQL clauses against your SCCM database prior to using this in a production environment.

In this example:

  • Asset records found against the Servers asset type will be verified using the SystemSerialNumber column from the DB query, and the h_serial_number column within the AssetsComputer entity in your Hornbill instance.
  • Asset records found against all other defined asset types will be verified using the MachineName column from the DB query, and the h_namecolumn within the Asset entity in your Hornbill instance.

 "SQLConf": {
     "Driver": "mssql",
     "Server": "",
     "Database": "",
     "Authentication": "Windows",
     "UserName": "",
     "Password": "",
     "Port": 1433,
     "Encrypt": false,
     "Query": "SELECT OARSys.ResourceID AS [AssetID], OARSys.User_Name0 AS [UserName], OARSys.Netbios_Name0 AS [MachineName], OARSys.Resource_Domain_OR_Workgr0 AS [NETDomain], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [OperatingSystemCaption], OARSys.Operating_System_Name_and0 AS [OperatingSystem], dbo.v_GS_OPERATING_SYSTEM.Version0 AS [OperatingSystemVersion], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [ServicePackVersion], dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [SystemManufacturer], dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [SystemModel], dbo.v_GS_PC_BIOS.SerialNumber0 AS [SystemSerialNumber], OAProc.MaxClockSpeed0 AS [ProcessorSpeedGHz], OAProc.Name0 AS [ProcessorName], dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [NumberofProcessors], dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [MemoryKB], dbo.v_GS_LOGICAL_DISK.Size0 AS [DiskSpaceMB], dbo.v_GS_LOGICAL_DISK.FreeSpace0 AS [FreeDiskSpaceMB], OAIP.IP_Addresses0 AS [IPAddress], OAMac.MAC_Addresses0 AS [MACAddress], dbo.v_GS_PC_BIOS.Description0 AS [BIOSDescription], dbo.v_GS_PC_BIOS.ReleaseDate0 AS [BIOSReleaseDate], dbo.v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [SMBIOSVersion], dbo.v_GS_SYSTEM.SystemRole0 AS [SystemType], OASysEncl.ChassisTypes0 AS [ChassisTypes], OASysEncl.TimeStamp AS [ChassisDate], OARSys.AD_Site_Name0 AS [SiteName] FROM dbo.v_R_System OUTER APPLY (SELECT TOP 1 * FROM dbo.v_R_System b WHERE b.Netbios_Name0 = dbo.v_R_System.Netbios_Name0 ORDER BY SMS_UUID_Change_Date0 DESC) OARSys OUTER APPLY (SELECT TOP 1 dbo.v_GS_SYSTEM_ENCLOSURE.* FROM dbo.v_GS_SYSTEM_ENCLOSURE WHERE dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID ORDER BY TimeStamp DESC) OASysEncl OUTER APPLY (SELECT TOP 1 IP_Addresses0, ROW_NUMBER() OVER (order by (SELECT 0)) AS rowNum FROM dbo.v_RA_System_IPAddresses WHERE dbo.v_RA_System_IPAddresses.ResourceID = dbo.v_R_System.ResourceID ORDER BY rowNum DESC) OAIP OUTER APPLY (SELECT TOP 1 MAC_Addresses0 FROM dbo.v_RA_System_MACAddresses WHERE dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_R_System.ResourceID ) OAMac OUTER APPLY (SELECT TOP 1 MaxClockSpeed0, Name0 FROM dbo.v_GS_PROCESSOR WHERE dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID ORDER BY TimeStamp DESC) OAProc LEFT JOIN dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_PC_BIOS ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_GS_LOGICAL_DISK ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID LEFT JOIN dbo.v_FullCollectionMembership ON (dbo.v_FullCollectionMembership.ResourceID = v_R_System.ResourceID) LEFT JOIN dbo.v_GS_SYSTEM ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_R_System.ResourceID WHERE dbo.v_GS_LOGICAL_DISK.DeviceID0 = 'C:' AND dbo.v_FullCollectionMembership.CollectionID = 'SMS00001' "
 },
 "AssetTypes": [{
     "AssetType": "Server",
     "Query": "AND OASysEncl.ChassisTypes0 IN (2, 17, 18, 19, 20, 21, 22, 23)",
     "AssetIdentifier": {
         "DBColumn": "SystemSerialNumber",
         "Entity": "AssetsComputer",
         "EntityColumn": "h_serial_number"
     }
 },
 {
       "AssetType": "Laptop",
       "Query": "AND OASysEncl.ChassisTypes0 IN (8, 9, 10, 14)",
       "AssetIdentifier": {
           "DBColumn": "MachineName",
           "Entity": "Asset",
           "EntityColumn": "h_name"
       }
   },
   {
       "AssetType": "Desktop",
       "Query": "AND OASysEncl.ChassisTypes0 IN (3, 4, 5, 6, 7, 12, 13, 15, 16)",
       "AssetIdentifier": {
           "DBColumn": "MachineName",
           "Entity": "Asset",
           "EntityColumn": "h_name"
       }
   },
   {
       "AssetType": "Virtual Machine",
       "Query": "AND OASysEncl.ChassisTypes0 = 1",
       "AssetIdentifier": {
           "DBColumn": "MachineName",
           "Entity": "Asset",
           "EntityColumn": "h_name"
       }
   }],
 "AssetGenericFieldMapping":{
     "h_name":"[MachineName]",
     "h_site":"[SiteName]",
     "h_asset_tag":"[MachineName]",
     "h_acq_method":"",
     "h_actual_retired_date":"",
     "h_beneficiary":"",
     "h_building":"",
     "h_cost":"",
     "h_cost_center":"",
     "h_country":"",
     "h_created_date":"",
     "h_deprec_method":"",
     "h_deprec_start":"",
     "h_description":"[MachineName] ([SystemModel])",
     "h_disposal_price":"",
     "h_disposal_reason":"",
     "h_floor":"",
     "h_geo_location":"",
     "h_invoice_number":"",
     "h_location":"",
     "h_location_type":"",
     "h_maintenance_cost":"",
     "h_maintenance_ref":"",
     "h_notes":"",
     "h_operational_state":"",
     "h_order_date":"",
     "h_order_number":"",
     "h_owned_by":"",
     "h_owned_by_name":"",
     "h_product_id":"",
     "h_received_date":"",
     "h_residual_value":"",
     "h_room":"",
     "h_scheduled_retire_date":"",
     "h_supplier_id":"",
     "h_supported_by":"",
     "h_used_by":"",
     "h_used_by_name":"",
     "h_version":"",
     "h_warranty_expires":"",
     "h_warranty_start":""
 },
 "AssetTypeFieldMapping":{
     "h_name":"[MachineName]",
     "h_mac_address":"[MACAddress]",
     "h_net_ip_address":"[IPAddress]",
     "h_net_computer_name":"[MachineName]",
     "h_net_win_domain":"[NETDomain]",
     "h_model":"[SystemModel]",
     "h_manufacturer":"[SystemManufacturer]",
     "h_cpu_info":"[ProcessorName]",
     "h_description":"[SystemModel]",
     "h_last_logged_on":"",
     "h_last_logged_on_user":"",
     "h_memory_info":"[MemoryKB]",
     "h_net_win_dom_role":"",
     "h_optical_drive":"",
     "h_os_description":"[OperatingSystem]",
     "h_os_registered_to":"",
     "h_os_serial_number":"",
     "h_os_service_pack":"[ServicePackVersion]",
     "h_os_type":"",
     "h_os_version":"[OperatingSystemVersion]",
     "h_physical_disk_size":"[DiskSpaceMB]",
     "h_serial_number":"[SystemSerialNumber]",
     "h_cpu_clock_speed":"[ProcessorSpeedGHz]",
     "h_physical_cpus":"[NumberofProcessors]",
     "h_logical_cpus":"",
     "h_bios_name":"[BIOSDescription]",
     "h_bios_manufacturer":"",
     "h_bios_serial_number":"",
     "h_bios_release_date":"[BIOSReleaseDate]",
     "h_bios_version":"[SMBIOSVersion]",
     "h_max_memory_capacity":"",
     "h_number_memory_slots":"",
     "h_net_name":"",
     "h_subnet_mask":""
 }

Lansweeper 7.1

The following is an example of the SQLConf, AssetTypes and data mapping configuration that could be used to import computer-type assets from a Lansweeper 7.1 data source. NOTE: The configuration example is provided as-is, and may not be suitable to import your organisations LanSweeper asset data. We highly recommend that a DBA review the SQL clauses against your LanSweeper database prior to using this in a production environment.

In this example:

  • Asset records found against all defined asset types will be verified using the AssetName column from the DB query, and the h_name column within the Asset entity in your Hornbill instance.

   "SQLConf": {
       "Driver": "mssql",
       "Server": "",
       "Database": "lansweeperdb",
       "Authentication": "SQL",
       "UserName": "",
       "Password": "",
       "Port": 1433,
       "Encrypt": false,
       "Query": "SELECT  at.AssetType AS AssetTypeID,  at.AssetTypename AS AssetTypeName,  a.AssetID,  a.AssetUnique,  a.Domain, a.Username AS ADUserID,  a.FQDN,  a.IPAddress,  a.SiteID,  CASE WHEN at.AssetTypename = 'Windows' THEN os.Caption WHEN at.AssetTypename = 'Apple Mac' THEN mos.SystemVersion END AS OperatingSystem,  a.SP,  convert(varchar, a.Firstseen, 20) as FirstSeen,  a.Description,  a.AssetName,  a.Mac,  a.Uptime,  a.Memory,  a.NrProcessors,  a.Processor,  convert(varchar, a.LastChanged, 20) as LastChanged,  os.Caption,  os.ProductType,  convert(varchar, ac.PurchaseDate, 20) as PurchaseDate,  convert(varchar, ac.Warrantydate, 20) as Warrantydate,  ac.Manufacturer,  ac.Model,  ac.Serialnumber,  u.Displayname AS ADUserName  FROM dbo.tblAssets AS a  LEFT JOIN dbo.tsysAssetTypes at ON a.Assettype = at.AssetType  LEFT JOIN dbo.tblOperatingsystem os ON a.AssetID = os.AssetID  LEFT JOIN dbo.tblMacOSInfo mos ON a.AssetID = mos.AssetID LEFT JOIN dbo.tblAssetCustom ac ON a.AssetID = ac.AssetID  LEFT JOIN lansweeperdb.dbo.tblADusers u ON a.Username = u.Username"
   },
   "AssetTypes": [{
           "AssetType": "Laptop",
           "Query": "WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1 AND ac.Model = 'Latitude E6320'",
           "AssetIdentifier": {
               "DBColumn": "AssetName",
               "Entity": "Asset",
               "EntityColumn": "h_name"
           }
       },
       {
           "AssetType": "Desktop",
           "Query": "WHERE (at.AssetTypename = 'Windows' AND os.ProductType = 1 AND ac.Model = 'Precision WorkStation T5500') OR at.AssetTypename = 'Apple Mac'",
           "AssetIdentifier": {
               "DBColumn": "AssetName",
               "Entity": "Asset",
               "EntityColumn": "h_name"
           }
       },
       {
           "AssetType": "Server",
           "Query": "WHERE os.ProductType IN (2, 3)",
           "AssetIdentifier": {
               "DBColumn": "AssetName",
               "Entity": "Asset",
               "EntityColumn": "h_name"
           }
       }
   ],
   "AssetGenericFieldMapping": {
       "h_name": "[AssetName]",
       "h_site": "=",
       "h_asset_tag": "[AssetName]",
       "h_acq_method": "",
       "h_actual_retired_date": "",
       "h_beneficiary": "",
       "h_building": "",
       "h_company_name": "",
       "h_cost": "",
       "h_cost_center": "",
       "h_country": "",
       "h_created_date": "[Firstseen]",
       "h_deprec_method": "",
       "h_deprec_start": "",
       "h_description": "[Description]",
       "h_disposal_price": "",
       "h_disposal_reason": "",
       "h_floor": "",
       "h_geo_location": "",
       "h_invoice_number": "",
       "h_location": "",
       "h_location_type": "",
       "h_maintenance_cost": "",
       "h_maintenance_ref": "",
       "h_notes": "",
       "h_operational_state": "",
       "h_order_date": "",
       "h_order_number": "",
       "h_owned_by": "[ADUserID]",
       "h_owned_by_name": "[ADUserName]",
       "h_product_id": "",
       "h_received_date": "",
       "h_residual_value": "",
       "h_room": "",
       "h_scheduled_retire_date": "",
       "h_supplier_id": "",
       "h_supported_by": "",
       "h_used_by": "[ADUserID]",
       "h_used_by_name": "[ADUserName]",
       "h_version": "",
       "h_warranty_expires": "[Warrantydate]",
       "h_warranty_start": "[PurchaseDate]"
   },
   "AssetTypeFieldMapping": {
       "h_name": "[AssetName]",
       "h_mac_address": "[Mac]",
       "h_net_ip_address": "[IPAddress]",
       "h_net_computer_name": "[FQDN]",
       "h_net_win_domain": "[Domain]",
       "h_model": "[Model]",
       "h_manufacturer": "[Manufacturer]",
       "h_cpu_info": "[Processor]",
       "h_description": "[Description]",
       "h_last_logged_on": "",
       "h_last_logged_on_user": "",
       "h_memory_info": "[Memory]",
       "h_net_win_dom_role": "",
       "h_optical_drive": "",
       "h_os_description": "[OperatingSystem]",
       "h_os_registered_to": "",
       "h_os_serial_number": "",
       "h_os_service_pack": "[ServicePackVersion]",
       "h_os_type": "",
       "h_os_version": "[OSCode]",
       "h_physical_disk_size": "",
       "h_serial_number": "[Serialnumber]",
       "h_cpu_clock_speed": "",
       "h_physical_cpus": "[NrProcessors]",
       "h_logical_cpus": "",
       "h_bios_name": "",
       "h_bios_manufacturer": "",
       "h_bios_serial_number": "",
       "h_bios_release_date": "",
       "h_bios_version": "",
       "h_max_memory_capacity": "",
       "h_number_memory_slots": "",
       "h_net_name": "[FQDN]",
       "h_subnet_mask": ""
   }

Command Line Parameters

  • file - Defaults to `conf.json` - Name of the Configuration file to load
  • dryrun - Defaults to `false` - Set to True and the XMLMC for the creation and update of assets will not be called, and instead the generated XML for each asset will be dumped to the log file. This is to aid in debugging the initial connection information.
  • concurrent - defaults to 1, with a maximum of 10. Allows you to change the number of concurrent threads used to process the import. This can improve performance on slow imports, but using too many threads can have an effect on the performance of your Hornbill instance.
  • debug - defaults to `false` = Set to true to enable debug mode, which will output debugging information to the log

Testing Overview

There is no substitute for hands-on experience when becoming familiar with the Hornbill import utilities.
The Database Asset import utility accepts and understands a number of "Command Line Parameters" that can be used when running the utility from the command line. The most important one for testing is the -dryrun=true command. When this is specified, no information will be written to Hornbill and it allows you to confirm that the conf file is correctly structured and that a connection to your database can be established. A dryrun will also output a log file which provides you with an opportunity to review and understand any error messages that may occur.
Below are some high level steps to help you build confidence in your configuration:

  1. In the conf.json file, specify a query to target a single asset record. (Its good practice to initially test on a single, or small set of, asset records as this allows the dryruns to complete quicker and there is less log content to sift through).
  2. Perform a dryrun (by executing the utility along with the -dryrun=true command line parameter).
  3. Review cmd output and log file for errors
  4. Check against "Common Error Messages" listed on the wiki and take action to rectify where necessary.
  5. Continue with dryrun tests until you are happy that all the errors are accounted for.
  6. Perform a live import with this single asset record still specified.
  7. Review the asset record in Hornbill and check all asset attributes are populated as expected i.e. Asset Name contains the name of the asset etc.
  8. Adjust conf file asset attribute mappings as necessary
  9. Loop through steps 6 - 8 as many times as is necessary until you are happy with the information being transported into the attributes of the Hornbill Asset record.
  10. Amend the query to target the asset records required for a full import.
  11. Perform a dryrun
  12. Review cmd output and log file for errors
  13. Check against "Common Error Messages" listed on the wiki and take action to rectify where necessary.
  14. Continue with dryrun tests until you are happy that all the errors are accounted for.


Example use of the dryrun command line parameter specified after the utility executable: goDBAssetImport_x64.exe -dryrun=true –file=conf_sccm_assetscomputer.json

Command Line Output

After each run of the utility, the command line will output a summary of the records that were processed.

Updated: xx - The number of assets that were updated successfully.
Updated Skipped: xx - The number of assets that were recognised as needing updating, but the update was not applied for some reason.
Created: xx - The number of new asset records that were created in Hornbill.
Created Skipped: xx - The number of assets that were recognised as needing to be created, but the creation of the record failed.

This output can also be found in the log files which should be examined to understand why records failed to be created or updated. In the case of a failed update, even if this is only due to a problem with one of the attributes, then the other attributes will also not be updated i.e. the entire asset record remains unchanged.

Logging Overview

All logging output is saved in the log directory, in the same directory as the executable. The file name contains the date and time the import was run Asset_Import_2015-11-06T14-26-13Z.log

Trouble Shooting

Common Error Messages

Below are some common errors that you may encounter in the log file and what they mean:

  • [ERROR] Error Decoding Configuration File:..... - this will be typically due to a missing quote (") or comma (,) somewhere in the configuration file. This is where an online JSON viewer/validator can come in handy rather than trawling the conf file looking for that proverbial needle in a haystack.
  • [ERROR] https:// ........invalid request :path "//xmlmc//apps/com.hornbill.servicemanager/?method=[methodName]" - If you identify errors stating an "invalid request path" for one or more API calls, this is typically due to a missing or incorrect instance name specified in the conf.json file. Check the instance id is correct. It also may be prudent to check you have added a valid API key too.
  • [ERROR] API Call failed when retrieving Asset Class:Post https: //eurapi.hornbill.com/[instanceName]/xmlmc//data/?method=entityBrowseRecords: dial tcp 78.129.xxx.xxx:443: connectex: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. - With this error it is always prudent to check the instance name and an API key exist in the conf file, check that the instance name is spelt correctly and uses lower case characters, and the API key is valid. However, this is typically encountered if you use a proxy for all of your internet traffic and you haven't set the "HTTP_PROXY environment" variable as described in the "HTTP Proxies" section on this page.
  • [ERROR] Database Query Error: driver: bad connection. - This can be associated with an incorrect driver, username, and/or password specified in the SQLConf section of the conf file. Check and confirm the required driver, username, and password of the database that you're trying to access.

Error Codes

  • 100 - Unable to create log File
  • 101 - Unable to create log folder
  • 102 - Unable to Load Configuration File

HTTP Proxies

If you use a proxy for all of your internet traffic, the HTTP_PROXY Environment variable needs to be set. The https_proxy environment variable holds the hostname or IP address of your proxy server. It is a standard environment variable and like any such variable, the specific steps you use to set it depends on your operating system.

For windows machines, it can be set from the command line using the following:
set HTTP_PROXY=HOST:PORT
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number.

Scheduling Overview

Windows

You can schedule goDBAssetImport_x64.exe to run with any optional command line argument from Windows Task Scheduler.

  • Ensure the user account running the task has rights to goDBAssetImport_x64.exe and the containing folder.
  • Make sure the Start In parameter contains the folder where goDBAssetImport_x64.exe resides in otherwise it will not be able to pick up the correct path.

Change Log

  • 1.9.0 (January 8th, 2020)
    • Change
      • Added support for clearing asset column values
    • Defect Fix
      • Fixed issue where asset update counts were not always accurate
  • 1.8.1 (October 4th, 2019)
    • Change
      • Improved handling of interface nil values
  • 1.8.0 (July 25th, 2019)
    • Change
      • Added debug mode to output additional debug data to the log
  • 1.7.3 (February 15th, 2019)
    • Change:
      • Extra gating to prevent duplicate records being created when there's a failure of the API call that checks if the asset already exists on the Hornbill instance
  • 1.7.2 (January 4th, 2018)
    • Changes:
      • Record data not correctly mapped when using certain ODBC drivers
      • Code tweaks for minor performance improvements
  • 1.7.1 (January 2nd, 2019)
    • Defect Fix:
      • Last Logged On User to URN conversion when updating existing assets
  • 1.7.0 (December 13th, 2018)
    • Feature:
      • Added support to use ODBC SQL Server driver as a data source
  • 1.6.0 (December 10th, 2018)
    • Features:
      • Added support for populating the company fields against an asset. The tool will perform a Company look-up if a company name (in the h_company__name mapping) has been provided, before populating the company name and ID fields against the new or updated asset
      • Additional logging
      • Removed need to provide zone CLI parameter
  • 1.5.0 (15th August, 2018)
    • Features:
      • Added support for searching other entity columns for existing asset records to prevent asset duplication
      • Removed mandatory status of username and password columns when authentication method is Windows
  • 1.4.2 (23rd April, 2018)
    • Feature:
      • Added account verification and URN building when value supplied to h_last_logged_on_user column
  • 1.4.1 (25th January, 2018)
    • Defect Fix:
      • Fixed issue with Used By not being populated with a valid URN
  • 1.4.0 (December 4th, 2017)
    • Features
      • Adds Asset URN to record once asset record has been created
      • Updates Asset URN during asset update
  • 1.3.2 (April 3rd, 2017)
    • Features
      • Added support for Windows authentication against MSSQL Server
      • Added example configuration files for all asset types
  • 1.3.1 (February 22nd, 2017)
    • Defect Fix:
      • Removed unnecessary double-quotes from configuration file
  • 1.3.0 (February 1st, 2017)
    • Features:
      • Refactored code in to separate Go files, for easier maintenance
      • Provided a more detailed log output when errors occur
      • The tool now supports a configuration defined maximum log file size, and will create multiple log files for an import where necessary
    • Defects fixed:
      • Updating Last User or Owner columns for existing assets replaced the user URN with a user ID
      • Updating a Primary column required a change to a Related entity columm
  • v1.2.1 - 25/10/2016
    • Removed specification of Asset Owner or Used By as Hornbill Contacts, to be consistent with the Service Manager application
  • v1.2.0 - 24/10/2016
    • Replaced Hornbill Instance Username and Password authentication with API Key functionality
    • Improved performance by adding support for concurrent import API calls
    • Added ability to specify whether the Asset Owner and Asset Used By records are Hornbill Contacts or Hornbill Users
  • v1.1.2 - 15/02/2016
    • Defect fix: NULL values within asset records being replaced by the string <nil>
  • v1.1.1 - 03/02/2016
    • Defect fix: Mapping name was being written to asset columns when column value from database was blank or NULL
  • v1.1.0 - 19/01/2016
    • Added support for MySQL versions 3.2.0 to 4.0
  • v1 - 22/12/2015
    • Initial Release
Retrieved from "https://wiki.hornbill.com/index.php?title=Asset_Data_Import_Tool&oldid=21878"