8 Replies Latest reply on Apr 7, 2011 10:08 AM by Steve Westerhout

    Batch report of all uncompleted jobs

      Share This:

      Hello,

       

      Is there a way to run a report at the end of a 24 hour period (before next load time) that will report any jobs that did not run for the period?

       

      I know that this can be done manually by using the Enterprise manager and setting the filter. Can I get a list in a text file by submitting a batch job?

       

      controlmfilter.JPG

       

      i.e. I have a job waiting on a file from an external source, and the job does not complete because the file is not available. Or in a worst case scenario we could have incorrectly set up a job to wait for a job that does not run that day, we would fix this to use a maybe condition in the long term, but we still  need to know that it did not run.

        • 1. Re: Batch report of all uncompleted jobs

          Hi Steve

           

          The information you are looking for is available in the Control-M/EM Reporting Facility. After you design the report template to filter the relevant jobs and include the relevant job attributes, you can schedule a Control-M job that generate the report in batch. You can either use the graphical form available with the “Control-M Report” job type (requires Control-M/EM v6.4) or run the Control-M/EM ‘emreportcli’ utility.

           

          Regards,

           

          Tom Geva
          Product Marketing Manager
          Control-M Workload Automation

          BMC Software

          • 2. Batch report of all uncompleted jobs
            Robert Stinnett

            Steve -

             

            Here is a query that I use.  Hope this helps!  This is run against the Control-M/Server database on the AJF.  You'd probably want to run this right before newday, or if you are using archived viewpoints you could manipulate it a bit to use the last archive to get the previous day run.

             

            SELECT     JOBNAME AS 'Job Name', SCHEDTAB AS 'Scheduling Table', MAXWAIT AS 'Max Wait (in Days)'

            FROM         CMR_AJF

            WHERE     (STATE NOT IN ('8', '2', '4', '5', '6', '8 ', ' A ', ' J ')) AND (STATUS <> ' Y ') AND (ODATE = '20101230')

            • 3. Batch report of all uncompleted jobs

              Robert – take a look at the “Jobs Execution” report under the Forecast category.

              Don’t get confused by the category name – this report shows detailed history of each job run.

              If you are not familiar with it – you might get surprised on the amount of information it contains :-)

              You can also schedule the report to be generated automatically using the “Control-M Report” job type…

              • 4. Batch report of all uncompleted jobs
                Robert Stinnett

                Tom -

                 

                We have tons of reports, and that is what I want to get away from.  Every morning I must get no fewer than 10 reports on various Control-M items.  So generating it via SQL query lets me do a few things:

                 

                1 - Store it as a text file in a format I can use

                2 - Write automation scripts to analyze the SQL output and take action vs. humans having to read a report and take actions.

                 

                So while the reporting facility is nice, I still like doing SQL queries for a lot of stuff just to get the data in the format I can use with the least amount of hassle.

                 

                Robert

                • 5. Batch report of all uncompleted jobs

                  Steve,

                   

                  We setup a job in controlM that runs just before our dayend process that checks to see if any jobs are still held or waiting - it runs a sql script with case statements like this (we check for held, waiting, executing)

                   

                  SELECT

                  'EXECUTING' AS [CTM_STATUS],

                  COUNT(*) AS Count,

                  CASE WHEN COUNT(*) > 0 THEN 'TRUE' ELSE 'FALSE' END As [SEND_MAIL]

                  FROM CMR_AJF

                  WHERE STATE = '4'

                  AND HOLDFLAG = 'N'

                  AND APPLGROUP != 'CTM_DAYEND'

                   

                  The last bit is to exlcude these jobs from the query.

                   

                  Then the job checks the sysout for '*TRUE*', and if its found then it runs another job to send the detail on those jobs to an email group.  We built a utility to construct an email from the contents of a file, so we just run the job detail query out to a file and then on the STEPS tab pass that filename to a shout destination pointing to the EMAIL_FROMFILE utility.

                   

                  SELECT 'EXECUTING' AS [CTM_STATUS],

                  SCHEDTAB,

                  JOBNAME,

                  CASE

                    WHEN STARTRUN != '' THEN SUBSTRING(STARTRUN,5,2) + '/' + SUBSTRING(STARTRUN,7,2) + '/' + SUBSTRING(STARTRUN,1,4)

                    ELSE ''

                  END AS START_DATE,

                  CASE

                    WHEN STARTRUN != '' THEN SUBSTRING(STARTRUN,9,2) + ':' + SUBSTRING(STARTRUN,11,2) + ':' + SUBSTRING(STARTRUN,13,2)

                    ELSE ''

                  END AS START_TIME,

                  CASE

                    WHEN ENDRUN != '' THEN SUBSTRING(ENDRUN,5,2) + '/' + SUBSTRING(ENDRUN,7,2) + '/' + SUBSTRING(ENDRUN,1,4)

                    ELSE ''

                  END AS END_DATE,

                  CASE

                    WHEN ENDRUN != '' THEN SUBSTRING(ENDRUN,9,2) + ':' + SUBSTRING(ENDRUN,11,2) + ':' + SUBSTRING(ENDRUN,13,2)

                    ELSE ''

                  END AS END_TIME

                  FROM CMR_AJF

                  WHERE STATE = '4'

                  AND APPLGROUP != 'CTM_DAYEND'

                   

                  Hoep that helps.

                  --Ric

                  1 of 1 people found this helpful
                  • 6. Batch report of all uncompleted jobs

                    In our environment I don't have direct access to the Sybase database that Control-M uses. I am assuming that our job would need the access to the database or can Control-M query itself?

                    • 7. Batch report of all uncompleted jobs
                      Robert Stinnett

                      Steve -

                       

                      You can use the reporting facility to design some reports (though it's a bit limited).

                      • 8. Batch report of all uncompleted jobs

                        I came accross this the other day.

                         

                        ctmpsm.exe -LISTAJFTAB CTMTABLENAME

                         

                        It provides a delmited list of the jobs and their status. I then wrote some code to go through the list and alert if any jobs are not completed. Works like a charm for what I need.