Jira + Steampipe

Jira provides on-demand cloud computing platforms and APIs to plan, track, and release great software.

Steampipe is an open-source zero-ETL engine to instantly query cloud APIs using SQL.

List users in your Jira account:

select
  display_name,
  account_type as type,
  active as status,
  account_id
from
  jira_user;

+-------------------------------+-----------+--------+-----------------------------+
| display_name                  | type      | status | account_id                  |
+-------------------------------+-----------+--------+-----------------------------+
| Confluence Analytics (System) | app       | true   | 557058:cbc04d7be567aa5332c6 |
| John Smyth                    | atlassian | true   | 1f2e1d34e0e56a001ea44fc1    |
+-------------------------------+-----------+--------+-----------------------------+

Documentation

Table definitions & examples →

Get started

Install

Download and install the latest Jira plugin:

steampipe plugin install jira

Credentials

Item	Description
Credentials	Jira requires an API token, site base url and username for all requests.
Radius	Each connection represents a single Jira site.

Configuration

Installing the latest jira plugin will create a config file (~/.steampipe/config/jira.spc) with a single connection named jira:

connection "jira" {
  plugin = "jira"

  # The baseUrl of your Jira Instance API
  # Can also be set with the JIRA_URL environment variable.
  # base_url = "https://your-domain.atlassian.net/"

  # The user name to access the jira cloud instance
  # Can also be set with the `JIRA_USER` environment variable.
  # username = "abcd@xyz.com"

  # Access Token for which to use for the API
  # Can also be set with the `JIRA_TOKEN` environment variable.
  # You should leave it empty if you are using a Personal Access Token (PAT)
  # token = "8WqcdT0rvIZpCjtDqReF48B1"

  # Personal Access Tokens are a safe alternative to using username and password for authentication.
  # This token is used in self-hosted Jira instances.
  # Can also be set with the `JIRA_PERSONAL_ACCESS_TOKEN` environment variable.
  # Personal Access Token can only be used to query jira_backlog_issue, jira_board, jira_issue and jira_sprint tables.
  # personal_access_token = "MDU0MDMx7cE25TQ3OujDfy/vkv/eeSXXoh/zXY1ex9cp"
}

base_url - The site url of your attlassian jira subscription.
personal_access_token - API PAT for self hosted Jira instances. This access token can only be used to query jira_backlog_issue, jira_board, jira_issue and jira_sprint tables.
token - API token for user's Atlassian account.
username - Email address of agent user who have permission to access the API.

Alternatively, you can also use the standard Jira environment variables to obtain credentials only if other arguments (base_url, username and token or personal_access_token) are not specified in the connection:

export JIRA_URL=https://your-domain.atlassian.net/
export JIRA_USER=abcd@xyz.com
export JIRA_TOKEN=8WqcdT0rvIZpCjtDqReF48B1
export JIRA_PERSONAL_ACCESS_TOKEN="MDU0MDMx7cE25TQ3OujDfy/vkv/eeSXXoh/zXY1ex9cp"

Postgres FDW

This plugin is available as a native Postgres FDW. Unlike Steampipe CLI, which ships with an embedded Postgres server instance, the Postgres FDW can be installed in any supported Postgres database version.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_postgres_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/postgres.sh)" -- jira

The installer will prompt you for the plugin name and version, download and install the appropriate files for your OS, system architecture, and Postgres version.

To configure the Postgres FDW, you will create an extension, foreign server, and schema and import the foreign schema.

CREATE EXTENSION IF NOT EXISTS steampipe_postgres_jira;
CREATE SERVER steampipe_jira FOREIGN DATA WRAPPER steampipe_postgres_jira OPTIONS (config '<your_config>');
CREATE SCHEMA jira;
IMPORT FOREIGN SCHEMA jira FROM SERVER steampipe_jira INTO jira;

SQLite Extension

This plugin is available as a SQLite Extension, making the tables available as SQLite virtual tables.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_sqlite_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/sqlite.sh)" -- jira

The installer will prompt you for the plugin name, version, and destination directory. It will then determine the OS and system architecture, and it will download and install the appropriate package.

To configure the SQLite extension, load the extension module and then run the steampipe_configure_jira function to configure it with plugin-specific options.

$ sqlite3
sqlite> .load ./steampipe_sqlite_extension_jira.so
sqlite> select steampipe_configure_jira('<your_config>');

Export

This plugin is available as a standalone Export CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- jira

You can pass the configuration to the command with the --config argument:

steampipe_export_jira --config '<your_config>' <table_name>