How to identify all jobs that were running at a particular point in time?

[Steve Atwood]

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.

[Steve Atwood]

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?

[pavanjosyula · Accepted solution]

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!

[Steve Atwood]

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