How do I know which objects are producing the largest reports?

[Dominic Irudayasamy]

How do I know which objects are producing the largest reports? 

I referred the following link
How do I know which objects are producing the largest reports?

Broadcom		remove preview
How do I know which objects are producing the largest reports? PLEASE NOTE: Automic Support does not recommend running SQL statements against a production database and cannot provide Support for any problems that arise due to running SQL statements. We would recommend running SQL statements agains a real-time clone or copy of a production database if necessary in order to lower the impact to the Automic system. View this on Broadcom >

it produces RT_AH_IDNR list, but I could not find the objects name from the RT_AH_IDNR, it did not show anything

ex:
select * from rt where RT_AH_IDNR=9799901

------------------------------
Dominic I
------------------------------

[Darren Sniezak]

select top 50 oh_name, count(RT_AH_IDNR) from rt, ah, oh where rt_ah_idnr = ah_idnr and ah_oh_idnr = oh_idnr group by OH_NAME order by COUNT(RT_AH_IDNR) desc;

That should also return an OH_NAME that you could be searching for

If you had a list of RT_AH_IDNRs, then you can link that to the AH table and pull the additional data, ex:

Select AH_NAME, AH_CLIENT from AH where AH_IDNR in (your query from above)

[Dominic Irudayasamy]

Thank you so much for your help, I can get the details by "select top 50 oh_name, count(RT_AH_IDNR) from rt, ah, oh where rt_ah_idnr = ah_idnr and ah_oh_idnr = oh_idnr group by OH_NAME order by COUNT(RT_AH_IDNR) desc;".

However, I could not find any details from AH table. The query "Select AH_NAME, AH_CLIENT from AH where AH_IDNR=10053766;" does not return any value. Is the query correct?

------------------------------
Dominic I
------------------------------

[Darren Sniezak]

Ahh I think I see the confusion.

The result it gives isn't actually an AH_IDNR, it is a count of the number of RT records that match the OH_Name returned. So not a single run, but the total number of instances of that object in the reports - the OH_NAME itself should be all you need to inspect an individual object causing that much total activity

If you wanted to dig to a more exact run, you'd need to run a more expensive query, but this

select RT_AH_IDNR from RT group by RT_AH_IDNR order by COUNT(RT_AH_IDNR) desc;

Would give a list of just runid's which you could limit/look at.

[Dominic Irudayasamy]

Wow, great... I got the desired result. Thank you so much for your help.

------------------------------
Dominic I
------------------------------

[Michael Kutswa]

Considering the bigest reports of jobs are the truncated ones you can use these SQL to find the truncated logs:

select OH_CLIENT as Mandant, OH_NAME as Jobname, Parent.AH_IDNR as RunNr, ah.AH_HOSTSRC as Agent, ah.ah_filenamesrc as Reportdatei from AH, AH Parent, OH where
ah.ah_parentact = Parent.ah_idnr
and Parent.ah_oh_idnr = OH_IDNR
and ah.AH_OTYPE='REPORT'
and ah.AH_TimeStamp4 >= to_timestamp('&DATE# 00:00:00', 'YYYYMMDD HH24:MI:SS')
and ah.AH_TimeStamp4 <= to_timestamp('&DATE# 23:59:59', 'YYYYMMDD HH24:MI:SS')
and ah.AH_MSGNR=29007
ORDER by Mandant, Jobname, RunNr