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

Document ID : KB000090034
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Find all Reports and Variants from a special host and client.
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;