Using Scriptrunner to query Jira's database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
Sometimes, the admin might not have direct access to the database, but if the ScriptRunner app is installed and has a database source configured, we can use it to run some queries!
Scriptrunner is a third-party app and Atlassian does not provide support for it.
For any related customizations, scripts, or development work, please see Atlassian Support Offerings to learn more
Solution
Environment
Any Jira version with the app ScriptRunner installed and a database source configured with it.
Setup ScriptRunner to access your Jira database
Use this guide Local Database Connection to setup your Jira database in the ScriptRunner's app.
How to perform SQL queries
Go to ScriptRunner's admin panel and find the Console tab:
Query templates
Now, using the database name you configured on the Sources (Example: Local), you can use a few example templates to perform the queries, such as these:
Selects:
1
2
3
4
5
6
import com.onresolve.scriptrunner.db.DatabaseUtil
DatabaseUtil.withSql('Local') { sql -> sql.rows("""
select * from project
""")}Updates:
1
2
3
4
5
6
7
8
9
import com.onresolve.scriptrunner.db.DatabaseUtil
DatabaseUtil.withSql('Local') { sql -> sql.executeUpdate("""
UPDATE jiraissue
SET resolution = '10200'
WHERE resolution = '10000'
""")}Inserts:
1
2
3
4
5
6
7
8
import com.onresolve.scriptrunner.db.DatabaseUtil
DatabaseUtil.withSql('Local') { sql -> sql.executeInsert("""
INSERT INTO project (id, pname, description, lead)
VALUES (12345, 'New Project', 'Description of new project', 'projectLeadUsername')
""")}More complex queries work as expected:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import com.onresolve.scriptrunner.db.DatabaseUtil
DatabaseUtil.withSql('Local') { sql -> sql.rows("""
SELECT DISTINCT u.lower_user_name,
u.first_name,
u.last_name,
u.email_address,
d.directory_name
FROM cwd_user u
JOIN cwd_membership m
ON u.id = m.child_id
AND u.directory_id = m.directory_id
JOIN globalpermissionentry gp
ON Lower(m.parent_name) = Lower(gp.group_id)
JOIN cwd_directory d
ON m.directory_id = d.id
WHERE gp.permission IN ('USE','ADMINISTER','SYSTEM_ADMIN')
AND d.active = '1'
AND u.active = '1'
ORDER BY directory_name,
lower_user_name;
""")}Support offerings and disclaimers
It is very important to note that Atlassian does not offers technical support for this application and this guide is presented as is!
For more detailed reference you can use the Adaptavist's official documentation:
Was this helpful?