This is a Working Script to Create a Nice Jobs Report

Some examples running with Control-M and Control-EM database.
Post Reply
User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

This is a Working Script to Create a Nice Jobs Report

Post by DocGoo » 13 Jan 2013 2:30

Here is the sql I used to create a really helpful report of all production jobs. The output is raw, and then I import to Excel to pretty it up.
We use SQL Server 2005 and CM 6.4
In Excel I do color banding and formating.

Have fun!!! -- Goo


select distinct
SUBSTRING(c.SCHED_TABLE,3,5) AS 'GROUP'
,UPPER(SUBSTRING(a.JOB_NAME,3,10)) AS 'JOB'
,UPPER(a.MEMNAME) AS 'SCRIPT'
,ISNULL(REPLACE(a.W_DAY_STR,',',' '),'') AS 'WK DAY'
,ISNULL(REPLACE(REPLACE(a.DAYS_AND_OR,'O','OR'),'A','AND'),'') AS 'A/O'
,ISNULL(REPLACE(a.DAY_STR,',',' '),'') AS 'MONTH DAY'
,CASE
WHEN CAST(a.MONTH_1 AS INT) +
CAST(A.MONTH_2 AS INT) +
CAST(A.MONTH_3 AS INT) +
CAST(A.MONTH_4 AS INT) +
CAST(A.MONTH_5 AS INT) +
CAST(A.MONTH_6 AS INT) +
CAST(A.MONTH_7 AS INT) +
CAST(A.MONTH_8 AS INT) +
CAST(A.MONTH_9 AS INT) +
CAST(A.MONTH_10 AS INT) +
CAST(A.MONTH_11 AS INT) +
CAST(A.MONTH_12 AS INT) = 12
THEN
'ALL'
ELSE
case when A.MONTH_1 = 1 then 'JAN ' else '' end +
case when A.MONTH_2 = 1 then 'FEB ' else '' end +
case when A.MONTH_3 = 1 then 'MAR ' else '' end +
case when A.MONTH_4 = 1 then 'APR ' else '' end +
case when A.MONTH_5 = 1 then 'MAY ' else '' end +
case when A.MONTH_6 = 1 then 'JUN ' else '' end +
case when A.MONTH_7 = 1 then 'JUL ' else '' end +
case when A.MONTH_8 = 1 then 'AUG ' else '' end +
case when A.MONTH_9 = 1 then 'SEP ' else '' end +
case when A.MONTH_10 = 1 then 'OCT ' else '' end +
case when A.MONTH_11 = 1 then 'NOV ' else '' end +
case when A.MONTH_12 = 1 then 'DEC ' else '' end
END 'MONTH'
,ISNULL(a.DAYS_CAL + a.WEEKS_CAL,'') AS 'CALENDER'
,CASE ':'
WHEN
ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':'
+ SUBSTRING(a.FROM_TIME,3,2),'')
THEN
''
ELSE
ISNULL(SUBSTRING(a.FROM_TIME,1,2) + ':'
+ SUBSTRING(a.FROM_TIME,3,2),'')
END 'START'
,ISNULL(ROUND(CAST(x.AVG_RUN_TIME AS FLOAT)/60,2),'') 'AVG MIN'
,ISNULL(REPLACE(a.DESCRIPTION,',',' '),'') AS ' JOB DESCRIPTION'
,ISNULL(SUBSTRING(e.CONDITION,3,25),'') AS 'IN CONDITION'
,REPLACE(ISNULL((SELECT MAX(d.DESCRIPTION)
FROM
XXX_CTM_EM64.dbo.DEF_VER_JOB d
where ISNULL(SUBSTRING(d.JOB_NAME, 3, LEN(d.JOB_NAME) ),'')
= ISNULL ( SUBSTRING(e.CONDITION, 3,
NULLIF( CHARINDEX( '-', e.CONDITION ) -3,-3) ), '' )
),''),',','')AS 'PRED_DESC'
FROM
XXX_CTM_EM64.dbo.DEF_VER_JOB a
JOIN XXX_CTM_EM64.dbo.DEF_VER_TABLES c
ON ( a.TABLE_ID = c.TABLE_ID )
LEFT OUTER JOIN XXX_CTM_EM64.dbo.AVG_RUN_INFO x
ON ( a.JOB_NAME = x.JOB_MEM_NAME )
LEFT OUTER JOIN XXX_CTM_EM64.dbo.DEF_VER_LNKI_P e
ON ( a.TABLE_ID = e.TABLE_ID AND a.JOB_ID = e.JOB_ID )
WHERE
c.DATA_CENTER LIKE '%PROD%'
AND c.DATA_CENTER NOT LIKE '%CANCEL%'
AND UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
AND a.IS_CURRENT_VERSION = 'Y'
ORDER BY
1,2

User avatar
futre25
Nouveau
Nouveau
Posts: 166
Joined: 11 Aug 2009 12:00

Post by futre25 » 13 Feb 2013 1:41

Hi,

I'm trying to run in oracle, but I can not run.

Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error en la línea: 32, columna: 51


Can you help?

Attach the query in oracle:

select distinct SUBSTR(c.SCHED_TABLE,3,5) AS "GROUP"
,UPPER(SUBSTR(a.JOB_NAME,3,10)) AS "JOB"
,UPPER(a.MEMNAME) AS "SCRIPT"
,NVL(REPLACE(a.W_DAY_STR,',',' '),'') AS "WK DAY"
,NVL(REPLACE(REPLACE(a.DAYS_AND_OR,'O','OR'),'A','AND'),' ') AS "A/O"
,NVL(REPLACE(a.DAY_STR,',',' '),' ') AS "MONTH DAY",
CASE
WHEN CAST(A.MONTH_1 AS INT) +
CAST(A.MONTH_2 AS INT) +
CAST(A.MONTH_3 AS INT) +
CAST(A.MONTH_4 AS INT) +
CAST(A.MONTH_5 AS INT) +
CAST(A.MONTH_6 AS INT) +
CAST(A.MONTH_7 AS INT) +
CAST(A.MONTH_8 AS INT) +
CAST(A.MONTH_9 AS INT) +
CAST(A.MONTH_10 AS INT) +
CAST(A.MONTH_11 AS INT) +
CAST(A.MONTH_12 AS INT) = 12
THEN 'ALL'
ELSE
case when A.MONTH_1 = 1 then 'JAN ' else ' ' end +
case when A.MONTH_2 = 1 then 'FEB ' else ' ' end +
case when A.MONTH_3 = 1 then 'MAR ' else ' ' end +
case when A.MONTH_4 = 1 then 'APR ' else ' ' end +
case when A.MONTH_5 = 1 then 'MAY ' else ' ' end +
case when A.MONTH_6 = 1 then 'JUN ' else ' ' end +
case when A.MONTH_7 = 1 then 'JUL ' else ' ' end +
case when A.MONTH_8 = 1 then 'AUG ' else ' ' end +
case when A.MONTH_9 = 1 then 'SEP ' else ' ' end +
case when A.MONTH_10 = 1 then 'OCT ' else ' ' end +
case when A.MONTH_11 = 1 then 'NOV ' else ' ' end +
case when A.MONTH_12 = 1 then 'DEC ' else ' ' end
END "MONTH"
,NVL(a.DAYS_CAL + a.WEEKS_CAL,' ') AS "CALENDER",
CASE ':'
WHEN NVL(SUBSTR(a.FROM_TIME,1,2) || ':' || SUBSTR(a.FROM_TIME,3,2),' ') THEN ' '
ELSE
NVL(SUBSTR(a.FROM_TIME,1,2) || ':' || SUBSTR(a.FROM_TIME,3,2),' ')
END "START"
,NVL(ROUND(CAST(x.AVG_RUN_TIME AS FLOAT)/60,2),' ') AS "AVG MIN"
,NVL(REPLACE(a.DESCRIPTION,',',' '),' ') AS "JOB DESCRIPTION"
,NVL(SUBSTR(e.CONDITION,3,25),' ') AS "IN CONDITION"
,REPLACE(NVL((SELECT MAX(d.DESCRIPTION)
FROM DEF_VER_JOB d
where NVL(SUBSTR(d.JOB_NAME, 3, LENGTH(d.JOB_NAME) ),' ') = NVL( SUBSTR(e.CONDITION, 3,
NULLIF(INSTR( '-', e.CONDITION ) -3,-3) ), ' ' ) ),' '),',',' ') AS "PRED_DESC"
FROM DEF_VER_JOB a
JOIN DEF_VER_TABLES c
ON ( a.TABLE_ID = c.TABLE_ID )
LEFT OUTER JOIN AVG_RUN_INFO x
ON ( a.JOB_NAME = x.JOB_MEM_NAME )
LEFT OUTER JOIN DEF_VER_LNKI_P e
ON ( a.TABLE_ID = e.TABLE_ID AND a.JOB_ID = e.JOB_ID )
WHERE c.DATA_CENTER LIKE '%PROD%'
AND a.IS_CURRENT_VERSION = 'Y'
ORDER BY 1,2

User avatar
fyot
Nouveau
Nouveau
Posts: 736
Joined: 26 Apr 2005 12:00
Location: PARIS
Contact:

Post by fyot » 14 Feb 2013 5:05

Hi

Nice script, yes.
But very difficult to maintain it, and to adapt it simpply what ever the database you want.

There is a simple and cheap tool to realise same report, with easy parameters.

APS Manager Console.
Go to www.apsware.com

User avatar
futre25
Nouveau
Nouveau
Posts: 166
Joined: 11 Aug 2009 12:00

Post by futre25 » 15 Feb 2013 12:02

Thanks very much for your reply.

I gor to run in oracle. Attached the select. Hope this helps:

select distinct SUBSTR(c.SCHED_TABLE,1,30) TABLA
,UPPER(SUBSTR(a.JOB_NAME,1,20)) JOBNAME
,UPPER(a.MEMNAME) SCRIPTS
,NVL(REPLACE(a.W_DAY_STR,',',' '),' ') WK_DAY
,NVL(REPLACE(REPLACE(a.DAYS_AND_OR,'O','OR'),'A','AND'),' ') A_O
,NVL(REPLACE(a.DAY_STR,',',' '),' ') MONTH_DAY
,decode( TO_NUMBER(A.MONTH_1) +
TO_NUMBER(A.MONTH_2) +
TO_NUMBER(A.MONTH_3) +
TO_NUMBER(A.MONTH_4) +
TO_NUMBER(A.MONTH_5) +
TO_NUMBER(A.MONTH_6) +
TO_NUMBER(A.MONTH_7) +
TO_NUMBER(A.MONTH_8) +
TO_NUMBER(A.MONTH_9) +
TO_NUMBER(A.MONTH_10) +
TO_NUMBER(A.MONTH_11) +
TO_NUMBER(A.MONTH_12) , 12 ,'ALL'
,decode ( A.MONTH_1 , '1', 'JAN ',decode ( A.MONTH_2 , '1', 'FEB ',decode ( A.MONTH_3 , '1', 'MAR ',decode ( A.MONTH_4 , '1', 'APR ',decode ( A.MONTH_5 , '1', 'MAY '
,decode ( A.MONTH_6 , '1', 'JUN ',decode ( A.MONTH_7 , '1', 'JUL ',decode ( A.MONTH_8 , '1', 'AUG ',decode ( A.MONTH_9 , '1', 'SEP '
,decode ( A.MONTH_10 , '1', 'OCT ',decode ( A.MONTH_11 , '1', 'NOV ',decode ( A.MONTH_12 , '1', 'DEC ',' '))))))))))))) MESES
,NVL(a.DAYS_CAL,' ') CALENDAR_DAY
,NVL(a.WEEKS_CAL,' ') CALENDAR_WEEKS
,CASE
WHEN NVL(a.FROM_TIME,' ') = ' ' THEN ' '
ELSE
SUBSTR(a.FROM_TIME,1,2) || ':' || SUBSTR(a.FROM_TIME,3,2)
END "START"
,decode (x.AVG_RUN_TIME,null,' ',to_char((TRUNC(x.AVG_RUN_TIME/60,2)),'99990D00')) RUN_TIME_MINUTOS
,NVL(REPLACE(a.DESCRIPTION,',',' '),' ') JOB_DESCRIPTION
,NVL(SUBSTR(e.CONDITION,1,25),' ') IN_CONDITION
,REPLACE(NVL((SELECT MAX(d.DESCRIPTION)
FROM DEF_VER_JOB d
where NVL(SUBSTR(d.JOB_NAME, 3, LENGTH(d.JOB_NAME) ),' ') = NVL( SUBSTR(e.CONDITION, 3,
nvl(INSTR( '-', e.CONDITION ) -3,-3) ), ' ' ) ),' '),',',' ') PRED_DESC
FROM DEF_VER_JOB a
, DEF_VER_TABLES c
, AVG_RUN_INFO x
, DEF_VER_LNKI_P e
WHERE a.TABLE_ID = c.TABLE_ID
and c.DATA_CENTER LIKE 'PRODUCCION%'
and a.JOB_NAME = x.JOB_MEM_NAME(+)
and a.TABLE_ID = e.TABLE_ID(+)
and a.JOB_ID = e.JOB_ID(+)
--and c.DATA_CENTER NOT LIKE '%CANCEL%'
--and UPPER(a.JOB_NAME) NOT LIKE '%CANCEL%'
AND a.IS_CURRENT_VERSION = 'Y'
ORDER BY 1,2

Regards.

User avatar
DocGoo
Nouveau
Nouveau
Posts: 73
Joined: 08 Sep 2010 12:00

Post by DocGoo » 15 Feb 2013 8:13

Hello All,

A bit hard to maintain but the usefulness to us is tremendous! I realize there are third party tools to do this, but we are a goverment agency and money is extremely scarce these days.

I use a PowerShell Gui interface to run these queries. Actually in my program the sql is broken apart and depending on Gui selections the appropiate sql is sent to the db. It can then be viewed on the screen or sent to Excel, again by operator choice. Once in Excel the Gui fires off a macro which formats the entire results and lightly color bands each other row by job name.

It sounds like a lot, but it really is not.

Glad you enjoyed it and I am expanding my Gui interface for many CM reports without having to spend money. Because it is simple and informative the work required is much less than the cost of third party tools.

--Goo

Post Reply