Database Asset Relationship Import: Difference between revisions
No edit summary |
m (→Configuration) |
||
Line 101: | Line 101: | ||
* <code>RemoveLinks</code> - Boolean true or flalse, defines whether or not to attempt removal of asset relationship records | * <code>RemoveLinks</code> - Boolean true or flalse, defines whether or not to attempt removal of asset relationship records | ||
* <code>RemoveQuery</code> - The basic SQL query to retrieve records for asset relationship removal from the data source | * <code>RemoveQuery</code> - The basic SQL query to retrieve records for asset relationship removal from the data source | ||
* <code>RemoveAssetIdentifier<code> - an object containing details to match asset information returned from the `RemovalQuery`, above, to existing asset and relationship records in your Hornbill instance: | * <code>RemoveAssetIdentifier</code> - an object containing details to match asset information returned from the `RemovalQuery`, above, to existing asset and relationship records in your Hornbill instance: | ||
** <code>Parent</code> - specifies the column from the above `RemoveQuery` that holds the Parent asset unique identifier | ** <code>Parent</code> - specifies the column from the above `RemoveQuery` that holds the Parent asset unique identifier | ||
** <code>Child</code> - specifies the column from the above `RemoveQuery` that holds the Child asset unique identifier | ** <code>Child</code> - specifies the column from the above `RemoveQuery` that holds the Child asset unique identifier |
Revision as of 16:14, 1 September 2021
Database Asset Relationship Import - GO
This tool allows you to insert and update relationships between assets in Hornbill - linked assets in Service Manager, as well as dependencies and impact records in Configuration Manager.
Installation
- Download the ZIP archive containing the import executable relevant for your operating system and architecture
- Extract zip into a folder you would like the application to run from e.g.
C:\assetrelationshipimport\
- Open
conf.json
and add in the necessary configration - Open a Command Line/Terminal as Administrator
- Change Directory to the folder with the executable and config file
C:\assetrelationshipimport\
- Run the command :
- For Windows Systems: goDBAssetRelationships.exe
- For *nix Systems: ./goDBAssetRelationships
Configuration
Example JSON File:
{ "APIKey": "", "InstanceId": "", "DBConf": { "Driver": "mysql", "Server": "127.0.0.1", "Database": "assetdb", "Authentication": "SQL", "UserName": "dbuserid", "Password": "dbpassword", "Port": 3306, "Encrypt": false }, "Query":"SELECT d.h_entity_l_id AS lid, al.h_name AS lname, d.h_entity_r_id AS rid, ar.h_name AS rname, d.h_dependency AS dep, i.h_impact AS imp FROM h_cmdb_config_items_dependency d LEFT JOIN h_cmdb_assets al ON d.h_entity_l_id = al.h_pk_asset_id LEFT JOIN h_cmdb_assets ar ON d.h_entity_r_id = ar.h_pk_asset_id LEFT JOIN h_cmdb_config_items_impact i ON d.h_entity_l_id = i.h_entity_l_id AND d.h_entity_r_id = i.h_entity_r_id", "AssetIdentifier": { "Parent": "lname", "Child": "rname", "Dependency": "dep", "Impact":"imp", "Hornbill": "Name" }, "DepencencyMapping": { "SourceDependency":"HornbillDependency", "Runs":"Runs", "Runs On":"Runs On", "Hosts":"Hosts", "Hosted On":"Hosted On", "Members":"Members", "Member Of":"Member Of" }, "ImpactMapping": { "SourceImpact":"HornbillImpact", "Low":"Low", "Medium":"Medium", "High":"High" }, "RemoveLinks": false, "RemoveQuery":"SELECT d.h_entity_l_id AS lid, al.h_name AS lname, d.h_entity_r_id AS rid, ar.h_name AS rname, d.h_dependency AS dep, i.h_impact AS imp FROM h_cmdb_config_items_dependency d LEFT JOIN h_cmdb_assets al ON d.h_entity_l_id = al.h_pk_asset_id LEFT JOIN h_cmdb_assets ar ON d.h_entity_r_id = ar.h_pk_asset_id LEFT JOIN h_cmdb_config_items_impact i ON d.h_entity_l_id = i.h_entity_l_id AND d.h_entity_r_id = i.h_entity_r_id", "RemoveAssetIdentifier": { "Parent": "lname", "Child": "rname", "Dependency": "dep", "Impact":"imp", "Hornbill": "Name", "RemoveBothSides": true } }
APIKey
- a Hornbill API key for a user account with the correct permissions to carry out all of the required API callsInstanceId
- the Hornbill Instance ID (case sensitive)DBConf
Driver
- the driver to use to connect to the database that holds the asset information:- mssql = Microsoft SQL Server (2005 or above)
- mysql = MySQL Server 4.1+, MariaDB
- mysql320 = MySQL Server v3.2.0 to v4.0
- odbc = ODBC Data Source using SQL Server driver
- When using ODBC as a data source, the
Database
,UserName
,Password
andQuery
parameters should be populated accordingly:- Database - this should be populated with the name of the ODBC connection on the PC that is running the tool
- UserName - this should be the SQL authentication Username to connect to the Database
- Password - this should be the password for the above username
- When using ODBC as a data source, the
Server
- The address of the SQL serverDatabase
- The name of the Database to connect toAuthentication
- The tupe 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
- 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 stringPassword
Password for above User Name - only used when Authentication is set to SQL: for Windows authentication this field can be left as an empty stringPort
SQL portEncrypt
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
Query
The basic SQL query to retrieve asset relationship information from the data sourceAssetIdentifier
- an object containing details to match asset information returned from theQuery
, above, to existing asset records in your Hornbill instance:Parent
- specifies the column from the aboveQuery
that holds the Parent asset unique identifierChild
- specifies the column from the aboveQuery
that holds the Child asset unique identifierDependency
- specifies the column from the aboveQuery
that holds the value of the DependencyImpact
- specifies the column from the aboveQuery
that holds the value of the ImpactHornbill
- specifies which column to use from the Hornbill asset records to match with theParent
andChild
column output from theQuery
. The following values are supported:Name
- This will attempt to match the Hornbill asset using the Name fieldTag
- This will attempt to match the Hornbill asset using the Asset Tag fieldDescription
- This will attempt to match the Hornbill asset using the Description field
DependencyMapping
- an object containing properties to match the dependency column output from theQuery
to the available Hornbill dependency values. The property names should be the dependencies as expected from theQuery
output, and their values should be the matching depencency from your Hornbill instanceImpactMapping
- an object containing properties to match the impact column output from theQuery
to the available Hornbill impact values. The property names should be the impacts as expected from theQuery
output, and their values should be the matching impact from your Hornbill instanceRemoveLinks
- Boolean true or flalse, defines whether or not to attempt removal of asset relationship recordsRemoveQuery
- The basic SQL query to retrieve records for asset relationship removal from the data sourceRemoveAssetIdentifier
- an object containing details to match asset information returned from the `RemovalQuery`, above, to existing asset and relationship records in your Hornbill instance:Parent
- specifies the column from the above `RemoveQuery` that holds the Parent asset unique identifierChild
- specifies the column from the above `RemoveQuery` that holds the Child asset unique identifierDependency
- specifies the column from the above `RemoveQuery` that holds the value of the DependencyImpact
- specifies the column from the above `RemoveQuery` that holds the value of the ImpactHornbill
- specifies which column to use from the Hornbill asset records to match with the `Parent` and `Child` column output from the `RemoveQuery`. The following values are supported:Name
- This will attempt to match the Hornbill asset using the Name fieldTag
- This will attempt to match the Hornbill asset using the Asset Tag fieldDescription
- This will attempt to match the Hornbill asset using the Description field
RemoveBothSides
- Boolean true or false, if the links on both sides of the relationship need to be removed
Execute
Command Line Parameters
file
- Defaults toconf.json
- Name of the Configuration file to loaddryrun
- Defaults tofalse
- Set totrue
and the XML for all XMLMC operations will be dumped to the log file, and any CREATE or UPDATE operations will be skipped. This is to aid in debugging the initial connection information.version
- Defaults tofalse
- when set totrue
, the tool will output its version number before exiting
Testing
If you run the application with the argument dryrun=true then no asset relationships will be created or updated, the XML used to create or update will be saved in the log file so you can ensure the data mappings are correct before running the import.
goDBAssetRelationships.exe -dryrun=true
Scheduling
Windows
You can schedule goDBAssetRelationships.exe to run with any optional command line argument from Windows Task Scheduler:
- Ensure the user account running the task has rights to goDBAssetRelationships.exe and the containing folder.
- Make sure the Start In parameter contains the folder where goDBAssetRelationships.exe resides in otherwise it will not be able to pick up the correct path.
Logging
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 assetRelationships20190925140000.log