SQL Query to find JiRA Project Admins from Database

Hello Friend,

This is one of my tutorial regarding Jira. Following is regarding the use of SQL query in the Database to find the user admins of the particular project in Jira. It is a simple sql query that includes common clauses that a normal query use. The SQL Query is shown below~

SELECT project.pname, permissionscheme.NAME,
perm_parameter, GROUP_CONCAT(membershipbase.USER_NAME) AS userid
FROM project, nodeassociation, permissionscheme, schemepermissions , membershipbase
WHERE 1
AND nodeassociation.SOURCE_NODE_ID=project.ID
AND nodeassociation.SOURCE_NODE_ENTITY='project'
AND nodeassociation.SINK_NODE_ENTITY='PermissionScheme'
AND nodeassociation.SINK_NODE_ID=permissionscheme.ID
AND schemepermissions.SCHEME=permissionscheme.ID
AND membershipbase.GROUP_NAME=schemepermissions.perm_parameter
AND schemepermissions.PERMISSION IN (23)
AND perm_type IN ('group' , 'user')
AND perm_parameter NOT IN ('jira-administrators' )

GROUP BY pname;

SELECT project.pname, permissionscheme.NAME,

perm_parameter, GROUP_CONCAT(membershipbase.USER_NAME) AS userid

FROM project, nodeassociation, permissionscheme, schemepermissions , membershipbase

WHERE 1

AND nodeassociation.SOURCE_NODE_ID=project.ID

AND nodeassociation.SOURCE_NODE_ENTITY='project'

AND nodeassociation.SINK_NODE_ENTITY='PermissionScheme'

AND nodeassociation.SINK_NODE_ID=permissionscheme.ID

AND schemepermissions.SCHEME=permissionscheme.ID

AND membershipbase.GROUP_NAME=schemepermissions.perm_parameter

AND schemepermissions.PERMISSION IN (23)

AND perm_type IN ('group' , 'user')

AND perm_parameter NOT IN ('jira-administrators' )

GROUP BY pname;

I hope this will help you.

Thanks,
Mohammad Salman Ali.

Leave a Reply Cancel reply