7 Replies Latest reply on Jul 19, 2019 3:52 PM by Kevin Burt

    SQL reports

    Steve Jones
      Share This:

      Hi,

       

      Has anyone got available some useful SQL reports as I am having trouble getting everything I need for the reporting facility.  We are running version 7 server and EM running postgres on distributed

       

      Thanks

       

      Steve

        • 1. Re: SQL reports
          Mark Francome

          This is a simple report based on the contents of the Active Jobs File. It will show you failed jobs, executingjobs, jobs waiting on input conditions and jobs waiting on resources. It then counts the number of executed job (both cyclic and non-cyclic).

           

          If you have Control-M for Databases the you can just strip out the SQL alone and use it via that (this turns the output into html and sends it as the body of an email. You may also need to add the PGPASSWORD=your_pw_here before the psql when running as a script.

           

          #!/bin/sh

          NOTIFYFILE=/tmp/cmjobs${$}.out

          echo "<font face="calibri" color="black" font size="3" >Please investigate any unexpected job states.<br /><br />" >$NOTIFYFILE

          psql -P format=html -T "border=1 bgcolor=#F08080 cellspacing=3 cellpadding=6 style='font-family:Calibri;color:Black'" >>$NOTIFYFILE << EOF

          select DESCRIPT AS "Failed Batch Jobs" from CMR_AJF where SCHEDTAB is not NULL AND STATE = '8' AND STATUS = 'N' AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D'
          ORDER BY DESCRIPT;

          EOF

          psql -P format=html -T "border=1 bgcolor=#F0E68C cellspacing=3 cellpadding=6 style='font-family:Calibri;color:SteelBlue'" >>$NOTIFYFILE << EOF

           

          /* Selects job icons that are yellow and that    */

          /* are not dummies and do have memnames (i.e. any executing job)        */

          select DESCRIPT AS "Currently Executing Batch Jobs" from CMR_AJF where SCHEDTAB is not NULL AND STATE = '4' AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D'

          ORDER BY DESCRIPT;


          EOF

          psql -P format=html -T "border=1 bgcolor=#DCDCDC cellspacing=3 cellpadding=6 style='font-family:Calibri;color:DimGrey'" >>$NOTIFYFILE << EOF

          /* Selects job icons that are grey and are waiting for input conditions to be */
          /* created. Also, these jobs are not dummies     */

          /* and do have memnames (i.e. waiting jobs that need input conds).         */

          select DESCRIPT AS "Batch Jobs Waiting on Input Conditions" from CMR_AJF where STATE = 'C' AND STATUS = 'N' AND SCHEDTAB is not NULL AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D'
          ORDER BY DESCRIPT;

          EOF

          psql -P format=html -T "border=1 bgcolor=#87CEFA cellspacing=3 cellpadding=6 style='font-family:Calibri;color:MidnightBlue'" >>$NOTIFYFILE << EOF


          /* Job icons that are blue (i.e. waiting for control/quantitative resources). */


          select DESCRIPT AS "Batch Jobs Waiting for Resources" from CMR_AJF where SCHEDTAB is not NULL AND STATE = 'D' AND STATUS = 'N' AND TASKTYPE <> 'U' AND HOLDFLAG <> 'D'

          ORDER BY DESCRIPT;

           

          EOF


          echo "<font face="calibri" color="black" font size="3" > Number of Non-Cyclic Batch Jobs Completed OK = </b></font>" >>$NOTIFYFILE


          psql -t >>$NOTIFYFILE << EOF


          select COUNT(*) from CMR_AJF where STATE = '8' AND STATUS = 'Y' AND SCHEDTAB is not NULL AND TASKTYPE <> 'U' ;


          EOF


          echo "<font face="calibri" color="black" font size="3" >  </font><br /><br />" >>$NOTIFYFILE


          echo "<font face="calibri" color="black" font size="3" > Number of Cyclic Batch Jobs Completed OK = </b></font>" >>$NOTIFYFILE


          psql -t >>$NOTIFYFILE << EOF


          select COUNT(*) from CMR_AJF where TASKTYPE <> 'U' AND STATUS = 'Y' AND STATE = 'C' OR STATE = 'B' AND TASKTYPE <> 'U' AND STATUS = 'Y';


          EOF


          echo "</font>" >>$NOTIFYFILE


          mailees="Service.ControlM@gmail.com"


          #cat $NOTIFYFILE |perl -ne 's/\<p\>\s*\(\d+\s+rows?\)//o; print "$_";' >${NOTIFYFILE}.new

          cat $NOTIFYFILE |perl -ne '

                  BEGIN { $t = 0 };
          s/\<p\>\s*\(\d+\s+rows?\)//o; # wipe out "x rows" lines.

                  if (/^\<table/o || $v) {

                          if (/^\<table/o) {

                                  $v = $_;

                                  $td = 0; $tr = 0;

                          } else {

                                  ++$td if (/\<td/o);

                                  ++$tr if (/\<tr/o);

                                  $v .= $_;

                                  if (/\<\/table/o) {

                                          if ( $tr>1 || $td ) {

                                          print "$v";

          } else {

          $_ = <>;

          print if (!/\<br/o);

          }

                                          $v = "";

                                  }

                          }

                  } else { print "$_" }

          ' >${NOTIFYFILE}.new

          /usr/sbin/sendmail ${mailees} <<-EOMAIL


          Subject: Control-M Job Summary; `date`

          From: xbgsunsa@gmail.com

          Content-type: text/html


          `cat ${NOTIFYFILE}.new`

          EOMAIL

          rm $NOTIFYFILE ${NOTIFYFILE}.new

          • 2. Re: SQL reports
            Steve Jones

            Thanks for that Mark, very useful, although our descriptions aren't so good.  Would you have anything to run on EM to get things like job failures in the last week etc.

             

            Cheers

             

            Steve

            • 3. Re: SQL reports
              Mark Francome

              That SQL relates to CMR_AJF, i.e. all currently active jobs. To see history over several days you'll have to look at various ACTIVE_NET_NAME entries - at least that is how it used to work but I can see BMC have changed the schema for this since I last looked and maybe somebody here will have something already to do the job.

               

              How about just using the ctmlog utility on the Control-M Server to get the desired report? E.g. -

               

              ctmlog LIST 150326 1100 150331 1800 | grep "TR|5134"

               

              Will list the "NOTOK" endings to jobs since 11 am on the 26th. The only problem with these big ctmlog searches is that they can put an overhead on the server.

               

              • 4. Re: SQL reports
                Robert Stinnett

                Here you go, wrote this years ago and it still provides value to us.

                 

                Control-M CTMLOG Analyzer Script | Robert Stinnett

                1 of 1 people found this helpful
                • 5. Re: SQL reports
                  Robert Stinnett

                  Hey Mark,

                   

                  You still got a copy of that script laying around?  I am afraid that I lost my original about two years back.  I’ll get it up on Github so it has a permanent home.

                   

                  Robert

                  • 6. Re: SQL reports
                    Mark Francome

                    Hi Robert, I do but you should check that it is the right version (posted on main page).

                    • 7. Re: SQL reports
                      Kevin Burt

                      So does anyone have a link to this SQL script or the Github repo?