Error rendering macro 'rw-search'

null

This documentation is for Insight for Server/Data Center only.

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

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 username used to authenticate

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