...
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)
ColumnsColumns.
| PROJECTSERVICEDESKS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT | X | Service Desk identifier. |
| PROJECTID | BIGINT | X | JIRA project identifier. |
Examples.
| Code Block |
|---|
| language | sql |
|---|
| title | Service Desk Projects |
|---|
|
SELECT *
FROM SERVICEDESK.PROJECTSERVICEDESKS |
...
(Visit the docs to learn how to create organizations and add customers to them)
Columns.
| ORGANIZATIONS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT |
| Identifier of the organization. |
| SERVICEDESKID | BIGINT | X | Service Desk identifier. |
| NAME | VARCHAR |
| Organization name's. |
...
| ORGANIZATIONUSERS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ORGANIZATIONID | BIGINT | X | Identifier of the organization. |
| USERNAME | VARCHAR |
| User in the organization. |
Examples.
| Code Block |
|---|
| language | sql |
|---|
| title | Organizations Service Desk Projects |
|---|
|
SELECT *
FROM SERVICEDESK.ORGANIZATIONS |
...
(View Using service desk queues, Make queues for your team for more information)
Columns.
| 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. |
...
| Section |
|---|
| Column |
|---|
|
| QUEUEISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| QUEUEID | BIGINT | X | Identifier of the queue. | | SERVICEDESKID | BIGINT | X | Service Desk identifier. | | ISSUEID | BIGINT |
| Issue identifier. |
|
| Column |
|---|
|
| Warning |
|---|
| (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.
| Code Block |
|---|
| language | sql |
|---|
| title | Service Desk Project Organizations |
|---|
|
SELECT *
FROM SERVICEDESK.QUEUES |
...
(View User types and roles for more information)
Columns.
| APPROVALISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT | X | Identifier of the approval. |
| ISSUEID | BIGINT | X | Issue identifier. |
| WORKFLOWSTATUS | VARCHAR |
| Status of the workflow where approval occurs. |
| APPROVALSTATUS | VARCHAR |
| State of approval {APPROVED | REJECTED | null} |
...
| APPROVALUSERS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| APPROVALISSUEID | BIGINT | X | Identifier of the approval. |
| USERNAME | VARCHAR |
| User who performs the approval. |
| DECISION | VARCHAR |
| Decision made by the user {APPROVED | REJECTED | null} |
Examples.
| Code Block |
|---|
| language | sql |
|---|
| title | Get approvals from a issue |
|---|
|
SELECT *
FROM SERVICEDESK.APPROVALISSUES
WHERE issueid = 10216 |
...
| Info |
|---|
|
Request participants follow issue-level security schemes. For example, if an administrator customizes requests so that only reporters can view them, then request participants won't be able to view the request. Administrators can refer to the instructions in Configuring Issue-level Security to update the issue security scheme. |
Columns.
| REQUESTISSUEPARTICIPANTS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| USERNAME | VARCHAR |
| User participant in the issue. |
| ISSUEID | BIGINT | X | Issue identifier. |
Examples.
| Code Block |
|---|
| language | sql |
|---|
| title | Get participants from a issue |
|---|
|
SELECT *
FROM SERVICEDESK.REQUESTISSUEPARTICIPANTS
WHERE issueid = 10216 |
...
Displays information about the status of a request.
The REQUESTISSUESTATUSHISTORY table contains in chronological order in which the status transition taken place.
Columns.
| REQUESTISSUESTATUSHISTORY |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ISSUEID | BIGINT | X | Issue identifier. |
| REQUESTSTATUS | VARCHAR |
| Request status. |
| TIMEEXECUTETRANSITION | TIMESTAMP |
| Instance in which the request was transitioned to the state. It is not the state of the workflow (View Customize the workflow statuses for a request type) |
Examples.
| Code Block |
|---|
| language | sql |
|---|
| title | History of a request status |
|---|
|
SELECT *
FROM SERVICEDESK.REQUESTISSUESTATUSHISTORY
WHERE issueid = 10216 |
...
(View Setting up SLAs for more information)
Columns.
| SLAMETRICS |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| ID | BIGINT | X | Identifier of the metric. |
| NAME | VARCHAR |
| Name of the metric. |
| SERVICEDESKID | BIGINT | X | Service Desk identifier. |
| CUSTOMFIELDID | BIGINT |
| Identifier of the custom field where the value of the metric is stored. |
...
| Section |
|---|
| Column |
|---|
|
| SLAMETRICISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| SLAMETRICID | BIGINT | X | Identifier of the metric. | | ISSUEID | BIGINT | X | Issue identifier. | | COMPLETEDCYCLE | BOOLEAN |
| Indicates whether the approval cycle has been completed, that is, whether the approval has been rejected or approved. | | ISBREACHED | BOOLEAN |
| 'True' if the SLA has breached. | | BREACHEDTIME | TIMESTAMP |
| The instant in time that the SLA breached or will breach. | | ELAPSEDTIME | VARCHAR |
| Time the SLA has been running. | | GOALDURATION | VARCHAR |
| The duration of the SLA goal. | | REMAININGTIME | VARCHAR |
| Time remaining on the SLA before it breaches (if it is running). | | STARTTIME | TIMESTAMP |
| The instant in time that the cycle started. | | STOPTIME | TIMESTAMP |
| The instant in time that the cycle stopped | | ISPAUSED | BOOLEAN |
| 'True' if the SLA cycle is currently paused | | WITHINCALENDARHOURS | BOOLEAN |
| 'True' if the SLA cycle is currently within calendar hours. |
|
| Column |
|---|
|
| Warning |
|---|
| The ISSUEID index is mandatory in queries. For example,
SELECT * FROM SERVICEDESK.SLAMETRICISSUES WHERE SLAMETRICID=2
WARNING: Not enough data to use the JIRA Java API on the table SLAMETRICISSUESSERVICEDESK.
------------------------------------------------------------------------------------------------------------
You have to provide values for some of the following columns:
- SLAMETRICID, ISSUEID
------------------------------------------------------------------------------------------------------------
; SQL statement:
SELECT * FROM SERVICEDESK.SLAMETRICISSUES WHERE SLAMETRICID =2 [0-186] null/0 (Help)
The query should be: SELECT * FROM SERVICEDESK.SLAMETRICISSUES WHERE SLAMETRICID = 2 AND ISSUEID = 10216
|
|
|
Examples.
| Code Block |
|---|
| language | sql |
|---|
| title | Get SLAs from Service Desk projects |
|---|
|
SELECT *
FROM SERVICEDESK.SLAMETRICS |
...
(View Setting up request types for more information)
Columns.
| REQUESTTYPES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| REQUESTTYPEID | BIGINT |
| Identifier of the request type. |
| SERVICEDESKID | BIGINT | X | Service Desk identifier. |
...
| Section |
|---|
| Column |
|---|
|
| REQUESTTYPEISSUES |
|---|
| COLUMN | TYPE | INDEXES |
|
|---|
| SERVICEDESKID | BIGINT | X | Service Desk identifier. | | REQUESTTYPEID | BIGINT | X | Identifier of the request type. | | ISSUEID | BIGINT | X | Issue identifier. |
|
| Column |
|---|
|
| Warning |
|---|
| If it is filtered by the value of the requesttypeid field, it is mandatory to indicate the value for the servicedeskid field. |
| Warning |
|---|
When obtaining the request types for all the issues of a servicedesk project, all the results may not be displayed. This is due to paging problems in the Service Desk API.
If you want to know the request type for all the issues of a project, please use a query like the following: | Code Block |
|---|
| 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.
| Code Block |
|---|
| language | sql |
|---|
| title | Get the types of requests for a service desk project |
|---|
|
SELECT rtd.*
FROM SERVICEDESK.REQUESTTYPEDENITIONS rtd
inner join SERVICEDESK.REQUESTTYPES rt on rtd.id = rt.REQUESTTYPEID
WHERE rt.SERVICEDESKID = 1 |
...