Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.
 

Aggregate List of Test Cases Edited After A Specific Date


#1

Hello,

I reviewed the api and didn’t notice any call that I could use to help me resolve this request.

What i’d like to aggregate is 2 things:

[list=]
[
]All test cases that have been edited between 2 dates[/]
[
]List of all newly created test cases after a certain date[/*]
[/list]

Is this possible? If so, can someone please point me into the right direction? Is t


#2

Hello,

Thanks for your posting. There is currently no API function that supports this but if you are using the download version of TestRail, you could:

a) Access the database directly
b) Add custom methods to the API (in a separate controller)

I’m happy to explain the details if you are interested.

Regards,
Tobias


#3

Hi,

Thanks for the help. I have a additional questions based on your reply. Can you elaborate further on part b? Are you suggesting i use a combination of the existing api calls in my own method?


#4

Hello,

b) basically means creating a small custom API that would query the database and then returns the data in the same format and with the same calling conventions as the standard API of TestRail. This would basically involve copying the mini API files, removing most code and adding new methods for your requirements (named e.g. get_cases_edited_between and get_cases_since). Do you have any experience with PHP and web development or a colleague who could help with this? We are happy to help in case you have any questions, of course.

Option a) would be easier to implement (basically just two SQL queries) but may not be possible if you don’t have direct access to the database.

I hope this helps and please let me know in case you have any further questions.

Regards,
Tobias


#5

I have java, js experience. We do have direct access to the db so writing a simple SQL script should suffice. With that said, I am interested in learning more about the mini API files. Can you point me to some documentation? I assume the API files are written in PHP? I have no experience in it but I think I can manage?


#6

You can take a look at the API files here (yes, the API is written in PHP):

http://code.gurock.com/p/testrail-miniapi/downloads/

The relevant SQL queries would basically be:

a)

SELECT cases.* FROM cases JOIN case_changes ON case_changes.case_id = cases.id WHERE case_changes.created_on BETWEEN ? and ?

b)

The ? are meant as placeholders for the actual dates/timestamps and should be in Unix timestamp format (seconds since 1/1/1970).

I hope this helps!

Regards,
Tobias


#7

Thank you for your help. I was able to get it working.

Do you think this could become implemented in the future? This is our current usecase:

  1. Create test suite / cases
  2. Develop automation framework based on step 1.

How can I ensure my automated tests are up to date? If I could populate a list of the tc’s that have changed on some iterative basis, I can update my automation. I am a little surprised this hasn’t been brought up before, or perhaps most just access the db directly.


#8

Sorry for the late response, I wasn’t notified about the update in this post. A long(er) term goal is to add a generic and property-based search functionality to TestRail (mainly for the UI but also maybe for the API) which would support such kind of queries. I would recommend using the custom SQL queries to implement this in the meantime.

I hope this helps and please let me know in case you have any further questions.

Regards,
Tobias


#9

I know this is an old thread but I have a related question. I am quering the tables directly in SQL and I have noted if I I do a pure

select * from Cases where created on >= date (or created_on >= and created_on <= date

I get alot of results. With many being copied (is_copy > 0). If I add

and is_copy = 0

I get a much more reasonable number.

It almost seems that everytime a case is updated, a new record is written into the case table. This makes the results not to be accurate as if I add in “is_copy = 0” then I will drop any cases that ARE copied to a new project/section. If I remove it - then I get al of the updates thru the days. I want the cases created and copied to a new procect/section alone between dates.

How can I get the information I would like to provide to my boss?

Thanks,

Brian


#10

Hello Brian,

Thanks for your posting. When TestRail closes a test runs, it creates internal copies of the test cases and those copies have the is_copy bit set to 1 (and the copyof_id set to the original test case). Test cases of test suites always have the is_copy bit set to 0 and you would need need to include this in your query (AND is_copy = 0 as you mentioned).

If you want to include the test cases for a specific project or test suite only, you can do this as follows:

SELECT * FROM cases WHERE .. AND suite_id = ?

Or:

SELECT * FROM cases JOIN suites ON cases.suite_id = suites.id WHERE .. AND project_id = ?

I hope this helps!

Regards,
Tobias


#11

Thanks Tobias - that explains the use of the is_copy field for me.


#12

You are welcome, Brian!

Regards,
Tobias


#13

Hi,

I know this is a very old thread, but has anything changed since 3 years ago? I would love to use the api to grab data after a certain date instead of grabbing everything each time without hosting my own database.


#14

Hi Celestine,

Thanks for your reply! Yes, the API would now support request filters that would allow you to retrieve test cases that have been updated after/before or created after/before a specific date. You can view the full list of request filters (just expand the Request filters section) as well as API request examples on our website here:

http://docs.gurock.com/testrail-api2/reference-cases#get_cases

Hope this helps!

Regards,
Marco