Hello,

Can someone help with SDK example on how to generate list of failed scheduled reports like it shows on Cognos Admin > Status > Past Activities tab. Which classes to look at. Does the SDK support Past Activities/History. 

Hi,

Using this SQL below in Content Store database, you will get all failed schedule.

SELECT 
CMID,
OBJECT_TYPE,
OWNER,
OBJECT_NAME, 
COUNT(HIST_SEVERITY) ERRORS_COUNT,
folder3, 
folder2, 
folder1,
folder

FROM(

SELECT
s.CMID,
UPPER(c.NAME) AS OBJECT_TYPE,
UPPER(COALESCE(props33.USERID, props33.NAME, 'N/A')) OWNER,
obj.NAME AS OBJECT_NAME,
date_trunc('DAYS' , props5.REQEXECTIME) REQEXECTIME,
LAG(date_trunc('DAYS' , props5.REQEXECTIME) , 1 , DATE('1900-01-01')) OVER(PARTITION BY s.CMID ORDER BY date_trunc('DAYS' , props5.REQEXECTIME)) PRIOR_REQEXECTIME,
MAX(hist.SEVERITY) HIST_SEVERITY,
opfolder3.NAME folder3, 
opfolder2.NAME folder2, 
opfolder1.NAME folder1,
opfolder.NAME folder

FROM CMOBJPROPS2 s 
INNER JOIN CMOBJECTS o ON s.CMID = o.CMID 
INNER JOIN CMOBJECTS p ON p.CMID = o.PCMID
INNER JOIN CMCLASSES c ON c.CLASSID = p.CLASSID 
INNER JOIN CMOBJNAMES obj ON obj.CMID = p.CMID AND obj.ISDEFAULT = 1
INNER JOIN CMREFNOORD2 REF ON o.CMID = REF.CMID
INNER JOIN CMOBJPROPS33 props33 ON REF.REFCMID = props33.CMID 
INNER JOIN (SELECT a.*, b.PCMID FROM CMOBJPROPS5 a INNER JOIN CMOBJECTS b ON a.CMID = b.CMID WHERE b.CLASSID = 111) props5 ON props5.PCMID = p.CMID
INNER JOIN (SELECT a.*, b.PCMID FROM CMOBJPROPS5 a INNER JOIN CMOBJECTS b ON a.CMID = b.CMID WHERE b.CLASSID = 112) hist ON hist.PCMID = props5.CMID

INNER JOIN CMOBJECTS opf ON p.PCMID = opf.CMID
INNER JOIN CMOBJNAMES opfolder ON opf.CMID = opfolder.CMID AND opfolder.ISDEFAULT = 1
INNER JOIN CMOBJECTS opf1 ON opf.PCMID = opf1.CMID
INNER JOIN CMOBJNAMES opfolder1 ON opf1.CMID = opfolder1.CMID AND opfolder1.ISDEFAULT = 1
LEFT JOIN CMOBJECTS opf2 ON opf1.PCMID = opf2.CMID
LEFT JOIN CMOBJNAMES opfolder2 ON opf2.CMID = opfolder2.CMID AND opfolder2.ISDEFAULT = 1
LEFT JOIN CMOBJECTS opf3 ON opf2.PCMID = opf3.CMID
LEFT JOIN CMOBJNAMES opfolder3 ON opf3.CMID = opfolder3.CMID AND opfolder3.ISDEFAULT = 1

GROUP BY
s.CMID,
UPPER(c.NAME),
UPPER(COALESCE(props33.USERID, props33.NAME, 'N/A')) ,
obj.NAME,
date_trunc('DAYS' , props5.REQEXECTIME),
props5.REQEXECTIME,
opfolder3.NAME , 
opfolder2.NAME , 
opfolder1.NAME ,
opfolder.NAME 

HAVING MAX(hist.SEVERITY) = 1

) T

WHERE PRIOR_REQEXECTIME <> REQEXECTIME

GROUP BY 
CMID,
OBJECT_TYPE,
OWNER,
OBJECT_NAME,
folder3, 
folder2, 
folder1,
folder

That's an excellent start Jeam!

Just need to be careful only filtering for SEVERITY=1

From what I have noticed, SEVERITY in (1, 2, 4) seem to indicate a failure for importDeployment, jobDefinition, reportView, and report classes

Whereas SEVERITY=3 seems to indicate success without any warnings or failures

SELECT CMID,OBJECT_TYPE,OWNER,OBJECT_NAME, COUNT(HIST_SEVERITY) ERRORS_COUNT,folder3, folder2, folder1,folderFROM(SELECTs.CMID,UPPER(c.NAME) AS OBJECT_TYPE,UPPER(COALESCE(props33.USERID, props33.NAME, 'N/A')) OWNER,obj.NAME AS OBJECT_NAME,date_trunc('DAYS' , props5.REQEXECTIME) REQEXECTIME,LAG(date_trunc('DAYS' , props5.REQEXECTIME) , 1 , DATE('1900-01-01')) OVER(PARTITION BY s.CMID ORDER BY date_trunc('DAYS' , props5.REQEXECTIME)) PRIOR_REQEXECTIME,MAX(hist.SEVERITY) HIST_SEVERITY,opfolder3.NAME folder3, opfolder2.NAME folder2, opfolder1.NAME folder1,opfolder.NAME folderFROM 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.CLASSID INNER 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 INNER JOIN (SELECT a.*, b.PCMID FROM CMOBJPROPS5 a INNER JOIN CMOBJECTS b ON a.CMID = b.CMID WHERE b.CLASSID = 111) props5 ON props5.PCMID = p.CMIDINNER JOIN (SELECT a.*, b.PCMID FROM CMOBJPROPS5 a INNER JOIN CMOBJECTS b ON a.CMID = b.CMID WHERE b.CLASSID = 112) hist ON hist.PCMID = props5.CMIDINNER JOIN CMOBJECTS opf ON p.PCMID = opf.CMIDINNER JOIN CMOBJNAMES opfolder ON opf.CMID = opfolder.CMID AND opfolder.ISDEFAULT = 1INNER JOIN CMOBJECTS opf1 ON opf.PCMID = opf1.CMIDINNER JOIN CMOBJNAMES opfolder1 ON opf1.CMID = opfolder1.CMID AND opfolder1.ISDEFAULT = 1LEFT JOIN CMOBJECTS opf2 ON opf1.PCMID = opf2.CMIDLEFT JOIN CMOBJNAMES opfolder2 ON opf2.CMID = opfolder2.CMID AND opfolder2.ISDEFAULT = 1LEFT JOIN CMOBJECTS opf3 ON opf2.PCMID = opf3.CMIDLEFT JOIN CMOBJNAMES opfolder3 ON opf3.CMID = opfolder3.CMID AND opfolder3.ISDEFAULT = 1GROUP BYs.CMID,UPPER(c.NAME),UPPER(COALESCE(props33.USERID, props33.NAME, 'N/A')) ,obj.NAME,date_trunc('DAYS' , props5.REQEXECTIME),props5.REQEXECTIME,opfolder3.NAME , opfolder2.NAME , opfolder1.NAME ,opfolder.NAME HAVING MAX(hist.SEVERITY) = 1) TWHERE PRIOR_REQEXECTIME <> REQEXECTIMEGROUP BY CMID,OBJECT_TYPE,OWNER,OBJECT_NAME,folder3, folder2, folder1,folder