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

Jira and Jira Align Integration: Jira Query Language (JQL) Best Practices

A very useful tool used to search for Jira Software issues is JQL, which stands for Jira Query Language. Jira Software users can use JQL to create database queries to provide faster access to information in Jira. If you have a technical background and are familiar with SQL, then you have a head start because Atlassian's implementation of JQL is similar. To learn more about JQL and how to use it with Jira Software, please consult our Jira documentation:

JQL functions the same way in Jira Align as it does with a Jira Software Advanced Search--it allows you to create custom queries that search for data in Jira Software. When a JQL query is used in Jira Align, it returns data directly from Jira Software and performs a one-way sync to Jira Align (i.e., data is pulled from Jira into Jira Align, but not pushed from Jira Align into Jira). For example, you can use a JQL query to search for a specific sprint in Jira Software, and have the found sprint added to Jira Align's backlog and/or Sprint grid.

The primary use case for JQL queries is to search for legacy data in Jira Software and have it pulled into Jira Align. For example, let's suppose Jira Software was integrated with Jira Align via the connector on a specific date, such as 01/01/18. In this case, Jira Software and Jira Align data has been syncing since the integration date, but what if you wanted to sync data that was created before the integration date, for example from the year 2017? You would need to create a JQL query that searches for your target data in Jira Software and syncs it with Jira Align.

Best Practice: We recommend that you build the query statement first in Jira using a Jira Advanced Search, and test it to make sure it returns the target data. Query strings are easier to build directly in Jira, as Jira will suggest operators and spaces to use in the string. Once tested, the string can be cut and pasted into the Jira Integration page of Jira Align to conduct a search.

Follow these steps to search for and sync legacy Jira Software data with Jira Align:

  1. Make sure you have performed all of the prerequisites and followed instructions for Jira Setup, so that sprints and other work items are correctly mapped from Jira Software to Jira Align.
  2. In the Nav menu, select Administration. Under the Connectors section of the menu, select Jira Settings; the Jira Integration Settings page displays.
  3. Click the Jira Integration tab. image.png
  4. In the Projects field select the Projects that you would like to query.  If you do not select any Projects all integrated Projects will be queried.
  5. In the Jira Search Query field, type or paste your JQL string. For example, to find sprint 35 string would be sprint = 135.

    Note: When using a target date range in your query, you must use one of the following supported date formats: 'yyyy/MM/dd HH:mm',  'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd'. You may also use a period format, e.g., '-5d', '4w 2d'.
  6. In the Order by field, enter your JQL string that dictates how the returned data is sorted. For example, to order by created date ascending, your string would be "order by created ASC".  This field should be left blank if the order data is synchronized does not matter.
  7. In the lower-left of the panel is a Boards to sync field and a drop-down list with Yes/No options for Run Board, Sprint and Fix Version sync along with the search query; select the Yes to run a full sync concurrently with the search on the selected Boards, and No if you do not want to run a full sync.  If you select Yes with no Boards selected then all Boards will be queried.  It is recommended to select No for Run Board, Sprint and Fix Version sync along with the search query and let the connector run its routine Board syncs as per the timer on Administration > Jira Settings as selecting yes will make the custom JQL take significantly more time.  Yes should only be selected when you are looking to also immediately query associated boards to the data you are pulling in.  
  8. Click the Run button in the lower-right of the panel.
  9. Refresh the page after several minutes until you get a notification to the left of the Run button that indicates the run is complete, for example, Last Run: 11/21/2018 10:30:02 AM.
  10. Verify that your target data has been successfully pulled into Jira Align. 

*An Alternative to the custom JQL inside of Jira Align is utilizing a Bulk Edit in Jira.  The Jira bulk update is described here:

https://confluence.atlassian.com/jiracoreserver073/editing-multiple-issues-at-the-same-time-861257342.html

The main reason to utilize a Bulk Edit in Jira instead of a custom JQL in Jira Align is that the custom JQL in Jira Align will lead to additional API load on the integration while the Bulk Edit in Jira will send updates to Jira Align during Jira Align's routine queries for issues.  The Jira Align custom JQL functionality also does not allow multiple custom JQLs to be queued in a row, while you can make multiple Bulk Edits in Jira.  It is recommended to Bulk Edit the issues in Jira with a Comment such as "Force Sync to Jira Align: Please Ignore" in order to flag Jira issues as updated for the integration to pick them up and send the updates to Jira Align.

It is highly recommended to disable email notifications for the update to avoid any necessary spam to users:

Disable notifications for bulk operations - You can disable mail notifications for a particular bulk operation by deselecting the Send Notification checkbox in the bulk operation wizard. For this option to be available, you must be an administrator or project administrator of all the projects associated with your selected issues. 

If your organization has a workflow enabled to watch issues after a bulk update you can utilize the following steps to stop watching any bulk updated issues:

To stop watching multiple issues: 

  1. Choose the Jira icon ( or ) > Issues and filters.
  2. Select Search issues.
  3. Click more (•••) at the extreme top-right of the page and choose Bulk Change all <n> issues.
  4. Select the issues you'd like to perform the bulk operation on, and click Next.
  5. Select Stop Watching Issues, and click Next.
  6. Review your bulk changes and click Confirm

1 comment

Thanks @Tim Keyes

Very useful article

Like Tim Keyes likes this

Comment

Log in or Sign up to comment
TAGS
Community showcase
Published in Jira Align

Virtual/Remote PI Planning Best Practices and FAQ Table of Contents

Due to recent challenges of most companies having to move to virtual and remote PI planning events, the Jira Align Solutions Architects wanted to start to capture some best practices and FAQ Table of...

3,579 views 3 31
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