How to get the list of Jira projects that has Zephyr data

Ramanji Alugula February 22, 2024

We have a Jira(Data Center) instance with more than 400 projects and we use Zephyr Squad add-on for test case management. Is there any way we can get list of Jira projects that has Zephyr test cases?

I really appreciate your inputs on this.

Thank you !

3 answers

0 votes
Ramanji Alugula February 22, 2024

Thank you @Matt Parks and @Pramodh M for your inputs.

I found the following SQL query to list projects in Jira that have a specific issue type along with the count of issues for each project:

SELECT p.pname AS Project_Name, COUNT(i.id) AS Issue_Count
FROM jiraissue i
JOIN project p ON i.project = p.id
JOIN issuetype it ON i.issuetype = it.id
WHERE it.pname = 'Your_Issue_Type'
GROUP BY p.pname;

Replace 'Your_Issue_Type' with the name of the specific issue type you are looking for.

Matt Parks
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.
February 22, 2024

I'm not a SQL guru, but it appears that query will list how many issues have the particular issue type in each project?

That definitely seems like it would work, although, depending on the size of your organization, the DBAs may not allow even read access into the Jira DB.

Ramanji Alugula March 5, 2024

Yeah ! I agree in some organizations the DBAs may not allow even read access into the Jira DB. Luckily, we have the access to run the select queries.

Actually, I felt it is difficult for me export the issues to CSV and group the projects because we have more than 50k issues associated with Zephyr issue type. With this query we can get the list of issues have particular issue type in each project.

Thank you !

0 votes
Matt Parks
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.
February 22, 2024

Without using a plugin like Power Admin or Structure, you could just run a query to pull back any issues where issuetype = Test. Make sure the Project field is in the filter and then export to a CSV (I would recommend exporting just current fields) and group on that field in the csv.

0 votes
Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 22, 2024

@Ramanji Alugula 

You can analyze what issue types are used in Zephyr and compare that with how many projects have been used in Jira projects to see if Zephyr is used.

The add-ons such as Power Admin will help you get the reports on how many projects are configured with Teststep and Dashboards

Hope this helps

Thanks,
Pramodh

Ramanji Alugula February 22, 2024

@Hi @Pramodh M - Thank you for the response.

We are using "Zephyr Test" as default issue type for Zephyr, and I think it will be difficult to filter the project list if we use any JQL functionalities.

Can you please help me on how to get the reports with Power Admin add-on?

Also, is it possible to get the report with a database query? 

Thanks,
Ramanji

Suggest an answer

Log in or Sign up to answer