how to write query in JIRA/Zephy to find list of tests where last execution date of test

Question

Hi Everyone,

I want to see which are the tests in my jira project, that are now stale and have not been executed since 1 jan 2024. My Zql looks like thos:

project = "XYZ" AND cycleName IN ("Team A Regression","Team A Smoke") AND folderName IN ("Folder1","Folder 2") AND executionDate <= "2024-01-01".

The problem with this query is, it gove me the list of tests that were executed before this date and disregards the last date when the test case was executed.

Example: For test123 if the last execution date is 2024-10-10, then i do not want the query to show me this test case. but since test123 was executed before 2024-01-01, it shows up in the result.

I only want to see that are not executed after a certain date

Tuncay Senturk _Snapbytes_ · Answer

There is no single JQL that you can do the trick but here is a possible manual workaround

First, get the list of test cases that have been executed after January 1, 2024.

project = "XYZ" AND cycleName IN ("Team A Regression", "Team A Smoke") AND folderName IN ("Folder1", "Folder 2") AND executionDate > "2024-01-01"

Note the test issue keys from the results. This is the manual step, but you can write a script getting those issue keys.

Next, get the list of all test cases in the specified cycles and folders, regardless of execution date.

project = "XYZ" AND cycleName IN ("Team A Regression", "Team A Smoke") AND folderName IN ("Folder1", "Folder 2")

Since ZQL doesn't support direct subtraction, we'll use the NOT IN operator with the test issue keys.

project = "XYZ" AND cycleName IN ("Team A Regression", "Team A Smoke") AND folderName IN ("Folder1", "Folder 2") AND issueKey NOT IN ("TEST-1", "TEST-2", "TEST-3")

Jyoti Singh_tyep5i · Answer

Thanks Tuncay! Could you please explain with an example how can i do the subtraction?

Tuncay Senturk _Snapbytes_ · Answer

Hi @Jyoti Singh

As far as I know, due to the limitations in ZQL (Zephyr Query Language), there isn't a direct way to query based on the latest execution date of a test case within a single query.

I'm afraid you have to use a workaround like

* Fetch Test Cases Executed After January 1 2024.
* Fetch All Relevant Test Cases in Your Project.
* Subtract the Test Cases (after 1 Jan 2024) from all relevant test cases.