Greetings All,
I am trying to build a query to get the job status of specific AdHoc
jobs. I'm getting them out of Control Center since we have multiple
QDB's. What I am doing right now is stoping and starting jobs in
Control Center and noting their status in a Case statement. Is there an
easier way to show their Job Status? Below is the query I am using.
Many thanks in advance for your help.


----------------------------
declare @values table
(
Value varchar(1000)
)

insert into @values values ('Discovery_ExchangeDAG')
insert into @values values ('NT_FileChanged')
insert into @values values ('General_Counter-Exchange Avg bytes per
Msg')
insert into @values values ('General_Counter-Exchange disk reads')
insert into @values values ('General_Counter-Exchange disk writes')
insert into @values values ('General_Counter-Exchange LDAP')
insert into @values values ('General_Counter-Exchange Long Run LDAP')
insert into @values values ('General_Counter-Exchange Mem Pages')
insert into @values values ('General_Counter-Exchange Msg per Sec')
insert into @values values ('General_Counter-Exchange Msg Rec per Sec')
insert into @values values ('General_Counter-Exchange Msg Sent per
Sec')
insert into @values values ('General_Counter-Exchange Network Bytes
Total')
insert into @values values ('General_Counter-Exchange Processor')
insert into @values values ('General_Counter-Exchange Queue Length')
insert into @values values ('General_Counter-Exchange RPC Busy')
insert into @values values ('General_Counter-Exchange RPC Connection
Ct')
insert into @values values ('General_Counter-Exchange RPC Latency')
insert into @values values ('General_Counter-Exchange RPC User Ct')
insert into @values values ('General_Counter-Exchange Sub Mail per
Sec')
insert into @values values ('General_EventLog')
insert into @values values ('General_PingMachine')
insert into @values values ('ApacheUNIX_HealthCheck')
insert into @values values ('NT_FindFiles')
insert into @values values ('NT_ProcessUp')
insert into @values values ('NTAdmin_FileCheck')
insert into @values values ('NTAdmin_RunDOS')
insert into @values values ('ServerDown')
insert into @values values ('NT_ServiceDown')
insert into @values values ('SNMPTrap')
insert into @values values ('SQL_RunSql')
insert into @values values ('Traceroute')
insert into @values values ('TrapMonitor')
insert into @values values ('Web-RT')

--Get UTC Time
declare @UTCtd AS datetime
set @UTCtd = getutcdate()

--Get Today's Date and Time
declare @TodayTD AS datetime

set @TodayTD = GETDATE()

--Get Time Zone Informaiton
declare @TimeDif as int
set @TimeDif = DATEDIFF(hh, getutcdate(), getdate())

select
JobID,
ParentJobID,
DataSourceName,
KSName,
Computer,
CASE Status
WHEN 16777729 THEN 'Running'
WHEN 16842756 THEN 'Stopped'
WHEN 285212673 THEN 'Running'
WHEN 1 THEN 'Running'
WHEN 513 THEN 'Running'
WHEN 1025 THEN 'Running'
WHEN 16777217 THEN 'Running'
WHEN 16843265 THEN 'Running'
ELSE 'Unknown'
END as Status,
Status as Statusb,
SubmitUserName,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (SubmitTime)) as SubmitTime,
SyncTime,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (StopTime)) as LastStopTime,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (LastRunTime)) as
LastLastRunTime,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (ModificationTime)) as
LastModificationTime
from
ComputerJob WITH (NOLOCK)
where
KSName in (select Value from @values)


--
abel5405
------------------------------------------------------------------------
abel5405's Profile: https://forums.netiq.com/member.php?userid=5035
View this thread: https://forums.netiq.com/showthread.php?t=54957