Jira count how many 'Workflows' are associated with an 'Issue Type'

rick_chan October 17, 2024

-- Jira has many different projects.

-- Projects have their own 'Issue Types'.

-- An 'Issue Type' can have different 'Workflows'.

-- Question: How do you count how many 'Workflows' are associated with an 'Issue Type'?

 

-- The attachments 'IssueType.Project.1.Workflow.1' and 'IssueType.Project.2.Workflow.2' show that there are 2 'Workflows' related to an 'Issue Type' called 'Bug'.

-- Note: I have some knowledge of SQL, but I'm not very good at it.

 

-- I wonder if there is a way to count this data from 'jiradb' using SQL (MySQL), like: SELECT issuetype, pname, (workflow_count) FROM ...;

-- Other: Here are some tables info for reference: attachment 'jiradb.related.tables'.

 

-- Attchment Link: https://poe.com/s/OXOOZBW2jQ8I6wO67omH

2 answers

1 accepted

1 vote
Answer accepted
Radek Dostál
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 17, 2024

Well, you could do something like this:

SELECT i.pname, COUNT(wse) AS "associations" FROM issuetype i JOIN workflowschemeentity wse ON wse.issuetype = i.id JOIN jiraworkflows jw ON jw.workflowname = wse.workflow GROUP BY i.pname ORDER BY "associations" DESC LIMIT 10;

 

But I generally side with Marc, this is probably not the right data to look at.

And no I can't use code blocks because this form has been broken for ages.

rick_chan October 17, 2024

Thank you Radek.

0 votes
Marc - Devoteam
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 17, 2024

HI @rick_chan 

There is probably a way to SQL this in the Jira DB, bit I wouldn't if you by mistake make a mistake you could edit the DB and, this will lead to a corrupt Jira instance.

Other question, why would you like to know it?

Of you need more admin insights you can look at a 3rd party app like, optimizer-for-jira 

rick_chan October 17, 2024

Hi @Marc - Devoteam,

-- In summary: the company needs to roughly compile these data and then decide whether to take certain actions.


-- As for SQL, I’m not completely unfamiliar with it (but definitely not an expert), and since all executable code needs to be tested in a testing environment before running in production, there’s no need to worry too much about messing up the Jira instance.


-- So I'll post the issue in the community to see if I can obtain relevant SQL.

Marc - Devoteam
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 17, 2024

Hi @rick_chan 

Your choice off course, but any Jira admin will tell you not to mess with the DB.

"the company needs to roughly compile these data and then decide whether to take certain actions."

I don't get this The flows might be required by teams in a project and not all teams are fit to use the same workflow.

So I don't see why a number of flows would be a cause to action.

rick_chan October 17, 2024

Hi Marc, I understand your doubts, sitll, thank you for your answer.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
9.12.8
TAGS
AUG Leaders

Atlassian Community Events