Cognos Analytics

 View Only
Expand all | Collapse all

Create a report from the content store db providing details of schedules

  • 1.  Create a report from the content store db providing details of schedules

    Posted Fri August 27, 2021 01:12 PM
    Hi everybody,
    I need to create a Cognos report from the content store database which will provide details of the scheduled jobs and reports including:
    1. Name
    2. Location
    3. Type (Job, Report, etc)
    4. Step Name
    5. Step Location
    6. Schedule Status
    7. Schedule Owner
    8. Next Run Date/ Time
    9. Trigger (if exists)
    10. Last Run Date
    11. Last Run Duration
    I am having trouble finding the Last Run Duration and I am hoping there is someone who can assist. I can't use the Audit database because it doesn't have all the information that I need so I have to source the report from the content store

    Thanks very much in advance
    Regards,
    Christine

    ------------------------------
    Christine Beswick
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Create a report from the content store db providing details of schedules

    Posted Mon August 30, 2021 02:06 PM
    No guarantees, but have a look at content_store.p_job. It has a startdt and a lastcompletiondt. Both are timestamps, so you should be able to calculate the duration. Good luck.

    ------------------------------
    Michael Walter
    Business Analyst
    TX HHSC
    Austin
    ------------------------------

    Original Message


  • 3.  RE: Create a report from the content store db providing details of schedules

    Posted Tue August 31, 2021 12:39 AM
    Hi Michael,

    Thanks very much for your suggestion. Can you let me know where I can find content_store.p_job? I can't seem to find it anywhere.

    Regards,

    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 4.  RE: Create a report from the content store db providing details of schedules

    Posted Tue August 31, 2021 06:16 AM
    Hi Christine,

    Try the query below. It will give you a good starting point. I have added this query in a Framework manager package.
    In this way I can create Cognos reports with it.

    select ob2.cmid
    , c.name as classname
    , n.name as objectname
    , JL.JobStepName
    , JL.JobStepType
    , z2.name as owner
    , LE.LastExecDate
    , ob2.CREATED
    , ob2.MODIFIED
    , p.ACTIVE
    , p.TYPE
    from CMOBJPROPS2 p
    inner join CMOBJPROPS26 o on p.cmid=o.cmid
    inner join CMOBJECTS ob on ob.cmid=o.cmid
    inner join CMOBJECTS ob2 on ob.pcmid=ob2.cmid
    inner join CMOBJNAMES n on n.cmid=ob2.cmid
    inner join CMCLASSES c on ob2.classid=c.classid
    left join CMREFNOORD2 z1 on z1.cmid = p.cmid
    left join CMOBJPROPS33 z2 on z2.CMID = z1.REFCMID
    left join (
    select oh.PCMID
    , max(ACTEXECTIME) as LastExecDate
    from CMOBJPROPS5 h, CMOBJECTS oh
    where h.CMID = oh.CMID
    and oh.CLASSID = 111
    group by oh.PCMID
    ) LE
    on ob2.cmid = LE.PCMID
    left join (
    select jobStep.PCMID as jobID
    , jobStep.CMID as jobStepID
    , r.REFCMID as reportID
    , n.NAME as JobStepName
    , c.NAME as JobStepType
    from CMREFNOORD1 r
    INNER JOIN CMOBJECTS jobStep ON r.CMID=jobStep.CMID
    INNER JOIN CMOBJECTS o ON r.REFCMID=o.CMID
    INNER JOIN CMOBJNAMES n ON r.REFCMID=n.CMID
    INNER JOIN CMCLASSES c on o.classid=c.classid
    where r.propid=10
    and n.isdefault=1
    and o.CLASSID in (10, 19, 234)
    ) JL
    on ob2.cmid = JL.jobID
    where 1 = 1
    order by n.name, c.name


    ------------------------------
    Thomas van der Meer
    ------------------------------

    Original Message


  • 5.  RE: Create a report from the content store db providing details of schedules

    Posted Tue August 31, 2021 09:54 AM
    Hi Thomas,

    Thanks very much. This is very helpful. The only thing I am missing is the duration of the last run of the report/job

    Regards,

    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 6.  RE: Create a report from the content store db providing details of schedules

    Posted Fri February 16, 2024 10:30 AM

    This is great Thomas, thanks.
    Any suggestions on how to add the list of recipients from cmobjprops26.delivoptions xml in SQL Server?



    ------------------------------
    Dagur Egonsson
    ------------------------------

    Original Message


  • 7.  RE: Create a report from the content store db providing details of schedules

    Posted Wed September 01, 2021 09:16 AM
    @Christine Beswick This looks like a very useful report that you're developing. Please consider sharing the specs with the community (or in the Accelerator Catalog?). I can think of many occasions this would be helpful in our organization.

    Thanks,

    ------------------------------
    Wendi Lewis
    ------------------------------

    Original Message


  • 8.  RE: Create a report from the content store db providing details of schedules

    Posted Wed September 01, 2021 09:47 AM
    @Wendi Lewis Thanks for your message. Yes, it should be a very useful report once it is correct.

    Here is the code that I've developed (with the help from this forum especially @Matt Thiese) - please comment if you see issues​. I still need to convert the jrt.RUNTIME into hours/minutes/seconds, but that is fairly straightforward

    select 
      jobnames.name as job_name
    , cognos.Fn_GetSearchPath(job.cmid) as job_location
    , objectname.name as job_step_object_name
    , objectclass.name as type
    , cognos.Fn_GetSearchPath(jobstepobject.cmid) as step_location
    , CASE
      WHEN scheduleprops.active = 1 THEN 'Active'
      WHEN scheduleprops.active = 0 THEN 'Disabled'
      END as schedule_status
    , accountname.name as schedule_owner
    , cognos.Fn_GetNextRunDate(schedule.cmid, getdate()) as next_run_date
    , triggername.schedtrigname as trigger_name
    , dateadd(millisecond, ncq.RUN_DATE % 1000, dateadd(second, ncq.RUN_DATE / 1000, '19700101')) as next_run
    , dateadd(millisecond, tks.LAST_EXECUTION_AT % 1000, dateadd(second, tks.LAST_EXECUTION_AT/ 1000, '19700101'))    as last_run
    , dateadd(millisecond, ncq.RUN_DATE % 1000, dateadd(second, ncq.RUN_DATE/ 1000, '19700101')) as run_date
    , jrt.JOBNAME
    , jrt.LOCAL_TIMESTAMP
    , jrt.RUNTIME
    from
    cognos.CMOBJECTS job
    inner join cognos.CMOBJNAMES jobnames -- to get the name of the job
    on job.cmid = jobnames.cmid and jobnames.isdefault = 1
    inner join cognos.CMOBJECTS jobsteps -- to get the steps in the job
    on job.cmid = jobsteps.pcmid and jobsteps.classid = 57
    inner join cognos.CMREFNOORD1 jobsteprefs -- to get the objects linked to the job steps
    on jobsteps.cmid = jobsteprefs.cmid
    inner join cognos.CMOBJECTS jobstepobject -- to get the objects linked to the job steps
    on jobsteprefs.refcmid = jobstepobject.cmid
    inner join cognos.CMOBJNAMES objectname -- to get the name of the objects linked to the job steps
    on jobstepobject.cmid = objectname.cmid and objectname.isdefault = 1
    inner join cognos.CMCLASSES objectclass -- to get the type of job step
    on jobstepobject.classid = objectclass.classid
    left outer join cognos.CMOBJECTS schedule -- to get the schedule of the job
    on job.cmid = schedule.pcmid and schedule.classid = 39
    left outer join cognos.CMOBJPROPS2 scheduleprops -- the properties of the schedule
    on schedule.cmid = scheduleprops.cmid
    left outer join cognos.CMREFNOORD1 credential -- to get the credential linked to the scedule
    on schedule.cmid = credential.cmid
    left outer join cognos.CMOBJECTS account -- to get the account owning the credential
    on credential.refcmid = account.cmid
    left outer join cognos.CMOBJPROPS33 accountname -- name of schedule owner
    on account.pcmid = accountname.cmid
    left outer join cognos.CMOBJPROPS51 triggername
    on schedule.cmid = triggername.cmid
    left outer join cognos.R_NEWSITEMS_NCOBJECTS rnews
    on scheduleprops.taskid = rnews.nid
    left outer join cognos.NC_TASKSCHEDULE tks ---get the last execution date
    on rnews.FK_NCID = tks.FK_TASK_ID
    left outer join cognos.R_TASKSCHEDULE_SCHEDULE tksched
    on tks.PK_TASKSCHEDULE_ID = tksched.FK_TASKSCHEDULE_ID
    left outer join cognos.NC_SCHEDULE ncs
    on tksched.FK_SCHEDULE_ID = ncs.PK_SCHEDULE_ID
    left outer join cognos.NC_SCHEDULE_QUEUE ncq
    on ncq.SCHEDULE_ID=tks.PK_TASKSCHEDULE_ID
    left outer join (SELECT LEFT(SUBSTRING(COGIPF_JOBPATH, CHARINDEX('/jobDefinition[@name=', COGIPF_JOBPATH, 1) + 22, 128), CHARINDEX(']', SUBSTRING(COGIPF_JOBPATH, CHARINDEX('/jobDefinition[@name=', COGIPF_JOBPATH, 1) + 22, 128)) - 2) AS JOBNAME
    ,MAX([COGIPF_LOCALTIMESTAMP]) AS LOCAL_TIMESTAMP
    ,AVG(CAST([COGIPF_RUNTIME] AS FLOAT)) AS RUNTIME
    FROM [Cognos_11_Audit].[cognos].[COGIPF_RUNJOB]
    GROUP BY LEFT(SUBSTRING(COGIPF_JOBPATH, CHARINDEX('/jobDefinition[@name=', COGIPF_JOBPATH, 1) + 22, 128), CHARINDEX(']', SUBSTRING(COGIPF_JOBPATH, CHARINDEX('/jobDefinition[@name=', COGIPF_JOBPATH, 1) + 22, 128)) - 2)
    ) jrt
    on objectname.name = jrt.JOBNAME
    where
    job.classid = 55​


    Regards,



    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 9.  RE: Create a report from the content store db providing details of schedules

    Posted Thu November 04, 2021 08:34 AM
    can you share the code for your Fn_Search functions?

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 10.  RE: Create a report from the content store db providing details of schedules

    Posted Fri November 05, 2021 08:56 AM
    Hi Christine,

    Here is my SQL Query (with Oracle content store) to get infos about schedules :

    with

    base_query as
    (select classid, cmobjects.cmid, cmobjects.pcmid, cmobjnames.name as groupname
    from cmobjects , cmobjnames
    where cmobjects.cmid = cmobjnames.cmid
    and classid in (1,10,19,50,52,55,80,85,117,234)
    and cmobjnames.mapdlocaleid=184
    union
    select classid, cmobjects.cmid as cmid, cmobjects.pcmid, cmobjnames.name as groupname
    from cmobjects , cmobjnames
    where cmobjects.cmid = cmobjnames.cmid
    and cmobjects.cmid in (select distinct cmobjects.pcmid
    from cmobjects where classid in (1,10,19,50,52,55,80,85,117,234))
    and classid in (1,10,19,50,52,55,80,85,117,234)
    and cmobjnames.mapdlocaleid=184
    )
    ,

    ctebuildpath (cmid,pcmid,groupname,Hlevel,classid,directory_path) as
    (
    select base_query.cmid,
    base_query.pcmid,
    base_query.groupname,
    1 as Hlevel,classid,
    cast(base_query.groupname as varchar(500)) as directory_path
    from base_query
    union all
    select recur.cmid,
    recur.pcmid,
    recur.groupname,
    cte.Hlevel+1 as Hlevel,recur.classid,
    cast(cte.directory_path || ' > ' || recur.groupname as varchar(500)) as directory_path
    from base_query recur,
    ctebuildpath cte
    where cte.cmid = recur.pcmid),

    email (cmid,addresses) as
    (
    SELECT t.cmid,
    LISTAGG( x.address, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS addresses
    FROM CMOBJPROPS26 t
    CROSS JOIN
    XMLTABLE(
    '/SOAP-ENC/value/item[@xsi:type="bus:addressSMTP"]'
    PASSING XMLType(
    '<?xml version="1.0" encoding="utf-8"?>
    <SOAP-ENC xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">'
    || t.delivoptions
    || '</SOAP-ENC>'
    )
    COLUMNS address VARCHAR2(200) PATH 'text()'
    ) x
    GROUP BY t.cmid
    )


    select v_group.directory,cmn.name,
    class.NAME CLASS_NAME,
    p0.type,
    case
    when p0.type=4 then 'WEEKLY '
    when p0.type=0 then 'DAILY '
    when p0.type=1 then 'MONTHLY '
    when p0.type=2 then 'MONTHLY '
    when p0.type=5 then 'YEARLY '
    when p0.type=6 then 'YEARLY '
    when p0.type=7 then 'TRIGGER'
    else 'OTHER'
    end REPORT_FREQ
    ,p0.STARTDATE
    ,p0.ENDDATE
    ,case
    when p0.type=0 then
    'EVERY '||cast ( p0.EVERYNPERIODS as char(2))||
    case when p0.DAILYPERIOD=1 then ' HOURS'
    when p0.DAILYPERIOD=2 then ' MINUTES'
    when p0.DAILYPERIOD=0 then ' DAYS'
    end
    WHEN p0.type=4 THEN
    'EVERY '||cast ( p0.EVERYNPERIODS as char(2))||' WEEK(S) ON '||
    CASE when p0.WEEKLYMONDAY = 1 then 'MONDAY ' ELSE '' end
    || CASE WHEN p0.WEEKLYTUESDAY = 1 then 'TUESDAY ' ELSE '' end
    || CASE WHEN p0.WEEKLYWEDNESDAY = 1 then 'WEDNESDAY ' ELSE '' end
    || CASE WHEN p0.WEEKLYTHURSDAY = 1 then 'THURSDAY ' ELSE '' end
    || CASE WHEN p0.WEEKLYFRIDAY = 1 then 'FRIDAY ' ELSE '' end
    || CASE WHEN p0.WEEKLYSATURDAY = 1 then 'SATURDAY ' ELSE '' end
    || CASE WHEN p0.WEEKLYSUNDAY = 1 then 'SUNDAY' ELSE '' end
    WHEN p0.type =2 THEN
    CASE WHEN p0.MONTHLYRELWEEK = 0 then 'FIRST ' ELSE '' end
    || CASE WHEN p0.MONTHLYRELWEEK = 3 then 'SECOND ' ELSE '' end
    || CASE WHEN p0.MONTHLYRELWEEK = 2 then 'LAST ' ELSE '' end
    || CASE WHEN p0.MONTHLYRELWEEK = 1 then 'FOURTH ' ELSE '' end
    || CASE WHEN p0.MONTHLYRELWEEK = 4 then 'THIRD ' ELSE '' end
    || CASE WHEN p0.MONTHLYRELDAY = 1 THEN 'MONDAY' ELSE '' end
    || CASE WHEN p0.MONTHLYRELDAY = 2 THEN 'SATURDAY' ELSE '' end
    || CASE WHEN p0.MONTHLYRELDAY = 3 THEN 'SUNDAY' ELSE '' end
    || CASE WHEN p0.MONTHLYRELDAY = 4 THEN 'THURSDAY' ELSE '' end
    || CASE WHEN p0.MONTHLYRELDAY = 5 THEN 'TUESDAY' ELSE '' end
    || CASE WHEN p0.MONTHLYRELDAY = 6 THEN 'WEDNESDAY' ELSE '' end
    || CASE WHEN p0.MONTHLYRELDAY = 0 THEN 'FRIDAY' ELSE '' end
    || ' OF EVERY '||cast( p0.EVERYNPERIODS as char(1) ) || ' MONTH '
    WHEN p0.type =1 THEN
    'DAY '|| cast( p0.MONTHLYABSDAY as char(2)) ||' OF EVERY '|| cast( p0.EVERYNPERIODS as char(2) ) || ' MONTH(S) '
    WHEN p0.type =6 THEN
    ' EVERY '|| ' YEAR AT THE '
    || CASE WHEN p0.YEARLYRELWEEK = 0 then 'FIRST ' ELSE ' ' end
    || CASE WHEN p0.YEARLYRELWEEK = 1 then 'SECOND ' ELSE '' end
    || CASE WHEN p0.YEARLYRELWEEK = 2 then 'THIRD ' ELSE '' end
    || CASE WHEN p0.YEARLYRELWEEK = 3 then 'FOURTH ' ELSE '' end
    || CASE WHEN p0.YEARLYRELWEEK = 4 then 'LAST ' ELSE '' end
    || CASE WHEN p0.YEARLYRELDAY = 1 THEN 'MONDAY' ELSE '' end
    || CASE WHEN p0.YEARLYRELDAY = 2 THEN 'SATURDAY' ELSE '' end
    || CASE WHEN p0.YEARLYRELDAY = 3 THEN 'SUNDAY' ELSE '' end
    || CASE WHEN p0.YEARLYRELDAY = 4 THEN 'THURSDAY' ELSE '' end
    || CASE WHEN p0.YEARLYRELDAY = 5 THEN 'TUESDAY' ELSE '' end
    || CASE WHEN p0.YEARLYRELDAY = 6 THEN 'WED' ELSE '' end
    || CASE WHEN p0.YEARLYRELDAY = 0 THEN 'FRIDAY' ELSE '' end
    || ' OF EVERY '
    || CASE WHEN p0.YEARLYRELMONTH = 0 THEN 'JAN' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 1 THEN 'FEB' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 2 THEN 'MAR' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 3 THEN 'APR' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 4 THEN 'MAY' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 5 THEN 'JUN' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 6 THEN 'JUL' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 7 THEN 'AUG' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 8 THEN 'SEP' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 9 THEN 'OCT' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 10 THEN 'NOV' ELSE '' end
    || CASE WHEN p0.YEARLYRELMONTH = 11 THEN 'DEC' ELSE '' end
    WHEN p0.type =5 THEN
    'DAY '
    || CAST ( p0.YEARLYABSDAY as char(2)) ||' OF EVERY '
    || CASE WHEN p0.YEARLYABSMONTH = 0 THEN 'JAN' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 1 THEN 'FEB' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 2 THEN 'MAR' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 3 THEN 'APR' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 4 THEN 'MAY' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 5 THEN 'JUN' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 6 THEN 'JUL' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 7 THEN 'AUG' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 8 THEN 'SEP' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 9 THEN 'OCT' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 10 THEN 'NOV' ELSE '' end
    || CASE WHEN p0.YEARLYABSMONTH = 11 THEN 'DEC' ELSE '' end
    WHEN p0.type =7 THEN
    'TRIGGER NAME:'||p2.SCHEDTRIGNAME
    END SCHEDULE_PLAN,
    email.addresses
    from
    CMOBJECTS o
    join CMOBJPROPS2 p0 on o.CMID=p0.CMID
    left outer join email on o.CMID=email.CMID
    join CMOBJPROPS51 p2 on o.CMID=p2.CMID
    join CMOBJPROPS1 p3 on o.CMID=p3.CMID
    join cmobjects cmo on o.pcmid = cmo.cmid
    join cmobjnames cmn ON cmo.cmid = cmn.cmid
    join cmclasses class on class.classid=cmo.classid
    inner JOIN (SELECT ctebuildpath.cmid cmid, ctebuildpath.groupname name,ctebuildpath.directory_path as directory, classid
    FROM ctebuildpath where hlevel=(select max(hlevel) from ctebuildpath cte where ctebuildpath.cmid=cte.cmid)) v_group
    ON v_group.cmid = cmo.cmid
    where p0.active=1
    order by 1,2

    Regards

    Pierre-Frank


    ------------------------------
    Pierre-Frank MOUZON
    ------------------------------

    Original Message


  • 11.  RE: Create a report from the content store db providing details of schedules

    Posted Thu November 11, 2021 07:41 AM
    @Christine Beswick, or anybody that knows how to contact Christine Beswick, I am trying to get the code for her three Fn_Search functions for retrieving details about jobs and schedule. she provided some excellent code below, but it uses three functions that are not included in the code example. Thanks​

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 12.  RE: Create a report from the content store db providing details of schedules

    Posted Thu November 11, 2021 08:49 AM
    Hi @Brenda Grossnickle
    Can you give me details of what you are querying in the code?
    Regards,
    Christine​

    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 13.  RE: Create a report from the content store db providing details of schedules

    Posted Thu November 11, 2021 10:21 AM
    @Christine Beswick I am trying to run your code example above. i can get it to run if i comment out the functions Fn_GetSearchPath and Fn_GetNextRunDate (see below commented out code). Would like to get the code for these two functions. Thanks

    select
    jobnames.name as job_name
    /******************* , bank78cm_cognos11..Fn_GetSearchPath(job.cmid) as job_location  ***********************/
    , objectname.name as job_step_object_name
    , objectclass.name as type
    /******************* , bank78cm_cognos11..Fn_GetSearchPath(jobstepobject.cmid) as step_location   ***********************/
    , CASE
    WHEN scheduleprops.active = 1 THEN 'Active'
    WHEN scheduleprops.active = 0 THEN 'Disabled'
    END as schedule_status
    , accountname.name as schedule_owner
    /*********************** , bank78cm_cognos11..Fn_GetNextRunDate(schedule.cmid, getdate()) as next_run_date  ***********************/
    ​

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 14.  RE: Create a report from the content store db providing details of schedules

    Posted Fri November 12, 2021 01:54 AM
    Hi @Brenda Grossnickle,

    ​These functions are standard with the Cognos content store database. Check in your content store db under Programmability > Functions > Scalar-valued functions

    Regards,
    Christine

    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 15.  RE: Create a report from the content store db providing details of schedules

    Posted Fri November 12, 2021 07:55 AM
    not in my content store. only forgien keys are showing up. no FN, FS, FT, IF functions. i think someone must have put them there? can you script them out and send the code?



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 16.  RE: Create a report from the content store db providing details of schedules

    Posted Fri November 12, 2021 09:57 AM
    Here you go....


    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 17.  RE: Create a report from the content store db providing details of schedules

    Posted Fri November 12, 2021 10:46 AM
    i don't have those functions. Can you script them out and send me the tsql code? if you run the below code then copy and paste the definition here. Thanks for your patience and help.

    use Cognos_11
    SELECT
    OBJECT_NAME(sm.object_id) AS object_name,
    sm.definition
    FROM sys.sql_modules AS sm
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE sm.object_id = OBJECT_ID('cognos.FN_GetNextRunDate') or
    sm.object_id = OBJECT_ID('cognos.FN_GetSearchPath')
    ORDER BY o.type;

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 18.  RE: Create a report from the content store db providing details of schedules

    Posted Sun November 14, 2021 11:46 PM
    Fn_GetNextRunDate:

    USE [Cognos_11]

    CREATE FUNCTION [cognos].[Fn_GetNextRunDate]
    (@cmid int, @CurrentDate datetime )
    RETURNS datetime
    AS

    BEGIN

    DECLARE
    @NextRunDate datetime
    , @StartDate datetime
    , @EndDate datetime
    , @Type tinyint
    , @Active bit
    , @EveryNPeriods int
    , @DailyPeriod tinyint
    , @MonthlyAbsDay tinyint
    , @WeekStart datetime
    , @WeeklyMonday bit
    , @WeeklyTuesday bit
    , @WeeklyWednesday bit
    , @WeeklyThursday bit
    , @WeeklyFriday bit
    , @WeeklySaturday bit
    , @WeeklySunday bit
    , @MonthlyRelDay tinyint
    , @MonthlyRelWeek tinyint
    , @YearlyAbsDay tinyint
    , @YearlyAbsMonth tinyint
    , @DayOfWeek int
    , @YearlyRelDay tinyint
    , @YearlyRelMonth tinyint
    , @YearlyRelWeek tinyint

    SELECT
    @StartDate = Dateadd(hour, 2, startdate)
    , @EndDate = Dateadd(hour, 2, enddate)
    , @Type = type
    , @Active = active
    , @EveryNPeriods = everynperiods
    , @DailyPeriod = dailyperiod
    , @MonthlyAbsDay = MONTHLYABSDAY
    , @WeeklyMonday = WEEKLYMONDAY
    , @WeeklyTuesday = WEEKLYTUESDAY
    , @WeeklyWednesday = WEEKLYWEDNESDAY
    , @WeeklyThursday = WEEKLYTHURSDAY
    , @WeeklyFriday = WEEKLYFRIDAY
    , @WeeklySaturday = WEEKLYSATURDAY
    , @WeeklySunday = WEEKLYSUNDAY
    , @MonthlyRelDay =
    CASE
    WHEN MONTHLYRELDAY = 3 THEN 1 --Sunday
    WHEN MONTHLYRELDAY = 1 THEN 2 --Monday
    WHEN MONTHLYRELDAY = 5 THEN 3 --Tuesday
    WHEN MONTHLYRELDAY = 6 THEN 4 --Wednesday
    WHEN MONTHLYRELDAY = 4 THEN 5 --Thursday
    WHEN MONTHLYRELDAY = 0 THEN 6 --Friday
    WHEN MONTHLYRELDAY = 2 THEN 7 --Saturday
    END
    , @MonthlyRelWeek =
    CASE
    WHEN MONTHLYRELWEEK = 0 THEN 1
    WHEN MONTHLYRELWEEK = 3 THEN 2
    WHEN MONTHLYRELWEEK = 4 THEN 3
    WHEN MONTHLYRELWEEK = 1 THEN 4
    WHEN MONTHLYRELWEEK = 2 THEN 5
    END
    , @YearlyAbsDay = YEARLYABSDAY
    , @YearlyAbsMonth = YEARLYABSMONTH + 1
    , @YearlyRelDay =
    CASE
    WHEN YEARLYRELDAY = 3 THEN 1 --Sunday
    WHEN YEARLYRELDAY = 1 THEN 2 --Monday
    WHEN YEARLYRELDAY = 5 THEN 3 --Tuesday
    WHEN YEARLYRELDAY = 6 THEN 4 --Wednesday
    WHEN YEARLYRELDAY = 4 THEN 5 --Thursday
    WHEN YEARLYRELDAY = 0 THEN 6 --Friday
    WHEN YEARLYRELDAY = 2 THEN 7 --Saturday
    END
    , @YearlyRelMonth = YEARLYRELMONTH + 1
    , @YearlyRelWeek =
    CASE
    WHEN YEARLYRELWEEK = 0 THEN 1
    WHEN YEARLYRELWEEK = 3 THEN 2
    WHEN YEARLYRELWEEK = 4 THEN 3
    WHEN YEARLYRELWEEK = 1 THEN 4
    WHEN YEARLYRELWEEK = 2 THEN 5
    END
    FROM cognos.CMOBJPROPS2
    WHERE cmid = @cmid

    IF @Active = 0 --schedule disabled
    OR @EndDate < @CurrentDate -- schedule ended
    OR @Type = 7 -- by trigger
    OR @cmid is NULL -- no schedule
    RETURN NULL

    -- All other types: calculate
    SET @NextRunDate = @StartDate

    IF @Type = 1 -- by month: absolute day - go to first absolute day
    BEGIN
    SET @NextRunDate = Dateadd(day, (@MonthlyAbsDay - Day(@StartDate)) ,@NextRunDate)
    IF @MonthlyAbsDay < Day(@StartDate)
    SET @NextRunDate = Dateadd(month, 1 ,@NextRunDate)
    END

    IF @Type = 2 -- by month: relative day
    BEGIN
    -- go to the next relative week day
    SET @NextRunDate = Dateadd(day, (@MonthlyRelDay - DatePart(dw, @StartDate)) ,@NextRunDate)
    IF @MonthlyRelDay < DatePart(dw, @StartDate)
    SET @NextRunDate = Dateadd(week, 1 ,@NextRunDate)

    --go to the first proper run date i.e. correct week no
    WHILE Ceiling(day(@NextRunDate)/7.0) <> @MonthlyRelWeek
    BEGIN
    SET @NextRunDate = Dateadd(week, 1 , @NextRunDate)
    IF @MonthlyRelWeek = 5 and Ceiling(day(@NextRunDate)/7.0) = 1
    BEGIN
    SET @NextRunDate = Dateadd(week, -1 , @NextRunDate)
    BREAK
    END
    END

    END


    IF @Type = 4 -- by week - go to monday of week
    BEGIN
    SET @WeekStart = Dateadd(day, (2 - DatePart(dw, @StartDate)) ,@NextRunDate)
    IF DatePart(dw, @StartDate) = 1
    SET @WeekStart = Dateadd(week, -1 ,@WeekStart)

    SET @NextRunDate = @WeekStart
    END

    IF @Type = 5 -- by year: absolute day - go to first absolute day
    BEGIN
    SET @NextRunDate = Dateadd(day, (@YearlyAbsDay - Day(@StartDate)) ,@NextRunDate)
    SET @NextRunDate = Dateadd(month, (@YearlyAbsMonth - month(@StartDate)) ,@NextRunDate)
    IF @YearlyAbsMonth < Month(@StartDate)
    SET @NextRunDate = Dateadd(year, 1 ,@NextRunDate)

    END

    WHILE @NextRunDate < @CurrentDate
    BEGIN
    IF @Type = 0 -- by day
    BEGIN
    IF @DailyPeriod = 0 -- every x days
    SET @NextRunDate = DateAdd(day, @EveryNPeriods, @NextRunDate)

    IF @DailyPeriod = 1 -- every x hours
    SET @NextRunDate = DateAdd(hour, @EveryNPeriods, @NextRunDate)

    IF @DailyPeriod = 2 -- every x minutes
    SET @NextRunDate = DateAdd(minute, @EveryNPeriods, @NextRunDate)

    END

    IF @Type = 1 -- by month: absolute day
    BEGIN
    SET @NextRunDate = DateAdd(month, @EveryNPeriods, @NextRunDate)
    END

    IF @Type = 2 -- by month: relative day
    BEGIN
    --go to start of month
    SET @NextRunDate = Dateadd(day, (1 - Day(@NextRunDate)) ,@NextRunDate)

    --add months
    SET @NextRunDate = Dateadd(month, @EveryNPeriods, @NextRunDate)

    -- go to the next relative week day
    SET @DayOfWeek = DatePart(dw, @NextRunDate)
    SET @NextRunDate = Dateadd(day, (@MonthlyRelDay - @DayOfWeek) ,@NextRunDate)
    IF @MonthlyRelDay < @DayOfWeek
    SET @NextRunDate = Dateadd(week, 1 ,@NextRunDate)

    --go to the next run date with correct week
    WHILE Ceiling(day(@NextRunDate)/7.0) <> @MonthlyRelWeek
    BEGIN
    SET @NextRunDate = Dateadd(week, 1 , @NextRunDate)
    IF @MonthlyRelWeek = 5 and Ceiling(day(@NextRunDate)/7.0) = 1
    BEGIN
    SET @NextRunDate = Dateadd(week, -1 , @NextRunDate)
    BREAK
    END
    END
    END

    IF @Type = 3
    BEGIN
    RETURN NULL
    END

    IF @Type = 4 -- by week
    BEGIN
    IF @WeeklyMonday = 1
    BEGIN
    SET @NextRunDate = @WeekStart
    IF @NextRunDate > @CurrentDate
    RETURN @NextRunDate
    END
    IF @WeeklyTuesday = 1
    BEGIN
    SET @NextRunDate = DateAdd(day, 1, @WeekStart)
    IF @NextRunDate > @CurrentDate
    RETURN @NextRunDate

    END
    IF @WeeklyWednesday = 1
    BEGIN
    SET @NextRunDate = DateAdd(day, 2, @WeekStart)
    IF @NextRunDate > @CurrentDate
    RETURN @NextRunDate

    END
    IF @WeeklyThursday = 1
    BEGIN
    SET @NextRunDate = DateAdd(day, 3, @WeekStart)
    IF @NextRunDate > @CurrentDate
    RETURN @NextRunDate

    END
    IF @WeeklyFriday = 1
    BEGIN
    SET @NextRunDate = DateAdd(day, 4, @WeekStart)
    IF @NextRunDate > @CurrentDate
    RETURN @NextRunDate

    END
    IF @WeeklySaturday = 1
    BEGIN
    SET @NextRunDate = DateAdd(day, 5, @WeekStart)
    IF @NextRunDate > @CurrentDate
    RETURN @NextRunDate

    END
    IF @WeeklySunday = 1
    BEGIN
    SET @NextRunDate = DateAdd(day, 6, @WeekStart)
    IF @NextRunDate > @CurrentDate
    RETURN @NextRunDate

    END

    SET @WeekStart = DateAdd(week, @EveryNPeriods, @WeekStart)

    END

    IF @Type = 5 --By Year Absolute day
    BEGIN
    SET @NextRunDate = DateAdd(year, 1, @NextRunDate)
    END

    IF @Type = 6
    BEGIN
    RETURN NULL
    END
    END

    RETURN @NextRunDate

    END

    GO

    Fn_GetSearchPath:

    USE [Cognos_11]

    CREATE FUNCTION [cognos].[Fn_GetSearchPath](@cmid int )
    RETURNS nvarchar(1000)
    AS
    BEGIN

    DECLARE
    @ObjectID int -- cognos cmid
    ,@ParentID int -- parent object cmid
    ,@ObjectName nvarchar(256) -- object name
    ,@ClassName nvarchar(50) -- object type
    ,@SearchPath nvarchar(1000) --

    SET @ObjectID = @cmid
    SET @SearchPath = ''

    WHILE @ObjectID <> 0
    BEGIN
    --get details for current object
    SELECT
    @ParentID = obj.pcmid
    , @ObjectName = names.name
    , @ClassName = class.name --as class_name
    FROM
    cognos.CMOBJECTS obj
    , cognos.CMOBJNAMES names
    , cognos.CMCLASSES class
    WHERE
    obj.cmid = names.cmid
    AND obj.classid = class.classid
    AND names.isdefault = 1
    AND obj.cmid = @ObjectID


    SET @SearchPath = '/'+ @ClassName + '[@name=''' + @ObjectName + ''']' + @SearchPath
    SET @ObjectID = @ParentID

    END

    RETURN @SearchPath

    END



    GO


    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 19.  RE: Create a report from the content store db providing details of schedules

    Posted Tue November 16, 2021 11:32 AM
    @Christine Beswick Thanks for the code.  Really nice. The code works for jobs, but does not return individual scheduled reports. Do you have anything written for that.

    Also, the last three columns that return data from the audit database, are always NULL, unless you have a jobname that is the same as a step name. The subselect for the audit data returns job name, yet is trying to join on the step name. ​

    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 20.  RE: Create a report from the content store db providing details of schedules

    Posted Wed November 17, 2021 07:35 AM
    Have you considered using the Motio utility? There is a free version that does exactly what you are looking for and you can export the data to CSV and massage it if needed.
    Thanks

    ------------------------------
    William Sai-Palm
    ------------------------------

    Original Message


  • 21.  RE: Create a report from the content store db providing details of schedules

    Posted Wed November 17, 2021 09:20 AM
    we are a cognos reseller and manage data centers. our clients use cognos, but cannot install any software on the multi-tenant server. having a query that the clients can run in a custom sql report would allow them to review their job and schedules to better organize them and remove ones that are no longer needed and lessen the load of the servers. 

    i have used the motio pi free tool. it is very nice. we actually use it to verify our reports prior to a release. 


    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 22.  RE: Create a report from the content store db providing details of schedules

    Posted Thu December 23, 2021 11:06 AM

    Hi Brenda,
    I have updated my query to get info about schedules. I started with my own query and added things I found in these posts for my needs. It is made for a Oracle content store. It does not use functions.
    The result is a list of all schedules (active or not) of any type (for example report, query, reportView, jobDefinition, exportDeployment) individual scheduled or not. Jobs include jobsteps, jobsteptype, emailadresses, jobsteps emailadresses, schedule path (friendly name), steppath (friendly name). And the detail information like last execution date, creation date, report frequency. The only assumption is that only 1 namespace (besides Cognos) exists.
    It might be useful in your situation. Feel free to use it and let me know if something is not working correctly. It is still in development.


    WITH
    "CognosSchedules" AS
    (
    select ob2.cmid
    , ob2.pcmid
    , c.name as classname
    , n.name as objectname
    , JL.reportID
    , JL.reportIDparent
    , JL.jobID
    , JL.jobStepID
    , JL.JobStepName
    , JL.JobStepType
    , z2.name as owner
    , LE.LastExecDate
    , ob2.CREATED
    , ob2.MODIFIED
    , p.ACTIVE
    , p.TYPE
    , case
    when p.type=4 then 'WEEKLY'
    when p.type=0 then 'DAILY'
    when p.type=1 then 'MONTHLY'
    when p.type=2 then 'MONTHLY'
    when p.type=5 then 'YEARLY'
    when p.type=6 then 'YEARLY'
    when p.type=7 then 'TRIGGER'
    else 'OTHER'
    end REPORT_FREQ
    , p.STARTDATE
    , p.ENDDATE
    ,case
    when p.type=0 then
    'EVERY '||cast ( p.EVERYNPERIODS as char(2))||
    case when p.DAILYPERIOD=1 then ' HOURS'
    when p.DAILYPERIOD=2 then ' MINUTES'
    when p.DAILYPERIOD=0 then ' DAYS'
    end
    WHEN p.type=4 THEN
    'EVERY '||cast ( p.EVERYNPERIODS as char(2))||' WEEK(S) ON '||
    CASE when p.WEEKLYMONDAY = 1 then 'MONDAY ' ELSE '' end
    || CASE WHEN p.WEEKLYTUESDAY = 1 then 'TUESDAY ' ELSE '' end
    || CASE WHEN p.WEEKLYWEDNESDAY = 1 then 'WEDNESDAY ' ELSE '' end
    || CASE WHEN p.WEEKLYTHURSDAY = 1 then 'THURSDAY ' ELSE '' end
    || CASE WHEN p.WEEKLYFRIDAY = 1 then 'FRIDAY ' ELSE '' end
    || CASE WHEN p.WEEKLYSATURDAY = 1 then 'SATURDAY ' ELSE '' end
    || CASE WHEN p.WEEKLYSUNDAY = 1 then 'SUNDAY' ELSE '' end
    WHEN p.type =2 THEN
    CASE WHEN p.MONTHLYRELWEEK = 0 then 'FIRST ' ELSE '' end
    || CASE WHEN p.MONTHLYRELWEEK = 3 then 'SECOND ' ELSE '' end
    || CASE WHEN p.MONTHLYRELWEEK = 2 then 'LAST ' ELSE '' end
    || CASE WHEN p.MONTHLYRELWEEK = 1 then 'FOURTH ' ELSE '' end
    || CASE WHEN p.MONTHLYRELWEEK = 4 then 'THIRD ' ELSE '' end
    || CASE WHEN p.MONTHLYRELDAY = 1 THEN 'MONDAY' ELSE '' end
    || CASE WHEN p.MONTHLYRELDAY = 2 THEN 'SATURDAY' ELSE '' end
    || CASE WHEN p.MONTHLYRELDAY = 3 THEN 'SUNDAY' ELSE '' end
    || CASE WHEN p.MONTHLYRELDAY = 4 THEN 'THURSDAY' ELSE '' end
    || CASE WHEN p.MONTHLYRELDAY = 5 THEN 'TUESDAY' ELSE '' end
    || CASE WHEN p.MONTHLYRELDAY = 6 THEN 'WEDNESDAY' ELSE '' end
    || CASE WHEN p.MONTHLYRELDAY = 0 THEN 'FRIDAY' ELSE '' end
    || ' OF EVERY '||cast( p.EVERYNPERIODS as char(1) ) || ' MONTH '
    WHEN p.type =1 THEN
    'DAY '|| cast( p.MONTHLYABSDAY as char(2)) ||' OF EVERY '|| cast( p.EVERYNPERIODS as char(2) ) || ' MONTH(S) '
    WHEN p.type =6 THEN
    ' EVERY '|| ' YEAR AT THE '
    || CASE WHEN p.YEARLYRELWEEK = 0 then 'FIRST ' ELSE ' ' end
    || CASE WHEN p.YEARLYRELWEEK = 1 then 'SECOND ' ELSE '' end
    || CASE WHEN p.YEARLYRELWEEK = 2 then 'THIRD ' ELSE '' end
    || CASE WHEN p.YEARLYRELWEEK = 3 then 'FOURTH ' ELSE '' end
    || CASE WHEN p.YEARLYRELWEEK = 4 then 'LAST ' ELSE '' end
    || CASE WHEN p.YEARLYRELDAY = 1 THEN 'MONDAY' ELSE '' end
    || CASE WHEN p.YEARLYRELDAY = 2 THEN 'SATURDAY' ELSE '' end
    || CASE WHEN p.YEARLYRELDAY = 3 THEN 'SUNDAY' ELSE '' end
    || CASE WHEN p.YEARLYRELDAY = 4 THEN 'THURSDAY' ELSE '' end
    || CASE WHEN p.YEARLYRELDAY = 5 THEN 'TUESDAY' ELSE '' end
    || CASE WHEN p.YEARLYRELDAY = 6 THEN 'WED' ELSE '' end
    || CASE WHEN p.YEARLYRELDAY = 0 THEN 'FRIDAY' ELSE '' end
    || ' OF EVERY '
    || CASE WHEN p.YEARLYRELMONTH = 0 THEN 'JAN' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 1 THEN 'FEB' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 2 THEN 'MAR' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 3 THEN 'APR' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 4 THEN 'MAY' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 5 THEN 'JUN' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 6 THEN 'JUL' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 7 THEN 'AUG' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 8 THEN 'SEP' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 9 THEN 'OCT' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 10 THEN 'NOV' ELSE '' end
    || CASE WHEN p.YEARLYRELMONTH = 11 THEN 'DEC' ELSE '' end
    WHEN p.type =5 THEN
    'DAY '
    || CAST ( p.YEARLYABSDAY as char(2)) ||' OF EVERY '
    || CASE WHEN p.YEARLYABSMONTH = 0 THEN 'JAN' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 1 THEN 'FEB' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 2 THEN 'MAR' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 3 THEN 'APR' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 4 THEN 'MAY' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 5 THEN 'JUN' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 6 THEN 'JUL' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 7 THEN 'AUG' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 8 THEN 'SEP' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 9 THEN 'OCT' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 10 THEN 'NOV' ELSE '' end
    || CASE WHEN p.YEARLYABSMONTH = 11 THEN 'DEC' ELSE '' end
    WHEN p.type =7 THEN
    'TRIGGER NAME:'||p2.SCHEDTRIGNAME
    END SCHEDULE_PLAN
    --, o.DELIVOPTIONS as deliveryoptions
    ,email.addresses
    from CMOBJPROPS2 p -- schedule must exists
    inner join CMOBJPROPS26 o on p.cmid=o.cmid
    inner join CMOBJPROPS51 p2 on p.CMID=p2.CMID
    inner join CMOBJECTS ob on ob.cmid=o.cmid
    inner join CMOBJECTS ob2 on ob.pcmid=ob2.cmid
    inner join CMOBJNAMES n on n.cmid=ob2.cmid
    inner join CMCLASSES c on ob2.classid=c.classid
    left join CMREFNOORD2 z1 on z1.cmid = p.cmid
    left join CMOBJPROPS33 z2 on z2.CMID = z1.REFCMID
    left join (
    SELECT t.cmid,
    LISTAGG( x.address, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS addresses
    FROM CMOBJPROPS26 t
    CROSS JOIN
    XMLTABLE(
    '/SOAP-ENC/value/item[@xsi:type="bus:addressSMTP"]'
    PASSING XMLType(
    '<?xml version="1.0" encoding="utf-8"?>
    <SOAP-ENC xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">'
    || t.delivoptions
    || '</SOAP-ENC>'
    )
    COLUMNS address VARCHAR2(200) PATH 'text()'
    ) x
    GROUP BY t.cmid
    ) email
    on o.cmid = email.cmid
    left join (
    select oh.PCMID,
    max(ACTEXECTIME) as LastExecDate
    from CMOBJPROPS5 h, CMOBJECTS oh
    where h.CMID = oh.CMID
    and oh.CLASSID = 111
    group by oh.PCMID
    ) LE
    on ob2.cmid = LE.PCMID
    left join (
    select jobStep.PCMID as jobID
    , jobStep.CMID as jobStepID
    , r.REFCMID as reportID
    , o.PCMID as reportIDparent
    , n.NAME as JobStepName
    , c.NAME as JobStepType
    from CMREFNOORD1 r
    INNER JOIN CMOBJECTS jobStep ON r.CMID=jobStep.CMID
    INNER JOIN CMOBJECTS o ON r.REFCMID=o.CMID
    INNER JOIN CMOBJNAMES n ON r.REFCMID=n.CMID
    INNER JOIN CMCLASSES c on o.classid=c.classid
    where r.propid=10
    and n.isdefault=1
    and o.CLASSID in (10, 19, 234, 257)
    ) JL
    on ob2.cmid = JL.jobID
    where 1 = 1
    order by n.name, c.name
    )
    ,COGNOSCONTENT (CMID, PCMID, NAME, PARENT, CONTENT, PATH, LEVELNR, CLASSID)
    as (
    select
    A.CMID
    ,A.PCMID
    ,B.NAME
    ,B.NAME as PARENT
    ,C.NAME as CONTENT
    ,cast(B.NAME as varchar(512)) as PATH
    ,0 as LEVELNR
    ,a.CLASSID
    from CMOBJECTS A
    join CMOBJNAMES B on A.CMID = B.CMID
    join CMCLASSES C on A.CLASSID = C.CLASSID
    where B.ISDEFAULT = 1
    and A.PCMID = 0
    and A.CMID in ( 2, 4 ) -- 2 is teamcontent, 4 is personal folders
    UNION ALL
    select
    A.CMID
    ,A.PCMID
    ,B.NAME as PARENT
    ,D.NAME
    ,C.NAME
    ,D.PATH || '\' || CAST (B.NAME as varchar(512)) as PATH
    ,D.LEVELNR + 1 as LEVELNR
    ,A.CLASSID
    from CMOBJECTS A
    join CMOBJNAMES B on A.CMID = b.CMID
    join CMCLASSES C on A.CLASSID = C.CLASSID
    join COGNOSCONTENT D on A.PCMID = D.CMID
    where B.ISDEFAULT = 1
    and A.CLASSID in (1, 5, 8, 32) -- folder, account, namespaceFolder, namespace
    )
    ,UIDNAAM ( USERNAME, OBJID ) AS
    ( select
    upper(CMOBJPROPS33.NAME) as USERNAME
    ,CMOBJPROPS1.OBJID
    from CMOBJPROPS1 CMOBJPROPS1
    join CMOBJPROPS33 CMOBJPROPS33
    on CMOBJPROPS1.CMID = CMOBJPROPS33.CMID
    )
    ,NAMESPACE (NAMESPACE_ID) AS
    ( SELECT C.OBJID AS NAMESPACE_ID
    from CMOBJECTS A
    join CMOBJNAMES B on A.CMID = B.CMID
    join CMOBJPROPS1 C on A.CMID = C.CMID
    join CMCLASSES D on A.CLASSID = D.CLASSID
    where B.ISDEFAULT = 1
    and A.CLASSID in (32)
    and upper(B.NAME) <> 'COGNOS'
    )
    SELECT DISTINCT
    "CognosSchedules"."CMID" AS "CMID",
    "CognosSchedules"."PCMID" AS "PCMID",
    "CognosSchedules"."CLASSNAME" AS "CLASSNAME",
    "CognosSchedules"."OBJECTNAME" AS "OBJECTNAME",
    -- "CognosSchedules"."REPORTID" AS "REPORTID",
    -- "CognosSchedules"."REPORTIDPARENT" AS "REPORTIDPARENT",
    -- "CognosSchedules"."JOBID" AS "JOBID",
    -- "CognosSchedules"."JOBSTEPID" AS "JOBSTEPID",
    "CognosSchedules"."JOBSTEPNAME" AS "JOBSTEPNAME",
    "CognosSchedules"."JOBSTEPTYPE" AS "JOBSTEPTYPE",
    "CognosSchedules"."OWNER" AS "OWNER",
    "CognosSchedules"."LASTEXECDATE" AS "LASTEXECDATE",
    "CognosSchedules"."CREATED" AS "CREATED",
    "CognosSchedules"."MODIFIED" AS "MODIFIED",
    "CognosSchedules"."ACTIVE" AS "ACTIVE",
    "CognosSchedules"."TYPE" AS "TYPE",
    "CognosSchedules"."REPORT_FREQ" AS "REPORT_FREQ",
    "CognosSchedules"."STARTDATE" AS "STARTDATE",
    "CognosSchedules"."ENDDATE" AS "ENDDATE",
    "CognosSchedules"."SCHEDULE_PLAN" AS "SCHEDULE_PLAN",
    "CognosSchedules"."ADDRESSES" AS "ADDRESSES",
    -- CC.PATH,
    email_step.addresses_step AS EMAILSTEP,
    case
    when ( instr( CC.PATH, NAMESP.NAMESPACE_ID || ':u:') = 0 ) then ( CC.PATH )
    else ( UIDNAAM.USERNAME || substr(CC.PATH, instr( CC.PATH, '\My Folders') ) )
    end as SCHEDULEPATH,
    case
    when ( CCSTEP.PATH is null ) then ( CCSTEP.PATH )
    when ( instr( CCSTEP.PATH, NAMESP.NAMESPACE_ID || ':u:') = 0 ) then ( CCSTEP.PATH )
    else ( UIDNAAM.USERNAME || substr(CCSTEP.PATH, instr( CCSTEP.PATH, '\My Folders') ) )
    end as STEPPATH
    FROM "CognosSchedules"
    LEFT JOIN COGNOSCONTENT CC
    on cc.CMID = "CognosSchedules".PCMID
    cross join ( NAMESPACE ) NAMESP
    left join UIDNAAM
    on substr(CC.PATH, instr( CC.PATH, NAMESP.NAMESPACE_ID || ':u:'), 49) = UIDNAAM.OBJID
    LEFT JOIN COGNOSCONTENT CCSTEP
    on CCSTEP.CMID = "CognosSchedules".REPORTIDPARENT
    left join (
    SELECT t.cmid,
    LISTAGG( x.address, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS addresses_step
    FROM CMOBJPROPS26 t
    CROSS JOIN
    XMLTABLE(
    '/SOAP-ENC/value/item[@xsi:type="bus:addressSMTP"]'
    PASSING XMLType(
    '<?xml version="1.0" encoding="utf-8"?>
    <SOAP-ENC xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">'
    || t.delivoptions
    || '</SOAP-ENC>'
    )
    COLUMNS address VARCHAR2(200) PATH 'text()'
    ) x
    GROUP BY t.cmid
    ) email_step
    on "CognosSchedules".JOBSTEPID = email_step.cmid
    ORDER BY
    "CMID" ASC NULLS LAST,
    "CLASSNAME" ASC NULLS LAST,
    "OBJECTNAME" ASC NULLS LAST
    ;



    ------------------------------
    Thomas van der Meer
    ------------------------------

    Original Message


  • 23.  RE: Create a report from the content store db providing details of schedules

    Posted Wed November 17, 2021 11:37 PM
    Hi Brenda,

    Unfortunately, That is all I have done so far. If you have any ideas on your side, I would really appreciate it you would share them.

    Good luck!
    Regards,
    Christine

    ------------------------------
    Christine Beswick
    ------------------------------

    Original Message


  • 24.  RE: Create a report from the content store db providing details of schedules

    Posted Tue August 27, 2024 04:44 PM

    I took @Thomas van der Meer Oracle SQL above and converted it to MS SQL. 

    Made a couple minor changes and added support for 

    ++ job schedule steps that use the job default email address

    ++ added email subject

    ++ added external file name

    -- xmlnamespaces is required for Email_Address CTE. It must be first CTE
    ;WITH xmlnamespaces( 'a' as xsi, 'b' as [SOAP-ENC]  )  -- required for Email_Address CTE
    -- select cmid, email addresses in delivoptions. uses xmlnamespaces
    , Email_Address as (
    select  cmid, item.value('.', 'nvarchar(max)') as addresses
    from CMOBJPROPS26
    cross apply (values (cast(N'<xml xmlns:xsi="a" xmlns:SOAP-ENC="b">' + cast(delivoptions as nvarchar(max)) + N'</xml>' as xml))) t1(x)
    cross apply t1.x.nodes('//item[@xsi:type="bus:addressSMTP"]') as t2(item)
    )
    -- recursive select to find all text between 'subject;deliveryOptionEnum;'  and next '</value>' 
    -- subject;deliveryOptionEnum;deliveryOptionString;73;<value xsi:type="xsd:string">Schedule Test DV02 Version 11.0.13</value> -- example data
    -- cte would pull out 'deliveryOptionString;73;<value xsi:type="xsd:string">Schedule Test DV02 Version 11.0.13'
    , email_subject as  -- recursive select to find all 'subject;deliveryOptionEnum;'
    (
    select cmid, delivoptions, 0 as st, len('subject;deliveryOptionEnum;') as ln, 0 as en, cast(null as varchar(max)) as email_subject 
    from CMOBJPROPS26
    union all
    select cmid, delivoptions, t1.st, ln, t2.en, cast(t3.n as varchar(max))
    from email_subject
    cross apply (values (charindex('subject;deliveryOptionEnum;', delivoptions, email_subject.st + 1) ) ) t1(st)
    cross apply (values (charindex('</value>', delivoptions, t1.st)) ) t2(en)
    cross apply (values (case when t1.st > 0 then substring(delivoptions, t1.st + ln, t2.en - (t1.st + ln)) end) ) t3(n)
    where t1.st > 0
    )
    -- calls email_subject and takes returned text 'deliveryOptionString;73;<value xsi:type="xsd:string">Schedule Test DV02 Version 11.0.13' and pulls off 'Schedule Test DV02 Version 11.0.13'
    , email_subject_select as 
    (
    select cmid, ltrim(rtrim(reverse(left(reverse(email_subject), charindex('>',reverse(email_subject)) - 1)))) email_subject
    from email_subject
    where st > 0 
    )
    -- recursive select to find all text between 'filenameStub;archiveOptionEnum;;'  and next '</value>' 
    -- filenameStub;archiveOptionEnum;archiveOptionString;68;<value xsi:type="xsd:string">Officer Product Group Summary</value> -- example data
    -- cte would pull out 'archiveOptionString;68;<value xsi:type="xsd:string">Officer Product Group Summary'
    , filename as  -- recursive select to find all 'subject;deliveryOptionEnum;'
    (
    select cmid, delivoptions, 0 as st, len('filenameStub;archiveOptionEnum;') as ln, 0 as en, cast(null as varchar(max)) as filename 
    from CMOBJPROPS26
    union all
    select cmid, delivoptions, t1.st, ln, t2.en, cast(t3.n as varchar(max))
    from filename
    cross apply (values (charindex('filenameStub;archiveOptionEnum;', delivoptions, filename.st + 1) ) ) t1(st)
    cross apply (values (charindex('</value>', delivoptions, t1.st)) ) t2(en)
    cross apply (values (case when t1.st > 0 then substring(delivoptions, t1.st + ln, t2.en - (t1.st + ln)) end) ) t3(n)
    where t1.st > 0
    )
    -- calls filename and takes returned text 'deliveryOptionString;73;<value xsi:type="xsd:string">Schedule Test DV02 Version 11.0.13' and pulls off 'Schedule Test DV02 Version 11.0.13'
    , filename_select as 
    (
    select cmid, ltrim(rtrim(reverse(left(reverse(filename), charindex('>',reverse(filename)) - 1)))) filename
    from filename
    where st > 0 
    )
    -- general cte to get most of the schedule information
    , "CognosSchedules" AS
    (
    select ob2.cmid
    , ob2.pcmid
    , c.name as classname
    , n.name as objectname
    , JL.reportID
    , JL.reportIDparent
    , JL.jobID
    , JL.jobStepID
    , JL.JobStepName
    , JL.JobStepType
    , z2.name as owner
    , LE.LastExecDate
    , ob2.CREATED
    , ob2.MODIFIED
    , p.ACTIVE
    , p.TYPE
    , case
    when p.type=4 then 'WEEKLY'
    when p.type=0 then 'DAILY'
    when p.type=1 then 'MONTHLY'
    when p.type=2 then 'MONTHLY'
    when p.type=5 then 'YEARLY'
    when p.type=6 then 'YEARLY'
    when p.type=7 then 'TRIGGER'
    else 'OTHER'
    end REPORT_FREQ
    , p.STARTDATE
    , p.ENDDATE
    ,case
    when p.type=0 then
    'EVERY '+cast ( p.EVERYNPERIODS as char(2))+
    case when p.DAILYPERIOD=1 then ' HOURS'
    when p.DAILYPERIOD=2 then ' MINUTES'
    when p.DAILYPERIOD=0 then ' DAYS'
    end
    WHEN p.type=4 THEN
    'EVERY '+cast ( p.EVERYNPERIODS as char(2))+' WEEK(S) ON '+
    CASE when p.WEEKLYMONDAY = 1 then 'MONDAY ' ELSE '' end
    + CASE WHEN p.WEEKLYTUESDAY = 1 then 'TUESDAY ' ELSE '' end
    + CASE WHEN p.WEEKLYWEDNESDAY = 1 then 'WEDNESDAY ' ELSE '' end
    + CASE WHEN p.WEEKLYTHURSDAY = 1 then 'THURSDAY ' ELSE '' end
    + CASE WHEN p.WEEKLYFRIDAY = 1 then 'FRIDAY ' ELSE '' end
    + CASE WHEN p.WEEKLYSATURDAY = 1 then 'SATURDAY ' ELSE '' end
    + CASE WHEN p.WEEKLYSUNDAY = 1 then 'SUNDAY' ELSE '' end
    WHEN p.type =2 THEN
    CASE WHEN p.MONTHLYRELWEEK = 0 then 'FIRST ' ELSE '' end
    + CASE WHEN p.MONTHLYRELWEEK = 3 then 'SECOND ' ELSE '' end
    + CASE WHEN p.MONTHLYRELWEEK = 2 then 'LAST ' ELSE '' end
    + CASE WHEN p.MONTHLYRELWEEK = 1 then 'FOURTH ' ELSE '' end
    + CASE WHEN p.MONTHLYRELWEEK = 4 then 'THIRD ' ELSE '' end
    + CASE WHEN p.MONTHLYRELDAY = 1 THEN 'MONDAY' ELSE '' end
    + CASE WHEN p.MONTHLYRELDAY = 2 THEN 'SATURDAY' ELSE '' end
    + CASE WHEN p.MONTHLYRELDAY = 3 THEN 'SUNDAY' ELSE '' end
    + CASE WHEN p.MONTHLYRELDAY = 4 THEN 'THURSDAY' ELSE '' end
    + CASE WHEN p.MONTHLYRELDAY = 5 THEN 'TUESDAY' ELSE '' end
    + CASE WHEN p.MONTHLYRELDAY = 6 THEN 'WEDNESDAY' ELSE '' end
    + CASE WHEN p.MONTHLYRELDAY = 0 THEN 'FRIDAY' ELSE '' end
    + ' OF EVERY '+cast( p.EVERYNPERIODS as char(1) ) + ' MONTH '
    WHEN p.type =1 THEN
    'DAY '+ cast( p.MONTHLYABSDAY as char(2)) +' OF EVERY '+ cast( p.EVERYNPERIODS as char(2) ) + ' MONTH(S) '
    WHEN p.type =6 THEN
    ' EVERY '+ ' YEAR AT THE '
    + CASE WHEN p.YEARLYRELWEEK = 0 then 'FIRST ' ELSE ' ' end
    + CASE WHEN p.YEARLYRELWEEK = 1 then 'SECOND ' ELSE '' end
    + CASE WHEN p.YEARLYRELWEEK = 2 then 'THIRD ' ELSE '' end
    + CASE WHEN p.YEARLYRELWEEK = 3 then 'FOURTH ' ELSE '' end
    + CASE WHEN p.YEARLYRELWEEK = 4 then 'LAST ' ELSE '' end
    + CASE WHEN p.YEARLYRELDAY = 1 THEN 'MONDAY' ELSE '' end
    + CASE WHEN p.YEARLYRELDAY = 2 THEN 'SATURDAY' ELSE '' end
    + CASE WHEN p.YEARLYRELDAY = 3 THEN 'SUNDAY' ELSE '' end
    + CASE WHEN p.YEARLYRELDAY = 4 THEN 'THURSDAY' ELSE '' end
    + CASE WHEN p.YEARLYRELDAY = 5 THEN 'TUESDAY' ELSE '' end
    + CASE WHEN p.YEARLYRELDAY = 6 THEN 'WED' ELSE '' end
    + CASE WHEN p.YEARLYRELDAY = 0 THEN 'FRIDAY' ELSE '' end
    + ' OF EVERY '
    + CASE WHEN p.YEARLYRELMONTH = 0 THEN 'JAN' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 1 THEN 'FEB' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 2 THEN 'MAR' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 3 THEN 'APR' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 4 THEN 'MAY' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 5 THEN 'JUN' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 6 THEN 'JUL' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 7 THEN 'AUG' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 8 THEN 'SEP' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 9 THEN 'OCT' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 10 THEN 'NOV' ELSE '' end
    + CASE WHEN p.YEARLYRELMONTH = 11 THEN 'DEC' ELSE '' end
    WHEN p.type =5 THEN
    'DAY '
    + CAST ( p.YEARLYABSDAY as char(2)) +' OF EVERY '
    + CASE WHEN p.YEARLYABSMONTH = 0 THEN 'JAN' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 1 THEN 'FEB' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 2 THEN 'MAR' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 3 THEN 'APR' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 4 THEN 'MAY' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 5 THEN 'JUN' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 6 THEN 'JUL' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 7 THEN 'AUG' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 8 THEN 'SEP' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 9 THEN 'OCT' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 10 THEN 'NOV' ELSE '' end
    + CASE WHEN p.YEARLYABSMONTH = 11 THEN 'DEC' ELSE '' end
    WHEN p.type =7 THEN
    'TRIGGER NAME:'+p2.SCHEDTRIGNAME
    END SCHEDULE_PLAN
    ,Email_Address.addresses -- email addresses for report schedules
    ,Email_Subject_Select.EMail_Subject -- email subjects for report schedules
    ,Filename_Select.filename -- saved report filename
    , p.cmid as schedule_cmid -- schedule id
    from CMOBJPROPS2 p -- schedule must exists
    inner join CMOBJPROPS26 o on p.cmid=o.cmid
    inner join CMOBJPROPS51 p2 on p.CMID=p2.CMID
    inner join CMOBJECTS ob on ob.cmid=o.cmid
    inner join CMOBJECTS ob2 on ob.pcmid=ob2.cmid
    inner join CMOBJNAMES n on n.cmid=ob2.cmid
    inner join CMCLASSES c on ob2.classid=c.classid
    left join CMREFNOORD2 z1 on z1.cmid = p.cmid
    left join CMOBJPROPS33 z2 on z2.CMID = z1.REFCMID
    left join Email_Address on o.cmid = Email_Address.cmid -- email addresses for report schedules
    left join email_subject_select on o.cmid = email_subject_select.cmid -- email subject for report schedules
    left join filename_select on o.cmid = filename_select.cmid -- saved filename output
    left join ( -- lastexecdate
    select oh.PCMID,
    max(ACTEXECTIME) as LastExecDate
    from CMOBJPROPS5 h, CMOBJECTS oh
    where h.CMID = oh.CMID
    and oh.CLASSID = 111
    group by oh.PCMID
    ) LE
    on ob2.cmid = LE.PCMID
    left join ( -- job steps? 
    select jobStep.PCMID as jobID
    , jobStep.CMID as jobStepID
    , r.REFCMID as reportID
    , o.PCMID as reportIDparent
    , n.NAME as JobStepName
    , c.NAME as JobStepType
    from CMREFNOORD1 r
    INNER JOIN CMOBJECTS jobStep ON r.CMID=jobStep.CMID
    INNER JOIN CMOBJECTS o ON r.REFCMID=o.CMID
    INNER JOIN CMOBJNAMES n ON r.REFCMID=n.CMID
    INNER JOIN CMCLASSES c on o.classid=c.classid
    where r.propid=10
    and n.isdefault=1
    and o.CLASSID in (10, 19, 234, 257)
    ) JL
    on ob2.cmid = JL.jobID
    where 1 = 1
    )
    /*****************************************************************************************************************************/
    -- get the object path with recursive query
    ,COGNOSCONTENT (CMID, PCMID, NAME, PARENT, CONTENT, PATH, LEVELNR, CLASSID)  
    as (  
    select
    A.CMID
    ,A.PCMID
    ,B.NAME
    ,B.NAME as PARENT
    ,C.NAME as CONTENT
    ,cast(B.NAME as varchar(512)) as PATH
    ,0 as LEVELNR
    ,a.CLASSID
    from CMOBJECTS A
    join CMOBJNAMES B on A.CMID = B.CMID
    join CMCLASSES C on A.CLASSID = C.CLASSID
    where B.ISDEFAULT = 1
    and A.PCMID = 0
    and A.CMID in ( 2, 4 ) -- 2 is teamcontent, 4 is personal folders
    UNION ALL
    select
    A.CMID
    ,A.PCMID
    ,B.NAME as PARENT
    ,D.NAME
    ,C.NAME
    ,cast(D.PATH + '\' + CAST (B.NAME as varchar(512)) as varchar(512)) as PATH
    ,D.LEVELNR + 1 as LEVELNR
    ,A.CLASSID
    from CMOBJECTS A
    join CMOBJNAMES B on A.CMID = b.CMID
    join CMCLASSES C on A.CLASSID = C.CLASSID
    join COGNOSCONTENT D on A.PCMID = D.CMID
    where B.ISDEFAULT = 1 
    --and A.CLASSID in (1, 5, 8, 18, 32) -- original - folder, account, namespaceFolder, namespace
    and A.CLASSID in (1, 5, 8, 10, 18, 19, 32, 55) -- changed for, added 10 and 18, 55, 19 - 19 for report view, 55 for job, 
    )
    ,UIDNAAM ( USERNAME, OBJID ) AS -- users
    ( select  
    upper(CMOBJPROPS33.NAME) as USERNAME
    ,CMOBJPROPS1.OBJID
    from CMOBJPROPS1 CMOBJPROPS1
    join CMOBJPROPS33 CMOBJPROPS33
    on CMOBJPROPS1.CMID = CMOBJPROPS33.CMID
    )
    ,NAMESPACE (NAMESPACE_ID) AS -- namespace
    ( SELECT C.OBJID AS NAMESPACE_ID
    from CMOBJECTS A
    join CMOBJNAMES B on A.CMID = B.CMID
    join CMOBJPROPS1 C on A.CMID = C.CMID
    join CMCLASSES D on A.CLASSID = D.CLASSID
    where B.ISDEFAULT = 1
    and A.CLASSID in (32)
    and upper(B.NAME) <> 'COGNOS'
    )
    SELECT DISTINCT
    "CognosSchedules"."CMID" AS "OBJECTCMID",
    "CognosSchedules"."PCMID" AS "OBJECTPCMID",
    "CognosSchedules".schedule_cmid as "SCHEDULECMID",
    "CognosSchedules"."CLASSNAME" AS "CLASSNAME",
    "CognosSchedules"."OBJECTNAME" AS "OBJECTNAME",
    -- "CognosSchedules"."REPORTID" AS "REPORTID",
    -- "CognosSchedules"."REPORTIDPARENT" AS "REPORTIDPARENT",
    -- "CognosSchedules"."JOBID" AS "JOBID",
    -- "CognosSchedules"."JOBSTEPID" AS "JOBSTEPID",
    "CognosSchedules"."JOBSTEPNAME" AS "JOBSTEPNAME",
    "CognosSchedules"."JOBSTEPTYPE" AS "JOBSTEPTYPE",
    "CognosSchedules"."OWNER" AS "OWNER",
    "CognosSchedules"."LASTEXECDATE" AS "LASTEXECDATE",
    "CognosSchedules"."CREATED" AS "CREATED",
    "CognosSchedules"."MODIFIED" AS "MODIFIED",
    "CognosSchedules"."ACTIVE" AS "ACTIVE",
    "CognosSchedules"."TYPE" AS "TYPE",
    "CognosSchedules"."REPORT_FREQ" AS "REPORT_FREQ",
    "CognosSchedules"."STARTDATE" AS "STARTDATE",
    "CognosSchedules"."ENDDATE" AS "ENDDATE",
    "CognosSchedules"."SCHEDULE_PLAN" AS "SCHEDULE_PLAN",
    --"CognosSchedules"."ADDRESSES" AS "ADDRESSES",
    --"CognosSchedules"."EMAIL_SUBJECT" AS "EMAIL_SUBJECT",
    --email_step.addresses_step AS EMAILSTEP,
    coalesce("CognosSchedules"."ADDRESSES", Email_Address1.addresses, Email_Address2.addresses) as EMAILADDRESS, -- report schedule, then job step, then job defaults
    coalesce("CognosSchedules"."EMAIL_SUBJECT", Email_Subject1.Email_SUbject, Email_Subject2.Email_Subject) as EMAILSUBJECT, -- report schedule, then job step, then job defaults
    coalesce("CognosSchedules"."FileName", FileName1.FileName, Filename2.Filename) as SAVEDFILENAME, -- report schedule, then job step, then job defaults
    case
    when ( charindex( NAMESP.NAMESPACE_ID + ':u:', CC.PATH) = 0 ) then ( CC.PATH )
    else ( ISNULL(UIDNAAM.USERNAME, '') + substring(CC.PATH, charindex('\My Folders', CC.PATH), len(CC.PATH) ) ) 
    end as SCHEDULEPATH,
    case
    when ( CCSTEP.PATH is null ) then ( CCSTEP.PATH )
    when ( charindex(NAMESP.NAMESPACE_ID + ':u:', CCSTEP.PATH) = 0 ) then ( CCSTEP.PATH ) 
    else ( ISNULL(UIDNAAM.USERNAME, '') + substring(CCSTEP.PATH, charindex('\My Folders', CCSTEP.PATH), len(CCSTEP.PATH )) ) 
    end as STEPPATH
    FROM "CognosSchedules"
    LEFT JOIN COGNOSCONTENT CC on cc.CMID = "CognosSchedules".CMID -- PCMID, changed to CMID to get the path + object
    cross join NAMESPACE NAMESP 
    left join UIDNAAM on substring(CC.PATH, charindex(NAMESP.NAMESPACE_ID + ':u:', CC.PATH), len(NAMESP.NAMESPACE_ID) + 35) = UIDNAAM.OBJID  -- was 49, changed to len(NAMESP.NAMESPACE_ID) + 35
    LEFT JOIN COGNOSCONTENT CCSTEP on CCSTEP.CMID = "CognosSchedules".REPORTID -- was REPORTIDPARENT, i changed to REPORTID
    left join Email_Address Email_Address1 on "CognosSchedules".JOBSTEPID = Email_Address1.cmid -- email addresses for job step schedules
    left join email_subject_select Email_Subject1 on "CognosSchedules".JOBSTEPID = Email_Subject1.cmid -- email subject for report schedules
    left join Email_Address Email_Address2 on "CognosSchedules".cmid = Email_Address2.cmid -- email addresses for job steps that use the job default
    left join email_subject_select Email_Subject2 on "CognosSchedules".cmid = Email_Subject2.cmid -- email subject for job steps that use the job default
    left join filename_select as FileName1 on "CognosSchedules".JOBSTEPID = FileName1.cmid -- saved filename output for job step
    left join filename_select as FileName2 on "CognosSchedules".cmid = filename2.cmid -- saved filename output for job defaults
    ORDER BY 1, "CLASSNAME", "OBJECTNAME", jobstepname



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 25.  RE: Create a report from the content store db providing details of schedules

    Posted Fri August 30, 2024 08:11 AM

    I have made some changes to the posted ms sql above to return information regarding schedules. in returning the email subject - CHARINDEX does not work consistently on data type ntext. and i am working on the owner. it does not seem 100% correct. also going to add support for multiple namespaces. post here if you want the updated version



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 26.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 05:42 PM

    Hi @brenda grossnickle ,  I'm getting below errors. Do you have update SQL, please? Appreciate it.

    Msg 537, Level 16, State 6, Line 2
    Invalid length parameter passed to the LEFT or SUBSTRING function.

    LA



    ------------------------------
    Lyn
    ------------------------------

    Original Message


  • 27.  RE: Create a report from the content store db providing details of schedules

    Posted Wed September 04, 2024 12:30 PM

    The Msg 537, Level 16, State 6, Line 2 "Invalid length parameter passed to the LEFT or SUBSTRING function." is because CHARINDEX does not work consistently on data type ntext. So in the email_subject CTE i had to cast delivoption as varchar. Just replace the existing cte email_subject and email_subject_select with the following code. I know that OWNER is not working. Even if i take ownership of the report, or the schedule it still shows the previous owner. And ... it currently only works for a single namespace. When i have time i will work on those. 

    , email_subject as
        (
            select cmid, cast(delivoptions as varchar(max)) delivoptions, cast(0 as int) as st,cast( len('subject;deliveryOptionEnum;') as int) as ln, cast(0 as int) as en, cast(null as varchar(max)) as email_subject 
            from CMOBJPROPS26
            union all
            select cmid, cast(delivoptions as varchar(max)) delivoptions, cast(t1.st as int), cast(ln as int), cast(t2.en as int), cast(t3.n as varchar(max))
            from email_subject
            cross apply (values (charindex('subject;deliveryOptionEnum;', cast(delivoptions as varchar(max)), email_subject.st + 1) ) ) t1(st)
            cross apply (values (charindex('</value>', cast(delivoptions as varchar(max)), t1.st)) ) t2(en)
            cross apply (values (case when t1.st > 0 and t2.en > 0 then substring(delivoptions, t1.st + ln, t2.en - (t1.st + ln)) end) ) t3(n)
            where t1.st > 0
        )
    , email_subject_select as 
        (
        select cmid, st, ln, en, ltrim(rtrim(reverse(left(reverse(email_subject), charindex('>',reverse(email_subject)) - 1)))) email_subject
        from email_subject
        where st > 0 
        )



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 28.  RE: Create a report from the content store db providing details of schedules

    Posted Sun September 01, 2024 09:10 AM

    The most robust option would be a simple SDK code.

    Or, you can try free tools like Motio

    ===========================================
    MotioPI gives you the ability to search for schedules associated with reports, jobs, report views, and query objects in Cognos.
    Select the "Schedule Tab" from the panel column in MotioPI.

    https://motio.com/find-schedules-associated-with-cognos-reports/



    ------------------------------
    Andrei Istomine
    Open to work - anything Cognos
    https://www.linkedin.com/in/andreii/
    ------------------------------

    Original Message


  • 29.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 08:10 AM

    @andrei, i want to build a report of schedule information that the clients can run from within cognos. the thought is that my query will run against the content store and store the result set in a table in the reporting database (not sure what that is called). motio is a good tool but for that reason and others it will not work in this instance. Do we know that the SDK would give me all the information on schedules? Think that I asked this once on the forum and did not get an affirmative response. 



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 30.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 10:06 AM
    Edited by Andrei Istomine Tue September 03, 2024 10:07 AM

    In general, accessing Cognos Content Store DB directly is never recommended or supported.

    If you've never done any Cognos SDK programming it can be challenging to start.

    Cognos installer now contains all the SDK sample code.

    You can start with the links below.

    SDK How can I find all the schedules

    monthlyAbsoluteDay



    ------------------------------
    Andrei Istomine
    Open to work - anything Cognos
    https://www.linkedin.com/in/andreii/
    ------------------------------

    Original Message


  • 31.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 10:35 AM

    Yes i know direct query of the content store is not supported. And i have done some java and .net SDK development in the past for a standalone utility that creates the data sources. But our issue with the Schedule data is that we want it to execute every night and not be a standalone utility that is manually executed. Our product has an MS SQL backend and does not have a .net or java component. Plus we are a reseller of cognos and support several hundred individual instances of cognos. We would need a generic way to deliver and execute the SDK .net or java software that does not require a manual setup at each instance. We have not found that delivery method. We tried once to deliver an SDK that required the setup of a windows task and most of our clients never set it up. 



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 32.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 11:11 AM

    I agree that technically implementing an  SQL query  against CS requires less effort than an SDK code.

    But then you must  guess/reverse engineer the internal CS structure. I wonder how you would get this approved by your org Enterprise Architecture team.

     >We would need a generic way to deliver and execute the SDK .net or java software that does not require a manual setup at each instance. We have not found that delivery method. 

    Regardless of the solution you still have to deploy it to hundreds of Cognos instances.

    One robust option would be implementing an SDK code as a parametrized REST API datasource which returns list of schedule properties  to  be consumed by a report. You could deploy it under the same Cognos WAS instance.



    ------------------------------
    Andrei Istomine
    Open to work - anything Cognos
    https://www.linkedin.com/in/andreii/
    ------------------------------

    Original Message


  • 33.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 11:20 AM

    Reread my reply and could not find where i said that an SQL query against the content store was less effort than a .net solution using the Cognos SDK. 

    Can you point me to some resources for your robust option? 



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


  • 34.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 11:50 AM

    >Reread my reply and could not find where i said that an SQL query against the content store was less effort than a .net solution

    I meant that usually Cognos developers have somewhat solid SQL experience but rarely a lot coding exposure.

    Though, that SQL does not look easy  :-)

    Unfortunately, there is no public resources for my suggestion. It is just based on my own experience with Cognos integration.

    Essentially, you need to expose SDK output to Cognos data model for reporting.

    That's why I've suggested REST API datasource 

    Another way would be rendering SDK output as XML datasource

     



    ------------------------------
    Andrei Istomine
    Open to work - anything Cognos
    https://www.linkedin.com/in/andreii/
    ------------------------------

    Original Message


  • 35.  RE: Create a report from the content store db providing details of schedules

    Posted Tue September 03, 2024 11:07 AM

    Hi,

    I was able to find the link to the new file sent Friday. I have added this in and run the the auto tab. I also started with a fresh file. Included here are both the almactask.log and almacupdater.log files.

     

     

    Diego Ferrara
    Technical Service Team Leader | Almac Clinical Technologies

    Partnering to Advance Human Health

    T: +1 215 660 8520 Ext:21238
    M: 2674162223
    E: Diego.Ferrara@almacgroup.com
    1690 Sunneytown Pike | Lansdale | PA 19446 | United States of America

    www.almacgroup.com
     

    IXRS 3




    Original Message