Hornbill Data Export

About the Hornbill Data Export Tool

This utility provides a quick and easy method of running reports on your Hornbill instance and exporting the report output into a local database.

The tool will:

• Run pre-built reports on your Hornbill instance
• Wait for the reports to complete
• Retrieve the CSV file that was created as part of the report run (as defined within the report itself)
• Add and/or update the report records into the MySQL, MariaDB or Microsoft SQL Server database table of your choice

IMPORTANT - Any Report being run by this tool needs to be configured to provide CSV as an additional data format, within the Output Formats tab of the Report builder.

Open Source

The Hornbill Data Export utility is provided open source under the Hornbill Community Licence and can be found Here on GitHub.

Installation

Windows

• Download the ZIP archive
• Extract the ZIP archive into a folder you would like the application to run from e.g. 'C:\hornbillDataExport'.

Configuration

Example JSON File:

{
    "APIKey": "yourapikey",
    "InstanceID": "yourinstanceid",
    "Database":{
        "Driver": "mysql",
        "Server": "127.0.0.1",
        "Database": "hornbillbackup",
        "Authentication": "",
        "UserName": "root",
        "Password": "password",
        "Port": 3306,
        "Encrypt": false
    },
    "Reports":[
        {
            "ReportID":8,
            "ReportName":"Change Requests Incremental",
            "DeleteReportInstance": true,
            "DeleteReportLocalFile": true,
            "Database":{
                "TableName":"h_itsm_requests",
                "PrimaryKey":"h_pk_reference",
                "Mapping":{
                    "RequestID":"h_pk_reference",
                    "DateLogged":"h_datelogged",
                    "LastUpdate":"h_datelastmodified",
                    "Summary":"h_summary"
                }
            }
        }
    ]
}

• APIKey - A Valid API Assigned to a user with enough rights to run and retrieve the report (case sensitive)
• InstanceId - Instance ID (case sensitive)
• Database - An object containing the details of the database that the records should be written to:
  • Driver - The database type:
    • mysql - MySQL (v4 or above)or MariaDB
    • mssql - Microsoft SQL Server (2005 or above)
  • Database - The name of the database on the server
  • Authentication - The type of authentication to use to connect to the SQL server, if the Driver is set to mssql. Can be either:
    • Windows - Windows Account authentication, uses the logged-in Windows account to authenticate
    • 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 - SQL port
  • Encrypt - Boolean value to specify wether 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.
• Reports - An array containing objects defining the reports to be run and retrieved:
  • ReportID - The integer ID of the report to be run
  • ReportName - The name of the report to be run
  • DeleteReportInstance - Boolean true or false, to define if the report run instance should be removed after the report run has completed
  • DeleteReportLocalFile - Boolean true or false, to define if the report CSV file should be removed after the import has completed
  • Table - The table details required to map the report data in to:
    • TableName - The name of the table to insert/update report records in to
    • PrimaryKey - The name of the primary key column within the table above. Only used when the Database > Driver is set to mssql
    • Mapping - Allows you to map the report output columns to the database table columns. In the format

      "ReportColumnName":"DatabaseTableColumnName"

Execute

Command Line Parameters:

• file - This should point to your json configuration file and by default looks for a file in the current working directory called conf.json. If this is present you don't need to have the parameter.
• debug - Defaults to false. If set to true, then the log file will contain extra debugging information.

'goHornbillDataExport_x64.exe -file=conf.json'

Preparing to run the tool

• Open conf.json and add in the necessary configration;
• Open Command Line Prompt as Administrator;
• Change Directory to the folder with goHornbillDataExport*.* executables 'C:\hornbillDataExport
  • On 32 bit Windows PCs: goHornbillDataExport_x86.exe
  • On 64 bit Windows PCs: goHornbillDataExport_x64.exe
• Follow all on-screen prompts, taking careful note of all prompts and messages provided.

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 dataexport_20181004162344.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 Codes

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

HTTP Proxies

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

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

Scheduling Overview

Windows

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

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

Change Log

v1.1.0 - 28/01/2019

Feature

• Improved log output when column mappings are incorrect

Defects Fixed

• Issue with SQL query when empty columns returned within report rows
• Path issue when tool run on some non-Windows operating systems