CA Workload Automation R11.3 Custom Reports

[Eric Vaughan]

Hi Everyone

Is anyone using CA Workload Automation R11.3 Custom reporting? We have a request to send a business until a report with all their failed jobs for the week and the canned failed jobs report is kind of like a all or nothing report. We were thinking about using the custom reports but not sure really where to begin with it. Is anyone using it and maybe have an example we could use to help build a custom failed report for certain applications? Or does anyone have any better ideas on how to handle this type of request?

Any help would be great

[Ravi Kiran Kunduri]

Hi,

I would suggest that you use the same FAILED_JOBS canned report , edit the report design to include the list of failed jobs from 1 week (this can be done by first downloading the report from 'report perspective', create a copy of it , change the SQL query such that it will list failed jobs for last 1 week ,save it with different name , upload it) and schedule this report (using Report Event to schedule 'weekly').
By this , you can get report of all failed jobs on weekly basis .

Hope this helps!
Please let me know if there are any concerns over this.

Thanks and regards,
Ravi Kiran

[Legacy User]

 

How can you see the sql code of the report that you copied?

[SHARON SHIMANEK]

I have not found the report perspective to be very friendly. 

I would create an SQL (i use SQL Developer when creating a report), then set up a DB SQL job type and schedule the report to run as needed.  You can email the spool file to the correct group fr the Notification Email tab and check attach spool file upon completion of the report.

Sharon

[Legacy User]

Hello Sharon


Yes I found the wiki on the message board. Would be nice if people could put there sql for reports in there. I am trying to run a forecast report but the report tells the time the application will kick off but really exact for the jobs itself.


CA send me this code to add into a sql script. Do you have a report that will do this?




As discussed, the canned forecast report doesn't provide the actual job start/end time. You want to query ESP_GENERIC_JOB.START_DATE_TIME and ESP_GENERIC_JOB.END_DATE_TIME for those information. you can use the desktop client report facility or your own reporting tool.




I have this report but it is trying to pull history data. Not forecasted data.

DECLARE @FROMDATE datetime;
DECLARE @TODATE datetime;
set @FROMDATE = '2014-02-28 06:00';
set @TODATE = '2014-03-03 06:00';
SELECT ESP_GENERIC_JOB.JOB_NAME, ESP_GENERIC_JOB.JOB_QUALIFIER, ESP_APPLICATION.SCHEDULED_DATE_TIME, ESP_GENERIC_JOB.START_DATE_TIME,
ESP_GENERIC_JOB.END_DATE_TIME, ESP_GENERIC_JOB.COMPLETION_CODE, DATEDIFF(MI, ESP_GENERIC_JOB.START_DATE_TIME,ESP_GENERIC_JOB.END_DATE_TIME) as [TOTALQT]
FROM ESP_APPLICATION INNER JOIN
ESP_GENERIC_JOB ON ESP_APPLICATION.APPL_ID = ESP_GENERIC_JOB.APPL_ID
WHERE (ESP_GENERIC_JOB.START_DATE_TIME > @FROMDATE)
AND (ESP_GENERIC_JOB.START_DATE_TIME < @TODATE)
ORDER BY ESP_GENERIC_JOB.START_DATE_TIME;




I will look some more.


Hermien

From: CA Workload Automation Global User CommunityMessage Boards [mailto:[e-mail]]
Sent: Tuesday, February 25, 2014 4:00 PM
To: [e-mail]
Subject: [CA Workload Automation DE (dSeries Edition)] RE: CA Workload Automation R11.3 Custom Reports


I have not found the report perspective to be very friendly.

I would create an SQL (i use SQL Developer when creating a report), then set up a DB SQL job type and schedule the report to run as needed. You can email the spool file to the correct group fr the Notification Email tab and check attach spool file upon completion of the report.

Sharon
Posted by:shimaneks
--
CA Communities Message Boards
109964491
[e-mail]<mailto:[e-mail]>
https://communities.ca.com

[SHARON SHIMANEK]

Hi

ESP_GENERIC_JOB and ESP_APPLICATION store historical application and job run data.  You could create an sql for a report on failed jobs that meets your needs but I don't think these tables would not be helpful in forecasting. 

Do you have SQL Developer at your company?  It is a nice tool that shows you all the tables in a schema and all the fields within the table. 

ESP_AMS_DF is where the job definitions are but this is a CLOB and we have had no luck getting any good information out of this. 

Sharon

 

[SHARON SHIMANEK]

Hi,

I went to services, choose forecast and created a new forecast for 03/08 - All events, applications and jobs from 5pm - the next 5 hours.  I see estimated job run times and application run times. 

We have 2 types of applications - 1.  everything in the application is a job flow all inter dep  2. an application that contains many misc non dep standalone jobs or mini job flows. These applications load once a day uaually midnight is and the jobs all have different start times throughout the day.  This forecast doesn't pick up on any jobs that loaded fr an event that triggered prior to the start time of the forecast. 

So this forecast would not help us much.

 

Application: Event:	DI_APM INT.DI_APM		Mar 8, 2014 6:30:00 PM CST	Mar 8, 2014 6:41:50 PM CST	11 mins, 50 secs
	PDIL000553	UNIX	Mar 8, 2014 6:31:01 PM CST	Mar 8, 2014 6:33:02 PM CST	2 mins, 1 sec	10	AGENTGRP:UXPRD152_UXPRD153	BATCHPERF_DIMS
Predecessors:	ODS_COMPLETE
Successors:	BATCH_PERF_DIMS_COMPLETE
	PDIL000554	UNIX	Mar 8, 2014 6:31:01 PM CST	Mar 8, 2014 6:31:21 PM CST	20 secs	10	AGENTGRP:UXPRD152_UXPRD153	ENTERPRISE_DIMS
Predecessors:	ODS_COMPLETE
Successors:	ENTERPRISE_DIMS_COMPLETE
	PDIL000550	UNIX	Mar 8, 2014 6:31:01 PM CST	Mar 8, 2014 6:31:15 PM CST	14 secs	10	AGENTGRP:UXPRD152_UXPRD153	BATCHPERF_DIMS
Predecessors:	ODS_COMPLETE
Successors:	BATCH_PERF_DIMS_COMPLETE
	PDIL000549	UNIX	Mar 8, 2014 6:31:01 PM CST	Mar 8, 2014 6:31:16 PM CST	15 secs	10	AGENTGRP:UXPRD152_UXPRD153	BATCHPERF_DIMS
Predecessors:	ODS_COMPLETE
Successors:	BATCH_PERF_DIMS_COMPLETE
	PDIL000548	UNIX	Mar 8, 2014 6:31:01 PM CST	Mar 8, 2014 6:32:52 PM CST	1 min, 51 secs	10	AGENTGRP:UXPRD152_UXPRD153	BATCHPERF_DIMS
Predecessors:	ODS_COMPLETE
Successors:	BATCH_PERF_DIMS_COMPLETE
	ENTERPRISE_DIMS_COMPLETE	LINK	Mar 8, 2014 6:31:21 PM CST	Mar 8, 2014 6:31:21 PM CST	0 secs	10	WA_PROD	ENTERPRISE_DIMS
Predecessors:	PDIL000554
Successors:	PDIL000556
	BATCH_PERF_DIMS_COMPLETE	LINK	Mar 8, 2014 6:33:02 PM CST	Mar 8, 2014 6:33:02 PM CST	0 secs	10	WA_PROD	BATCHPERF_DIMS
Predecessors:	PDIL000548,PDIL000549,PDIL000550,PDIL000553,PDIL000552,PDIL000551
Successors:	PDIL000556
	PDIL000556	UNIX	Mar 8, 2014 6:33:02 PM CST	Mar 8, 2014 6:41:40 PM CST	8 mins, 38 secs	10	AGENTGRP:UXPRD152_UXPRD153	BATCHPERF_FACTS
Predecessors:	BATCH_PERF_DIMS_COMPLETE,ENTERPRISE_DIMS_COMPLETE
Successors:	BATCHPERF_FACTS_COMPLETE
	BATCHPERF_FACTS_COMPLETE	LINK	Mar 8, 2014 6:41:40 PM CST	Mar 8, 2014 6:41:40 PM CST	0 secs	10	WA_PROD	BATCHPERF_FACTS
Predecessors:	PDIL000556
Successors:	PDIL000558
	PDIL000558	UNIX	Mar 8, 2014 6:41:40 PM CST	Mar 8, 2014 6:41:50 PM CST	10 secs	10	AGENTGRP:UXPRD152_UXPRD153	APM
Predecessors:	BATCHPERF_FACTS_COMPLETE
Successors:	DI_APM_COMPLETE
	DI_APM_COMPLETE	LINK	Mar 8, 2014 6:41:50 PM CST	Mar 8, 2014 6:41:50 PM CST	0 secs	10	WA_PROD
Predecessors:	PDIL000558
Successors:

 

 

 

[Legacy User]

 

Hello

Yes  Thank you very much for the example report you ran.  Yes it kind of has the time that the application runs or event but not the exact time that the job itself is estimated to run.

I do have sql 2008 management studio where I can see the tables and run sql scripts.  

I would rather run an sql script then create a custom report.  Seems easier.