I needed to find a way to see all the reports that my users are putting in their "My Folders" directories. I'm posting this here in case someone else has the same challenge and is looking for an answer.
I wrote SQL to go directly against the Cognos Metadata as follows:
/* -----------------------------------------------------
Original SQL by TheCognosDave, Oct 18,2013
-------------------------------------------------------- */
SELECT Report_Name, MODIFIED, coalesce(B.Name,'') as CognosUser
FROM (
SELECT
NMS.NAME as Report_Name,
OBJ.MODIFIED,
CASE
WHEN OBJ_1.CLASSID=5 THEN OBJ_1.CMID
WHEN OBJ_2.CLASSID=5 THEN OBJ_2.CMID
WHEN OBJ_3.CLASSID=5 THEN OBJ_3.CMID
WHEN OBJ_4.CLASSID=5 THEN OBJ_4.CMID
WHEN OBJ_5.CLASSID=5 THEN OBJ_5.CMID
WHEN OBJ_6.CLASSID=5 THEN OBJ_6.CMID
WHEN OBJ_7.CLASSID=5 THEN OBJ_7.CMID
END as ACCT_CMID
FROM CMOBJECTS OBJ
INNER JOIN CMCLASSES CLS ON (OBJ.CLASSID = CLS.CLASSID)
INNER JOIN CMOBJNAMES NMS ON (OBJ.CMID = NMS.CMID)
INNER JOIN CMOBJECTS c_OBJ ON (OBJ.PCMID = c_OBJ.CMID)
INNER JOIN CMCLASSES c_CLS ON (c_OBJ.CLASSID = c_CLS.CLASSID)
INNER JOIN CMOBJNAMES c_NMS ON (c_OBJ.CMID = c_NMS.CMID)
INNER JOIN CMOBJECTS OBJ_1 ON (c_OBJ.PCMID = OBJ_1.CMID)
INNER JOIN CMOBJECTS OBJ_2 ON (OBJ_1.PCMID = OBJ_2.CMID)
INNER JOIN CMOBJECTS OBJ_3 ON (OBJ_2.PCMID = OBJ_3.CMID)
INNER JOIN CMOBJECTS OBJ_4 ON (OBJ_3.PCMID = OBJ_4.CMID)
INNER JOIN CMOBJECTS OBJ_5 ON (OBJ_4.PCMID = OBJ_5.CMID)
INNER JOIN CMOBJECTS OBJ_6 ON (OBJ_5.PCMID = OBJ_6.CMID)
INNER JOIN CMOBJECTS OBJ_7 ON (OBJ_6.PCMID = OBJ_7.CMID)
WHERE NMS.MAPDLOCALEID = 24 AND c_NMS.MAPDLOCALEID = 24 AND OBJ.CLASSID = 10
) A LEFT JOIN CMOBJPROPS33 B ON (A.ACCT_CMID=B.CMID)
ORDER BY MODIFIED DESC
/* ----------------------------------------------------- -------------------------------------------------------- */
I'm having fun lately reverse engineering the Cognos Datamodel to figure these things out. I hope it's useful to someone out there beside me ! :P
cheers,
Dave