SQL Query to get details of All Collect Tasks

Products

• CA Client Automation
• CA Client Automation - Asset Management
• CA Client Automation - IT Client Manager

Issue/Introduction

How to get an overview of all Collect Tasks in database :

 

- Where is it applied

- Its scheduling

- The status and last execution date on the computers

Environment

Client Automation - All Version

Resolution

Following SQL Queries return all details about Collect Tasks in database :

 

use mdb
IF OBJECT_ID ('tempdb..#TMP1', 'U') IS NOT NULL DROP TABLE #TMP1

SELECT a.object_uuid, a.object_uuid 'group_uuid', m.moid, m.domainid
INTO #TMP1 
FROM ca_agent a
   INNER JOIN linkmod l ON a.object_uuid=l.object_uuid
   INNER JOIN ncmodcfg m ON l.moid=m.moid and l.mdomid=m.domainid 
   LEFT OUTER JOIN statmod s ON s.object_uuid=a.object_uuid and s.mdomid=m.domainid and s.moid=m.moid
WHERE l.mostatus=0 and l.object_type=1
UNION ALL
SELECT a2.object_uuid, a.object_uuid 'group_uuid', m.moid, m.domainid
FROM ca_agent a
   INNER JOIN linkmod l ON a.object_uuid=l.object_uuid
   INNER JOIN ncmodcfg m ON l.moid=m.moid and l.mdomid=m.domainid 
   INNER JOIN ca_group_member G ON a.object_uuid=g.group_uuid
   INNER JOIN ca_agent a2 ON a2.object_uuid=g.member_uuid
WHERE  l.mostatus=0 and l.object_type in (0,1)

SELECT a.agent_name 'Linked To', a2.agent_name 'Computer Name', a2.ip_address, ss.label 'Scala Name',
  dateadd ( ss, a2.last_run_date + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Last Run Date',
  m.moname 'Module Name',
  CASE 
    WHEN (s.status IS NULL or s.status=-1) THEN 'WAITING'
    WHEN s.status=0 THEN 'ERROR'
    WHEN s.status=1 THEN 'OK'
  END 'Job Status', s.stcount 'Nb Execution',
  dateadd ( ss, s.stdate + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Last Execution Date',
  s.sttext 'Status Text', s.stelapse 'Last Execution Duration', m.moid 'Job ID',
  CASE m.mofreq
    WHEN 0 THEN 'ALWAYS RUN JOB'
    WHEN 1 THEN 'RUN ONLY ONCE'
    ELSE 'RUN '+LTRIM(STR(m.mofreq))+' TIMES'
  END 'Job Frequency',
  CASE m.moonceat
    WHEN 1 THEN 'ONCE A DAY STARTING '+LTRIM(STR(m.mooncenr))+' HOURS'
    WHEN 2 THEN 'ONCE A WEEK STARTING '+
   CASE m.mooncenr
     WHEN 0 THEN 'SUNDAY' WHEN 1 THEN 'MONDAY' WHEN 2 THEN 'TUESDAY' WHEN 3 THEN 'WEDNESDAY' 
     WHEN 4 THEN 'THURSDAY' WHEN 5 THEN 'SATURDAY'
   END
    WHEN 3 THEN 'ONCE A MONTH STARTING '+LTRIM(STR(m.mooncenr))+' DAYS'
 ELSE ''
  END 'Run only once a...',
  
  IIF(m.mosched/1000000=1,'SUNDAY ','')+ IIF((m.mosched/100000)-(m.mosched/1000000)*10=1,'MONDAY ','')+ IIF((m.mosched/10000)-(m.mosched/100000)*10=1,'TUESDAY ','')+
  IIF((m.mosched/1000)-(m.mosched/10000)*10=1,'WEDNESDAY ','')+ IIF((m.mosched/100)-(m.mosched/1000)*10=1,'THURSDAY ','')+ IIF((m.mosched/10)-(m.mosched/100)*10=1,'FRIDAY ','')+
  IIF(m.mosched-(m.mosched/10)*10=1,'SATURDAY ','') 'Run only on these days',
  IIF(m.mosdate=0,NULL,convert(varchar,dateadd(ss, m.mosdate, convert(datetime,'19700101')),102)) 'Date Range Start',
  IIF(m.moedate=0,NULL,convert(varchar,dateadd(ss, m.moedate, convert(datetime,'19700101')),102)) 'Date Range End',
  IIF(m.mostime=0,NULL,convert(varchar,dateadd(ss, m.mostime, convert(datetime,'00:00:00')),108)) 'Time Range Start',
  IIF(m.moetime=0,NULL,convert(varchar,dateadd(ss, m.moetime, convert(datetime,'00:00:00')),108)) 'Time Range End',
  m.mocont 'Module Content'
FROM #TMP1 t
   INNER JOIN ca_agent a ON t.group_uuid=a.object_uuid
   INNER JOIN ncmodcfg m ON t.moid=m.moid and t.domainid=m.domainid
   INNER JOIN ca_agent a2 ON a2.object_uuid=t.object_uuid
   INNER JOIN ca_server ss ON a2.server_uuid=ss.server_uuid
   LEFT OUTER JOIN statmod s ON s.object_uuid=a2.object_uuid and s.mdomid=t.domainid and s.moid=t.moid
ORDER BY [Linked To], [Computer Name], m.moid

 

Example :