Clarity

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 1/4/2011

Bonus Tip for Today:

These are simple and could be expanded, but they provide a simple yet powerful listing of what job or reports have run and the range of time they ran.

MS SQL Server

select name, runs.START_DATE, runs.END_DATE, runs.job_hostname, defs.executable
from cmn_sch_job_runs runs, cmn_sch_jobs jobs, cmn_sch_job_definitions defs
where runs.job_id = jobs.id and
jobs.JOB_DEFINITION_ID = defs.id and
runs.start_date > getdate() - 3


Oracle

select name, runs.START_DATE, runs.END_DATE, runs.job_hostname, defs.executable
from cmn_sch_job_runs runs, cmn_sch_jobs jobs, cmn_sch_job_definitions defs
where runs.job_id = jobs.id and
jobs.JOB_DEFINITION_ID = defs.id and
runs.start_date > sysdate - 3

-shawn

Thanks Shawn for the bonus tip this week!

Chris

This is AWESOME!

Thanks,
you might want to add/modify that a little like
(SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL) Time_now,
(SELECT TO_CHAR(cmn_sch_job_runs.start_date,'MM.DD HH24:MI:SS') FROM DUAL) start_date,
(SELECT TO_CHAR(cmn_sch_job_runs.end_date, 'HH24:MI:SS') FROM DUAL) end_date,
CASE
WHEN cmn_sch_job_runs.end_date is null
THEN TO_CHAR((SYSDATE- cmn_sch_job_runs.start_date)*8*60*60)
ELSE 'DONE'
END
Runtime_now,

cmn_sch_job_runs.total_execution_time/1000 Exec_time_s,
cmn_sch_job_runs.percent_complete pcnt_compl,
cmn_sch_job_runs.Status_code,
concat(concat(cmn_sec_users.last_name,', '),cmn_sec_users.first_name) RUNNER
from cmn_sch_job_runs, cmn_sch_jobs, cmn_sec_users
Where cmn_sch_jobs.id = cmn_sch_job_runs.job_id
and cmn_sch_job_runs.user_id = cmn_sec_users.id

to make it more cleat which jobs are done and which ones are running and for how long or failed and who is getting all the cumulating notifications of completed jobs.

You really need something like that after the OOTB jobs don't give any log entries any more.

Just wondering again...
When I make that into a portlet I don't see timeslicing while querying directly the db I can see timeslicing while it is running.
For that purpose time now and the execution time help catching how long it runs.

Martti K.

Shawn

I keep coming back to this post and I have yet to figure out the answer to my question. I have tried to search the forums, but I don't see what I am seeking.
Is there a way to tweak your query to pull the last run PORTLETs or most run PORTLETS?

I sure hope you get this, since this is an older post.

Thanks
Lynn

Lynn,

The jobs/reports tables won't show this information. And the only way may actually be by accessing the app-access.logs, which in 12.1 is a delimited file. This makes it something that can easily be imported into a spreadsheet, database, ect.

We've imported this data in the past for various diagnostics and metrics and it has been useful. We don't currently have a tool to do this. (but it is in the forefront of our minds.... :wink: )

-shawn

Hi Shawn,

We had to get a list of portlets being accessed in the last N number of days, along with who ran them in Clarity.

Just wanted to reconfirm: is there any other place other than the app-access.logs?

Thanks,
Georgy

Georgy,

Yeah, that’s pretty much the way you would do it. There really isn’t any other way.

-shawn

From: CA Clarity Global User Community [mailto:CommunityAdmin@communities-mail.ca.com]
Sent: Thursday, August 23, 2012 6:32 AM
To: mb.12877143.98895226@myca-email.ca.com
Subject: [Tuesday's Tips] RE: TIP: ** Bonus Tip** Query: What Jobs/Reports Ran Over the Last n Days?

Hi Shawn,

We had to get a list of portlets being accessed in the last N number of days, along with who ran them in Clarity.

Just wanted to reconfirm: is there any other place other than the app-access.logs?

Thanks,
Georgy
Posted by:Georgy
--
CA Communities Message Boards
98897766
mb.12877143.98895226@myca-email.ca.com<mailto:mb.12877143.98895226@myca-email.ca.com>
https://communities.ca.com