Automic Workload Automation

Hi, 

how can i get a list of the active workflows from sysdate-1 (23.00 o'clock) until today (06.00 o'clock)?
I need the following informations: 
Startdate and time
Workflow name
title 
runtime

Do you have an idea?


Thanks in advance.
Original Message:
Sent: 01-08-2018 04:39 AM
From: Claus Jambrich
Subject: To get the Job stats based on OH, AH and JPP Tables

SudharsonNarayanamoorthy603801, maybe that can help:

SELECT ah2.ah_name         AS "Workflow"      , ah1.ah_name      AS "Task"      , ah1.ah_runtime   AS "Runtime in seconds"      , ah1.AH_STATUS    AS "Status"      , MSGTX.MSGTX_TEXT AS "Status-text"      , TO_CHAR(ah1.ah_timestamp1, 'DD-MON-YYYY HH24:MI:SS') AS "Activation time"      , TO_CHAR(ah1.ah_timestamp2, 'DD-MON-YYYY HH24:MI:SS') AS "Start time"      , TO_CHAR(ah1.ah_timestamp4, 'DD-MON-YYYY HH24:MI:SS') AS "End time" FROM uc4.ah ah1      , uc4.ah ah2      , uc4.msgtx WHERE ah1.ah_client = '22'      AND ah1.ah_otype = 'JOBS'      AND ah2.ah_otype = 'JOBP'      AND ah1.AH_ParentAct = ah2.ah_idnr      AND ah1.AH_STATUS = MSGTX.MSGTX_MSG_IDNR         AND MSGTX.MSGTX_MSGL_SHORT = 'E'         AND ah1.ah_timestamp2 BETWEEN to_date(to_char(sysdate-1,'DD-MON-YYYY')           || ' 19:00:00','DD-MON-YYYY HH24:MI:SS')           AND to_date(to_char(sysdate, 'DD-MON-YYYY')           ||' 08:00:00','DD-MON-YYYY HH24:MI:SS') ORDER BY 1, 6 ;

As SamahSohrab607228 said, OH is not necessary. I make a join on MSGTX as you get only status-numbers in AH. You need MSGTX to translate them to status text. If the number is ok for you, you can leave that part away.

I use 
SELECT ah_alias as Job
,convert ( date, ah_timestamp1) Act_Date
,convert ( time, ah_timestamp1) Act_Time
,convert ( date, ah_timestamp2) Start_Date
,convert ( time, ah_timestamp2) Start_Time
,convert ( date, ah_timestamp4) End_Date
,convert ( time, ah_timestamp4) End_Time
,DATEDIFF(second, ah_timestamp2, ah_timestamp4) as Sec_Dur
,ah_title as Title
,ah_idnr as Runid
,ah_otype as Type
,ah_status as Status
,ah_RetCode as RC
,m.msgtx_text as Status_text
,ah_hostdst as Host
FROM ah a, msgtx m
WHERE ah_client = ?
and (ah_hostdst in (?) or ? IS NULL)
and (ah_status in (?) or ? IS NULL)
and (ah_otype IN (?) or ? IS NULL)
and m.msgtx_msgl_short = ?
and CASE
WHEN 'ACTIVATION' = ? THEN ah_timestamp1
WHEN 'START' = ? THEN ah_timestamp2
ELSE ah_timestamp4
END between ? and ?
and m.msgtx_msg_idnr = a.ah_status
and (ah_otype = 'JOBS')
and (convert( time, ah_timestamp3) between '00:00:00.0000000' and '07:00:00.0000000')
and ah_alias <> 'JOBP.DSMIGN.WORKFLOW'
and ah_alias <> 'JOBS.UNIX.DSMIGN'
and ah_alias <> 'JOBS.GENERIC.CHECK.FILE'
and ah_alias <> 'SCRI.GET_STEPS'
and ah_alias not like 'CALL.ALERT%'
order by ah_alias, ah_otype, ah_status, ah_timestamp2
;
Original Message:
Sent: 01-08-2018 12:07 PM
From: Sudharson Narayanamoorthy
Subject: To get the Job stats based on OH, AH and JPP Tables

Hi ClausJambrich602320,
Thanks much for the feedback. Query you provided was much helpful.
I will use it as a template for future development.