Hi,
Here is a SQL query I used to get the maximum informations about schedules. You may have to adapt filters on languages and object classes according to your Cognos environment. In the same way this script was made for an Oracle database, so some functions may not work with DB2 or SQL Server.
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 MOUZON
------------------------------
Original Message:
Sent: Fri October 15, 2021 04:20 AM
From: jonathan chesterton
Subject: Managing Scheduled Reports and Jobs
Hi,
Whilst it wont give you absolutely everything you are seeking have you tried MotioPI ( free version). It will happily provide you with a list of all schedules within your environment, their current status and the owners. You can see the detail behind each including the email distribution details but in the free version you cant grab this in a list. Not sure if the professional version provides anymore capability.
Not a sales pitch as I don't work for IBM or Motio but I reckon its worth a look.
:)
------------------------------
jonathan chesterton
NHS Supply Chain
Original Message:
Sent: Thu October 14, 2021 10:58 PM
From: JEAM COELHO
Subject: Managing Scheduled Reports and Jobs
Hi,
You might take a look into content store table CMOBJPROPS2.
This SQL may help you as a starting point:
SELECTUPPER(c.NAME) AS OBJECT_TYPE,obj.NAME AS OBJECT_NAME,HOUR(STARTDATE + CURRENT TIMEZONE) AS HOUR,UPPER(COALESCE(props33.USERID, props33.NAME, 'N/A')) SCHEDULE_OWNERFROM CMOBJPROPS2 s INNER JOIN CMOBJECTS o ON s.CMID = o.CMID INNER JOIN CMOBJECTS p ON p.CMID = o.PCMIDINNER JOIN CMCLASSES c ON c.CLASSID = p.CLASSIDINNER JOIN CMOBJNAMES obj ON obj.CMID = p.CMID AND obj.ISDEFAULT = 1INNER JOIN CMREFNOORD2 REF ON o.CMID = REF.CMIDINNER JOIN CMOBJPROPS33 props33 ON REF.REFCMID = props33.CMID WHERE ACTIVE = 1AND STARTDATE IS NOT NULLAND COALESCE( S.ENDDATE, CURRENT_TIMESTAMP) >= CURRENT_TIMESTAMPAND c.NAME IN ('dataSet2' , 'query' , 'report' , 'reportView' , 'jobDefinition')
------------------------------
JEAM COELHO
Cognos Solution Architect
LinkedIn: https://www.linkedin.com/in/jeamcoelho/
Original Message:
Sent: Thu October 07, 2021 12:00 PM
From: Jessica Lynch
Subject: Managing Scheduled Reports and Jobs
My organization is looking for a way to better manage our scheduled reports. We have over 1,000 active schedules, and we need a way to catalog and audit schedules and recipients to ensure we are not running multiple reports and jobs simultaneously and negatively impacting our server response time. We have Cognos Analytics through UKG Pro.
------------------------------
Jessica Lynch
------------------------------
#CognosAnalyticswithWatson