How to get the list of knowledge spaces linked to each JSM project from the Jira database
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The purpose of this article is to provide a way to get the list all the knowledge base spaces that are linked to JSM (Jira Service Management) projects, by querying the Jira Data base.
Environment
Any Jira Service Management (JSM) on Server/Data Center from the version 4.0.0
Solution
Note that the SQL query below was written and tested on a PostgreSQL Database. For different types of databases, the query might need to be slightly modified (by removing all the double quote characters, for example):
SELECT p.pkey AS "Project Key",
p.pkey AS "Project Name",
vp."PROJECT_ID" AS "Project ID",
vp."NAME" AS "Portal Name",
kb."SPACE_NAME" AS "KB Space",
kb."APPLINK_NAME" AS "Application Link Name"
FROM "AO_54307E_CONFLUENCEKB" kb
JOIN "AO_54307E_SERVICEDESK" sd ON sd."ID" = kb."SERVICE_DESK_ID"
JOIN "AO_54307E_VIEWPORT" vp ON sd."PROJECT_ID" = vp."PROJECT_ID"
JOIN project p ON p.id = vp."PROJECT_ID";Example
Here is an example of output from this query executed in an environment that has 2 JSM projects, with each project being linked to one KB (knowledge base) space:
|Project Key|Project Name|Project ID|Portal Name |KB Space |Application Link Name |
|-----------|------------|----------|---------------|------------|----------------------------------------------|
|ITSD |ITSD |10000 |IT Service Desk|New KB space|Confluence Application used for Knowledge Base|
|SDBASIC |SDBASIC |10100 |SDBASIC |For SD Basic|Confluence Application used for Knowledge Base|