Hi Martti,
Are you able to see any results if you run the below query? We skipped the CMN_SCH_JOB_LOGS table, as in some cases we see that no entries created in it even after trying to run a report from clarity.
select csjr.id CSJR_ID,csjd.id CSJD_ID, csj.id CSJ_ID,
csj.name Job_Name,csjr.start_date,csjr.end_date,
round((csjr.total_execution_time/1000)/60,2) Time_Taken_In_Minutes,
csjr.status_code,
csu.first_name||' '||csu.last_name Run_By,csjr.scheduler_id,
csjr.job_hostname
from
CMN_SCH_JOB_RUNS csjr
inner join CMN_SCH_JOBS csj on csjr.job_id=csj.id
inner join CMN_SCH_JOB_DEFINITIONS csjd on csj.job_definition_id=csjd.id
inner join CMN_LOOKUPS L on l.id=csjd.job_type and l.lookup_type = 'SCH_JOB_TYPE' and l.lookup_code = 'REPORT'
inner join CMN_SEC_USERS csu on csjr.user_id=csu.id
---where csu.user_name='966644'
Regarding Report Library rights, I think you should check the SP for Remove Job Logs and Report Library entries job, which is 'cmn_job_logs_delete_sp' as it has steps to remove those instance rights too. Below is a query made from the same SP. Please try this.
select per.id,csj.name Report_Name,csu.first_name||' '||csu.last_name Run_By,csr.right_type, csr.permission_code
from cmn_sec_assgnd_obj_perm per
inner join cmn_sec_users csu on per.principal_id=csu.id
inner join cmn_sch_jobs csj on csj.id=per.object_instance_id
inner join cmn_sec_right csr on csr.right_id=per.right_id and csr.permission_id=per.permission_id
where per.object_instance_id in
(
select j.id from cmn_sch_jobs j, cmn_sch_job_definitions jd, cmn_lookups l
where l.lookup_code = 'REPORT'
and l.lookup_type = 'SCH_JOB_TYPE'
and jd.job_type = l.id
and j.is_visible = 0
and jd.id = j.job_definition_id
)
order by per.id
Thanks,
Georgy