Revision as of 15:58, 18 November 2021

About the Hornbill Clean Utility

The utility provides a quick and easy method of removing requests, assets or users from a specified Hornbill instance.

WARNING

This utility permanently deletes request, asset or user records from a specified Hornbill instance, and records of entities that are associated to the deleted requests/assets. It is primarily intended to be used only by an administrator of a Hornbill instance at the appropriate stage of the switch-on process, to remove demonstration and test data prior to go-live.

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:\hornbill_cleaner\'.

Configuration Overview

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

       "CleanRequests": true,
       "RequestServices":[
               1,
               2,
               3
       ],
       "RequestStatuses":[
               "status.open",
               "status.cancelled",
               "status.closed",
               "status.resolved"
       ],
       "RequestTypes":[
               "Incident",
               "Service Request"
       ],
       "RequestLogDateFrom":"2016-01-01 00:00:00",
       "RequestLogDateTo":"-P1D2H3M4S",
       "RequestClosedDateFrom":"2016-01-01 00:00:00",
       "RequestClosedDateTo":"2018-01-01 00:00:00",
       "RequestReferences":[
               "CHR00000021",
               "INC00000003"
       ],
       "CleanAssets": false,
       "AssetClassID": "",
       "AssetTypeID": 0,
       "AssetFilters": [
                       {
                               "ColumnName": "h_name"
                               "ColumnValue": "YourAssetName",
                               "Operator": "Equals",
                               "IsGeneralProperty": true
                       }
       ],
       "CleanUsers": true,
       "Users":[
               "UserIDOne",
               "UserIDTwo"
       ],
       "CleanServiceAvailabilityHistory": false,
       "ServiceAvailabilityServiceIDs": [],
       "CleanContacts": true,
       "ContactIDs": [
               5,
               8,
               13,
               21
       ],
       "CleanOrganisations": true,
       "OrganisationIDs": [
               34,
               55
       ],
       "CleanSuppliers": true,
       "SupplierIDs": [
               89,
               144
       ],
       "CleanSupplierContracts": true,
       "SupplierContractIDs": [
               "C20210700233",
               "C20210700377",
       ],
       "CleanEmails": false,
       "EmailFilters": {
               "FolderIDs": [
                       1,
                       7,
                       18
               ],
               "RecipientAddress": "",
               "RecipientClass": "",
               "ReceivedFrom": "2016-01-01 00:00:00",
               "ReceivedTo": "2017-01-01 00:00:00",
               "Subject": "%out of office%"
       }

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.
- KeepRequestsCancelBPTasks : a boolean (defaulting to false) which if set to true will NOT actually delete the selected requests, but will cancel the Business Process Workflow and any Tasks connected to the request.

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/
AssetFilters: Array of filters to apply to the query when returning assets to delete. Each object in the array should contain:
- ColumnName: The name of the column in the assets general or extended table;
- ColumnValue: The value to filter by;
- Operator: The operator to apply, can be one of:
  - Empty - column is null or an empty string;
  - Equals - column equals the provided value;
  - NotEquals - column does not equal the provided value;
  - Greater - column value is greater than the provided value;
  - Less - column value is less than than the provided value;
  - LastXDays - date columns, value in the last X days where X is provided in ColumnValue;
  - LastMonth - date columns, value in the last month;
  - PreviousMonth - date columns, value in the previous month;
  - ThisMonth - date columns, value this month;
  - LastWeek - date columns, value last week
  - Yesterday - date columns, value yesterday;
  - Today - date columns, value today;
  - Before - date columns, value before the datetime value provided in ColumnValue;
  - After - date columns, value after the datetime value provided in ColumnValue;
  - BeforeXDays - date columns, before the number of days provided in ColumnValue;
  - Regex - column matches the regular expression provided in ColumnValue;
  - NotRegex - column doesn't match the regular expression provided in ColumnValue;
- IsGeneralProperty: If true, the column exists in the general assets table. If false, the column is in the extended assets table;

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
CleanServiceAvailabilityHistory - Set to true to remove the Service Availability History records for all services listed in the ServiceAvailabilityServiceIDs array
- ServiceAvailabilityServiceIDs: Array of integers, contains a list of all Service IDs whose Availability Historty records should be deleted
CleanContacts - Set to true to remove the Contacts listed in the ContactIDs array
- ContactIDs: Array of integers, contains a list of all Contact IDs that should be deleted
CleanOrganisations - Set to true to remove the Organisations listed in the OrganisationIDs array
- OrganisationIDs: Array of integers, contains a list of all Organisation IDs that should be deleted
CleanSuppliers - Set to true to remove the Suppliers listed in the SupplierIDs array, and all associated records
- SupplierIDs: Array of integers, contains a list of all Supplier IDs that should be deleted
CleanSupplierContracts - Set to true to remove the Supplier Contracts listed in the SupplierContractIDs array, and all associated records
- SupplierContractIDs: Array of strings, contains a list of all Supplier Contact IDs that should be deleted
CleanEmails - Set to true to remove any email records that match the supplied filters
- EmailFilters - The list of conditions to filter the email records for deletion by:
  - FolderIDs - MANDATORY - Array of integers, to contain a list of Folder IDs to filter the email records by
  - RecipientAddress - Deletes emails from this recipient email address
  - RecipientClass - Deletes emails from this recipient email class. Can be one of:
    - unknown
    - to
    - cc
    - bcc
    - from
    - replyTo
    - returnReceiptTo
  - ReceivedFrom - Date/time field, delete all emails with a date/time stamp greater than or equal to this value
  - ReceivedTo - Date/time field, delete all emails with a date/time stamp less than or equal to this value
  - Subject - Filter emails by this subject. Supports % wildcard characters

Running the utility

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

Open conf.json and add in the necessary configuration;
On Windows:
1. Open a Command Line Prompt as Administrator;
On OSX or Linux:
1. 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: 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.

PLEASE NOTE: Only reset the auto values for the entities you have cleared down:

itsmRequestsAutoId - this auto value is responsible for generating request reference numbers (only reset this if you have deleted all your requests).
itsmAssetAutoId - this auto value generates the id for new asset records (only reset this if you have deleted all your asset records).

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.

API Key Rules

This utility uses (API keys):

admin:getApplicationList
admin:userDelete
bpm:processCancel
bpm:processDelete
data:entityBrowseRecords2
data:entityDeleteRecord
data:entityGetRecord
data:getRecordCount
data:queryExec
task:taskCancel
task:taskDelete
time:timerDelete
time:timerEventDelete
system:logMessage
apps/com.hornbill.boardmanager/Card:removeCard
apps/com.hornbill.core/Task:getEntityTasks

Troubleshooting

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.

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

https://files.hornbill.com/instances/INSTANCENAME/zoneinfo - Allows access to lookup your Instance API Endpoint
https://files.hornbill.co/instances/INSTANCENAME/zoneinfo - Backup URL for when files.hornbill.com is unavailable
https://eurapi.hornbill.com/INSTANCENAME/xmlmc/ - This is your Instance API Endpoint, eurapi can change so you should use the endpoint defined in the previous URL
https://api.github.com/repos/hornbill/asset-rel-import/tags - Allows the utility to self-update. Optional

Change Log

Click "Read More" to view the Change Log.

1.11.1 (February 12th 2020)
- Change
  - Updated system & BPM event timer delete order to remove unnecessary errors in 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

Difference between revisions of "Hornbill Clean Utility"