pshastany1 Absent Member.
Absent Member.
883 views

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
0 Likes
2 Replies
AutomaticReply Absent Member.
Absent Member.

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/partnerfinder/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



0 Likes
Micro Focus Expert
Micro Focus Expert

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
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.