/
Service Desk (JIRA Java API)

Service Desk (JIRA Java API)

Jan 25, 2019

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.

Important

The SQL for JIRA Service Desk tables are similar to database views. They have no data and are populated at runtime according to the user performing the query.

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. 

 

Condition in the results

The results obtained are conditioned to the permissions that have the user logged in JSD (JIRA Service Desk) that executes the queries.
For example,
1. The "Service Desk Projects" query will only display the service desk projects to which the user executing the query has access.
2. The "Get requests of the type 'Reciba ayuda sobre las TI' from a service desk project for the logged user" query only will show information of the requests created by the logged in user.

 

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.

PROJECTSERVICEDESKS

PROJECTSERVICEDESKS

COLUMN

TYPE

INDEXES

 

ID

BIGINT

X

Service Desk identifier.

PROJECTID

BIGINT

X

JIRA project identifier.

 

Examples.

Service Desk Projects
SELECT * FROM SERVICEDESK.PROJECTSERVICEDESKS

 

ID

PROJECTID

1

10000

2

10001

 

 

Service Desk Projects join JIRA Projects
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

1

10000

10000

SD

Servi

service_desk

 

null

david

null

43

2

10001

10001

SD2

Service Desk 2

service_desk

 

null

david

 

3

 

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.

ORGANIZATIONS

ORGANIZATIONS

COLUMN

TYPE

INDEXES

 

ID

BIGINT

 

Identifier of the organization.

SERVICEDESKID

BIGINT

X

Service Desk identifier.

NAME

VARCHAR

 

Organization name's.

 

ORGANIZATIONUSERS

ORGANIZATIONUSERS

COLUMN

TYPE

INDEXES

 

ORGANIZATIONID

BIGINT

X

Identifier of the organization.

USERNAME

VARCHAR

 

User in the organization.

 

Examples.


Organizations Service Desk Projects
SELECT * FROM SERVICEDESK.ORGANIZATIONS

 

ID

SERVICEDESKID

NAMEORGANIZATION

1

1

SD - Organization1

2

1

SD - Organization2

3

2

SD2 - Organization

 

 

Users of organizations for Service Desk 1
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

1

1

SD - Organization1

jevans-sd-demo

Jennifer Evans

1

1

SD - Organization1

invitado

Invitado

1

1

SD - Organization1

agrant-sd-demo

Alana Grant

1

1

SD - Organization1

david

David

1

2

SD - Organization2

vwong-sd-demo

Vincent Wong

1

2

SD - Organization2

rlee-sd-demo

Ryan Lee

1

2

SD - Organization2

mdavis-sd-demo

Mitch Davis

 

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.

QUEUES

QUEUES

COLUMN

TYPE

INDEXES

 

ID

BIGINT

X

Identifier of the queue.

SERVICEDESKID

BIGINT

X

Service Desk identifier.

NAME

VARCHAR

 

Name of the queue.

COUNTER

BIGINT

 

Number of issues in the queue.

FIELDS

VARCHAR

 

Columns names that will display in this queue (View Customize the fields on a request type)

QUEUEJQL

VARCHAR

 

JQL that indicates the issues that will appear in the queue.

QUEUEISSUES
COLUMNTYPEINDEXES
QUEUEIDBIGINTXIdentifier of the queue.
SERVICEDESKIDBIGINTXService Desk identifier.
ISSUEIDBIGINT
Issue identifier.

Double index

(QUEUEID, SERVICEDESKID) is a double index and must indicate values for both data in the query. If they are not indicated, you will get a warning when executing the query:

Must indicate some index for QUEUEISSUES table.; SQL statement:
SELECT * FROM SERVICEDESK.QUEUEISSUES [0-186] null/0 (Help)

 

Examples.


Service Desk Project Organizations
SELECT * FROM SERVICEDESK.QUEUES

ID

SERVICEDESKID

NAME

COUNTER

FIELDS

QUEUEJQL

1

1

Todo abierto

24

customfield_10120,issuetype,issuekey,status,summary,created,priority,reporter

project = SD AND resolution = Unresolved ORDER BY "Tiempo hasta resolución" ASC

2

1

Problemas sin asignar

2

customfield_10120,issuetype,issuekey,priority,status,summary,components,created,reporter

project = SD AND assignee is EMPTY AND resolution = Unresolved ORDER BY "Tiempo hasta resolución" ASC

3

1

Asignado a mí

14

customfield_10120,issuetype,issuekey,status,summary,created,priority,reporter

project = SD AND assignee = currentUser() AND resolution = Unresolved ORDER BY "Tiempo hasta resolución" ASC

4

1

↳ Esperándome

0

customfield_10120,issuetype,issuekey,status,summary,created,reporter,duedate

project = SD AND assignee = currentUser() AND resolution = Unresolved AND status in ("Esperando por la asistencia", "In progress", Escalated) ORDER BY "Tiempo hasta resolución" ASC

5

1

Incidentes

5

customfield_10120,customfield_10121,issuetype,issuekey,status,summary,components,created,priority,customfield_10105,customfield_10111,reporter

project = SD AND resolution = Unresolved AND issuetype = Incidente ORDER BY "Tiempo hasta resolución" ASC

6

1

↳ Reported in the last 60 minutes

0

customfield_10120,customfield_10121,issuetype,issuekey,status,summary,components,created,priority,customfield_10105,customfield_10111,reporter

project = SD AND issuetype = Incidente AND created >= -60m ORDER BY created DESC

7

1

↳ Critical

2

customfield_10120,customfield_10121,issuetype,issuekey,status,summary,components,created,priority,customfield_10105,customfield_10111,reporter

project = SD AND resolution = Unresolved AND issuetype = Incidente AND priority in (Highest, High) ORDER BY created DESC

8

1

Solicitudes de servicio

16

customfield_10120,issuetype,issuekey,status,summary,customfield_10001,created,priority,reporter

project = SD AND resolution = Unresolved AND issuetype = "Solicitud de servicio" ORDER BY "Tiempo hasta resolución" ASC

9

1

↳ Vence en 24 horas

5

customfield_10120,issuetype,issuekey,status,summary,customfield_10001,created,priority,reporter

project = SD AND resolution = Unresolved AND issuetype = "Solicitud de servicio" AND due <= 24h ORDER BY due ASC

10

1

Cambiar

2

customfield_10123,issuetype,issuekey,status,summary,components,customfield_10106,customfield_10109,customfield_10110,created,priority,reporter

project = SD AND resolution = Unresolved AND issuetype = Cambiar

11

1

↳ Ready for implementation

0

customfield_10123,issuetype,issuekey,status,summary,components,customfield_10106,customfield_10109,customfield_10110,created,priority,reporter

project = SD AND resolution = Unresolved AND issuetype = Cambiar AND status = "Esperando implementación" ORDER BY created DESC

12

1

↳ Emergency change

0

issuekey,status,summary,components,customfield_10109,customfield_10110,created,priority

project = SD AND resolution = Unresolved AND issuetype = Cambiar AND "Change type" = Emergency ORDER BY created DESC

13

1

Problema

1

customfield_10120,issuetype,issuekey,status,summary,created,reporter,duedate

project = SD AND resolution = Unresolved AND issuetype = Problema ORDER BY priority DESC

14

1

↳ Completed last 30 days

0

customfield_10120,issuetype,issuekey,status,summary,created,reporter,duedate

project = SD AND issuetype = Problema AND status = Completed AND resolved >= -30d

15

1

Resueltos recientemente

0

customfield_10120,issuetype,issuekey,status,summary,created,reporter,customfield_10100

project = SD AND resolved >= -1w ORDER BY resolved DESC

24

1