Database import
This page is about Assets & Inventory Plugin for Jira DC. Using Cloud? Click here.
This guide explains how to import assets and keep them synchronized with a database or Configuration Management Database (CMDB) tool.
Set up database synchronization
Navigate to Settings > External System Configuration.
Click Add Database Configuration.
Define the connection details.
Name: Enter a recognizable name for this configuration (for example,
Production Database).URL: Provide the database connection URL. Refer to your database provider's documentation for specific URL formats (for example, PostgreSQL:
jdbc:postgresql://localhost:5432/jira8.5.1).Driver: Specify the JDBC driver class required to connect to your database.
Download the appropriate driver JAR file.
Place the JAR file in the Jira installation directory under the
libfolder.Restart Jira for the changes to take effect.
Database | Driver Class | Description |
|---|---|---|
Microsoft SQL Server | com.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/en/connector-j-versions.html for more information. |
PostgreSQL | org.postgresql.Driver |
|
Oracle | oracle.jdbc.OracleDriver |
|
User: Enter the username with access to the database.
Password: Provide the password for the specified user. (Note: Password is encrypted during storage. Anytime you want to edit a configuration, you must enter the password again.)
Cron Expression: Schedule synchronization using a cron expression (for example,
0 0 * * *for daily sync at midnight).Acting Jira User: Select a Jira user with permissions to create, update, and delete assets.
Is Active: Enable or disable synchronization for this configuration.
Test Connection: Verify the connection details by clicking Test Connection.
Click Save.
Asset Type mapping
Click Assets > Settings > External System Configuration.
After creating a database configuration, select its name.
Click Configure to access the mapping options.
Define mapping details.
Name: Assign a descriptive name for this mapping (for example,
Import Servers).Asset Type: Select the Jira asset type you want to populate with data from the database.
Selector: The selector is an SQL query. Write an SQL query to retrieve data from your database table. You can test and refine these queries directly in a database client tool. Data will be fetched from this execution result.
Database | Example |
|---|---|
Microsoft SQL Server | Select * FROM Northwind.dbo.Customers |
MySQL | SELECT * FROM employees |
Oracle | SELECT * FROM products |
PostgreSQL | SELECT * 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.
Consider database schema requirements (tables or views) before defining selectors.
Date Format: Specify date formats (for example, yyyy-MM-dd for 1994-01-17) if your database stores these values in character fields. Refer to https://docs.oracle.com/javase/10/docs/api/java/text/SimpleDateFormat.html for formatting options.
Date Time Format: Specify date-time formats (for example, yyyy-MM-dd hh:mm:ss.s for 1994-01-17 09:00:00.0) if your database stores these values in character fields. Refer to https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html for formatting options.
Delimiter: If multiple values are kept in a character field, a delimiter (for example, comma) will split them while importing. Delimiter provides importing multiple select attributes like Checkbox and List Box - Multiple.
Define Non-Existing Attribute Options: When the imported value is not an option for the attribute, this option allows you to define these values as a new option for attributes like Checkbox, Dropdown List, List Box, List Box - Multiple, and Radio Button.
Ignore Attribute Existence: When an attribute is not defined in the Asset Type Schemes of the selected Asset Type, this option allows the asset to be created without undefined attribute value. Otherwise, the asset will not be created.
Delete Missing Objects: This option provides full synchronization with the database. If enabled, the synchronization system will delete the manually created assets (the assets that are not imported from the database) in the selected Asset Type.
Update Multiple Objects: Identifiers provide the Asset Uniqueness. When multiple assets are found with the identifier search, this option decides whether all found assets will be updated or none of them.
Ignore Empty Values: Prevent empty values from being imported.
Ignore Invalid Values: When 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 has an invalid value.
Is Active: Enable or disable synchronization for this specific Asset Type Mapping. (Note: Asset Name Field Mapping must be defined for activation.)
Click Save.
Field Mapping
This allows you to define how data from your database columns will be used to populate attributes in your assets.
Navigate to Assets > Settings > External System Configuration.
Go to the database configuration you’ve created, and expand it.
Click Create new Field Mapping.
Define mapping details.
Attribute Name: Select the Jira asset type attribute you want to import data for.
If Asset List or Asset List by Form is selected as Attribute, these fields will appear:
Referenced Asset Match Type: Decide which way to fill asset list fields.
If the Match with Query Column is 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 are found in the search result, decide match:
ALL: References all found assets
FIRST: References the assets with the lowest ID
NONE: References none of them
Query/Scripted Column: With this field, it is possible to find the assets that you want to reference with search functions. Each line is accepted as a different search. Each search result will be referenced on the Attribute.
referenceAsset("asset.name", "${asset_name}", "Country", "Turkey", "City", "${city}) referenceAssetByType("Employee", "asset.name", "${asset_name}")To perform an EXACT query, surround the variable with an escaped double quotes: "\"${c_emp_id}\""For example:Search ScriptCurrent row valueAssets to match matchreferenceAssetByType("Employee", "Employee ID", "${c_emp_id}")Emp 001Emp 001Emp 002referenceAssetByType("Employee", "Employee ID", "\"${c_emp_id}\"")Emp 001Emp 001The value between "${}" must be the name of the column of the database from where you get the search parameter.
Example:
The referenceAsset() function in the first line of the Scripted Column retrieves the asset.name and City fields from the "asset_name" and "city" columns in the database.
If the Match with Scripted Column is selected:
Query Column: Choose the column containing the data to import.
Delimiter: Define a delimiter (if needed) to separate multiple values imported into list-based attributes. This field is available for Asset List, Asset List By Form, Checkbox, List Box - Multiple, and Jira Multiple Users attributes.
Concatenator: Use this to combine data from multiple database columns into a single attribute value in Jira.
Is Identifier: This searches if the imported asset exists in the system. If the asset exists, it will be updated. If not, a new asset will be created.
Click Save.