Introduction.

SQL for JIRA wraps the JIRA Service Desk Java API for reporting (only GET methods are supported → Read only). Therefore, it is intended for Request-based reports.

All the database tables are under the SERVICEDESK schema. You have to join the PUBLIC schema tables to get JIRA Core/Business data related to issues, projects, versions, etc.

Entity Relationship Diagram (ERD)

The model entity relationship for JIRA Service Desk is as follows:

The tables of the model are divided into the different concepts of JIRA Service Desk. 

Service Desk Project.

Check the Service Desk projects of your instance and extract information from the JIRA project. 
(View JIRA Service Desk Server 3.6 documentation for more information)

Columns.

Examples.

SELECT * 
FROM SERVICEDESK.PROJECTSERVICEDESKS

ID

PROJECTID

10001

SELECT * 
FROM SERVICEDESK.PROJECTSERVICEDESKS sdp 
INNER JOIN 
PROJECTS p ON sdp.projectid = p.id

ID

PROJECTID

ID

KEY

NAME

TYPEPROJECT

DESCRIPTION

CATEGORY

LEAD

URL

COUNTER

10000

10000

service_desk

10001

10001

service_desk

Organizations.

Organizations are groups of customers that you can add to multiple projects. Customers can be members of multiple organizations, and can:

• raise requests in all projects that use the organization.  
• view and search the organization's requests from the My Requests page in the portal.
• receive notifications about the organization's requests.
• share requests with the organization. 

(Visit the docs to learn how to create organizations and add customers to them)

Columns.

Examples.

SELECT * 
FROM SERVICEDESK.ORGANIZATIONS

ID

SERVICEDESKID

NAMEORGANIZATION

SELECT os.servicedeskid, ou.organizationid, os.name, u.username, u.fullname
FROM SERVICEDESK.ORGANIZATIONUSERS  ou
INNER JOIN
SERVICEDESK.ORGANIZATIONS os on os.id=ou.organizationid
INNER JOIN
USERS u on ou.username = u.username
where os.servicedeskid = 1

SERVICEDESKID

ORGANIZATIONID

NAMEORGANIZATION

USERNAME

FULLNAME

Queues.

A queue is a list of issues that are displayed based on a set of criteria. JIRA Service Desk provides some pre-configured queues that sort issues for your team. You can create additional custom queues to further optimize the view for the agents. 

Here's how queues work:

• Service desk administrators can create new queues.
• Agents can view queues but can't create their own custom queues or change the order the queues appear in. Keep this in mind when you design queues.
• You can control the order of issues in a queue by the way you structure the JQL statement you use to set up the queue.

(View Using service desk queues, Make queues for your team for more information)

Columns.

Examples.

SELECT * 
FROM SERVICEDESK.QUEUES

ID

SERVICEDESKID

NAME

COUNTER

FIELDS

QUEUEJQL

SELECT  q.name as Queue, i.key, i.summary, i.assignee, i.reporter, i.assignee
FROM SERVICEDESK.QUEUEISSUES qi
INNER JOIN ISSUES i on qi.issueid = i.id
INNER JOIN SERVICEDESK.QUEUES q on q.id=qi.queueid
WHERE qi.servicedeskId = 1 
AND qi.queueid = 5
AND qi.issueid = i.id
AND q.id=qi.queueid

QUEUE

KEY

SUMMARY

ASSGINEE

REPORTER

CREATED

Approvals.

Some requests might need approval before your team can work on them. For example, a manager might approve leave requests, or an IT manager might approve new system accounts. To require approval for a request, you add an approval to the workflow the request follows. Here’s how approvals fit into the request workflow:

1. A person sends a request via the portal or email.

2. When the request transitions to a status that needs approval, the approver is notified that there's a pending approval.

3. In the customer portal, the approver approves or declines the request.

4. The request transitions to the next status in the workflow, and the customer is notified about any comments that the approver added.

(View User types and roles for more information)

Columns.

Examples.

SELECT * 
FROM SERVICEDESK.APPROVALISSUES 
WHERE issueid = 10216

ID

ISSUEID

WORKFLOWSTATUS

APPROVALSTATUS

SELECT * 
FROM SERVICEDESK.APPROVALUSERS 
WHERE APPROVALISSUEID = 2

APPROVALISSUEID

USERNAME

DECISION

Participants.

Request participants are JIRA Service Desk customers who watch requests. You might add request participants so they can provide more information about a request, or to keep them in the loop about a request's progress.

Request participants are customers and organizations who can view, comment, and receive notifications about the request. Participants receive the same notifications as the reporter, and can turn off notifications at any time. 

Both agents and customers can add and remove request participants. Who they can add as participants depends on the project's Set up customer permissions.  
(View Adding request participants for more information)

Columns.

Examples.

SELECT * 
FROM SERVICEDESK.REQUESTISSUEPARTICIPANTS 
WHERE issueid = 10216

USERNAME

ISSUEID

10216

10216

10216

Request History.

Displays information about the status of a request.
The REQUESTISSUESTATUSHISTORY table contains in chronological order in which the status transition taken place.

Columns.

Examples.

SELECT * 
FROM SERVICEDESK.REQUESTISSUESTATUSHISTORY 
WHERE issueid = 10216

ISSUEID

REQUESTSTATUS

TIMEEXECUTETRANSITION

SLA's.

JIRA Service Desk provides powerful built-in Service Level Agreement (or SLA) management so you can track your team's progress against agreements you've set for customers. An SLA is made up of two settings:

• A time metric, which lets you define how time will be measured for this SLA; and,
• A goal for selected issues, which lets you define a target for the time metric. Different sets of issues can have different goals.

(View Setting up SLAs for more information)

Columns.

Examples.

SELECT * 
FROM SERVICEDESK.SLAMETRICS

ID

NAME

SERVICEDESKID

CUSTOMFIELDID

SELECT sla.name, mi.*
FROM SERVICEDESK.SLAMETRICISSUES mi
INNER JOIN SERVICEDESK.SLAMETRICS sla on mi.SLAMETRICID = sla.id
WHERE issueid = 10216

NAME

SLAMETRICID

ISSUEID

COMPLETEDCYCLE

ISBREACHED

BREACHEDTIME

ELAPSEDTIME

GOALDURATION

REMAININGTIME

STARTTIME

STOPTIME

ISPAUSED

WITHINCALENDARHOURS

Request Types.

Request types let you define and organize incoming issues so your service desk team can more efficiently help your customers.
JIRA Service Desk provides a set of default request types that are configured for basic IT help desk scenarios. You can configure the default request types or add new ones to suit the needs of your customers and team. Request types can be organized into groups to help customers find the request they need on the customer portal.

(View Setting up request types for more information)

Columns.

SELECT jql.ISSUEID, key(jql.ISSUE) as "KEY", rti.SERVICEDESKID, rti.REQUESTTYPEID, rtd.NAME
FROM JQL jql
INNER JOIN SERVICEDESK.REQUESTTYPEISSUES rti on rti.ISSUEID = jql.ISSUEID
INNER JOIN SERVICEDESK.REQUESTTYPEDENITIONS rtd on rtd.ID = rti.REQUESTTYPEID 
WHERE jql.query='project=<xxx>'

Examples.

SELECT rtd.*
FROM SERVICEDESK.REQUESTTYPEDENITIONS rtd
inner join SERVICEDESK.REQUESTTYPES rt on rtd.id = rt.REQUESTTYPEID 
WHERE rt.SERVICEDESKID = 1

ID

ISSUETYPEID

NAME

DESCRIPTION

SELECT rtg.id as groupId, rtg.name as groupname, rtd.id as requestId, rtd.name as requestname, rt.SERVICEDESKID 
FROM SERVICEDESK.REQUESTTYPESGROUPS rtg
INNER JOIN SERVICEDESK.REQUESTTYPES rt on rtg.REQUESTTYPEID = rt.REQUESTTYPEID 
INNER JOIN SERVICEDESK.REQUESTTYPEDENITIONS rtd on rtg.REQUESTTYPEID = rtd.ID
WHERE rt.SERVICEDESKID = 2

GROUPID

GROUPNAME

REQUESTID

REQUESTNAME

SERVICEDESKID

SELECT rti.servicedeskid, rti.requesttypeid, rtd.name as requestname, i.id as issueid, i.key, i.summary, i.reporter
FROM SERVICEDESK.REQUESTTYPEISSUES rti
INNER JOIN ISSUES i on rti.issueid = i.id
INNER JOIN SERVICEDESK.REQUESTTYPEDENITIONS rtd on rti.REQUESTTYPEID = rtd.ID 
WHERE rti.servicedeskid = 1 and rti.requesttypeid = 1

SERVICEDESKID

REQUESTTYPEID

REQUESTNAME

ISSUEID

KEY

SUMMARY

REPORTER