The custom reporting within SpiraTest or SpiraTeam makes this very easy.

For example, the following query will return back a list of all the test runs in the current project that failed today and previously passed yesterday:

select value R1 from SpiraTestEntities.R_TestRuns as R1 where R1.PROJECT_ID = ${ProjectId}
and R1.EXECUTION_STATUS_ID = 1 and TruncateTime(R1.END_DATE) = TruncateTime(CurrentUtcDateTime())
and R1.TEST_CASE_ID in (
select value R2.TEST_CASE_ID from SpiraTestEntities.R_TestRuns as R2 where R2.PROJECT_ID = ${ProjectId}
and R2.EXECUTION_STATUS_ID = 2 and TruncateTime(R2.END_DATE) = TruncateTime(AddDays(CurrentUtcDateTime(), -1)))

To use this query:

  1. Go to Administration
  2. Click on System > Edit Reports
  3. Create a new report
  4. Enter the name of the report
  5. Select the output formats (e.g. MS-Excel)
  6. Add a new custom section
  7. Enter the query from above
  8. Click the button to create the default template
  9. Save the section and the entire report.
  10. Now you can run this report

One of our customers (thanks Walter) modified this further to allow you to correlate the results by test set:

select value R1.TEST_CASE_ID
from SpiraTestEntities.R_TestRuns as R1, SpiraTestEntities.R_TestRuns as R2 
where R1.TEST_SET_ID = R2.TEST_SET_ID 
and   R1.PROJECT_ID = ${ProjectId} 
and   R2.PROJECT_ID = ${ProjectId} 
and   R1.EXECUTION_STATUS_ID = 1 
and   R2.EXECUTION_STATUS_ID = 2 
and   TruncateTime(R1.END_DATE) = TruncateTime(CurrentUtcDateTime()) 
and   TruncateTime(R2.END_DATE) = TruncateTime(AddDays(CurrentUtcDateTime(), -1))