01-24-2014 08:37 AM - edited 03-01-2019 09:07 AM
TES 6.1.0.391
From time to time, we have a need to identify all jobs that were running at a particular moment in time on a particular agent (we have about 800 agents)...eg "what was running ("Active") at 09:03:42 a.m. two days ago on agent XYZ?"
I've used other job schedulers, and have written queries to extract that info, but I thought before I work on one for Tidal that I would ask the community....how are you getting this info?
Any help is greatly appreciated, thanks.
Solved! Go to Solution.
01-27-2014 09:54 AM
I had some time over the weekend and was able to come up with something of use.
Please note that our repository is MSSQL
select jobmst_prntname as ParentJobName,
a.jobmst_prntid as ParentJobId,
a.jobmst_id as JobId,
a.jobdtl_id as JobDetailID,
jobmst_name as JobName,
b.owner_name as JobOwnerName,
c.jobdtl_cmd as JobCommand,
c.jobdtl_params as JobParameters,
jobmst_lstchgtm as LastUpdateDate,
d.nodlstmst_name as AgentListName
,[jobrun_status]
,[jobrun_duration]
,[jobrun_time] as starttime
,DATEADD(ss,jobrun_duration, jobrun_time) as endtime
,f.nodmst_name as AgentName
,[jobrun_owner]
,[jobrun_cmd]
,[jobrun_rundt]
,[jobrun_batch]
,[jobrun_params]
,[jobrun_launchtm]
,[jobrun_fullpath]
from Admiral..jobmst a,
Admiral.dbo.[owner] b,
Admiral.dbo.jobdtl c,
Admiral.dbo.nodlstms d,
Admiral.dbo.jobrun e,
[Admiral].[dbo].[nodmst] f
where a.jobmst_owner=b.owner_id
and a.jobdtl_id=c.jobdtl_id
and c.nodlstmst_id=d.nodlstmst_id
and e.jobmst_id=a.jobmst_id
and e.nodmst_id=f.nodmst_id
and jobmst_active='Y' --This condition shows only the active jobs
and jobrun_rundt ='2014-01-26' --This is the job run date. If the job finishes the next day, that is what is going to be used.
and f.nodmst_name = 'abc' --This is where you input your agent name
Hope this helps!
01-25-2014 11:01 AM
Well, almost there, getting great results but not perfect results.
The biggest stumbling block is with the jobrun table: while it records the "original status" of a job that did not succeed on the first attempt (before it was "marked" as anything) it does not record the **date/time** of when it reached that original status. (Msglog table does record the date/time of every status the job has, but doesn't have an indicator of the "original completion status" that correlates with any field in the jobrun table, so I can't see how to make that association in the query).
Consequently, since the query can only select the date/time of the "most recent status" of the job, it is falsely reporting some jobs as running at a particular point in time, when in fact they were sitting in a failed state at that time. No good.
I'm thinking of opening a ticket with TAC and asking for an enhancement, perhaps a new field in the jobrun table: "jobrun_orgstatus_datetime" or some such...until a solution is reached we'll have to put up with some "false positives" whenever we run the query to "get a list of all running jobs at a particular point in time".
Unless I'm missing something....I'm still hopeful someone out there can shed some light?
01-27-2014 09:54 AM
I had some time over the weekend and was able to come up with something of use.
Please note that our repository is MSSQL
select jobmst_prntname as ParentJobName,
a.jobmst_prntid as ParentJobId,
a.jobmst_id as JobId,
a.jobdtl_id as JobDetailID,
jobmst_name as JobName,
b.owner_name as JobOwnerName,
c.jobdtl_cmd as JobCommand,
c.jobdtl_params as JobParameters,
jobmst_lstchgtm as LastUpdateDate,
d.nodlstmst_name as AgentListName
,[jobrun_status]
,[jobrun_duration]
,[jobrun_time] as starttime
,DATEADD(ss,jobrun_duration, jobrun_time) as endtime
,f.nodmst_name as AgentName
,[jobrun_owner]
,[jobrun_cmd]
,[jobrun_rundt]
,[jobrun_batch]
,[jobrun_params]
,[jobrun_launchtm]
,[jobrun_fullpath]
from Admiral..jobmst a,
Admiral.dbo.[owner] b,
Admiral.dbo.jobdtl c,
Admiral.dbo.nodlstms d,
Admiral.dbo.jobrun e,
[Admiral].[dbo].[nodmst] f
where a.jobmst_owner=b.owner_id
and a.jobdtl_id=c.jobdtl_id
and c.nodlstmst_id=d.nodlstmst_id
and e.jobmst_id=a.jobmst_id
and e.nodmst_id=f.nodmst_id
and jobmst_active='Y' --This condition shows only the active jobs
and jobrun_rundt ='2014-01-26' --This is the job run date. If the job finishes the next day, that is what is going to be used.
and f.nodmst_name = 'abc' --This is where you input your agent name
Hope this helps!
01-29-2014 10:07 AM
Thanks Pavan!
While I don't see where in your query you can get all jobs running at a precise point in time (not a concern, since I already had that worked out), you did give me the clue I needed, namely to simply add duration to launchtime to get "actual" fin time....I was so locked into wanting to see that value in a table that I wasn't looking at anything else, so thanks again for that lesson!
I realize now this query will never be 100% accurate, since Tidal has a big shortcoming in that it only records one status and fin time - the final - of a job in the jobrun table. Jobs that are rerun (either by design or manually) will wipe out the previous run record, so the query won't pick those up.
I suppose with a lot more work I could link in the msglog table, just don't have the time or enery for that....so this will have to do for our purposes.
We're on Oracle, so this is pl/sql, start/fin times are hardcoded for readability, not selecting on any particular agent here, selecting all running jobs:
SELEct a.jobrun_externid_str "PID",
j.jobmst_name "JOB",
a.jobrun_launchtm "Started",
(a.jobrun_launchtm+(a.jobrun_duration/86600)) "Finished",
round(a.jobrun_duration/60,0) "Elapsed (mins)",
TO_CHAR(A.jobrun_PRODdt,'DD-MON-YYYY') "SCHED DATE",
j.jobmst_prntname "PARENT",
round(d.jobdtl_duration/60,0) "AVG (mins)",
jobrun_id "JOBID",
B.NODMST_NAME "Agent"
FROM tidal.jobrun a
JOIN tidal.nodmst b ON b.nodmst_id = a.nodmst_id
JOIN tidal.jobmst j ON j.jobmst_id= a.jobmst_id
JOIN tidal.jobdtl d ON D.JOBDTL_ID= J.JOBMST_ID
WHERE (a.jobrun_launchtm <= ('28-JAN-14 14:15:32') AND (A.jobrun_launchtm+(a.jobrun_duration/86600)) >= ('28-JAN-14 14:15:32') or a.jobrun_lstchgtm is null)
order by jobrun_id desc
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide