Home

Results 1 to 3 of 3

Thread: SQL query for stopped jobs in management group

Hybrid View

  1. #1
    Join Date
    Apr 2019
    Posts
    1

    SQL query for stopped jobs in management group

    Andy,

    We periodically will see stopped jobs both ad-hoc and monitoring policy. I noticed there are some queries to find stopped jobs within the QDB, but I'm wondering if there is a query that can be used to pull in the number of stopped jobs per management group? NQCCDB. I would like to run a daily query SQLServer_RunCmdin the morning that could tell me how many monitoring policy jobs and ad-hoc jobs outside of discovery jobs were stopped?


    When you have over 4K servers to monitor this could be fairly big, and as you know it is sometimes tough to pinpoint why a job may have stopped?

    Phil

  2. #2
    Join Date
    May 2016
    Posts
    1,717

    Re: SQL query for stopped jobs in management group

    pshastany1,

    It appears that in the past few days you have not received a response to your
    posting. That concerns us, and has triggered this automated reply.

    These forums are peer-to-peer, best effort, volunteer run and that if your issue
    is urgent or not getting a response, you might try one of the following options:

    - Visit https://www.microfocus.com/support-and-services and search the knowledgebase and/or check
    all the other self support options and support programs available.
    - Open a service request: https://www.microfocus.com/support
    - You could also try posting your message again. Make sure it is posted in the
    correct newsgroup. (http://forums.microfocus.com)
    - You might consider hiring a local partner to assist you.
    https://www.partnernetprogram.com/pa...nder/find.html

    Be sure to read the forum FAQ about what to expect in the way of responses:
    http://forums.microfocus.com/faq.php

    Sometimes this automatic posting will alert someone that can respond.

    If this is a reply to a duplicate posting or otherwise posted in error, please
    ignore and accept our apologies and rest assured we will issue a stern reprimand
    to our posting bot.

    Good luck!

    Your Micro Focus Forums Team
    http://forums.microfocus.com



  3. #3
    Join Date
    Nov 2016
    Location
    Houston, TX
    Posts
    29

    Re: SQL query for stopped jobs in management group

    pshastany1 on 4/1/2019 at 12:54:02 PM wrote:

    >
    >Andy,
    >
    >We periodically will see stopped jobs both ad-hoc and monitoring
    >policy. I noticed there are some queries to find stopped jobs within
    >the QDB, but I'm wondering if there is a query that can be used to
    >pull in the number of stopped jobs per management group? NQCCDB. I
    >would like to run a daily query SQLServer_RunCmdin the morning that
    >could tell me how many monitoring policy jobs and ad-hoc jobs outside
    >of discovery jobs were stopped?
    >
    >
    >When you have over 4K servers to monitor this could be fairly big, and
    >as you know it is sometimes tough to pinpoint why a job may have
    >stopped?
    >
    >Phil


    I'm not Andy, but give this a try:

    The query below returns a list of stopped jobs. Modify the first line
    to change the MG you look in and the second line if you want to check
    all jobs rather than just policy (if the value is 0 then all jobs are
    checked, if it is 1 then only policy). Run in the CCDB

    -- Start Script --

    declare @MGName nvarchar(256) = 'Master'
    declare @OnlyPolicy bit = 1
    *
    declare @MGIntID int
    *
    select
    ****** @MGIntID = TableID
    from
    ****** dbo.ManagementGroup
    where
    ****** Name = @MGName
    *
    if @MGIntID is null
    begin
    ****** raiserror('Can''t find Management Group: %s', 18, 1, @MGName)
    ****** return
    end
    *
    select
    ****** Repository = D.Name,
    ****** Agent = CS.Computer,
    ****** CJ.JobID,
    ****** CJ.KSName,
    ****** JobStatus = convert(varbinary, CJ.Status)
    from
    ****** dbo.GetMGMembers(@MGIntID) M
    ****** inner join dbo.ComputerServer CS
    ************* on CS.DataSourceIntID = M.DataSourceIntID
    ************* and CS.ObjID = M.MachineObjID
    ****** inner join dbo.ComputerJob CJ
    ************* on CJ.DataSourceIntID = CS.DataSourceIntID
    ************* and CJ.MachineObjID = CS.RootMachineObjID
    ****** inner join dbo.DataSource D
    ************* on D.DataSourceIntID = M.DataSourceIntID
    where
    ****** CJ.ParentJobID is not null
    ****** and CJ.Status &0x00000004 !=0
    ****** and (@OnlyPolicy = 0 or (CJ.Status &0x40000000 !=0 and
    @OnlyPolicy = 1))
    order by
    ****** D.Name,
    ****** CS.Computer,
    ****** CJ.JobID

    -- End Script --

    --ebell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •