Custom Form Records Audit SQLs

Audit by time interval

select
  r.record_id as "Record ID", 
  f.caption as "Form Name", 
  concat(u.first_name, concat(' ', u.last_name)) as "User", 
  r.update_time as "Update Time",
  r.activity_status as "Status"
from
  catissue_form_record_entry r
  inner join catissue_form_context fc on fc.identifier = r.form_ctxt_id
  inner join catissue_user u on u.identifier = r.updated_by
  inner join dyextn_containers f on f.identifier = fc.container_id
where
  f.deleted_on is null and
  fc.deleted_on is null and
  r.update_time between :startTs and :endTs
order by
  r.update_time desc

Replace :startTs and :endTs by start and end date of the interested time interval. For example: To obtain record IDs for Q1 of CY 2017, use startTs='2017-01-01' and endTs = '2017-03-31'

Audit for specific user in a specified time interval

select
  r.record_id as "Record ID", 
  f.caption as "Form Name", 
  concat(u.first_name, concat(' ', u.last_name)) as "User", 
  r.update_time as "Update Time",
  r.activity_status as "Status"
from
  catissue_form_record_entry r
  inner join catissue_form_context fc on fc.identifier = r.form_ctxt_id
  inner join catissue_user u on u.identifier = r.updated_by
  inner join dyextn_containers f on f.identifier = fc.container_id
where
  f.deleted_on is null and
  fc.deleted_on is null and
  u.login_name = :loginName and
  r.update_time between :startTs and :endTs
order by
  r.update_time desc

Replace :startTs and :endTs by start and end date of the interested time interval. Replace :loginName by user login ID whose activity log is being reviewed.

Audit for a specific entity type and user in a specified time interval

select
  r.record_id as "Record ID", 
  f.caption as "Form Name", 
  concat(u.first_name, concat(' ', u.last_name)) as "User", 
  r.update_time as "Update Time",
  r.activity_status as "Status"
from
  catissue_form_record_entry r
  inner join catissue_form_context fc on fc.identifier = r.form_ctxt_id
  inner join catissue_user u on u.identifier = r.updated_by
  inner join dyextn_containers f on f.identifier = fc.container_id
where
  f.deleted_on is null and
  fc.deleted_on is null and
  fc.entity_type = 'Participant' and
  u.login_name = :loginName and
  r.update_time between :startTs and :endTs
order by
  r.update_time desc

Replace :startTs and :endTs by start and end date of the interested time interval. Replace :loginName by user login ID whose activity log is being reviewed.

Valid values for entity_type are documented below: Participant, SpecimenCollectionGroup, Specimen, SpecimenEvent