projects and agile boards stored in the database?

[HaRsHaS_ V]

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

[WW · Accepted solution]

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.

[Andy Heinzer · Accepted solution]

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

[Ahmet Kilic]

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 :(

[Rodolfo Romero - Adaptavist]

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]

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.

[John Price]

I just ran into this on 8.20.11 and it's odd that there's no direct association with a project.  Does Jira list "boards in this project" by parsing the filters? 

I looked in entity_property for a link but don't see anything.  

This is how I'm getting "most" of the project names for a clean project, but there's got to be a direct link somehow:

-- Gets a list of Jira agile board filters including owner info.
-- A regex pulls the project name or key out of the filter query. For filters
-- listing multiple projects, it returns the first one.
-- See https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions138.htm#SQLRF06303
select board.name, board.owner_user_name, board.sprints_enabled,
s.id saved_filter_id, s.filtername, s.authorname, 
s.reqcontent querytext, 
length(s.reqcontent) filter_length,
CAST(REGEXP_SUBSTR(REGEXP_SUBSTR(s.reqcontent, '(project)(="| = "| ="| = | in \("?)([^|"]*)', 1, 1, 'i', 3), '(.*?)( ORDER| AND|$)', 1, 1, 'i', 1) AS VARCHAR2(4000)) project_name
FROM AO_60DB71_RAPIDVIEW board
INNER JOIN searchrequest s ON board.saved_filter_id = s.id