#CognosAnalyticswithWatson

Hi Eli,

>if there are ways to use the SDK to accomplish this task, would you kindly send me the solution

I can offer you a step towards solving your problem. Not the solution itself.

The Cognos base object from the searchpath query, e.g. //report contains what you are looking for, depending on the QueryProps you set. In general the information is there, i can confirm that.

I posted on the solution [SDK] Cognos Configuration and Automation.

If you need help to implement, feel free to create an issue for that on github and I would be more then happy to have a look.

I would be interested to implement a generic way of asking for ContentStore objects, if there was more need for such.

Hth.

Ralf

------------------------------
Ralf Roeber
https://linkedin.com/in/ralf-roeber-470425a/
------------------------------

Original Message:
Sent: Wed March 02, 2022 03:31 PM
From: Elihu El
Subject: Listing Who and When Reports Were Last Run

Good Afternoon,

I hope that your day is going well. I would like to generate a list of reports along with various attributes such as Last Run Date, Last Run User, etc. Here is the SQL that I have so far:

select * from CMOBJECTS as CO

inner join CMOBJECTS as CO2 on CO2.PCMID = CO.CMID

inner join CMOBJNAMES as CON on CON.CMID = CO.CMID

inner join CMOBJPROPS7 as CO7 on CO7.CMID = CO.CMID

where

(CO.classid = 10 or CO.classid = 209)

I do not want Auditing turned on.  However, if there are ways to use the SDK to accomplish this task, would you kindly send me the solution.  Thanks.

If you have any questions, call me at 410-312-8822.

Warm Regards,

Eli

#CognosAnalyticswithWatson

Hello

Below is SQL we use against our Oracle audit database. We are at version 11.1.7. Hope it helps. The formatting (indendation) was lost when I cut and pasted into the IBM's site used to email the listserv. Sorry. It would make things easier to understand. The query returns  username, userid, latest_report_name, latest_report_package, latest_report_package_path, latest_report_path, latest_rpt_execution_date, num_report_executions.

Michael Sullivan
North Shore Community College

SELECT logons.cogipf_username username,
logons.cogipf_userid userid,
SUBSTR(rpts.cogipf_reportname,
INSTR(rpts.cogipf_reportname,CHR(39)) + 1, -- start after first quote or at position one if is not a quote
CASE -- go for entire length if is no second quote
WHEN INSTR(rpts.cogipf_reportname,CHR(39),1,1) = 0 -- otherwise go for entire length to second quote minus one minus length to first quote
THEN LENGTH(rpts.cogipf_reportname)
ELSE INSTR(rpts.cogipf_reportname,CHR(39),1,2) - 1 - INSTR(rpts.cogipf_reportname,CHR(39),1,1)
END) latest_report_name,
CASE
WHEN INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) <> 0
THEN SUBSTR(rpts.cogipf_package,
INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) + 15,
INSTR(rpts.cogipf_package,CHR(39),INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) + 15)
- (INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) + 15))
WHEN INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) <> 0
THEN SUBSTR(rpts.cogipf_package,
INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) + 14,
INSTR(rpts.cogipf_package,CHR(39),INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) + 14)
- (INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) + 14))
ELSE NULL
END latest_report_package,
rpts.cogipf_package latest_report_package_path,
rpts.cogipf_reportpath latest_report_path,
MAX(TO_CHAR(rpts.cogipf_localtimestamp,'YYYYMMDD HH24MISS')) latest_rpt_execution_date,
COUNT(*) num_report_executions
FROM cogipf_userlogon logons,
cogipf_runreport rpts
WHERE logons.cogipf_logon_operation = 'Logon'
AND logons.cogipf_status = 'Success'
AND logons.cogipf_sessionid = rpts.cogipf_sessionid (+)
AND rpts.cogipf_status (+)= 'Success'
GROUP BY
logons.cogipf_username,
logons.cogipf_userid,
SUBSTR(rpts.cogipf_reportname,
INSTR(rpts.cogipf_reportname,CHR(39)) + 1, -- start after first quote or at position one if is not a quote
CASE -- go for entire length if is no second quote
WHEN INSTR(rpts.cogipf_reportname,CHR(39),1,1) = 0 -- otherwise go for entire length to second quote minus one minus length to first quote
THEN LENGTH(rpts.cogipf_reportname)
ELSE INSTR(rpts.cogipf_reportname,CHR(39),1,2) - 1 - INSTR(rpts.cogipf_reportname,CHR(39),1,1)
END),
CASE
WHEN INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) <> 0
THEN SUBSTR(rpts.cogipf_package,
INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) + 15,
INSTR(rpts.cogipf_package,CHR(39),INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) + 15)
- (INSTR(rpts.cogipf_package,'package[@name=' || CHR(39)) + 15))
WHEN INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) <> 0
THEN SUBSTR(rpts.cogipf_package,
INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) + 14,
INSTR(rpts.cogipf_package,CHR(39),INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) + 14)
- (INSTR(rpts.cogipf_package,'module[@name=' || CHR(39)) + 14))
ELSE NULL
END,
rpts.cogipf_package,
rpts.cogipf_reportpath

------------------------------
Michael Sullivan
------------------------------

Original Message:
Sent: Tue March 08, 2022 12:16 PM
From: Ralf Roeber
Subject: Listing Who and When Reports Were Last Run

Hi Eli,

>if there are ways to use the SDK to accomplish this task, would you kindly send me the solution

I can offer you a step towards solving your problem. Not the solution itself.

The Cognos base object from the searchpath query, e.g. //report contains what you are looking for, depending on the QueryProps you set. In general the information is there, i can confirm that.

I posted on the solution [SDK] Cognos Configuration and Automation.

If you need help to implement, feel free to create an issue for that on github and I would be more then happy to have a look.

I would be interested to implement a generic way of asking for ContentStore objects, if there was more need for such.

Hth.

Ralf

------------------------------
Ralf Roeber
https://linkedin.com/in/ralf-roeber-470425a/

Original Message:
Sent: Wed March 02, 2022 03:31 PM
From: Elihu El
Subject: Listing Who and When Reports Were Last Run

Good Afternoon,

I hope that your day is going well. I would like to generate a list of reports along with various attributes such as Last Run Date, Last Run User, etc. Here is the SQL that I have so far:

select * from CMOBJECTS as CO

inner join CMOBJECTS as CO2 on CO2.PCMID = CO.CMID

inner join CMOBJNAMES as CON on CON.CMID = CO.CMID

inner join CMOBJPROPS7 as CO7 on CO7.CMID = CO.CMID

where

(CO.classid = 10 or CO.classid = 209)

I do not want Auditing turned on.  However, if there are ways to use the SDK to accomplish this task, would you kindly send me the solution.  Thanks.

If you have any questions, call me at 410-312-8822.

Warm Regards,

Eli

#CognosAnalyticswithWatson

i have some ms sql that writes all the cognos objects to a table. i use the inventory table to compare before and after releases so that nothing "accidentally" gets changed, added or deleted. i also have sql sprocs that will search for text in report definitions and change text in report definitions. but it will not give you last run user. and i think that last modified date might be last run date. but not sure. the owner of our reports is always squirrely because it is development and our team members come and go and we change out authentication. this is an example. i can send the code if you want.



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

Original Message:
Sent: Wed March 02, 2022 03:31 PM
From: Elihu El
Subject: Listing Who and When Reports Were Last Run

Good Afternoon,

I hope that your day is going well. I would like to generate a list of reports along with various attributes such as Last Run Date, Last Run User, etc. Here is the SQL that I have so far:

select * from CMOBJECTS as CO

inner join CMOBJECTS as CO2 on CO2.PCMID = CO.CMID

inner join CMOBJNAMES as CON on CON.CMID = CO.CMID

inner join CMOBJPROPS7 as CO7 on CO7.CMID = CO.CMID

where

(CO.classid = 10 or CO.classid = 209)

I do not want Auditing turned on.  However, if there are ways to use the SDK to accomplish this task, would you kindly send me the solution.  Thanks.

If you have any questions, call me at 410-312-8822.

Warm Regards,

Eli

#CognosAnalyticswithWatson

@InnerCircle I would love the code if you could sent it. I appreciate it. I am also looking for a way to query the content store to pull the database objects for each report.
Thanks!
Will
​

------------------------------
Will Phillips
------------------------------

Original Message:
Sent: Tue March 08, 2022 01:39 PM
From: brenda grossnickle
Subject: Listing Who and When Reports Were Last Run

i have some ms sql that writes all the cognos objects to a table. i use the inventory table to compare before and after releases so that nothing "accidentally" gets changed, added or deleted. i also have sql sprocs that will search for text in report definitions and change text in report definitions. but it will not give you last run user. and i think that last modified date might be last run date. but not sure. the owner of our reports is always squirrely because it is development and our team members come and go and we change out authentication. this is an example. i can send the code if you want.



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

Original Message:
Sent: Wed March 02, 2022 03:31 PM
From: Elihu El
Subject: Listing Who and When Reports Were Last Run

Good Afternoon,

I hope that your day is going well. I would like to generate a list of reports along with various attributes such as Last Run Date, Last Run User, etc. Here is the SQL that I have so far:

select * from CMOBJECTS as CO

inner join CMOBJECTS as CO2 on CO2.PCMID = CO.CMID

inner join CMOBJNAMES as CON on CON.CMID = CO.CMID

inner join CMOBJPROPS7 as CO7 on CO7.CMID = CO.CMID

where

(CO.classid = 10 or CO.classid = 209)

I do not want Auditing turned on.  However, if there are ways to use the SDK to accomplish this task, would you kindly send me the solution.  Thanks.

If you have any questions, call me at 410-312-8822.

Warm Regards,

Eli

#CognosAnalyticswithWatson

Hi Brenda, could you share with me the code to get this table, I'm interested on the paths to all cognos objects.

------------------------------
Paulo Mcmiller Crisostomo de Oliveira
Banco do Brasil
83941906703
------------------------------

Original Message:
Sent: Tue March 08, 2022 01:39 PM
From: brenda grossnickle
Subject: Listing Who and When Reports Were Last Run

i have some ms sql that writes all the cognos objects to a table. i use the inventory table to compare before and after releases so that nothing "accidentally" gets changed, added or deleted. i also have sql sprocs that will search for text in report definitions and change text in report definitions. but it will not give you last run user. and i think that last modified date might be last run date. but not sure. the owner of our reports is always squirrely because it is development and our team members come and go and we change out authentication. this is an example. i can send the code if you want.



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

Original Message:
Sent: Wed March 02, 2022 03:31 PM
From: Elihu El
Subject: Listing Who and When Reports Were Last Run

Good Afternoon,

I hope that your day is going well. I would like to generate a list of reports along with various attributes such as Last Run Date, Last Run User, etc. Here is the SQL that I have so far:

select * from CMOBJECTS as CO

inner join CMOBJECTS as CO2 on CO2.PCMID = CO.CMID

inner join CMOBJNAMES as CON on CON.CMID = CO.CMID

inner join CMOBJPROPS7 as CO7 on CO7.CMID = CO.CMID

where

(CO.classid = 10 or CO.classid = 209)

I do not want Auditing turned on.  However, if there are ways to use the SDK to accomplish this task, would you kindly send me the solution.  Thanks.

If you have any questions, call me at 410-312-8822.

Warm Regards,

Eli

#CognosAnalyticswithWatson

@brenda grossnickle Can you please share the SQL code? I'm interested in seeing how you got the Owner name, created, and last modified. 

------------------------------
Raj Acharya
------------------------------

Original Message:
Sent: Tue March 08, 2022 01:39 PM
From: brenda grossnickle
Subject: Listing Who and When Reports Were Last Run

i have some ms sql that writes all the cognos objects to a table. i use the inventory table to compare before and after releases so that nothing "accidentally" gets changed, added or deleted. i also have sql sprocs that will search for text in report definitions and change text in report definitions. but it will not give you last run user. and i think that last modified date might be last run date. but not sure. the owner of our reports is always squirrely because it is development and our team members come and go and we change out authentication. this is an example. i can send the code if you want.



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

Original Message:
Sent: Wed March 02, 2022 03:31 PM
From: Elihu El
Subject: Listing Who and When Reports Were Last Run

Good Afternoon,

I hope that your day is going well. I would like to generate a list of reports along with various attributes such as Last Run Date, Last Run User, etc. Here is the SQL that I have so far:

select * from CMOBJECTS as CO

inner join CMOBJECTS as CO2 on CO2.PCMID = CO.CMID

inner join CMOBJNAMES as CON on CON.CMID = CO.CMID

inner join CMOBJPROPS7 as CO7 on CO7.CMID = CO.CMID

where

(CO.classid = 10 or CO.classid = 209)

I do not want Auditing turned on.  However, if there are ways to use the SDK to accomplish this task, would you kindly send me the solution.  Thanks.

If you have any questions, call me at 410-312-8822.

Warm Regards,

Eli

#CognosAnalyticswithWatson

I wrote this query to catch reports that executed with errors.

This code looks in history table. This table has request time, init time and end time. May be useful for you with some changes.

SELECT 
CMID,
OBJECT_TYPE,
OWNER,
OBJECT_NAME, 
COUNT(HIST_SEVERITY) QTD_SEVERITY_1,
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

HAVING COUNT(HIST_SEVERITY) >= 3​

------------------------------
JEAM COELHO
Cognos Solution Architect

LinkedIn: https://www.linkedin.com/in/jeamcoelho/
------------------------------

Original Message:
Sent: Wed March 02, 2022 03:31 PM
From: Elihu El
Subject: Listing Who and When Reports Were Last Run

Good Afternoon,

I hope that your day is going well. I would like to generate a list of reports along with various attributes such as Last Run Date, Last Run User, etc. Here is the SQL that I have so far:

select * from CMOBJECTS as CO

inner join CMOBJECTS as CO2 on CO2.PCMID = CO.CMID

inner join CMOBJNAMES as CON on CON.CMID = CO.CMID

inner join CMOBJPROPS7 as CO7 on CO7.CMID = CO.CMID

where

(CO.classid = 10 or CO.classid = 209)

I do not want Auditing turned on.  However, if there are ways to use the SDK to accomplish this task, would you kindly send me the solution.  Thanks.

If you have any questions, call me at 410-312-8822.

Warm Regards,

Eli

#CognosAnalyticswithWatson