Clarity

Hi Everyone,

Just recently released a report that just takes a project ID as a parameter to run. Was wondering if there's anywhere in the database that stores the report name, the user ID of the user who ran it, the datetime it was run, and the value entered into the required parameter? I think I found a few tables that could help after speaking with CA Support but I we couldn't quite get it working the way it needed to.

The closest I have to getting it working was this query:

select * from cmn_attribute_values where ATTRIBUTE_SET_ID = (select id from  cmn_attribute_value_sets where name = 'your_saved_parameter_name')

But unfortunately CMN_ATTRIBUTE_VALUE_SETS.NAME is stored as a number not as the text name I gave it in Clarity, and I'm not sure what table has a value that related this NAME number back to the text name.

Any help or insight would be greatly appreciated.

See TIP: ** Bonus Tip** Query: What Jobs/Reports Ran Over the Last n Days?

which should get you the job (i.e. report) and job runner (i.e. executing user).

Then for the parameters you need to look in the cmn_sch_job_run_params and cmn_attributes tables I think.

Hi David,

Thanks for the tip post. Is there anything similar for reports as that's strictly looking at the job tables.

For clarification, when I run the script from that tip it's only showing me scheduled jobs that Niku Administrator has run. I set it back a month (because I know reports have been run in the last month and I can't seem to find anything. Perhaps I just need to fiddle with it though.

The same tables should contain reports (i.e. the system models reports as a type of job) - the query I use to pull data from these tables filters on cmn_sch_job_definitions.job_type = 50316 to pick up reports only.

(I'd share my query, but its a bit fiddly where it is pulling out the parameters for local bespoke reports so I can't share it "as is")

Much appreciated! I finally got it working with some playing around. Then it was just a matter of understanding the CMN_SEC_USERS table as well as the CMN_SCH_JOB_RUN_PARAMS table. Luckily I only have one parameter for this report so I didn't find it difficult to differentiate between the different params attached to the report.

Final query (in case anyone is interested - and note that this is only looking for one parameter on one report, but I'm sure you could adjust it to look for specific parameters depending on the report if looking for multiple reports):

SELECT  CMN_SCH_JOBS.NAME REPORT,

        Count(CMN_SCH_JOB_RUNS.USER_ID) AS TIMES_RUN,

        Concat(Concat(CMN_SEC_USERS.LAST_NAME, ', '), CMN_SEC_USERS.FIRST_NAME) RUNNER,

        Concat(Concat(INV_INVESTMENTS.CODE, ' - '), INV_INVESTMENTS.NAME) PROJECT_REPORTED

FROM    CMN_SCH_JOB_RUNS,

        CMN_SEC_USERS,

        CMN_SCH_JOBS,

        CMN_SCH_JOB_DEFINITIONS,

        CMN_SCH_JOB_RUN_PARAMS,

        INV_INVESTMENTS      

WHERE   CMN_SCH_JOB_RUNS.JOB_ID = CMN_SCH_JOBS.ID

        AND CMN_SCH_JOBS.JOB_DEFINITION_ID = CMN_SCH_JOB_DEFINITIONS.ID

        AND CMN_SCH_JOB_DEFINITIONS.JOB_TYPE = 50316

        AND CMN_SCH_JOB_RUNS.USER_ID = CMN_SEC_USERS.ID

        AND CMN_SCH_JOB_RUN_PARAMS.JOB_RUN_ID = CMN_SCH_JOB_RUNS.ID

        AND CMN_SCH_JOB_RUN_PARAMS.ATTRIBUTE_ID = 5116015

        AND CMN_SCH_JOB_RUN_PARAMS."VALUE" = INV_INVESTMENTS.ID

        AND Trunc(CMN_SCH_JOB_RUNS.START_DATE, 'MM') = Trunc(SYSDATE, 'MM')

GROUP BY Concat(Concat(CMN_SEC_USERS.LAST_NAME, ', '), CMN_SEC_USERS.FIRST_NAME), Concat(Concat(INV_INVESTMENTS.CODE, ' - '), INV_INVESTMENTS.NAME), CMN_SCH_JOBS.NAME

Excellent!!

Thanks for posting this Matthew.Paxman

Good stuff - note that entries in those tables will get cleared out by housekeeping jobs though, so if you want a "long term" history of usage you'll need to save the information elsewhere (if you are just after recent info, you are fine though).