Can I query all projects to see which teams have not started their sprint?

Question

As Lean Agile Transformation Admin, I need to easily see which team/project has not started their sprint.  We have 150 teams each with their own project and run in 14 day incremental sprints starting on a Wednesday.  On the Thursday following the sprint, I need to check to see which teams are behind and have not started their sprint.   Is there a way to query for that information?   My psuedo code query would state something like this;    Select distinct project name where active sprint does not contain 2.6  This should get me all active sprints that are not 2.6 which is the current sprint.   Our sprint names are coded by project team Key, Year, Quarter and sprint count for example DLT 2022 Q2.6 so by saying does not contain (or is not in) 2.6 then it should give me the sprints that are outside of this sprint.

Make sense?

Sandra Fuchs · Answer

Thanks G.. .I did use that method to produce a list of issues. And you are right - it was very tedious because it returns all of the issues for the projects in the list.   I sorted by sprint name so that I could tell which issues had the last sprint's name in it (looking for 2.5).    Surely there is a better way!

Do you know if you can utilize a parsing function in JQL?  For example, Mid() or RIght() or Left() Something like this ?

Where Right(opensprint(name),4) = 'Q2.5"

I would hope would return only the issues where the right 4 characters of the open sprint name equal to Q2.5.   Just a thought but I don't know if that exists in JQL or not.

Thanks for your help

G subramanyam · Answer

Hi @Sandra Fuchs welcome to the Atlassian community.

That's a pretty unique use case. Their are 2 options I see in your scenario:

1) Running a JQL: project in ("LIST OF PROJECTS") sprint in openSprints()

Then export the output and filter for project. In this case you will have the projects with open sprints. But please be noted, for the first time, you will need to add all 150 team project names in that JQL ("LIST OF PROJECTS") query. This is very tedious for the first time, but you can save this JQL filter.

2) Use Atlassian market place plugins like:  AIO Reports and Timesheets for Jira OR BigPicture and try.