The following are various queries I use on Control-M for Distributed Systems to gather information from both the Server and the EM database. These queries should work just fine on Control-M 6.4.01 and above (and may work with earlier versions as well). Although these were developed on SQL Server, they should work with any database platform with minor modifications. I update this list often as I create new queries that I find useful. If you have any questions about a query please don’t hesitate to get in contact with me.
Calculate how much space the database is using, and how much is free (click to download script)
USE em700;
SELECTdb_tag.FILEID,
CONVERT(decimal(12,2),ROUND(db_tag.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(db_tag.name,’SpaceUsed’)/128.000,2)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((db_tag.size-fileproperty(db_tag.name,’SpaceUsed’))/128.000,2)) as [FREESPACEINMB],
db_tag.name as [DATABASENAME],
db_tag.FILENAME as [FILENAME]FROM
dbo.sysfiles db_tag
Find all jobs in AJF that have ended NOTOK
This query goes against your Control-M/Server database.
SELECT CAST(JOBNAME AS Varchar(30)) AS JOB_NAME, ODATE, CAST(OSCOMPSTAT AS varchar(4)) AS COMPSTAT, CAST(NODEID AS Varchar(15)) AS NODE_ID, CAST(SCHEDTAB AS VARCHAR(15)) AS SCHED_TABLE FROM CMR_AJF WHERE (TASKTYPE <> ‘G’) AND (OSCOMPSTAT <> ’0′) AND (STATUS = ‘N’) AND (HOLDFLAG <> ‘D’)
Find all jobs for a specific date that show as Dissappeared Status
This query goes against your Control-M/Server database.
select JOBNAME,NODEID,LOGTIME from CMR_IOALOG where MESSAGE LIKE ‘%%DISAPPEARED AT%%’AND LOGDATE=’20101224′
Find all jobs for a specific date that show as Unknown Status
This query goes against your Control-M/Server database.
select JOBNAME,NODEID,LOGTIME from CMR_IOALOG where MESSAGE LIKE ‘%%Unknown%%’AND ODATE=’20101224′
Find all jobs that were changed for a specific calendar date
This query goes against your Control-M/Server database.
SELECT JOBNAME,SCHEDTAB,AUTHOR
FROM CMS_JOBDEF
WHERE (SUBSTRING(CHANGEDATETIME, 1, 8 ) = ‘%%REPORTDATE’)
Find new jobs that were put in on a specific calendar date.
This query goes against your Control-M/Server database.
SELECT JOBNAME,SCHEDTAB,AUTHOR
FROM CMS_JOBDEF
WHERE (SUBSTRING(CREATIONDATETIME, 1, 8 ) = ‘%%REPORTDATE’)
Find all jobs in AJF that have not run (at least once, for cyclic) yet.
This query goes against your Control-M/Server database.
SELECT JOBNAME AS ‘Job Name’, SCHEDTAB AS ‘Scheduling Table’, MAXWAIT AS ‘Max Wait (in Days)’
FROM CMR_AJF
WHERE (STATE NOT IN (’8′, ’2′, ’4′, ’5′, ’6′, ’8 ‘, ‘ A ‘, ‘ J ‘)) AND (STATUS <> ‘ Y ‘) AND (ODATE = ’20101230′)