Find cases whose competence team was

I believe the TeamID is audited so you should be able to query the audit table.

this will give you the case id, the team before and after the change

select parent_id, before_value_string, after_value_string 
from cases_audit 
where field_name = 'team_id'

You can join the teams table to the query by matching the teams.id to the before_value_string and search for just those with the team name in questions.

FrancescaS

Thank you, Francesca,

but I'm looking for a rest API to retrieve this information (preferably all in one shot).

I believe the only REST endpoint that queries the audit log is for retrieving the audit log for a specific record:  /<module>/:record/audit

However, you could build your own REST endpoint to meet your exact needs:

https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_9.0/Integration/Web_Services/REST_API/E… 

FrancescaS