Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

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

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.

  • Cisco Workload Automation (formerly known as TES) Customer Advisory Community
Everyone's tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
New Member

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

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!

3 REPLIES
New Member

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

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?

New Member

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

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!

New Member

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

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

270
Views
5
Helpful
3
Replies