Versions Compared

Old Version 12

changes.mady.by.user Tolga Tunca

Saved on

compared with

New Version 13

changes.mady.by.user Tolga Tunca

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Database synchronization definition can be made from External System Configuration tab which is under the Settings menu.


SettingDescription
Name

Specify a name for your configuration.

URL

The URL to connect to the database.

PostgreSQL example: jdbc:postgresql://localhost:5432/jira8.5.1

Driver

The driver class to connect to the database.

Please do not forget to place JDBC driver (*.jar) to <Jira Installation Directory>/lib folder

and restart Jira after placing the driver class.

DatabaseDriver classDescription
Microsoft SQL Servercom.microsoft.sqlserver.jdbc.SQLServerDriver
MySQL

com.mysql.jdbc.Driver

com.mysql.cj.jdbc.Driver

Connector/J version 5.1

Connector/J version 8

See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-versions.html for more information.

PostgreSQLorg.postgresql.Driver
Oracleoracle.jdbc.OracleDriver


UserAn authorized user for the database.
Password

Authorized user password.

Password will be kept as encrypted.

Every time when want to edit a configuration you must enter the password again.

Cron ExpressionDetermine when synchronization should work.
Acting Jira UserA Jira user to perform create, update, delete operations.
Is ActiveSynchronization can be disabled by this option.
Test ConnectionSettings can be tested by clicking Test Connection


Asset Type Mapping

Asset Type Mapping is for the mapping queries with asset types. 

You can reach the Asset Type Mapping configuration page by configuring a created database configuration.

    


SettingDescription
NameSpecify a name for the configuration.
Asset TypeSelect an Asset Type that you want to import.
Selector

The selector is a SQL query. Enter the SQL query which you want to execute when connection established to the database. Data will be fetched from this execution result.

DatabaseExample
Microsoft SQL ServerSELECT * FROM Northwind.dbo.Customers
MySQLSELECT * FROM employees
OracleSELECT * FROM products
PostgreSQLSELECT * FROM "inventory_example"


Additional columns  (not on the table)

You can add constant values to the query or database functions to add calculated values as columns. For example; you can add status and substatus static values to the query where they are not in the table.

select t.*, 'In stock' as c_status, 'Available' as c_substatus from assets.t_service_category t


Notes:

  • Please try SQL queries on a database client first.
  • Database scheme may be needed before table or view.
Date FormatThis field is very necessary when a date value imported from a char field. If you are keeping date values in char fields please specify a date format.
Date Time FormatThis field is very necessary when a date-time value imported from a char field. If you are keeping date-time values in char fields please specify a date-time format.
DelimiterIf multiple values are kept in a char field, delimiter will split them while importing. Delimiter provides importing multiple select attributes like Checkbox, List Box - Multiple.
Define Non-Existing Attribute Options

When imported value is not an option for the attribute this option provides you to define these values as a new option for attributes like Checkbox, Dropdown List, List Box, List Box - Multiple, Radio Button.

Ignore Attribute ExistenceWhen an attribute is not defined in asset type schemes of selected asset type with this option provides the asset created without undefined attribute value. Otherwise, the asset will not be created.
Delete Missing ObjectsThis option provides full synchronization with the database. If this option is enabled, the synchronization system will delete the manually created assets(the assets which are not imported from the database) in selected asset type.
Update Multiple ObjectsIdentifiers(You will see in the Field Mapping configurations) provides the asset uniqueness. When multiple assets found with the identifier search this option decides they all found assets will be updated or non of them.
Ignore Empty ValuesIf imported value is an empty value, with this option you disallow to import them.
Ignore Invalid ValuesWhen an imported value is not invalid for an attribute, the asset will not be created. With this option, the asset will be created without the attribute which have invalid value.
Is Active

When this option is disabled synchronization will not work for this Asset Type Mapping.

Note: Asset Type Mapping can't be activated without Asset Name Field Mapping is defined.


Field Mapping

Field Mapping is for the map attributes with database columns.



SettingDescription
Attribute NameThe attribute that you want to import data.
Query Column

The Column which you want to get data. Columns are fetched from the database.

Please be sure the connection with the database is successful.

Delimiter

It is possible to define different delimiters for different attributes.

This field available for Asset List, Asset List By Form, Checkbox, List Box - Multiple, Jira Multiple Users attributes. 

Concatenator

The concatenator is for the concatenate more than one query columns.

Example: 

Assume that for an Employee asset type, the asset name field needs to imported from two different columns like first_name and last_name

and concatenator is defined as "-". The asset name field will be imported as First-Name.

Is Identifier

The identifier is to search if the imported asset is existing in the system. If asset exist, the asset will be updated.

Otherwise, new asset will be created.


When Asset List or Asset List by Form selected as attribute these fields will be appear
Referenced Asset Match Type

Decide which way to fill asset list fields.

If Query Column selected, the asset name search will be held with the imported value.

(For Example: referenceAssets("asset.name", "imported value"))

When Multiple Asset Found, Match

With the given parameters if multiple assets found in search result, decide match:

      ALL: References all found assets

      FIRST: References the assets with the lowest ID

      NONE: References none of them

Scripted Column

With this field, it is possible to find the assets which you want to reference with search functions.

Each line is accepted as a different search. Each search result will be referenced on attribute.

Code Block
referenceAsset("asset.name", "${asset_name}", "Country", "Turkey", "City", "${city})
referenceAssetByType("Employee", "asset.name", "${asset_name}")


The value between "${}" must be the name of the column of the database from where you get the search parameter.

For example:

     At the first line of the Scripted Column referenceAsset() function will get the asset.name and City fields parameters from the columns asset_name and city of the database.