How to check the status of Asset job in database with SQL Query ?

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

How to check the status of an asset job executed by the agents using a SQL Query in the database mdb ?

Environment

Client Automation - All Versions.

Resolution

Following SQL Query could be used.

Example for job name = "Windows Security"

 

Replace "Windows Security" by the job name to check

SELECT M.joname,M.agent_name,s.status, s.stcount,
dateadd ( ss, stdate + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Last Executed'
FROM
(SELECT DISTINCT j.joname,a.agent_name,a.object_uuid,j.jobid, j.domainid
FROM ncjobcfg j, linkjob l, ca_agent a
WHERE j.joname ='Windows Security' and l.jobid=j.jobid and l.jdomid=j.domainid
and ( (a.object_uuid=l.object_uuid and l.object_type=1) OR
(l.object_type=0 and a.object_uuid in (SELECT m.member_uuid FROM ca_group_member m WHERE m.group_uuid=l.object_uuid)))
) AS M
LEFT JOIN statjob s ON (s.jobid=M.jobid and s.jdomid=M.domainid and s.object_uuid=M.object_uuid)
ORDER BY 1,2

 

Output example :

- First column is the name of job to check. 
- Second column is the computer name (or agent name) which has executed the job.

- Third column is the status of job execution

-1 = Waiting
0 = ERROR
1 = OK
NULL = Waiting

 

- Fourth column is the number of times the job was executed
- Fifth column the the last executed date