Difference between revisions of "SQL Organisation Import"

From Hornbill
Jump to navigation Jump to search
(Created page with "== About the Hornbill SQL Contact Import Utility == The utility provides a simple, safe and secure way to create contacts on the Hornbill platform by synchronizing with data h...")
 
Line 1: Line 1:
−
== About the Hornbill SQL Contact Import Utility ==
+
=About the SQL Organisation Import Utility=
−
The utility provides a simple, safe and secure way to create contacts on the Hornbill platform by synchronizing with data held in your Database.  The tool is designed to run behind your corporate firewall, connect to your database, query the required contact information, transform and load into the Hornbill instance.  The tool connects to the Hornbill instance in the cloud over HTTPS/SSL so as long as you have standard internet access then you should be able to use 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 utility provides a quick and easy method of uploading and updating organisations from an external data source into Hornbill instance.
  
−
The SQL Contact Import Utility is provided open source under the [https://wiki.hornbill.com/index.php/The_Hornbill_Community_License_(HCL) Hornbill Community Licence] and can be found [https://github.com/hornbill/goDb2HcontactImport here] on GitHub
+
==Open Source==
  
−
== Installation Overview ==
+
The Hornbill Cleaner Utility is provided open source under the [https://wiki.hornbill.com/index.php/The_Hornbill_Community_License_(HCL) Hornbill Community Licence] and can be found [https://github.com/hornbill/goHornbillCleaner Here] on GitHub
  
−
=== Windows Installation ===
+
==Installation Overview==
−
* Download the [https://github.com/hornbill/goDb2HcontactImport/releases/latest latest package] from GitHub
 
−
* Extract zip into a folder you would like the application to run from e.g. '''C:\Hornbill_Import\'''
 
−
* Open '''conf.json''' and add in the necessary configration
 
−
* Open Command Line Prompt as Administrator
 
−
* Change Directory to the folder with contactImport_x64.exe '''C:\Hornbill_Import\'''
 
−
* Run the command contactImport_x64.exe -dryrun=true
 
  
−
Change "x64" to "x86" depending on your architecture
+
===Windows Installation===
  
−
== Configuration Overview ==
+
* Download the [https://github.com/hornbill/goHornbillCleaner/releases/latest ZIP archive] relevant to your OS and architecture. This contains the cleaner executable, configuration file and license;
−
The following section will detail how to prepare the conf.json file to successfully import your Contact records.
+
* Extract the ZIP archive into a folder you would like the application to run from e.g. 'C:\organisation_import\'.
  
−
===Prerequisites===
+
==Configuration Overview==
−
Before importing any contact records into your Hornbill instance, the Organisation records must be created first. This then allows the contact import utility to associate each contact record to an Organisation record based on what is specified in the "Company" field. Details on how to create a Hornbill Organisation record can be found here: [[Organizations|'''external organization records''']]
 
  
−
Prior to configuring the .json file, it is advisable to read the following wiki page regarding [[Contacts|'''Hornbill Contact records''']] as it will provide some context to what this import will create.
+
The configuration of this utility is managed through a JSON file (conf.json), which is supplied with each release:
  
−
To create a 64 bit ODBC connector you would need the Microsoft Access Database Engine which can be found as a 2010 Redistributable (https://www.microsoft.com/en-us/download/details.aspx?id=13255) or as an optional part of the Microsoft Office Suite
+
<code>
−
<br>
+
     "APIKey": "your_api_key_here",
−
<br>
+
     "InstanceId": "your_instance_id_here",
−
 
+
     "OrganisationAction": "Create/Update/Both",
−
===Completing the conf.json file===
 
−
A default configuration file is provided conf.json, if a configuration file is not specified as a command line argument then conf.json must exist.
 
−
 
 
−
    {
 
−
     "APIKey": "", /* this is the API-key which is associated to a user in the Hornbill instance [1] */
 
−
     "InstanceId": "", /* your Hornbill instance name : not likely to change */
 
−
     "ContactAction": "Create", /* options : Create/Update/Both ; on what action to assign roles to a user */
 
−
    "AttachCustomerPortal": true, /* options : true/false ; whether contact is allowed to enter the customer portal */
 
−
    "CustomerPortalOrgView": true, /* options : true/false ; whether contact is allowed to view orgaisation calls in the customer portal */
 
−
    "CustomerPortalOrgViewRevoke": false, /* options : true/false ; whether the contact visibility to organisation calls in the customer portal is to be revoked */
 
−
    "UpdateContactStatus": false,
 
 
     "SQLConf": {
 
     "SQLConf": {
−
         "Driver": "csv",
+
         "Driver": "",
−
         "Server": "localhost",
+
         "Server": "",
−
         "Database": "CSV64",
+
         "Database": "",
−
         "UserName": "root",
+
         "UserName": "",
 
         "Password": "",
 
         "Password": "",
−
         "Port": 5002,
+
         "Port": ,
−
         "ContactID": "FIELD1",   /* FieldID is the Hornbill field against which the record */
+
         "Encrypt": true/false,
−
         "FieldID": "h_logon_id", /* (identified by the data contained in the ContactID field) is searched */
+
         "OrganizationName": "FIELD 1",
−
        "Encrypt": false,
+
         "Query": ""
−
         "Query": "SELECT * FROM adbc.csv"
 
 
     },
 
     },
−
     "ContactMapping":{
+
     "OrganizationMapping":{
−
         "logon_id":"&#123;&#123;.FIELD1&#125;&#125;",
+
         "organization_name":"FIELD 1",
−
         "firstname":"&#123;&#123;.FIELD2&#125;&#125;",
+
         "address":"FIELD 2",
−
         "lastname":"&#123;&#123;.FIELD3&#125;&#125;",
+
         "city":"FIELD 3",
−
         "company":"&#123;&#123;.FIELD1&#125;&#125;",
+
         "state":"FIELD 4",
−
         "email_1":"&#123;&#123;.FIELD2&#125;&#125;",
+
         "postal_code":"FIELD 5",
−
         "email_2":"",
+
         "country":"FIELD 6",
−
         "tel_1":"&#123;&#123;.FIELD4&#125;&#125;",
+
         "industry":"FIELD 7}",
−
         "tel_2":"",
+
         "phone_number":"FIELD 8",
−
         "jobtitle":"",
+
         "website":"FIELD 9",
−
        "description":"",
+
"language":"FIELD 10",
−
        "notes":"",
+
"custom_1":"FIELD 11",
−
        "country":"",
+
"custom_2":"FIELD 12",
−
        "language":"",
+
"custom_3":"FIELD 13",  
−
        "private":"0",
+
"custom_4":"FIELD 14",
−
        "rights":"0",
+
"custom_5":"FIELD 15",  
−
        "contact_status":"0"
+
"custom_6":"FIELD 16",  
 +
"custom_7":"FIELD 17",  
 +
"custom_8":"FIELD 18",  
 +
"custom_9":"FIELD 19",  
 +
"custom_10":"FIELD 20",  
 +
"custom_11":"FIELD 21"  
 
     }
 
     }
−
    }
+
</code>
−
# An API key is set up against a user within Hornbill (https://wiki.hornbill.com/index.php/API_keys). For example, if admin user is doing the import, enter the API Key for the admin user.
 
  
−
===Configuring the ODBC Connector for CSV===
 
  
−
[[File:ODBC CSV Settings 20170406.png]]
 
  
−
== 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 Create and Update users will not be called and instead the XML will be dumped to the log file, this is to aid in debugging the initial connection information.
 
−
* concurrent - Defaults to `1` - Allows you to change the number of worker threads used to process the import, this can improve performance on slow import but using too many workers have a detriment to performance of your Hornbill instance.
 
−
* logprefix - add prefix to the log file
 
−
* version - outputs the tool version
 
−
* matchlike - defaults to '''''false''''' - Set to True if you want to mimick the post v1.0.3 behaviour which searches with LIKE instead of an exact match (eg "abc" would find "abc1" as the record to update between v1.0.3 and v1.4.0).
 
  
−
== Testing Overview ==
+
* CleanRequests : Set to true to remove all Service Manager Requests (and related entity data) from a Hornbill instance. Filter the requests to be deleted using the following parameters
−
If you run the application with the argument dryrun=true then no users will be created or updated, the XML used to create or update will be saved in the log file so you can ensure the LDAP mappings are correct before running the import.
+
** RequestServices : An array containing Service ID Integers to filter the requests for deletion against. An empty array will remove the Service filter, meaning requests with any or no service associated will be deleted
−
 
+
** RequestStatuses : An array containing Status strings to filter the requests for deletion against. An empty array will remove the Status filter, meaning requests at any status will be deleted
−
<code>
+
** RequestTypes : An array containing Request Type strings to filter the requests for deletion against. An empty array will remove the Type filter, meaning requests of any Type will be deleted
−
contactImport_x64.exe -dryrun=true
+
** RequestLogDateFrom :  A date to filter requests against log date (requests logged after or equal to this date/time). Can take one of the following values:
−
</code>
+
*** An empty string will remove the Logged From filter.
 +
*** A date string in the format YYYY-MM-DD HH:MM:SS.
 +
*** A duration string, to calculate a new datetime from the current datetime:
 +
**** Example: <code>-P1D2H3M4S</code> This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
 +
**** See the CalculateTimeDuration function documentation in https://github.com/hornbill/goHornbillHelpers for more details
 +
** RequestLogDateTo : A date to filter requests against log date (requests logged before or equal to this date/time). Can take one of the following values:
 +
*** An empty string will remove the Logged Before filter.
 +
*** A date string in the format YYYY-MM-DD HH:MM:SS.  
 +
*** A duration string, to calculate a new datetime from the current datetime:
 +
**** Example: <code>-P1D2H3M4S</code> This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
 +
**** See the CalculateTimeDuration function documentation in https://github.com/hornbill/goHornbillHelpers for more details
 +
** RequestClosedDateFrom :  A date to filter requests against close date (requests closed after or equal to this date/time). Can take one of the following values:
 +
*** An empty string will remove the closed From filter.
 +
*** A date string in the format YYYY-MM-DD HH:MM:SS.
 +
*** A duration string, to calculate a new datetime from the current datetime:
 +
**** Example: -P1D2H3M4S - This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
 +
**** See the CalculateTimeDuration function documentation in <https://github.com/hornbill/goHornbillHelpers> for more details
 +
** RequestClosedDateTo : A date to filter requests against close date (requests closed before or equal to this date/time). Can take one of the following values:
 +
*** An empty string will remove the Closed Before filter.
 +
*** A date string in the format YYYY-MM-DD HH:MM:SS.
 +
*** A duration string, to calculate a new datetime from the current datetime:
 +
**** Example: -P1D2H3M4S - This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
 +
**** See the CalculateTimeDuration function documentation in <https://github.com/hornbill/goHornbillHelpers> for more details
 +
** RequestReferences : An array of Request References to delete. If requests are defined in this array, then ONLY these requests will be deleted. The other parameters above will be ignored. In the example above, requests with reference CHR00000021 and INC00000003 would be deleted, and no other requests would be removed.
 +
<br>
 +
* CleanAssets : Set to true to remove all Assets (and related entity data) from a Hornbill instance
 +
* AssetClassID: Filter assets for deletetion by a single asset class ID (basic, computer, computerPeripheral, mobileDevice, printer, software, telecoms)
 +
* AssetTypeID: Filter assets for deletion by a single asset type ID - the primary key value of the asset type from the database. Can also be found in the URL when viewing an asset type, 18 in this example: https://live.hornbill.com/yourinstanceid/servicemanager/asset/type/view/18/
 +
<br>
 +
* CleanUsers : Set to true to remove all Users listed in the Users array
 +
** Users : Array of strings, contains a list of User IDs to remove from your Hornbill instance
  
−
To actually do the import, use this:
+
==Running the utility==
  
−
<code>
+
When you are ready to clear-down your request, asset or user records:
−
contactImport_x64.exe
 
−
</code>
 
  
−
== Example ==
+
# Open '''conf.json''' and add in the necessary configuration;
 +
# On Windows:
 +
## Open a Command Line Prompt as Administrator;
 +
# On OSX or Linux:
 +
## Open a Terminal;
 +
# Change Directory to the folder containing the cleaner executable and configuration files E.g. 'C:\hornbill_cleaner\' or '/Users/YourUserID/hornbillCleaner/';
 +
# Run the appropriate command:
 +
#:: Windows: <code>hornbillCleaner.exe -instance=yourinstancename -apikey=yourapikey</code>
 +
#:: OSX or Linux Terminal: <code>./hornbillCleaner -instance=yourinstancename -apikey=yourapikey</code>
 +
<br>
 +
'''PLEASE NOTE''':
 +
* Follow all on-screen prompts, taking care to read all messages provided.
 +
* If any errors are experienced, first review the Trouble Shooting section below. If you are unable to find the answer there, please seek further advice on the [https://forums.hornbill.com Hornbill Forums]
  
−
The following is an example of a successful import.
+
==Post Utility Actions==
−
The CSV file can contain the contacts you wish to import
+
After the Hornbill Clean Utility has been run successfully, you can reset your request reference number or asset ID back to 0:
−
* Ensure the order is as shown in the example otherwise the data may populate incorrect fields
 
−
eg:
 
  
−
[[File:exampleCSV.JPG]]
+
===Resetting Application Auto Values===
  
−
Populate the API key with the Hornbill user's API Key, enter the instance id and username and password.  
+
After the Hornbill Clean utility has been run successfully, you may like to reset the relevant application Auto Values such as the request reference number. This is not essential, more a personal preference.
−
* This is an example of what the Schema.ini file would look like
+
<br>
−
eg:
+
The Application Auto Values can be reset via '''''Hornbill Administration > Home > System > Data > Auto Values'''''.
 +
* itsmRequestsAutoId - Request Reference Number
 +
* itsmAssetAutoId - Asset Auto ID
  
−
[[File:exampleSchema.JPG]]
+
Click the "Reset Counter" button to reset the Auto Value
  
−
* The example JSON file below shows the fields that match the CSV file
+
==Command Line Parameters==
  
−
eg:
+
* -instance - This should be the ID of your instance
 +
* -apikey - This should be an API of a user on your instance that has the correct rights to perform the search & deletion of the specified records
 +
* -file - This is the name of the Configuration file to load. If this parameter is not specified, by default the utility will look for `conf.json'
 +
* -blocksize ''x'' - Where '''x''' is the number of records that should be retrieved and deleted as "blocks". If this parameter is not specified, the default is 3, and under normal circumstances this should not need to be overridden.
 +
* -dryrun - Requires Service Manager build >= 1392 to work with request data. This boolean flag allows a "dry run" to be performed - the tool identifies the primary key for all parent records that would have been deleted, and outputs them to the log file without deleting any records. Defaults to false.
 +
* -justrun - This boolean flag allows you to skip the confirmation prompts when the tool is run. This allows the tool to be scheduled, with the correct configuration defined to delete request records over a certain age for example. Defaults to false.
  
−
[[File:JSON.JPG]]
+
==Logging Overview==
  
−
* This is an example of the actual run using the command prompt AS ADMIN user
+
All logging output is saved to the Hornbill instance, in a log called Hornbill_Clean, and can be accessed from within the Hornbill Administration tool.
−
eg:
 
−
 
 
−
[[File:exampleImport.JPG]]
 
−
 
 
−
== Logging Overview ==
 
−
 
 
−
All Logging output is saved in the "log" directory which can be found in the same location as the executable. The file name contains the date and time the import was run '''''SQL_Contact_Import_2015-11-06T14-26-13Z.log'''''
 
  
 
==Trouble Shooting==
 
==Trouble Shooting==
  
 
===Common Error Messages===
 
===Common Error Messages===
−
 
 
Below are some common errors that you may encounter in the log file and what they mean:
 
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.
+
* ''' ''SQL Query was unsuccessful'' ''' - this occurs when the utility is prevented from running the query to obtain the list of requests to delete. This happens when the system setting ''security.database.allowSqlQueryOperation'' is set to false (OFF). To successfully run the utility, this setting must be set to true (ON). It can be found in Hornbill Administration > System > Settings > Advanced, and filter on "security".
−
* ''' ''[ERROR] Get https://api.github.com/repos/hornbill/goDb2HcontactImport/tags: dial tcp xx.xx.xx.xx:xxx: ........'' ''' - this most likely indicates that you have a HTTP proxy server on your network between the host running the executable and your Hornbill API endpoint. Ensure the http_proxy environment variable is set (See the section on "HTTP Proxies" for more information) and that the proxy is configured to allow this communication.
 
−
* ''' ''[ERROR] Unable to Create User: Invalid value for parameter '[parameter name]': The text size provided (31 characters) is greater than the maximum allowable size of 20 characters for column [column name]'' ''' - the contents of your directory attribute exceed the maximum number of characters that can be placed in the Hornbill database column.
 
−
* ''' ''panic: runtime error: invalid memory address or nil pointer deference [recovered]...'' ''' - this error is suggesting an incorrectly specified attribute in the conf file. Where information is being obtained from a directory attribute, the attribute must be in the following format: ''<nowiki>{{.directoryAttributeName}}</nowiki>''
 
−
* ''' ''[ERROR] Unable to Create User: The value in element <userId> did not meet the required input pattern constraints. at location '/methodCall/params/userId' '' ''' - the user id contains characters that are not allowed. The User Id should be made up of alphanumeric characters. Full stops (.) and underscores (_) are also supported.
 
−
* ''' ''[ERROR] Unable to Update User: Invalid value for parameter '[parameter name]': Error setting value for column '[column name]'. bad lexical cast: source type value could not be interpreted as target'' ''' - this error is indicating that the contents of your directory attribute are in a format that is not compatible with the type of the Hornbill database column. For example, you will get this when trying to place text into a database field that is of type "INT" (accepts integer values only).
 
−
* ''' ''[ERROR] Unable to Load LDAP Attribute: '[LDAP attribute name]' For Input Param: '[Hornbill Parameter name]' '' ''' - When the import utility is unable to load a particular LDAP attribute, this means that the attribute field in your directory does not contain a value. This error will not prevent the user account being created or updated in Hornbill and can be considered more as a warning rather than an outright failure or problem.
 
  
 
=== Error Codes ===
 
=== Error Codes ===
Line 160: Line 166:
 
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number.
 
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number.
  
−
== Scheduling Overview ==
+
<div class="mw-collapsible mw-collapsed" data-collapsetext="Show Less" data-expandtext="Read More" style="width:1050px">
 +
==Change Log==
 +
Click "Read More" to view the Change Log.
 +
<div class="mw-collapsible-content">
 +
 
 +
* 1.11.0 (September 27th, 2019)
 +
** Features
 +
*** Added support to clean-up Configuration Manager Dependency, Impact and Policy records when Assets are deleted
 +
*** Refactored to remove duplicate code
 +
*** Improved log output consistency
 +
 
 +
* 1.10.0 (July 12th, 2019)
 +
** Features
 +
*** Added support to filter assets for deletion by class and type
 +
 
 +
* 1.9.0 (March 15th, 2019)
 +
** Features
 +
*** Replaced large data set calls to entityBrowseRecords2 with paginated queries
 +
*** Removed calls to sysOptionGet and sysOptionSet
 +
*** Added support to remove additional Request BPM timers and events
 +
*** Added support to remove Request SLM events
 +
 
 +
* 1.8.2 (February 1st, 2019)
 +
** Defect Fix
 +
*** Fixed issue with AssetsLinks records not being cleared
 +
 
 +
* 1.8.1 (January 28th, 2019)
 +
** Defect Fix
 +
*** Fixed issue with RequestClosedDateFrom and RequestClosedDateTo returning a query error
 +
 
 +
* 1.8.0 (December 18th, 2018)
 +
** Features
 +
*** Added new filters for requests for deletion:
 +
*** RequestClosedDateFrom : Delete requests that were closed on or after this date/time
 +
*** RequestClosedDateTo: Delete requests that were closed before or at this date/time
 +
 
 +
* 1.7.0 (November 29th, 2018)
 +
** Features
 +
*** Added a "dryrun" CLI input parameter, which when enabled allows users to run the tool without deleting any records, and the primary key for each record that would have been deleted is output to logs for review. Requires Service Manager build 1392 or above to work with request data
 +
*** Added a "justrun" CLI input parameter, which when enabled will skip the initial "do you want to delete the data" prompts. This allows the tool to be run on a schedule
 +
*** Improved logging output:
 +
**** Logs everything on both client and server side now, instead of just server side, to aid in the reviewing of logs
 +
**** At the start of the log, all configuration options (the CLI params AND all options & filters from the config JSON) are now logged for auditing purposes
 +
 
 +
* 1.6.0 (November 1st, 2018)
 +
** Features
 +
*** Added support to delete Board Manager cards when parent Requests are deleted, if Board Manager is installed on the instance at build 100 or greater
 +
*** Improved logging output
 +
 
 +
* 1.5.1 (October 10th, 2018)
 +
** Features:
 +
*** Outputs relevant error message if the instance ID could not be resolved
 +
*** Improved performance and better sharing of HTTP sessions
 +
** Defect fixes:
 +
*** Fixed memory leak
 +
 
 +
* 1.5.0 (September 9th, 2018)
 +
** Features:
 +
*** Added support for supplying a duration string, as well as the existing hard-coded datetime string in the RequestLogDateFrom and RequestLogDateTo configuration parameters. This allows for the calculation of datetimes using the runtime datetime.
 +
*** General tidy-up of the code, split code in to separate Go files for ease of maintenance
 +
 
 +
* 1.4.0 (June 4th, 2018)
 +
** Feature:
 +
*** Replaced Username & Password session authentication with API key
 +
*** Replaced stored username, password and instance URL with command line inputs for instance ID and API Key
 +
 
 +
* 1.3.0 (February 1st, 2018)
 +
** Features:
 +
*** When requests are being deleted, any asset links records are now also deleted.
 +
*** Added ability to delete User records
 +
 
 +
* 1.2.0 (November 24th, 2017)
 +
** Feature:
 +
*** Added ability to delete specific requests using their reference numbers.
 +
 
 +
* 1.1.0 (September 1st, 2017)
 +
** Feature:
 +
*** Requests to be deleted can now be filtered by:
 +
**** Multiple Service IDs
 +
**** Multiple Statuses
 +
**** Multiple Types
 +
**** Requests logged after a specific date & time
 +
**** Requests logged before a specific date & time
 +
*** NOTE - this version requires Hornbill Service Manager Update 1048 or above.
 +
 
 +
* v1.0.6 - 17/07/2017
 +
** Feature:
 +
*** Now supports the deletion of Asset CMDB links when clearing down asset records
 +
 
 +
* v1.0.5 - 01/02/2017
 +
** Defect Fix:
 +
*** Changed the order in which request extended information is deleted, so that workflow tasks can be deleted successfully
 +
 
 +
* v1.0.4 - 12/01/2017
 +
** Features:
 +
** Added code to process the deletion of:
 +
*** Request Workflow instances
 +
*** Request Activities
 +
*** Request timer events
 +
 
 +
* v1.0.3 - 03/08/2016
 +
** Features:
 +
*** Added parameter within configuration file, to specify class of requests to delete 
 +
*** Improved performance of request deletion
 +
*** Improved error output to display
 +
 
 +
* v1.0.2 - 08/06/2016
 +
** Features:
 +
*** Reduced record block size default down to 3
 +
*** Improved logging output
  
−
=== Windows ===
+
* v1.0.1 - 12/05/2016
−
You can schedule .exe to run with any optional command line argument from Windows Task Scheduler.
+
** Features:
−
* Ensure the user account running the task has rights to ldap_import.exe and the containing folder.
+
*** Reduced record block size default down to 20
−
* Make sure the Start In parameter contains the folder where the executable resides in, otherwise it will not be able to pick up the correct path. eg:
+
*** Added flag to allow default block-size to be overridden at runtime
  
−
[[File:Ldap_import_schedule.png]]
+
* v1.0.0 - 10/03/2016
 +
** Initial Release
 +
</div>
 +
</div>
  
−
[[Category:Integration]]
+
[[Category:Administration]]

Revision as of 15:57, 2 October 2019

About the SQL Organisation Import Utility

The utility provides a quick and easy method of uploading and updating organisations from an external data source into Hornbill instance.

Open Source

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

Installation Overview

Windows Installation

  • Download the ZIP archive relevant to your OS and architecture. This contains the cleaner executable, configuration file and license;
  • Extract the ZIP archive into a folder you would like the application to run from e.g. 'C:\organisation_import\'.

Configuration Overview

The configuration of this utility is managed through a JSON file (conf.json), which is supplied with each release: 

   "APIKey": "your_api_key_here",
   "InstanceId": "your_instance_id_here",
   "OrganisationAction": "Create/Update/Both",
   "SQLConf": {
       "Driver": "",
       "Server": "",
       "Database": "",
       "UserName": "",
       "Password": "",
       "Port": ,
       "Encrypt": true/false,
       "OrganizationName": "FIELD 1",
       "Query": ""
   },
   "OrganizationMapping":{
       "organization_name":"FIELD 1",
       "address":"FIELD 2",
       "city":"FIELD 3",
       "state":"FIELD 4",
       "postal_code":"FIELD 5",
       "country":"FIELD 6",
       "industry":"FIELD 7}",
       "phone_number":"FIELD 8",
       "website":"FIELD 9",

"language":"FIELD 10", "custom_1":"FIELD 11", "custom_2":"FIELD 12", "custom_3":"FIELD 13", "custom_4":"FIELD 14", "custom_5":"FIELD 15", "custom_6":"FIELD 16", "custom_7":"FIELD 17", "custom_8":"FIELD 18", "custom_9":"FIELD 19", "custom_10":"FIELD 20", "custom_11":"FIELD 21" 

   }



  • CleanRequests : Set to true to remove all Service Manager Requests (and related entity data) from a Hornbill instance. Filter the requests to be deleted using the following parameters
    • RequestServices : An array containing Service ID Integers to filter the requests for deletion against. An empty array will remove the Service filter, meaning requests with any or no service associated will be deleted
    • RequestStatuses : An array containing Status strings to filter the requests for deletion against. An empty array will remove the Status filter, meaning requests at any status will be deleted
    • RequestTypes : An array containing Request Type strings to filter the requests for deletion against. An empty array will remove the Type filter, meaning requests of any Type will be deleted
    • RequestLogDateFrom : A date to filter requests against log date (requests logged after or equal to this date/time). Can take one of the following values:
      • An empty string will remove the Logged From filter.
      • A date string in the format YYYY-MM-DD HH:MM:SS.
      • A duration string, to calculate a new datetime from the current datetime:
        • Example: -P1D2H3M4S This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
        • See the CalculateTimeDuration function documentation in https://github.com/hornbill/goHornbillHelpers for more details
    • RequestLogDateTo : A date to filter requests against log date (requests logged before or equal to this date/time). Can take one of the following values:
      • An empty string will remove the Logged Before filter.
      • A date string in the format YYYY-MM-DD HH:MM:SS.
      • A duration string, to calculate a new datetime from the current datetime:
        • Example: -P1D2H3M4S This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
        • See the CalculateTimeDuration function documentation in https://github.com/hornbill/goHornbillHelpers for more details
    • RequestClosedDateFrom : A date to filter requests against close date (requests closed after or equal to this date/time). Can take one of the following values:
      • An empty string will remove the closed From filter.
      • A date string in the format YYYY-MM-DD HH:MM:SS.
      • A duration string, to calculate a new datetime from the current datetime:
        • Example: -P1D2H3M4S - This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
        • See the CalculateTimeDuration function documentation in <https://github.com/hornbill/goHornbillHelpers> for more details
    • RequestClosedDateTo : A date to filter requests against close date (requests closed before or equal to this date/time). Can take one of the following values:
      • An empty string will remove the Closed Before filter.
      • A date string in the format YYYY-MM-DD HH:MM:SS.
      • A duration string, to calculate a new datetime from the current datetime:
        • Example: -P1D2H3M4S - This will subtract 1 day (1D), 2 hours (2H), 3 minutes (3H) and 4 seconds (4S) from the current date & time.
        • See the CalculateTimeDuration function documentation in <https://github.com/hornbill/goHornbillHelpers> for more details
    • RequestReferences : An array of Request References to delete. If requests are defined in this array, then ONLY these requests will be deleted. The other parameters above will be ignored. In the example above, requests with reference CHR00000021 and INC00000003 would be deleted, and no other requests would be removed.


  • CleanAssets : Set to true to remove all Assets (and related entity data) from a Hornbill instance
  • AssetClassID: Filter assets for deletetion by a single asset class ID (basic, computer, computerPeripheral, mobileDevice, printer, software, telecoms)
  • AssetTypeID: Filter assets for deletion by a single asset type ID - the primary key value of the asset type from the database. Can also be found in the URL when viewing an asset type, 18 in this example: https://live.hornbill.com/yourinstanceid/servicemanager/asset/type/view/18/


  • CleanUsers : Set to true to remove all Users listed in the Users array
    • Users : Array of strings, contains a list of User IDs to remove from your Hornbill instance

Running the utility

When you are ready to clear-down your request, asset or user records:

  1. Open conf.json and add in the necessary configuration;
  2. On Windows:
    1. Open a Command Line Prompt as Administrator;
  3. On OSX or Linux:
    1.  Open a Terminal;
  4. Change Directory to the folder containing the cleaner executable and configuration files E.g. 'C:\hornbill_cleaner\' or '/Users/YourUserID/hornbillCleaner/';
  5. Run the appropriate command:
    Windows: hornbillCleaner.exe -instance=yourinstancename -apikey=yourapikey
    OSX or Linux Terminal: ./hornbillCleaner -instance=yourinstancename -apikey=yourapikey


PLEASE NOTE:

  • Follow all on-screen prompts, taking care to read all messages provided.
  • If any errors are experienced, first review the Trouble Shooting section below. If you are unable to find the answer there, please seek further advice on the Hornbill Forums

Post Utility Actions

After the Hornbill Clean Utility has been run successfully, you can reset your request reference number or asset ID back to 0:

Resetting Application Auto Values

After the Hornbill Clean utility has been run successfully, you may like to reset the relevant application Auto Values such as the request reference number. This is not essential, more a personal preference.
The Application Auto Values can be reset via Hornbill Administration > Home > System > Data > Auto Values.

  • itsmRequestsAutoId - Request Reference Number
  • itsmAssetAutoId - Asset Auto ID

Click the "Reset Counter" button to reset the Auto Value

Command Line Parameters

  • -instance - This should be the ID of your instance
  • -apikey - This should be an API of a user on your instance that has the correct rights to perform the search & deletion of the specified records
  • -file - This is the name of the Configuration file to load. If this parameter is not specified, by default the utility will look for `conf.json'
  • -blocksize x - Where x is the number of records that should be retrieved and deleted as "blocks". If this parameter is not specified, the default is 3, and under normal circumstances this should not need to be overridden.
  • -dryrun - Requires Service Manager build >= 1392 to work with request data. This boolean flag allows a "dry run" to be performed - the tool identifies the primary key for all parent records that would have been deleted, and outputs them to the log file without deleting any records. Defaults to false.
  • -justrun - This boolean flag allows you to skip the confirmation prompts when the tool is run. This allows the tool to be scheduled, with the correct configuration defined to delete request records over a certain age for example. Defaults to false.

Logging Overview

All logging output is saved to the Hornbill instance, in a log called Hornbill_Clean, and can be accessed from within the Hornbill Administration tool.

Trouble Shooting

Common Error Messages

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

  • SQL Query was unsuccessful - this occurs when the utility is prevented from running the query to obtain the list of requests to delete. This happens when the system setting security.database.allowSqlQueryOperation is set to false (OFF). To successfully run the utility, this setting must be set to true (ON). It can be found in Hornbill Administration > System > Settings > Advanced, and filter on "security".

Error Codes

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

HTTP Proxies

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

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

Change Log

Click "Read More" to view the Change Log.

  • 1.11.0 (September 27th, 2019)
    • Features
      • Added support to clean-up Configuration Manager Dependency, Impact and Policy records when Assets are deleted
      • Refactored to remove duplicate code
      • Improved log output consistency
  • 1.10.0 (July 12th, 2019)
    • Features
      • Added support to filter assets for deletion by class and type
  • 1.9.0 (March 15th, 2019)
    • Features
      • Replaced large data set calls to entityBrowseRecords2 with paginated queries
      • Removed calls to sysOptionGet and sysOptionSet
      • Added support to remove additional Request BPM timers and events
      • Added support to remove Request SLM events
  • 1.8.2 (February 1st, 2019)
    • Defect Fix
      • Fixed issue with AssetsLinks records not being cleared
  • 1.8.1 (January 28th, 2019)
    • Defect Fix
      • Fixed issue with RequestClosedDateFrom and RequestClosedDateTo returning a query error
  • 1.8.0 (December 18th, 2018)
    • Features
      • Added new filters for requests for deletion:
      • RequestClosedDateFrom : Delete requests that were closed on or after this date/time
      • RequestClosedDateTo: Delete requests that were closed before or at this date/time
  • 1.7.0 (November 29th, 2018)
    • Features
      • Added a "dryrun" CLI input parameter, which when enabled allows users to run the tool without deleting any records, and the primary key for each record that would have been deleted is output to logs for review. Requires Service Manager build 1392 or above to work with request data
      • Added a "justrun" CLI input parameter, which when enabled will skip the initial "do you want to delete the data" prompts. This allows the tool to be run on a schedule
      • Improved logging output:
        • Logs everything on both client and server side now, instead of just server side, to aid in the reviewing of logs
        • At the start of the log, all configuration options (the CLI params AND all options & filters from the config JSON) are now logged for auditing purposes
  • 1.6.0 (November 1st, 2018)
    • Features
      • Added support to delete Board Manager cards when parent Requests are deleted, if Board Manager is installed on the instance at build 100 or greater
      • Improved logging output
  • 1.5.1 (October 10th, 2018)
    • Features:
      • Outputs relevant error message if the instance ID could not be resolved
      • Improved performance and better sharing of HTTP sessions
    • Defect fixes:
      • Fixed memory leak
  • 1.5.0 (September 9th, 2018)
    • Features:
      • Added support for supplying a duration string, as well as the existing hard-coded datetime string in the RequestLogDateFrom and RequestLogDateTo configuration parameters. This allows for the calculation of datetimes using the runtime datetime.
      • General tidy-up of the code, split code in to separate Go files for ease of maintenance
  • 1.4.0 (June 4th, 2018)
    • Feature:
      • Replaced Username & Password session authentication with API key
      • Replaced stored username, password and instance URL with command line inputs for instance ID and API Key
  • 1.3.0 (February 1st, 2018)
    • Features:
      • When requests are being deleted, any asset links records are now also deleted.
      • Added ability to delete User records
  • 1.2.0 (November 24th, 2017)
    • Feature:
      • Added ability to delete specific requests using their reference numbers.
  • 1.1.0 (September 1st, 2017)
    • Feature:
      • Requests to be deleted can now be filtered by:
        • Multiple Service IDs
        • Multiple Statuses
        • Multiple Types
        • Requests logged after a specific date & time
        • Requests logged before a specific date & time
      • NOTE - this version requires Hornbill Service Manager Update 1048 or above.
  • v1.0.6 - 17/07/2017
    • Feature:
      • Now supports the deletion of Asset CMDB links when clearing down asset records
  • v1.0.5 - 01/02/2017
    • Defect Fix:
      • Changed the order in which request extended information is deleted, so that workflow tasks can be deleted successfully
  • v1.0.4 - 12/01/2017
    • Features:
    • Added code to process the deletion of:
      • Request Workflow instances
      • Request Activities
      • Request timer events
  • v1.0.3 - 03/08/2016
    • Features:
      • Added parameter within configuration file, to specify class of requests to delete
      • Improved performance of request deletion
      • Improved error output to display
  • v1.0.2 - 08/06/2016
    • Features:
      • Reduced record block size default down to 3
      • Improved logging output
  • v1.0.1 - 12/05/2016
    • Features:
      • Reduced record block size default down to 20
      • Added flag to allow default block-size to be overridden at runtime
  • v1.0.0 - 10/03/2016
    • Initial Release
Retrieved from "https://wiki.hornbill.com/index.php?title=SQL_Organisation_Import&oldid=21387"