8 Replies Latest reply on Feb 21, 2020 2:21 AM by Mun Keong Lee

    Select jobs removed from daily without executions

    Fabiana Delfino
      Share This:

      Hi guys,

       

      Is there a way to create a report or  query do search the jobs tha were removed from daily without execution?

       

       

      Regards,

       

      Fabiana

        • 1. Re: Select jobs removed from daily without executions
          Jesse Richardson

          Hi - what BMC product is this regarding? I can move the question to the best product community for it, but from the context, I was not sure which is the right one

          • 3. Re: Select jobs removed from daily without executions
            Jesse Richardson

            Successfully moved to Control-M so community product experts can help

            • 4. Re: Select jobs removed from daily without executions
              Mun Keong Lee

              Hi Fabiana

              I think it's better that I reply to this post instead. The other one is a 3 year old post which actually asked a different question.

              You will need to set the value of PGPASSWORD to the password of the EM database owner (default is emuser) before executing the script below if you do not wish to reply to the password prompt interactively. The easiest way to set this up is in the login script of the EM OS account.

               

              If login shell is csh, add the following line at the end of the file, .cshrc, in the home directory:

              setenv PGPASSWORD <password>

               

              If login shell is bash, add the following line at the end of the file, .bash_profile, in the home directory:

              PGPASSWORD=<password>

              export PGPASSWORD

              If login shell is sh or korn, add the above lines to the end of the file, .profile, in the home directory.

               

               

              wait_jobs_removed.sh script (Waiting jobs that were removed after the new day)

              --------------------------------------------

              #!/bin/bash

              # get list of active nets

              anets=`em psql -A -t -c "select active_net_name,ctm_odate,data_center from COMM where active_net_name !='';"`

               

              for anet_info in $anets;

              do

                IFS='|' read -a strarr <<< "${anet_info}"

                #

                today_net=${strarr[0]}

                yesterday=$(date -d "${strarr[1]} -1 days" +"%Y%m%d")

                dc=${strarr[2]}

                yesterday_net=`em psql -A -t -t -c "SELECT max(netname) FROM public.download where netdate='${yesterday}' group by netdate"`

                today_table=a${today_net}job

                yesterday_table=a${yesterday_net}job

                echo "List of waiting jobs that were deleted in Data Center: ${dc}"

                em psql -c "\

                  SELECT application, group_name, job_name, status, odate, task_type, order_id \

                  FROM ${yesterday_table} \

                  where status like 'Wait%' \

                  and order_id not in (select order_id from ${today_table});"

              done

              --------------------------------------------

               

               

               

              1 of 1 people found this helpful
              • 5. Re: Select jobs removed from daily without executions
                Fabiana Delfino

                Lee,

                 

                I'm still having some trouble with the  script because I have more the one server.

                The script is selecting one server but when set the yesterday variable is selecting another server.

                How can I select only one server?

                 

                em psql -A -t -c 'select active_net_name,ctm_odate,data_center from COMM where data_center LIKE '\''ctmprd'\'';'

                + anets='200218123_A|20200218|ctmprd'

                + for anet_info in '$anets'

                + IFS='|'

                + read -a strarr

                + today_net=200218123_A

                ++ date -d '20200218 -1 days' +%Y%m%d

                + yesterday=20200217

                + dc=ctmprd

                ++ em psql -A -t -t -c 'SELECT max(netname) FROM public.download where netdate='\''20200217'\'' group by netdate'

                + yesterday_net=200217255_A

                + today_table=a200218123_Ajob

                + yesterday_table=a200217255_Ajob

                 

                Regards,

                 

                Fabiana

                • 6. Re: Select jobs removed from daily without executions
                  Mun Keong Lee

                  Hi Fabiana

                  Thank you for your feedback and sorry for the bug. Here's the corrected script and appreciate any feedback on this script.

                  -------------------------------------------------------------------------------------------------------------------

                  #!/bin/bash -x

                  # get list of active nets

                  anets=`em psql -A -t -c "select active_net_name,ctm_odate,data_center from COMM where active_net_name !='';"`

                   

                   

                  # list jobs from each active net

                  for anet_info in $anets;

                  do

                    IFS='|' read -a strarr <<< "${anet_info}"

                    today_net=${strarr[0]}

                    yesterday=$(date -d "${strarr[1]} -1 days" +"%Y%m%d")

                    dc=${strarr[2]}

                    yesterday_net=`em psql -A -t -c "\

                      SELECT max(netname) \

                      FROM public.download \

                      where netdate='${yesterday}' and data_center='${dc}' \

                      group by netdate"`

                    today_table=a${today_net}job

                    yesterday_table=a${yesterday_net}job

                    echo "List of waiting jobs that were deleted in Data Center: ${dc}"

                    em psql -c "\

                      SELECT application, group_name, job_name, status, odate, task_type, order_id \

                      FROM ${yesterday_table} \

                      where status like 'Wait%' \

                      and order_id not in (select order_id from ${today_table});"

                  done

                  -------------------------------------------------------------------------------------------------------------------

                  Regards,

                  MK

                  1 of 1 people found this helpful
                  • 7. Re: Select jobs removed from daily without executions
                    Fabiana Delfino

                    Hi Lee,

                     

                    Thanks for the answer. It worked well.

                    But I had to add the filter "state is null' in the last select.

                    Otherwise the select would bring even the cyclic jobs that had already executed at list one time.

                     

                     

                    Regards,

                     

                    Fabiana

                    • 8. Re: Select jobs removed from daily without executions
                      Mun Keong Lee

                      Hi Fabiana

                      Good catch and thanks for the feedback. I would like to post a new update to my earlier script. I simplified the last SQL to select only those jobs where the rerun_counter = 0 which is a good indicator that the job has never been executed before the new day kicks in and delete it.

                       

                      ------------------------------------------------------------------

                      #!/bin/bash

                      # get list of active nets

                      anets=`em psql -A -t -c "select active_net_name,ctm_odate,data_center from COMM where active_net_name !='';"`

                       

                       

                      # list jobs from each active net

                      for anet_info in $anets;

                      do

                        IFS='|' read -a strarr <<< "${anet_info}"

                        today_net=${strarr[0]}

                        yesterday=$(date -d "${strarr[1]} -1 days" +"%Y%m%d")

                        dc=${strarr[2]}

                        yesterday_net=`em psql -A -t -c "\

                          SELECT max(netname) \

                          FROM public.download \

                          where netdate='${yesterday}' and data_center='${dc}' \

                          group by netdate"`

                        today_table=a${today_net}job

                        yesterday_table=a${yesterday_net}job

                        echo "List of waiting jobs that were deleted in Data Center: ${dc}"

                        em psql -c "\

                          SELECT application, group_name, job_name, status, odate, task_type, order_id \

                          FROM ${yesterday_table} \

                          where rerun_counter = 0 \

                          and order_id not in (select order_id from ${today_table});"

                      done

                      ------------------------------------------------------------------