It is currently 30 May 2017 3:25


Post new topic  Reply to topic  [ 5 posts ] 
Author Message
PostPosted: 13 Jan 2013 11:30 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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


Top
   
 Post subject:
PostPosted: 13 Feb 2013 10:41 
Offline
Nouveau
Nouveau
User avatar

Joined: 11 Aug 2009 8:00
Posts: 166
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


Top
   
 Post subject:
PostPosted: 15 Feb 2013 2:05 
Offline
Nouveau
Nouveau
User avatar

Joined: 26 Apr 2005 8:00
Posts: 686
Location: PARIS
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


Top
   
 Post subject:
PostPosted: 15 Feb 2013 9:02 
Offline
Nouveau
Nouveau
User avatar

Joined: 11 Aug 2009 8:00
Posts: 166
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.


Top
   
 Post subject:
PostPosted: 16 Feb 2013 5:13 
Offline
Nouveau
Nouveau
User avatar

Joined: 08 Sep 2010 8:00
Posts: 73
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


Top
   
Display posts from previous:  Sort by  
Post new topic  Reply to topic  [ 5 posts ] 

All times are UTC+10:00


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB® Forum Software © phpBB Limited
The site created by Franck YOT. Images, logo, content and trademarks about Control-M products are property of BMC Software.
All the comments are property of their posters. Images, logo, content and design are © copyright by Scheduler-Usage.com. All Rights Reserved