report Schedule information in Content store

[oscarca]

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.

[saumil287]

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.

[oscarca]

Hi,

The goal is to try to create a Schedule Status page within Cognos Analytics, where you can see which schedules are running atm

[dougp]

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.

[oscarca]

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...

[ashley]

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.

Code Select Expand

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

[oscarca]

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

[jackson.eyton]

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.

Code Select Expand

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.