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

[#1]

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:

• Search Jira like a Boss with JQL
• JQL: The Most Flexible Way to Search Jira 

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. 
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

[vlad_puhach]

Thanks @Tim Keyes

Very useful article

[Jeffrey Gordon]

One should not have to add a Comment to each issue when doing a bulk edit to "force" the synchronization of Jira Align.  Updating anything in the Jira issue (except perhaps Votes or Watchers) should increment the "Last Updated" date on the Jira issue.  That in and of itself should be enough for the synchronization to kick in when Jira Align next runs a sync.

[Séverin MAZOUNIE]

Hi @Tim Keyes

Great article! Do you know if the connector is able to process a query with 1000+ results? I ran such query, Align notified me after 9min that the run is complete, I randomly checked for a few targeted issues if they were imported in Align, successfully so, but I don't know if it only processed and synced the first 1000 issues, as Jira does when queried over 1000 issues, or if it managed to processed all of them..

[Tim Keyes]

Hi @Séverin MAZOUNIE

I hope all is well! Thank you for the question.

The connector is able to process queries that return 1000 + results.

The connector processes queries in batches of 100 and will continue to process the batches until all data has synced.

"Jql query, with data, batch 15"  is a log example when our connector was processing the 15 batch (1500 issues).

Please reach out with any further questions.

Thank you!
Tim

[Séverin MAZOUNIE]

Awesome! Thank you very much for your prompt answer! 

Best, 
Severin