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

History

This tool is an evolution the previous Database Asset Import v1.*, combining the CSV Asset Import v1.* tool and a revamp of the templating system.

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,
 "CSVConf": {
   "CommaCharacter":",",
   "LazyQuotes":false,
   "FieldsPerRecord":0,
   "CarriageReturnRemoval":false
 },
 "SQLConf": {
     "Driver": "",
     "Server": "",
     "Database": "",
     "Authentication": "",
     "UserName": "",
     "Password": "",
     "Port": 0,
     "Encrypt": false,
     "Query": ""
 },
 "AssetTypes": [{
         "AssetType": "Server",
         "OperationType": "Both",
         "PreserveShared": false,
         "PreserveState": false,
         "PreserveSubState": false,
         "PreserveOperationalState": false,
         "Query": "",
         "AssetIdentifier": {
             "DBColumn": "SystemSerialNumber",
             "Entity": "AssetsComputer",
             "EntityColumn": "h_serial_number"
         }
     },
     {
         "AssetType": "Laptop",
         "OperationType": "Both",
         "PreserveShared": false,
         "PreserveState": false,
         "PreserveSubState": false,
         "PreserveOperationalState": false,
         "Query": "",
         "AssetIdentifier": {
             "DBColumn": "MachineName",
             "Entity": "Asset",
             "EntityColumn": "h_name"
         }
     },
     {
         "AssetType": "Desktop",
         "OperationType": "Both",
         "PreserveShared": false,
         "PreserveState": false,
         "PreserveSubState": false,
         "PreserveOperationalState": false,
         "Query": "",
         "AssetIdentifier": {
             "DBColumn": "MachineName",
             "Entity": "Asset",
             "EntityColumn": "h_name"
         }
     },
     {
         "AssetType": "Virtual Machine",
         "OperationType": "Both",
         "PreserveShared": false,
         "PreserveState": false,
         "PreserveSubState": false,
         "PreserveOperationalState": false,
         "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. Details on how to create an API key can be found here.
  • "InstanceId" - the name of your Hornbill instance and can be found within the URL you use to navigate to it: live.hornbill.com/[instance name]/. E.g. if the URL you use to access your instance is live.hornbill.com/arescomputing/, then your instance id would be "arescomputing". This value is case sensitive.
  • "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.

CSVConf

Only in use if SQLConfig.Driver is set to csv

  • "CommaCharacter" - The field separator (single) character - if left out, the default character will be a comma.
  • "CSVLazyQuotes" - The ability to give the CSV reader a hint that the csv file might be using lazy quotes. Defaults to false.
  • "CSVFieldsPerRecord" - The ability to give the CSV reader a hint about the number of fields in each record. Defaults to 0, leaving the CSV reader to do the heavy lifting.
  • "CSVCarriageReturnRemoval" - Certain CSV exporting systems will add extra carriage returns as a record delimiter. This is expected not to be common, hence the setting is left out of the configuration files (it is added to conf_computerSystem.json only for completeness sake). IF not set, then the default value is false and no carriages returns will be stripped from the data. IF set to true, then all carriage returns (possibly even intended ones) will be stripped.

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)
    • csv = CSV/Text file(s) - field names are expected in the first line. SQLConfig.Query will be IGNORED, AssetTypes[#].Query will be used instead.
    • 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.
    • nexthink - To use Nexthink Cloud or On-Premise API endpoint as the data source. When using Nexthink as a data source, the `Server`. `UserName` and `Password` parameters should be populated accordingly:
      • Server - this should be populated with the API endpoint, for example: https://your.domain.nexthink.cloud:443/2
      • UserName - this should be populated with the Username of the account being used to connect to Nexthink
      • Password - this should be populated with the Password for the above account
  • "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. When using this with CSV, the filename will suffice (eg "abc.csv") - the first line will be considered the column names. When using Nexthink as a data source, this should be left blank and the Nexthink query should be stored in the query property of the specific asset type object.

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
    • OperationType - The type of operation that should be performed on discovered assets - can be Create, Update or Both. Defaults to Both if no value is provided
    • PreserveShared - If set to true, when updating assets that are Shared, then the Used By fields will not be updated. Defaults to false
    • PreserveState - If set to true then the State field will not be updated. Defaults to false
    • PreserveSubState - If set to true then the SubState fields will not be updated. Defaults to false
    • PreserveOperationalState - If set to true then the Operational State field will not be updated. Defaults to false
    • NexthinkPlatform - When using Nexthnk as a data source, this can be optionally set to one of the following values, depending on which type of asset records you wish to import:
      • windows
      • mac_os
      • mobile
    • Query - either:
      • additional SQL clauses to be appended to the Query from SQLConf, to retrieve assets of that asset type.
      • primary query for returning Nexthink asset details
    • 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
    • SoftwareInventory - an object containing details pertaining to the import of software inventory records for the specified asset type:
      • AssetIDColumn - the column from the asset type query that contains its primary key
      • AppIDColumn - the column from the Software Inventory that holds the software unique ID. NOTE: this is ignored when performing imports from Nexthink - the App ID to match is a concatenation of the publisher, name and version fields (no spaces between them)
      • Query - the query that will be run per asset, to return its software inventory records. {{AssetID}} (Please note: this is NOT being processed as a template (note that the absence of the full stop)) in the query will be replaced by each assets primary key value, whose column is defined in the AssetIDColumn property
      • Mapping - maps data into the software invemtory records

AssetGenericFieldMapping

  • Maps data in to the generic Asset record
  • Any value templated with {{.columnName}} 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.
  • Please note that the field name is case sensitive
  • Should the column name contain a space (this is more likely when the data is coming from a CSV file), the following format will get the data: {{index . \"Computer name\" }}. Please be advised that there is still a distinct preference for the column names NOT to contain spaces.
  • Although it is preferred that any data manipulation is handled in the SQL query, should some post-production be necessary, this is possible as such
    • {{.columnName | Upper}} will UPPERCASE the value contained in columnName
    • {{.columnName | Lower}} will lowercase the value contained in columnName

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",
     "PreserveShared": false,
     "Query": "AND OASysEncl.ChassisTypes0 IN (2, 17, 18, 19, 20, 21, 22, 23)",
     "AssetIdentifier": {
         "DBColumn": "SystemSerialNumber",
         "Entity": "AssetsComputer",
         "EntityColumn": "h_serial_number"
     }
 },
 {
       "AssetType": "Laptop",
       "PreserveShared": false,
       "Query": "AND OASysEncl.ChassisTypes0 IN (8, 9, 10, 14)",
       "AssetIdentifier": {
           "DBColumn": "MachineName",
           "Entity": "Asset",
           "EntityColumn": "h_name"
       }
   },
   {
       "AssetType": "Desktop",
       "PreserveShared": false,
       "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",
       "PreserveShared": false,
       "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",
           "PreserveShared": false,
           "Query": "WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1 AND ac.Model = 'Latitude E6320'",
           "AssetIdentifier": {
               "DBColumn": "AssetName",
               "Entity": "Asset",
               "EntityColumn": "h_name"
           }
       },
       {
           "AssetType": "Desktop",
           "PreserveShared": false,
           "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",
           "PreserveShared": false,
           "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
  • forceupdates - one of the optimisations, previously included, is the comparison between new data and old data in order to decided whether the record needs an update or not. The decision is, in essence, a detection of changes in SOURCE data, not any data that might have been manipulated by using the template. This paramater caters for this - i.e. an update will always be attempted - whether or not there are going to be any changes.

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.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] Unable to write to log Post /system/?method=logMessage: unsupported protocol scheme. - this suggests that the import was unable to access the csv file via ODBC. There could be two reasons for this:
  1. Someone had moved the csv from its default location
  2. The csv file was open at the time and this locked the import

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 and HTTPS_PROXY Environment variables needs to be set. These environment variables hold 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

set HTTPS_PROXY=HOST:PORT
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number. IF you require a username and password to go through the proxy, the format for the setting is as follows:
set HTTP_PROXY=username:password@HOST:PORT

set HTTPS_PROXY=username:password@HOST:PORT

Scheduling Overview

Windows

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

  • Ensure the user account running the task has rights to goDBAssetImport.exe and the containing folder.
  • Make sure the Start In parameter contains the folder where goDBAssetImport.exe resides in otherwise it will not be able to pick up the correct path.
Retrieved from "https://wiki.hornbill.com/index.php?title=Asset_Data_Import_Tool&oldid=27097"