Database Asset Import

From Hornbill
Jump to navigation Jump to search

About the Hornbill 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, LDAP, or another database containing asset records. The tool is designed to run behind your corporate firewall, connect to your asset data source, 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 the example configuration file relevant to your environment, 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_example_db_sccm.json

For Mac OSX and Linux Systems: ./goDBAssetImport -dryrun=true -file=conf_example_db_sccm.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_example_db_sccm.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": "yourapikey",
    "InstanceId": "yourinstanceid",
    "KeysafeKeyID": 0,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "mssql",
        "Database": {
            "Authentication": "SQL",
            "Encrypt": false,
            "Query": ""
        }
    },
    "AssetTypes": [{
            "AssetType": "Server",
            "OperationType": "Both",
            "Query": "",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "AssetIdentifier": {
                "SourceColumn": "SystemSerialNumber",
                "Entity": "AssetsComputer",
                "EntityColumn": "h_serial_number",
                "SourceContractColumn": "Contract",
                "SourceSupplierColumn": "Supplier"
            }
        },
        {
            "AssetType": "Laptop",
            "OperationType": "Both",
            "Query": "",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "AssetIdentifier": {
                "SourceColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        },
        {
            "AssetType": "Desktop",
            "OperationType": "Both",
            "Query": "",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "AssetIdentifier": {
                "SourceColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        },
        {
            "AssetType": "Virtual Machine",
            "OperationType": "Both",
            "Query": "",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "AssetIdentifier": {
                "SourceColumn": "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 required API calls. Here are details on creating an API key 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.
  • KeysafeKeyID - The ID of the Keysafe Key that holds your data source credentials. Set to 0 for importing directly from CSV files. Supported Keysafe key types are:
    • Database Authentication
    • Username + Password
    • LDAP Authentication
    • Google Workspace
  • 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 and not split the results between multiple logs.
  • HornbillUserIDColumn - used to specify the Hornbill User ID column for matching users - options: h_user_id (default), h_employee_id, h_email, h_name, h_attrib1, h_attrib8 & h_login_id. Please note: last logged on, owned by and used by will use the same field - i.e. one can NOT specify which column to match to individually.

SourceConfig

  • Source - The data source that the tool will connect to:
    • mssql - Microsoft SQL Server (2005 or above) - will use Keysafe key type Database Authentication
    • mysql - MySQL Server - will use Keysafe key type Database Authentication
    • mysql320 - MySQL Server v3.2.0 to v4.0 - will use Keysafe key type Database Authentication
    • odbc - ODBC driver - will use Keysafe key type Database Authentication
    • swsql - Supportworks SQL (Core Services v3.x) - will use Keysafe key type Database Authentication
    • csv - CSV / Text file(s) - doesn't require Keysafe keys
    • nexthink - Nexthink - will use Keysafe key type Username + Password
    • ldap - LDAP / Active Directory - will use Keysafe key type LDAP Authentication
    • google - Google Workspace Enterprise Chrome OS - will use Keysafe key type Google Workspace
    • certero - Certero - will use Keysafe key type Certero
    • workspaceone - vmware Workspace One UEM - will use Keysafe type VMWare Workspace One UEM
  • CSV - Only in use if Source is set to csv
    • CarriageReturnRemoval - 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.
    • CommaCharacter - The field separator (single) character - if left out, the default character will be a comma.
    • FieldsPerRecord - 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.
    • LazyQuotes - The ability to give the CSV reader a hint that the csv file might be using lazy quotes. Defaults to false.
  • Database - Only in use if Source is set to mssql, mysql, mysql320 or swsql
    • 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 authentication details in the Keysafe Key
      • SQL - uses SQL Server authentication, and will use the details in the Keysafe Key defined to connect. The Key Type should be Database Authentication
    • 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.
  • LDAP - Only in use if Source is set to ldap
    • Server - LDAP host specific configuration:
      • InsecureSkipVerify - Boolean value, used in conjunction with SSL or TLS connection types and allows the verification of SSL Certifications to be disabled i.e. "ON" sets the InsecureSkipVerify variable to "true".
      • Debug - Boolean value - enables LDAP Connection Debugging. This should only ever be enabled to troubleshoot connection issues during the initial setup and testing.
      • ConnectionType - The type of HTTP connection to use when communicating with the directory Server. Normal HTTP (leave the value blank), SSL, and TLS are supported.
    • Query - LDAP query specific configuration:
      • Attributes - A list of LDAP attributes to return as part of the query
      • Scope - Search Scope (ScopeBaseObject = 0, ScopeSingleLevel = 1, ScopeWholeSubtree = 2) Default is 1.
      • DerefAliases - Dereference Aliases (NeverDerefAliases = 0, DerefInSearching = 1, DerefFindingBaseObj = 2, DerefAlways = 3), allows you to choose at what stage during the search operation (LDAP Query) aliases are dereferenced. The default value (1) should be suitable for most.
      • TypesOnly - Enabling this setting will cause the query to return attribute types (descriptions) rather than attribute values.
      • SizeLimit - Allows you to set a size limit in relation to the result set that can be returned. Setting to '0' disables this setting.
      • TimeLimit - Allows you to impose a time limit (seconds) in relation to how long the LDAP query can run before timing out. Setting to '0' disables this setting.
  • Google - Only in use if Source is set to google
    • Customer - The Customer ID of the Google Account. Supports my_customer to return devices enrolled to the deafult account that the KeySafe key was created with
    • Query - Search string as per the Google API Documentation
    • OrgUnitPath - The full path of the Google Organisational Unit where the devices reside
  • Certero - Only in use if Source is set to google
    • Expand - The Certero oData query which defines the columns and associated entitiy records that can be mapped into Hornbill asset records

AssetTypes

An array of objects details the asset types to import.

NOTE - As of v1.16.0, during the import process assets of each type as defined below are retrieved from Hornbill and cached locally in memory to aid in the matching of existing asset records for update. This is to improve the performance of the import process, as well as to significantly reduce load on your Hornbill instance when the imports are running. If you have imported asset records into one particular Hornbill asset type, then re-import the same asset records from the source into a different Hornbill asset type, then you may see duplicate asset records being created. We suggest you run the tool against your configuration in dryrun mode and check that no duplicates would be created, and if they are then you need to update your import configuration accordingly. See AssetType, below, for one such method of avoiding duplicates.

  • AssetType - the Asset Type Name which needs to match a correct Asset Type Name in your Hornbill Instance, OR if OperationType below is set to Update, and you wish to update assets of ALL types of a specific class, then you can populate this property with __all__:computer, where computer is the asset class to update. Valid classes are:
      • basic
      • computer
      • computerPeripheral
      • mobileDevice
      • networkDevice
      • printer
      • software
      • telecoms
  • 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
  • CSVFile - The path to the CSV file to use as the data source for this specific asset type. Only required when SourceConfig > Source is set to CSV
  • 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
  • LDAPDSN - The Distinguished Name of the LDAP container to execute your query in. Only required when SourceConfig > Source is set to LDAP
  • Query - Not used for Workspace One imports. For the other data sources:
    • Additional SQL clauses to be appended to the Query from SourceConfig > Database > Query, to retrieve assets of that asset type.
    • Certero oData filter for returning asset details for that asset type
    • Nexthink query for returning asset details for that asset type
    • LDAP query for returning asset details for that asset type
  • Filter - Used exclusively for applying filters to Workspace One queries, all are optional:
    • User - Username the device enrolled under
    • ModelIdentifier - Partial search by device model. Search by MD20 would return device with model MD200LL
    • DevicePlatformType - Device platform type, i.e. Apple, Android, WindowsPC, etc.
    • Ownership - Device ownership type i.e. Corporate, Employee, Shared
    • OrganizationGroupUUID - The UUID of the Organization Group that the devices are members of. Defaults to the keysafe key user's OrganizationGroup
    • ComplianceStatus - The compliance status of the devices i.e. Compliant, NonCompliant etc
    • SeenSince - Specifies the datetime filter for device search, which retrieves the devices that are seen after this datetime stamp.
  • 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:
    • SourceColumn - specifies the unique identifier column from the source data query. This can be either a column name, or a Go Template. NOTE: Importing from Certero requires this to be a Go template.
    • Entity - the Hornbill entity where data is stored, one of:
      • Asset
      • AssetsBasic
      • AssetsComputer
      • AssetsComputerPeripheral
      • AssetsMobileDevice
      • AssetsNetworkDevice
      • AssetsPrinter
      • AssetsSoftware
      • AssetsTelecoms
    • EntityColumn - specifies the unique identifier column from the Hornbill entity specified above
    • SourceContractColumn - specifies the unique identifier column from the database query - the data in this column of the query result should be the Contract ID within Hornbill (eg C20200700001 from /suppliermanager/suppliercontract/view/C20200700001/; 'C20200700001' AS Contract hardcoded in SQL)
    • SourceSupplierColumn - specifies the unique identifier column from the database query - the data in this column of the query result should be the Supplier ID within Hornbill (eg ### from suppliermanager/supplier/view/###/)
    • 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. This can be either a column name, or a Go Template. NOTE: this is ignored when performing imports from Workspace One.
      • AppIDColumn - the column from the Software Inventory that holds the software unique ID. This can be either a column name, or a Go Template. NOTE: this is ignored when performing imports from Certero, CSV, LDAP or 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. NOTE: Not used when importing assets from Certero or Workspace One.
      • Mapping - maps data into the software inventory 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
  • {{.columnName | epoch}} will convert an epoch value to the YYYY-MM-DD HH:II:SS format required for a Hornbill DateTime field
  • {{.columnName | epoch_clear}} will convert an epoch value to the YYYY-MM-DD HH:II:SS format required for a Hornbill DateTime field. Defaulting to CLEAR the column if unable to convert.
  • {{.columnName | date_conversion "date time format of the content in .columnName"}} Provide the input format based on the following reference time of Jan 1st 2006 4 minutes and 5 seconds past 3pm - eg "02/01/2006 15:04:05" will convert the regular UK/European date time format to the format useable in the Hornbill datetime field, whereas "01/02/2006 15:04" will process default US date time. Please note that IF your formatting is already in the Hornbill date time format (2006-01-02 15:04:05), you don't need to convert anything.
  • {{.columnName | date_conversion_clear "date time format of the content in .columnName"}} Provide the input format based on the following reference time of Jan 1st 2006 4 minutes and 5 seconds past 3pm - eg "02/01/2006 15:04:05" will convert the regular UK/European date time format to the format useable in the Hornbill datetime field, whereas "01/02/2006 15:04" will process default US date time. Defaulting to CLEAR the column if unable to convert.

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 SourceConfig, 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.
{
    "APIKey": "yourapikey",
    "InstanceId": "yourinstanceid",
    "KeysafeKeyID": 0,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "mssql",
        "Database": {
            "Authentication": "SQL",
            "Encrypt": false,
            "Query": "SELECT dbo.v_R_System.ResourceID AS [AssetID], dbo.v_R_System.User_Name0 AS [UserName], dbo.v_R_System.Netbios_Name0 AS [MachineName], dbo.v_R_System.Resource_Domain_OR_Workgr0 AS [NETDomain], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [OperatingSystemCaption], dbo.v_R_System.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], dbo.v_R_System.AD_Site_Name0 AS [SiteName] FROM dbo.v_R_System 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": "Desktop",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "AND OASysEncl.ChassisTypes0 IN (3, 4, 5, 6, 7, 12, 13, 15, 16) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "SourceColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name",
                "SourceContractColumn": "",
                "SourceSupplierColumn": ""
            },
            "SoftwareInventory": {
                "AssetIDColumn": "AssetID",
                "AppIDColumn": "AppID",
                "Query": "SELECT AppID = CASE WHEN Publisher0 IS NULL AND Version0 IS NULL THEN DisplayName0 WHEN Publisher0 IS NOT NULL AND Version0 IS NULL THEN Publisher0+DisplayName0 ELSE Publisher0+DisplayName0+Version0 END, DisplayName0 , Version0, FCM.Name, convert(datetime, InstallDate0, 112) AS InstallDate0, Publisher0, ProdID0, FCM.ResourceID FROM v_Add_Remove_Programs AS ARP JOIN v_FullCollectionMembership As FCM on ARP.ResourceID=FCM.ResourceID WHERE FCM.CollectionID = 'SMS00001' AND FCM.ResourceID = '{{AssetID}}' AND DisplayName0 IS NOT NULL AND DisplayName0 != '' AND DisplayName0 NOT LIKE '%Update for Windows%' ORDER BY ProdID0 ASC",
                "Mapping": {
                    "h_app_id":"{{.AppID}}",
                    "h_app_name": "{{.DisplayName0}}",
                    "h_app_vendor":"{{.Publisher0}}",
                    "h_app_version":"{{.Version0}}",
                    "h_app_install_date":"{{.InstallDate0}}",
                    "h_app_help":"",
                    "h_app_info":""
                }
            }
        },
        {
            "AssetType": "Server",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "AND OASysEncl.ChassisTypes0 IN (2, 17, 18, 19, 20, 21, 22, 23) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "SourceColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            },
            "SoftwareInventory": {
                "AssetIDColumn": "AssetID",
                "AppIDColumn": "AppID",
                "Query": "SELECT AppID = CASE WHEN Publisher0 IS NULL AND Version0 IS NULL THEN DisplayName0 WHEN Publisher0 IS NOT NULL AND Version0 IS NULL THEN Publisher0+DisplayName0 ELSE Publisher0+DisplayName0+Version0 END, DisplayName0 , Version0, FCM.Name, convert(datetime, InstallDate0, 112) AS InstallDate0, Publisher0, ProdID0, FCM.ResourceID FROM v_Add_Remove_Programs AS ARP JOIN v_FullCollectionMembership As FCM on ARP.ResourceID=FCM.ResourceID WHERE FCM.CollectionID = 'SMS00001' AND FCM.ResourceID = '{{AssetID}}' AND DisplayName0 IS NOT NULL AND DisplayName0 != '' AND DisplayName0 NOT LIKE '%Update for Windows%' ORDER BY ProdID0 ASC",
                "Mapping": {
                    "h_app_id":"{{.AppID}}",
                    "h_app_name": "{{.DisplayName0}}",
                    "h_app_vendor":"{{.Publisher0}}",
                    "h_app_version":"{{.Version0}}",
                    "h_app_install_date":"{{.InstallDate0}}",
                    "h_app_help":"",
                    "h_app_info":""
                }
            }
        },
        {
            "AssetType": "Virtual Machine",
            "OperationType": "Both",
            "PreserveShared": false,
            "PreserveState": false,
            "PreserveSubState": false,
            "PreserveOperationalState": false,
            "Query": "AND OASysEncl.ChassisTypes0 = 1 AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "SourceColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            },
            "SoftwareInventory": {
                "AssetIDColumn": "AssetID",
                "AppIDColumn": "AppID",
                "Query": "SELECT AppID = CASE WHEN Publisher0 IS NULL AND Version0 IS NULL THEN DisplayName0 WHEN Publisher0 IS NOT NULL AND Version0 IS NULL THEN Publisher0+DisplayName0 ELSE Publisher0+DisplayName0+Version0 END, DisplayName0 , Version0, FCM.Name, convert(datetime, InstallDate0, 112) AS InstallDate0, Publisher0, ProdID0, FCM.ResourceID FROM v_Add_Remove_Programs AS ARP JOIN v_FullCollectionMembership As FCM on ARP.ResourceID=FCM.ResourceID WHERE FCM.CollectionID = 'SMS00001' AND FCM.ResourceID = '{{AssetID}}' AND DisplayName0 IS NOT NULL AND DisplayName0 != '' AND DisplayName0 NOT LIKE '%Update for Windows%' ORDER BY ProdID0 ASC",
                "Mapping": {
                    "h_app_id":"{{.AppID}}",
                    "h_app_name": "{{.DisplayName0}}",
                    "h_app_vendor":"{{.Publisher0}}",
                    "h_app_version":"{{.Version0}}",
                    "h_app_install_date":"{{.InstallDate0}}",
                    "h_app_help":"",
                    "h_app_info":""
                }
            }
        },
        {
            "AssetType": "Laptop",
            "OperationType": "Both",
            "PreserveShared": false,
            "Query": "AND OASysEncl.ChassisTypes0 IN (8, 9, 10, 14) AND dbo.v_R_System.Obsolete0 = 0 ORDER BY dbo.v_R_System.ResourceID ASC",
            "AssetIdentifier": {
                "SourceColumn": "MachineName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            },
            "SoftwareInventory": {
                "AssetIDColumn": "AssetID",
                "AppIDColumn": "AppID",
                "Query": "SELECT AppID = CASE WHEN Publisher0 IS NULL AND Version0 IS NULL THEN DisplayName0 WHEN Publisher0 IS NOT NULL AND Version0 IS NULL THEN Publisher0+DisplayName0 ELSE Publisher0+DisplayName0+Version0 END, DisplayName0 , Version0, FCM.Name, convert(datetime, InstallDate0, 112) AS InstallDate0, Publisher0, ProdID0, FCM.ResourceID FROM v_Add_Remove_Programs AS ARP JOIN v_FullCollectionMembership As FCM on ARP.ResourceID=FCM.ResourceID WHERE FCM.CollectionID = 'SMS00001' AND FCM.ResourceID = '{{AssetID}}' AND DisplayName0 IS NOT NULL AND DisplayName0 != '' AND DisplayName0 NOT LIKE '%Update for Windows%' ORDER BY ProdID0 ASC",
                "Mapping": {
                    "h_app_id":"{{.AppID}}",
                    "h_app_name": "{{.DisplayName0}}",
                    "h_app_vendor":"{{.Publisher0}}",
                    "h_app_version":"{{.Version0}}",
                    "h_app_install_date":"{{.InstallDate0}}",
                    "h_app_help":"",
                    "h_app_info":""
                }
            }
        }
    ],
	"AssetGenericFieldMapping": {
        "h_name": "{{.MachineName}}",
        "h_site": "{{.SiteName}}",
        "h_asset_tag": "{{.MachineName}}",
        "h_description": "{{.MachineName}} ({{.SystemModel}})",
        "h_owned_by": "{{.UserName}}",
        "h_used_by": "{{.UserName}}"
    },
    "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_memory_info": "{{.MemoryKB}}",
        "h_os_description": "{{.OperatingSystem}}",
        "h_os_service_pack": "{{.ServicePackVersion}}",
        "h_os_version": "{{.OperatingSystemVersion}}",
        "h_physical_disk_size": "{{.DiskSpaceMB}}",
        "h_serial_number": "{{.SystemSerialNumber}}",
        "h_cpu_clock_speed": "{{.ProcessorSpeedGHz}}",
        "h_physical_cpus": "{{.NumberofProcessors}}",
        "h_bios_name": "{{.BIOSDescription}}",
        "h_bios_release_date": "{{.BIOSReleaseDate}}",
        "h_bios_version": "{{.SMBIOSVersion}}"
    }
}

Lansweeper 7.1

The following is an example of the SourceConfig, 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.
{
    "APIKey": "yourapikey",
    "InstanceId": "yourinstanceid",
    "KeysafeKeyID": 0,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "mssql",
        "Database": {
            "Authentication": "SQL",
            "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",
            "OperationType": "Both",
            "Query": "WHERE at.AssetTypename = 'Windows' AND os.ProductType = 1 AND ac.Model = 'Latitude E6320'",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name",
                "SourceContractColumn": "",
                "SourceSupplierColumn": ""
            }
        },
        {
            "AssetType": "Desktop",
            "OperationType": "Both",
            "Query": "WHERE (at.AssetTypename = 'Windows' AND os.ProductType = 1 AND ac.Model = 'Precision WorkStation T5500') OR at.AssetTypename = 'Apple Mac'",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        },
        {
            "AssetType": "Server",
            "OperationType": "Both",
            "Query": "WHERE os.ProductType IN (2, 3)",
            "AssetIdentifier": {
                "SourceColumn": "AssetName",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            }
        }
    ],
    "AssetGenericFieldMapping": {
        "h_name": "{{.AssetName}}",
        "h_asset_tag": "{{.AssetName}}",
        "h_created_date": "{{.Firstseen}}",
        "h_description": "{{.Description}}",
        "h_owned_by": "{{.ADUserID}}",
        "h_owned_by_name": "{{.ADUserName}}",
        "h_used_by": "{{.ADUserID}}",
        "h_used_by_name": "{{.ADUserName}}",
        "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_memory_info": "{{.Memory}}",
        "h_os_description": "{{.OperatingSystem}}",
        "h_os_service_pack": "{{.ServicePackVersion}}",
        "h_os_version": "{{.OSCode}}",
        "h_serial_number": "{{.Serialnumber}}",
        "h_physical_cpus": "{{.NrProcessors}}",
        "h_net_name": "{{.FQDN}}"
    }
}

Nexthink

The following is an example of the SourceConfig, AssetTypes and data mapping configuration that could be used to import computer-type assets from Nexthink. NOTE: The configuration example is provided as-is, and may not be suitable to import your organisations Nexthink asset data. We highly recommend that a Nexthink administrator review the query and mapping against your Nexthink account prior to using this in a production environment.

{
    "APIKey": "yourapikey",
    "InstanceId": "yourinstanceid",
    "KeysafeKeyID": 0,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "nexthink"
    },
    "AssetTypes": [
        {
            "AssetType": "Server",
            "OperationType": "Both",
            "PreserveShared": false,
            "Query": "(select (id name device_manufacturer device_model last_logon_time last_logged_on_user mac_addresses ip_addresses total_ram os_version_and_architecture os_build system_drive_capacity device_serial_number cpu_model cpu_frequency number_of_cores logical_cpu_number bios_serial_number distinguished_name) (from device (where device (eq os_version_and_architecture (pattern 'Windows*Server*')))))",
            "NexthinkPlatform": "windows",
            "AssetIdentifier": {
                "SourceColumn": "name",
                "Entity": "Asset",
                "EntityColumn": "h_name"
            },
            "SoftwareInventory": {
                "AssetIDColumn": "name",
                "Query": "(select ((package (id publisher first_installation name program version)))(from (device package) (with package(where device (eq id (identifier {{AssetID}})))(where package(eq type (enum 'program'))(eq status (enum 'installed')))))(limit 20000))",
                "Mapping": {
                    "h_app_id":"{{.publisher}}{{.name}}{{.version}}",
                    "h_app_name": "{{.name}}",
                    "h_app_vendor":"{{.publisher}}",
                    "h_app_version":"{{.version}}",
                    "h_app_install_date":"{{.first_installation}}"
                }
            }
        }
    ],
    "AssetGenericFieldMapping": {
        "h_name": "{{.name}}",
        "h_asset_tag": "{{.name}}",
        "h_description": "From Nexthink: {{.name}} ({{.device_model}})",
        "h_notes": "System Drive Capacity: {{.system_drive_capacity}}\nRAM: {{.total_ram}}",
        "h_used_by": "{{.last_logged_on_user}}"
    },
    "AssetTypeFieldMapping": {
        "h_name": "{{.name}}",
        "h_mac_address": "{{.mac_addresses}}",
        "h_net_ip_address": "{{.ip_addresses}}",
        "h_net_computer_name": "{{.name}}",
        "h_model": "{{.device_model}}",
        "h_manufacturer": "{{.device_manufacturer}}",
        "h_description": "{{.device_manufacturer .device_model}}",
        "h_last_logged_on": "{{.last_logon_time}}",
        "h_last_logged_on_user": "{{.last_logged_on_user}}",
        "h_memory_info": "{{.total_ram}}",
        "h_os_description": "{{.os_version_and_architecture}}",
        "h_os_version": "{{.os_build}}",
        "h_physical_disk_size": "{{.system_drive_capacity}}",
        "h_serial_number": "{{.device_serial_number}}",
        "h_cpu_info": "{{.cpu_model}}",
        "h_cpu_clock_speed": "{{.cpu_frequency}}",
        "h_physical_cpus": "{{.number_of_cores}}",
        "h_logical_cpus": "{{.logical_cpu_number}}",
        "h_bios_serial_number": "{{.bios_serial_number}}",
        "h_net_name": "{{.distinguished_name}}"
    }
}

Certero

The following is an example of the SourceConfig, AssetTypes and data mapping configuration that could be used to import computer-type assets from Certero. NOTE: The configuration example is provided as-is, and may not be suitable to import your organisations Certero asset data. We highly recommend that a Certero administrator review the filter, expand column query and mappings against your Certero account prior to using this in a production environment.

{
    "APIKey": "yourapikey",
    "InstanceId": "yourinstanceid",
    "KeysafeKeyID": 0,
    "LogSizeBytes": 1000000,
    "HornbillUserIDColumn": "h_user_id",
    "SourceConfig": {
        "Source": "certero",
        "Certero": {
            "Expand": "ComputerSystemInventory($select=ComputerSystemObjectId,ComputerDomain,ComputerName,IpAddress,MacAddress,SubnetMask,Manufacturer,Model,Username;$expand=OperatingSystem($select=Caption,VersionString)),ComputerSystemProcessorInfo($select=ClockSpeed,Cores,CoresPerCpu,Manufacturer,Model),WindowsSystemSoftwareProducts($select=SoftwareProductObjectId,WindowsSystemObjectId,InstallDate,SupportUrl,UninstallString,SoftwareProduct;$expand=SoftwareProduct($select=SoftwareProductObjectId,DisplayName,ProductCode,Publisher,Version))"
        }
    },
    "AssetTypes": [{
        "AssetType": "Server",
        "OperationType": "Both",
        "PreserveShared": false,
        "Query": "startswith(ComputerSystemInventory/OperatingSystem/Caption,'Windows Server')",
        "AssetIdentifier": {
            "SourceColumn": "{{.ComputerSystemInventory.ComputerName}}",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        },
        "SoftwareInventory": {
            "AppIDColumn": "{{.SoftwareProduct.ProductCode}}",
            "ParentObject": "WindowsSystemSoftwareProducts",
            "Mapping": {
                "h_app_id":"{{.SoftwareProduct.ProductCode}}",
                "h_app_name": "{{.SoftwareProduct.DisplayName}}",
                "h_app_vendor":"{{.SoftwareProduct.Publisher}}",
                "h_app_version":"{{.SoftwareProduct.Version}}",
                "h_app_install_date":"{{.InstallDate}}",
                "h_app_help":"{{.SupportUrl}}"
                
            }
        }
    },
    {
        "AssetType": "Desktop",
        "OperationType": "Both",
        "PreserveShared": false,
        "Query": "contains(ComputerSystemInventory/OperatingSystem/Caption,'Windows') and contains(ComputerSystemInventory/OperatingSystem/Caption,'Server') eq false",
        "AssetIdentifier": {
            "SourceColumn": "{{.ComputerSystemInventory.ComputerName}}",
            "Entity": "Asset",
            "EntityColumn": "h_name"
        },
        "SoftwareInventory": {
            "AppIDColumn": "{{.SoftwareProduct.ProductCode}}",
            "ParentObject": "WindowsSystemSoftwareProducts",
            "Mapping": {
                "h_app_id":"{{.SoftwareProduct.ProductCode}}",
                "h_app_name": "{{.SoftwareProduct.DisplayName}}",
                "h_app_vendor":"{{.SoftwareProduct.Publisher}}",
                "h_app_version":"{{.SoftwareProduct.Version}}",
                "h_app_install_date":"{{.InstallDate}}",
                "h_app_help":"{{.SupportUrl}}"
            }
        }
    }],
    "AssetGenericFieldMapping": {
        "h_name": "{{.ComputerSystemInventory.ComputerName}}",
        "h_asset_tag": "{{.ComputerSystemInventory.ComputerName}}",
        "h_description": "From Certero: {{.ComputerSystemInventory.Manufacturer}} ({{.ComputerSystemInventory.Model}})",
        "h_external_id": "{{.ComputerSystemObjectId}}",
        "h_external_source": "Certero"
    },
    "AssetTypeFieldMapping": {
        "h_name": "{{.ComputerSystemInventory.ComputerName}}",
        "h_mac_address": "{{.ComputerSystemInventory.MacAddress}}",
        "h_net_ip_address": "{{.ComputerSystemInventory.IpAddress}}",
        "h_net_computer_name": "{{.ComputerSystemInventory.ComputerName}}",
        "h_model": "{{.ComputerSystemInventory.Model}}",
        "h_description": "From Certero: {{.ComputerSystemInventory.Manufacturer}} ({{.ComputerSystemInventory.Model}})",
        "h_os_description": "{{.ComputerSystemInventory.OperatingSystem.Caption}}",
        "h_os_version": "{{.ComputerSystemInventory.OperatingSystem.VersionString}}",
        "h_cpu_info": "{{.ComputerSystemProcessorInfo.Model}}",
        "h_cpu_clock_speed": "{{.ComputerSystemProcessorInfo.ClockSpeed}}",
        "h_physical_cores": "{{.ComputerSystemProcessorInfo.Cores}}",
        "h_last_logged_on_user": "{{.ComputerSystemInventory.Username}}",
        "h_subnet_mask": "{{.ComputerSystemInventory.SubnetMask}}"
    }
}

API Key Rules

This utility uses (API keys):

  • admin:getApplicationList
  • admin:getGroupList2
  • data:entityAddRecord
  • data:entityBrowseRecords2
  • data:entityDeleteRecord
  • data:entityUpdateRecord
  • data:queryExec
  • system:logMessage
  • apps/com.hornbill.core:getSitesList
  • apps/com.hornbill.suppliermanager/SupplierAssets:addSupplierAsset
  • apps/com.hornbill.suppliermanager/SupplierContractAssets:addSupplierContractAsset

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_example_db_sccm.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.

Trouble Shooting

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

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 need 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

URLs to White List

Occasionally on top of setting the HTTP_PROXY variable the following URLs need to be white listed to allow access out to our network

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.