Cognos Analytics

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

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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Mon August 30, 2021 02:06 PM
From: Michael Walter
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Tue August 31, 2021 06:15 AM
From: Thomas van der Meer
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Tue August 31, 2021 06:15 AM
From: Thomas van der Meer
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

@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



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

Original Message:
Sent: Wed September 01, 2021 09:16 AM
From: Wendi Lewis
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

can you share the code for your Fn_Search functions?

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

Original Message:
Sent: Wed September 01, 2021 09:47 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Wed September 01, 2021 09:16 AM
From: Wendi Lewis
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Thu November 04, 2021 08:33 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

can you share the code for your Fn_Search functions?

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

Original Message:
Sent: Wed September 01, 2021 09:47 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Wed September 01, 2021 09:16 AM
From: Wendi Lewis
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Fri November 12, 2021 09:57 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Fri November 12, 2021 10:46 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 09:57 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

@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:
Sent: Sun November 14, 2021 11:46 PM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 10:46 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 09:57 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Tue November 16, 2021 11:31 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Sun November 14, 2021 11:46 PM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 10:46 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 09:57 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Wed November 17, 2021 07:35 AM
From: William Sai-Palm
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Tue November 16, 2021 11:31 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Sun November 14, 2021 11:46 PM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 10:46 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 09:57 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Wed November 17, 2021 09:20 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Wed November 17, 2021 07:35 AM
From: William Sai-Palm
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Tue November 16, 2021 11:31 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Sun November 14, 2021 11:46 PM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 10:46 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 09:57 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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

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:
Sent: Tue November 16, 2021 11:31 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Sun November 14, 2021 11:46 PM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 10:46 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 09:57 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Here you go....


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

Original Message:
Sent: Fri November 12, 2021 07:55 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Fri November 12, 2021 01:53 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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:
Sent: Thu November 11, 2021 10:21 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Thu November 11, 2021 08:48 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

Hi @Brenda Grossnickle
Can you give me details of what you are querying in the code?
Regards,
Christine​

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

Original Message:
Sent: Thu November 11, 2021 07:40 AM
From: brenda grossnickle
Subject: Create a report from the content store db providing details of schedules

@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:
Sent: Fri August 27, 2021 10:43 AM
From: Christine Beswick
Subject: Create a report from the content store db providing details of schedules

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