Find all Reports and Variants from a special host and client.

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Find all Reports and Variants from a special host and client.

Environment

Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

Symptoms

Find all Reports and Variants from a special host and client.

With the following SQL statement you are able to find all executed R3jobs from a specified host and client.

This statement will view the start and stop time, run number, job name,client, SAP report name and SAP variant.

---

Resolution

Please adapt the parameter AH_Client and AH_HostDst to your client and host.

MSSQL:

select ah.AH_TimeStamp1 as Startzeit, AH.AH_TimeStamp4 as Endzeit,  AH.AH_idnr as Runnumber, AH.AH_Name as Jobname,AH.AH_Client,t1.Report, t1.Variante from 

 ah,

 (select RT_AH_Idnr, 

 substring(

  rt_content,

  (charindex('VARIANT=',rt_content,0) + 9),

  (charindex('"' ,rt_content,charindex('VARIANT=',rt_content,0)+9)-(charindex('VARIANT=',rt_content,0) + 9)  )   

  ) as Variante,

  substring(

  rt_content,

  (charindex('REPORT=',rt_content,0) + 8),

  (charindex('"' ,rt_content,charindex('REPORT=',rt_content,0)+8)-(charindex('REPORT=',rt_content,0) + 8)  )   

  ) as Report

 from 

 (select RT_Content,RT_AH_Idnr from RT where rt_ah_idnr in (select ah_idnr from AH where AH_Client = 1 and AH_OType = 'JOBS' and AH_HostDst ='SAP_RTH_ECC' ) and RT_Type='PLOG') temp

 where temp.RT_Content like '%R3_%VARIANT="%' and temp.RT_Content like '%R3%REPORT="%') t1

 where t1.RT_AH_Idnr=AH_Idnr order by ah.ah_client,AH.AH_Name  

ORACLE:

select ah.AH_TimeStamp1 as Startzeit, AH.AH_TimeStamp4 as Endzeit, AH.AH_idnr as Runnumber, AH.AH_Name as Jobname,AH.AH_Client,t1.Report, t1.Variante from ah, 

(select RT_AH_Idnr, substr( rt_content, (instr(rt_content,'VARIANT=') + 9), (instr(rt_content,'"',instr(rt_content,'VARIANT=')+9)-(instr(rt_content,'VARIANT=') + 9) ) ) Variante,

substr( rt_content, (instr(rt_content,'REPORT=') + 8), (instr(rt_content,'"',instr(rt_content,'REPORT=')+8)-(instr(rt_content,'REPORT=') + 8) ) ) Report

from (select RT_Content,RT_AH_Idnr from RT where rt_ah_idnr in (select ah_idnr from AH where AH_Client = 1 and AH_OType = 'JOBS' and AH_HostDst ='SAP_RTH_ECC' ) and RT_Type='PLOG') temp 

where temp.RT_Content like '%R3_%VARIANT="%' and temp.RT_Content like '%R3%REPORT="%') t1 

where t1.RT_AH_Idnr=AH_Idnr order by ah.ah_client,AH.AH_Name;