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

>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

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
****** @MGIntID = TableID
****** dbo.ManagementGroup
****** Name = @MGName
if @MGIntID is null
****** raiserror('Can''t find Management Group: %s', 18, 1, @MGName)
****** return
****** Repository = D.Name,
****** Agent = CS.Computer,
****** CJ.JobID,
****** CJ.KSName,
****** JobStatus = convert(varbinary, CJ.Status)
****** 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
****** 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 --