Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

projects and agile boards stored in the database?

Which table connect the agile boards and projects in jira database?

What connects both these- I need to find the projects and corresponding agile boards

This gives the agile board details

SELECT [CARD_COLOR_STRATEGY]
,[ID]
,[KAN_PLAN_ENABLED]
,[NAME]
,[OWNER_USER_NAME]
,[SAVED_FILTER_ID]
,[SHOW_DAYS_IN_COLUMN]
,[SPRINTS_ENABLED]
,[SPRINT_MARKERS_MIGRATED]
,[SWIMLANE_STRATEGY]
,[OLD_DONE_ISSUES_CUTOFF]
,[SHOW_EPIC_AS_PANEL]
FROM [dbo].[AO_60DB71_RAPIDVIEW]
GO

 

This is for projects

SELECT [ID]
,[pname]
,[URL]
,[LEAD]
,[DESCRIPTION]
,[pkey]
,[pcounter]
,[ASSIGNEETYPE]
,[AVATAR]
,[ORIGINALKEY]
,[PROJECTTYPE]
FROM [dbo].[project]
GO

2 answers

1 accepted

0 votes
Answer accepted
Andy Heinzer Atlassian Team Apr 30, 2020

Hi,

I understand that you are taking a closer look at the SQL database for Jira Software server, and you are looking to better understand how these two tables connect to each other.

The short answer is that they do not.  These SQL tables do not have a direct connection to each other.  Agile Boards are not specifically bound to a single project.

The longer answer:  When a board is created in Jira Software, you have to assign it a JQL filter.  This is being referenced in the SAVED_FILTER_ID field in the table AO_60DB71_RAPIDVIEW.  That JQL filter is what determines which Jira issues are expected to appear on that board.  While many of these might default to a format such as

project=xyz order by rank desc

and in turn this will only show you issues from project XYZ, there is no requirement that this board use the project operator when searching JQL.  Hence a board could be showing issues from multiple or all the projects potentially.

You can match the id value in "AO_60DB71_RAPIDVIEW"."SAVED_FILTER_ID" with the searchrequest.id field.  That way you can at least understand better what JQL filter is being used on the board, and what the JQL search is there.  The searchrequest.reqcontent will have the JQL syntax to determine what issues appear on that board.

But again, these projects don't directly connect to a board.  Instead the projects contain Jira issues.  And those issues are getting selected for display on a board based on the JQL filter.  I hope this helps to explain the data structure here of the SQL database a bit better.

Please let me know if this helps, or if you have any followup concerns related to this.

Cheers,

Andy

From DB Table I understood that very well. But which sql statement displays the boards under the proper project?

 

Once again, if you click on a project that have 5 boards. How do you get or with which relation do you achieve this?

 My solution:

  • Connect board table with filter table

"AO_60DB71_RAPIDVIEW"."SAVED_FILTER_ID"=searchrequest.id

  • Connect filter table with project table

project.id=searchrequest.projectid

  • Put it all together

select
project.pname,
boards."NAME",
searchrequest.filtername,
searchrequest.projectid
from "AO_60DB71_RAPIDVIEW" as boards
join searchrequest on searchrequest.id=boards."SAVED_FILTER_ID"
left join project on project.id=searchrequest.projectid
where searchrequest.projectid is not null;

 

but no result :(

Hi @Andy Heinzer,

Following up with the question from @Ahmet Kilic, how is determined the list of boards showing for a particular project. Is it base on the JQL? This list of boards is the one that shows under the name of the project in the Agile boards.

Thanks,

Rodolfo

Andy Heinzer Atlassian Team Jun 11, 2021

I have tried to explain before, boards are not specifically bound to a project.  However in more recent versions of Jira Software, when you first visit a project, frequently you are being directed to an agile board that shows issues in that project.

Hovering over the top icon in the left navbar will show you a list of "Boards in this project".  If this is where you are expecting to see a board, then only if the JQL filter of the board explicitly searches that project will the board using that filter appear here.  So for example if the JQL filter does not contain a project such as something like

issuetype=bugs ORDER BY RANK ASC

Then there might be issues from that project that would appear in that board.  But viewing a default board from a project landing page would not display this board as being "Boards in this project".  So yes, the contents of the JQL filter do matter here.  I do not know if there is a specific SQL query that can be used here to easily determine what appears there since projects can be referred to in JQL by either their project name, project key, or even project id.

Furthermore, the JQL filter itself has to be shared with other users or groups or project roles in order for other users to be able to see the board at all.  By default new filters tend to only be visible by the user that created them.  If the creator has not shared out the filter to be visible by other users, then no one else will be able to see that board anywhere within Jira.

I was also looking to try to do this to determine which of our users need a software license vs. a JSM license, since currently, they're all lumped together, but we need to separate them out.

From what I can figure, you'd need to somehow parse the project key(s) (or even name(s)) from the filters associated with boards, then, assuming you can get either all keys or all names, you could link the results to the project table.

There really ought to be an easier way of doing this, though.


You can start with this (MySQL) query to get the board related filters:

SELECT sr.ID as filterID, sr.filtername_lower, sr.reqcontent as filter, rv.ID as boardID,rv.name as boardName FROM searchrequest as sr
INNER JOIN AO_60DB71_RAPIDVIEW as rv ON sr.ID = rv.SAVED_FILTER_ID
WHERE sr.reqcontent like "%project%"

Good luck on the parsing part, though, if you have a lot of projects and/or filters!

This will not show any filters that cover all projects.  You could possibly do a reverse of the above Where clause for that to get all where not like "%project%".  That's if you wanted to separate them out.

I would really hope, though, that a board doesn't cover every single project you have in your instance, unless it's a very small instance.  But, in that case, you probably wouldn't need to do a db search.

You could also leave off the Where clause altogether, and use something else to parse whether or not a board filter specifies the project.

It's gonna be tough any way you slice it.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira

Admins, notify your Jira instance of system-wide changes with the new admin announcement banner

Hi All! We’re excited to share the launch of an announcement banner that lets Jira site administrators communicate directly to their users across Jira Cloud instance.   📢 Get y...

232 views 11 9
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you