3 Replies Latest reply on Oct 22, 2015 8:32 PM by Bill Robinson

    Query for Configuration->Job Schedules View?

    michael huttner

      From what I see here, several other posters detailed challenges getting job and schedule information, especially with complex hierarchies of jobs.  Likely I am not the only user attempting to help make job scheduling management easier and more transparent by producing custom reports and spreadsheet data.  The console gui does not provide sufficient views nor flexible access to the data, and it's rather non-trivial to query our nested hierarchy of jobs and schedules (using either blcli or sql) to do this.  I'm digesting some of Bill's blcli suggestions as well as Yanick's ideas and sql queries/views.  


      However, I suspect Bladelogic produces its console view outputs using java library methods, sql queries or stored procedures - and would like to know how to find out what is used by the BL server to produce some of these console reports and views, eg: the Configuration->Job Schedules View.  Is this information available in logs, or how can we get this info?

        • 1. Re: Query for Configuration->Job Schedules View?
          Bill Robinson

          what are you trying to do w/ this data ?  if you have bdssa i think this information may be available there as well.



          • 2. Re: Query for Configuration->Job Schedules View?
            michael huttner

            We're trying to maximize server utilization and response by identifying, optimizing and eliminating jobs and schedules which impact system response and resources (memory, job threads, etc), causing performance issues (multiple job execution hotspots, intervals less than four hours, etc) - and produce "bad neighbor" reports/notifications, and alerts to netcool/ITM advising when our thresholds have been exceeded. 

            • 3. Re: Query for Configuration->Job Schedules View?
              Bill Robinson

              if you are looking to actually monitor what's happening realtime you should be looking at the jmx interface to the appserver to see what's going on at any given time in the appserver.  really the number of jobs is not relevant.  what matters is the workitem thread usage and what those jobs are doing.  you could have 100 jobs running but still have work item threads available, or you could have 1 job running and no work item threads available (meaning no other job will be able to actually do anything).  it would make more sense i think to start graphing that to see  if/when you have problem times. 



              it would make sense to look at what jobs are running during a given time.  so here's one query for that which covers a couple time ranges. 


              WITH temp1 (group_id, path) AS


                          select group_id, cast(name as varchar(1000))

                             from blgroup bg1

                             where parent_group_id = 0

                             UNION ALL

                        select bg1.group_id,

                                   cast(bg2.path ||'/'|| cast(bg1.name as varchar(100)) as varchar(1000))

                             from blgroup bg1

                             inner join temp1 bg2 on bg1.parent_group_id = bg2.group_id


              select temp1.path ||'/'|| j.name,o.name,jr.job_run_id,to_char(jr.start_time,'YYYY-MM-DD HH24:MI:SS'),to_char(jr.end_time,'YYYY-MM-DD HH24:MI:SS'),ap.display_name from temp1

                    inner join job j on temp1.group_id = j.group_id

                    join job_run jr on j.job_id = jr.job_id and j.job_version_id = jr.job_version_id

                    join object_type o on j.object_type_id = o.object_type_id

                    join application_server ap on jr.application_server_id = ap.application_server_id

                    where j.is_saved_explicitly = 1

                    and (

              (jr.end_time > to_date('2014-01-01 00:00:00",'YYYY-MM-DD HH24:MI:SS') and jr.start_time < to_date('2014-01-01 00:30:00','YYYY-MM-DD HH24:MI:SS'))


              (jr.end_time > to_date('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') and jr.start_time < to_date('2014-01-01 00:30:00','YYYY-MM-DD HH24:MI:SS'))




              i think for the job schedules you can use  this from the sqlmap.properties.  you could add the logic to get the path to the job to that.


              SELECT j.name, j.object_type_id, s.schedule_id, s.created_by_user_id, s.created_by_role_id, s.modified_by_user_id, \

              s.modified_by_role_id, s.frequency_type_id, s.job_id, s.execution_task_id, \

              s.once_date_time, s.days_of_week, s.day_of_month, s.frequency, \

              s.recurring_time, s.recurring_time_zone_id, s.start_time, s.interval_day, \

              s.interval_hour, s.interval_minute, s.scheduler_type_id, s.next_run_time, \

              s.last_run_time, s.max_time_in_sched_q, s.locale_id, s.scheduler_lock_id, \

              s.date_created, s.date_modified, s.is_deleted, s.approval_request_id, \

              s.approval_status_id, s.job_priority_id, s.transaction_id \

              FROM schedule s INNER JOIN job j ON s.job_id = j.job_id \

              WHERE s.is_deleted = 0 \

              and s.next_run_time is not null \

              and j.object_type_id != 7036 \

              and j.is_deleted = 0 \

              and j.is_latest_version = 1 \

              and not exists \

              (SELECT 1 FROM batch_job_job INNER JOIN job ON batch_job_job.batch_job_id = job.job_id \

              WHERE batch_job_job.job_id = s.job_id AND ((job.object_type_id = 30 AND job.is_deleted = 1 AND job.is_latest_version = 1) OR \

              job.object_type_id = 7036));