Hello,
Does anyone know in what table, data concerning report Schedules are stored ? I know you can find some information in the [CMOBJNAMES] table and [CMOBJPROPS2], but their I cannot find the status of the Schedule or when it was last run. I would like to see whether a Schedule is running or not.
Hi ,
Can't you access the cognos administration, ?,
From there you can check schedule information of each and every report., also the information gets stored in particular table., You need to search each and every table, some table are having only 2 to 3 cols of information.
Hi,
The goal is to try to create a Schedule Status page within Cognos Analytics, where you can see which schedules are running atm
It looks like the last run and next run info is in milliseconds on NC_TASKSCHEDULE. Good luck relating this to the schedule on CMOBJPROPS2. You may be better off using the SDK to ETL the data to a database that is designed for reporting.
Hey Dough,
Thanks for the reply!
It feels like this should be possible though, since the information/data is presented within Cognos Administration and should be stored somewhere...
Can't remember where I sourced this information from, but found this in my "handy scripts" folder :P. It provides a list of all scheduled reports and when they were last run. My recommendation would be to work through each table used to get an understanding of the available data.
select
cmobjnames.name as report,
dateadd(millisecond, nc_schedule_queue.run_date % 1000, dateadd(second, nc_schedule_queue.run_date / 1000, '19700101')) as next_run,
dateadd(millisecond, LAST_EXECUTION_AT % 1000, dateadd(second, LAST_EXECUTION_AT/ 1000, '19700101')) as last_run
from
nc_schedule
join
r_taskschedule_schedule
on r_taskschedule_schedule.fk_schedule_id = nc_schedule.pk_schedule_id
join
nc_taskschedule
on nc_taskschedule.pk_taskschedule_id = r_taskschedule_schedule.fk_taskschedule_id
join
r_newsitems_ncobjects
on r_newsitems_ncobjects.fk_ncid = nc_taskschedule.fk_task_id
join
cmobjprops2
on cmobjprops2.taskid = r_newsitems_ncobjects.nid
join
cmobjprops26
on cmobjprops2.cmid = cmobjprops26.cmid
join
cmobjects a
on a.cmid=cmobjprops26.cmid
join
cmobjects b
on a.pcmid=b.cmid
join
cmobjnames
on cmobjnames.cmid=b.cmid
join
nc_schedule_queue
on nc_schedule_queue.schedule_id=nc_taskschedule.pk_taskschedule_id
order by 1
Hello Ashley,
This is really great stuff and thank you for sharing this. I wonder if this somehow can be used to get somekind of running status. For example: if(scheduleStatus = 1 then "running"... etc
Quote from: ashley on 08 Jul 2019 03:56:31 AM
Can't remember where I sourced this information from, but found this in my "handy scripts" folder :P. It provides a list of all scheduled reports and when they were last run. My recommendation would be to work through each table used to get an understanding of the available data.
select
cmobjnames.name as report,
dateadd(millisecond, nc_schedule_queue.run_date % 1000, dateadd(second, nc_schedule_queue.run_date / 1000, '19700101')) as next_run,
dateadd(millisecond, LAST_EXECUTION_AT % 1000, dateadd(second, LAST_EXECUTION_AT/ 1000, '19700101')) as last_run
from
nc_schedule
join
r_taskschedule_schedule
on r_taskschedule_schedule.fk_schedule_id = nc_schedule.pk_schedule_id
join
nc_taskschedule
on nc_taskschedule.pk_taskschedule_id = r_taskschedule_schedule.fk_taskschedule_id
join
r_newsitems_ncobjects
on r_newsitems_ncobjects.fk_ncid = nc_taskschedule.fk_task_id
join
cmobjprops2
on cmobjprops2.taskid = r_newsitems_ncobjects.nid
join
cmobjprops26
on cmobjprops2.cmid = cmobjprops26.cmid
join
cmobjects a
on a.cmid=cmobjprops26.cmid
join
cmobjects b
on a.pcmid=b.cmid
join
cmobjnames
on cmobjnames.cmid=b.cmid
join
nc_schedule_queue
on nc_schedule_queue.schedule_id=nc_taskschedule.pk_taskschedule_id
order by 1
I am curious if this query is still valid, I am gettign an error regarding the NC_SCHEDULE% tables as not existing in the ContentStore database.