You can run this SQL against the Control-M Enterprise Manager Database. I have run this against Control-M 9 using BMC supplied PostgreSQL.
You only need to create a DB job type, and pass the start and end date for each month as variables.
select order_date, sched_table, job_mem_name, application, group_name, start_time, end_time, data_center, order_id, node_id from runinfo_history where ended_status = 32 and start_time > '2016-12-01 00:00:00' and end_time < '2017-01-30 00:00:00'
Syntax may vary by database type. Status code 32 = jobs not ok. if the job is rerun OK, the status in runinfo-history does not change, a second entry is inserted with a code of 16 for success. If you only need jobs that ended NOT and never rerun to success, then thats a slightly different scenario.
3 of 3 people found this helpful
There are 2 parts to this -
Part 1: Creating the template for the report.
Part 2: Scheduling the reporting job using the created template.
1. Select the template "Execution" under the "Forecast Analysis"
2. Fill in the details in the first screen "General".
3. In "Filters"
- For "Time Frame", Select "Last" - 30 days.
- For "Field Filters", Select the filters you need as requirement.
+ I selected the ControlM Server Name / Completion Status "Ended Not Ok".
4. In "Fields", select the fields you need in your final report.
- I have selected the - ControlM Server Name / Folder / Job Name / Start Time.
5. In "Grouping", select how you want to group them.
- I used the ControlM Server Name / Start Time
6. In "Summaries", select by which field you need the summary.
- I have given as below :
+ Summary By Field : Start Time
+ Summary For Group : Whole Report
+ Summaries Function : Count Values
7. In "Sorting", select the fields by which you need to sort the report.
- I have selected ControlM Server Name / Start Time / Folder in order.
8. In "Style", select the style of report you need.
9. In "Report Details", the summary of the details we have selected till now in all the screen will be shown. Also, there will be a Q to generate the report now ?
- Select Yes, if u want the report now, else you can select no and same it.
Here, your template is ready.
If you want to schedule this report on a monthly basis, you will have to create a reporting job in the PLANNING.
Reporting job is same as other OS jobs, just that this will need to have additional reporting jobs attributes as mentioned below -
Run As: The user name that will be used to run the report.
User/password : A user who has access to the server where control is installed and can execute the reports. This can be avoided if we are providing the path for password file.
Password File : A file that contains the user/password details. A normal txt file with only the username and password. Both these should be in a new line.
Server host : The host name from where the control jobs are running.
emreportcli Path: This is the path to the utility that runs the template that we created in the Part 1. By default, the utility will be in the path mentioned in the image, unless someone replaced it.
Template: The name of template that we created in Part 1.
Template Path: The path of the template. By default, the folder mentioned in the image will have the templates.
Output, i.e the report can be taken as a PDF or as a SYSOUT in the job itself.
Output type can be selected, you will have multiple options like PDF, excel, csv, txt and so on.
Output to File - will create a report and will save in the path mentioned in the next line "Output file" You have options to give dynamic names. The format given in image will save the report with date every time it is creatd.
Output to Sysout - will add the final output to the existing sysout of the job.
In case we are using the dynamic values while creating the template in Part 1, we can give those values here in REPORT PARAMETERS section to take the actuals.
Thank you everyone for replying, your assistance has been invaluable.