Import data from a database into Insight.

Some use cases could be:

  • Import data from another CMDB like BMC Remedy or Service Now
  • Import CRM data from a proprietary legacy system
  • And so much more....


Import Type Fields

These are the module type specific fields that need to be configured for this import type. All of these fields are mandatory.

Setting

Description

URL

The database connection URL

Driver

The driver class to use to connect to the database.

Place the JDBC driver (*.jar) to the <Jira_INSTALL>/lib folder

Don't forget to restart Jira after adding the driver to lib folder.

Username

The username used to authenticate

Password

The password used to authenticate

You can enter the password for your database.

If you then come to edit your database import later, the password field will be disabled. If you want to edit the password, you have to check the Change value checkbox provided adjacent to the Password field as shown in the screenshot below.

To save your edited password, make sure you keep the Change value checkbox checked until you click on the Next button. If you uncheck it accidentally, the password field is disabled again with its original value retained and the newly entered password will not be saved.

If you want to query the Jira database, you can look into the dbconfig.xml found in Jira_HOME directory, to find out the correct configuration.

Object Type Mapping configuration

NameDescription
Selector

The selector for the Database import is a SQL query. How to build SQL queries depends on the database type.

How to build up the selector (the SQL statement) depends on how you have configured the database connection. Below you will see some examples that will hopefully guide you in the right direction when setting this up.

Database TypeExample
Microsoft SQL Server

select * from <database.name>.<schema-name>.Application

<schema-name> is "dbo" as default so an example could be:

select * from dbname.dbo.Application
Oracle

select * from <schema-name>.Application

If <schema-name> is "Jiraschema" the query will be:

select * from Jiraschema.Application
PostgreSQL
select * from "Application"
MySQL
select * from Application

If you want to query the Jira database, you can look into the dbconfig.xml found in Jira_HOME directory, to find out the correct configuration, such as schema name etc.

Predefined Structure & Configuration

There are no predefined structure and configuration implemented for the Database type

Supported database types

We are using a common framework (jdbc) to connect to the database so we support many database types such as:

Database TypeDriver

Microsoft SQL Server

before Jira 7.5 net.sourceforge.jtds.jdbc.Driver

after Jira 7.5 com.microsoft.sqlserver.jdbc.SQLServerDriver

Oracle

oracle.jdbc.OracleDriver

PostgreSQL

org.postgresql.Driver

MySQL

com.mysql.jdbc.Driver


URL Examples

If you want to connect to the Jira database, you can look into the dbconfig.xml in Jira_HOME to find the correct configuration and URL

Here are some examples of URL configurations for different database types:

Database TypeURL

Microsoft SQL Server

before Jira 7.5 jdbc:jtds:sqlserver://localhost:41972/Jiradb

after Jira 7.5 jdbc:sqlserver://localhost:1433;databaseName=Jiradb

Oracle

jdbc:oracle:thin:@//localhost:1521/ORCL

PostgreSQL

jdbc:postgresql://localhost:5433/Jiradb

MySQL

jdbc:mysql://localhost:3306/Jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=storage_engine=InnoDB


Another connection example:

Connection URL with domain for AD authentication:
URL: jdbc:jtds:sqlserver://10.10.1.1:1433/DB;domain=example.com
Driver: net.sourceforge.jtds.jdbc.Driver


  • No labels