Obsolete Objects Report Query (including Folder location)

[Laura Albrecht]

I really hate to post another question on this topic - this seems to be a recurring theme for me.  Between all the various forums over the years (that are no longer accessible or the referenced links don't work) I've posted this question a variety of ways depending on what customer / client I'm working with, but I have never gotten a good query to facilitate generating a report to help identify obsolete objects.

I need a query that will:

- Identify objects that have NEVER executed.
- Identify objects that have not executed in x days.

This can be 1 or 2 queries if needed, but the kicker is that the folder location of said object has to be part of the result set.

I have a query that I got from an Automic consultant awhile back (below), but it wasn't thoroughly tested (by me) and while syntactically it is correct - no errors and it shows results - the results for the 2nd requirement above (not executed in x days) aren't accurate.

I am not a SQL guru and this is a bit beyond me.  If anyone could review / tweak or provide their own query for this - I'd greatly appreciate it.

For the rows that show (null) for Last Executed Date (objects never executed) this appears to be good results.

However, for the rows that show a Last Executed Date - the object identified DID run on that date (x days ago), but there are later runs after that, so it obviously shouldn't be on the list.  Do i need to add a MAX or something somewhere?

I'm on an Oracle 12c db and v11.2.7.  We're trying to do some cleanup prior to the upgrade to v12 next month.

And if there is another post that has this information - feel free to send the link.  I DID search, but did not find what I needed.

TIA.
Laura

**

with AllFolderPaths AS (

  select

    level

    ,ofs_oh_idnr_f as parent

    ,ofs_oh_idnr_o as child

    ,oh_name

    ,SYS_CONNECT_BY_PATH(substr(oh_name,instr(oh_name,'\')+1) , '/' ) as path

  from ofs, oh

  where oh.oh_idnr = ofs_oh_idnr_f

  start with ofs_oh_idnr_f =

    (

      select oh_idnr from oh

      where oh_otype = 'CLNT'

  -- enter client number

      and oh_client = 555

    )

  connect by prior ofs_oh_idnr_o = ofs_oh_idnr_f

)

select

  Path

  , o.OH_Client Client

  , o.OH_Name Object_Name

  , o.oh_moddate Last_Modified_Date

  , history.Last_Executed_Date

from

  AllFolderPaths

  , OH o

  , (select ah_oh_idnr, max(ah_timestamp4) last_executed_date from ah

    -- subtract the number of days from current date

     where ah_timestamp4 < SYSDATE - 400

     group by ah_oh_idnr) history

  , OFS 

where o.OH_Idnr=OFS_OH_Idnr_O

and OFS_OH_Idnr_F=parent

and o.OH_Idnr=history.AH_OH_Idnr(+)

-- enter client number

and o.oh_client = 555

and o.oh_otype in ('JOBS', 'JOBF', 'JOBP', 'EVNT', 'SCRI')

and o.oh_deleteflag = 0

GROUP BY path, o.oh_client, o.oh_otype, o.oh_name, o.oh_moddate, last_executed_date

order by path, o.oh_name;

------------------------------
Enterprise Scheduling Lead
Takeda
------------------------------

[Pete Wirfs]

I have used this to find jobs that have no run history in the AH table.  But if your datacenter doesn't keep much history in the AH table, then this might be an undesirable solution;

select oh_client, oh_name, oh_otype
from oh
where not exists (select 1
                  from ah 
				  where ah_oh_idnr = oh_idnr)
and oh_otype in ('JOBS', 'JOBP')
and oh_client = 100
and oh_deleteflag = 0;​

I then tweaked it a bit to select those jobs that have a latest activation time prior to 12/31/2019 like so.  You should be able to manipulate this into what you need;

select oh_client, oh_name, oh_otype
    , (select max(ah_timestamp1)
         from ah 
		where ah_oh_idnr = oh_idnr) as Latest_Activation
from oh
where (select max(ah_timestamp1)
         from ah 
		where ah_oh_idnr = oh_idnr) < '2019-12-31'
and oh_otype in ('JOBS', 'JOBP')
and oh_client = 100
and oh_deleteflag = 0;​

Regarding the desire to include folder name?   I have never agreed to provide this to people.  It is rarely worth the time and effort.

------------------------------
Pete
------------------------------

[Pete Wirfs]

I found this SQLServer query that returns the folder for a single object.  But I don't know how to make it work as a sub-select to the above two queries;

      with objectpath(OH_IDNR, OH_NAME, LVL) as (
      select OH_IDNR, CAST('\' + OH_Name AS VARCHAR(MAX)), 0
      from OH
      where OH_Client = 100
      and OH_Name = 'UW.DAILY'
      UNION ALL
      select OFS_OH_IDNR_F,
      SUBSTRING(OH.OH_Name, CHARINDEX('\', OH.OH_Name), 200) +
      objectpath.OH_Name,
      LVL + 1
      from objectpath
      inner join OFS on objectpath.OH_Idnr = OFS_OH_Idnr_O
      inner join OH on OFS_OH_IDNR_F = OH.OH_Idnr
      )
      select TOP 1 OH_Name
      from objectpath
      order by LVL desc;​

------------------------------
Pete
------------------------------

[Ralf Schmidt]

Hello Laura,

if necessary, a VARA.SQLI can be used here, whose found objects are evaluated using UC4 Script.

Here is an example of how we search for outdated JOBP on a MS SQL.

The folder location is not included here, but the objects can be moved with MOVE_OBJECT or exported via EXPORT as XML file.

Maybe with the script examples from Pete and an additional VARA.SQLI you can get it running as desired.

!VARA_SQLI.GET_NAMES
select OH_NAME, OH_ModDate from OH WITH (NOLOCK)
where
OH_Client = 100 and --Client
OH_DeleteFlag = 0 and
OH_OTYPE in ('JOBP') and --Object type
OH_NAME like '%_ABC%' and --Part of the name
OH_NAME NOT like 'XYZ%' and --Exceptions
order by 1

!SCRI.GET_STATISTIC_DETAIL_SQLI
!
:PRINT " Start listing JOBP last run -180 or never run and modification date -180 days."
:PRINT
:SET &Zaehler# = 0
:SET &ZaehlerGes# = 0
:SET &HND#=PREP_PROCESS_VAR(VARA_SQLI.GET_NAMES)
:PROCESS &HND#
: SET &name# = GET_PROCESS_LINE(&HND#, 2)
: SET &Ausnahme#="N"
: SET &ZaehlerGes# = ADD(&ZaehlerGes#, 1)
!
! Exceptions:
! Fourth digit must be an underscore and 1-4th digit not CBA_
: IF &Ausnahme# eq "N"
: IF SUBSTR(&name#, 4, 1) ne "_"
: SET &Ausnahme#="Y"
: ENDIF
: ENDIF
!
: IF &Ausnahme# eq "N"
: IF SUBSTR(&name#, 1, 4) = "CBA_"
: SET &Ausnahme#="Y"
: ENDIF
: ENDIF
!
! WorkFlows are output that were last run more than 180 days ago or never run before.
: IF &Ausnahme# ne "Y"
: SET &ACTIVATION# = GET_STATISTIC_DETAIL(, ACTIVATION_TIME, "&name#")
: IF &ACTIVATION# ne ""
: SET &ACT_Datum# = SUBSTR(&ACTIVATION#, 1, 10)
: SET &DIFF# = DIFF_DATE("DD-MM-YYYY:&$PHYS_DATE_DD_MM_YYYY_d#", "YYYY-MM-DD:&ACT_Datum#")
: SET &DIFF# = FORMAT("&DIFF#", "00")
: IF &DIFF# > 180
: SET &Zaehler# = ADD(&Zaehler#, 1)
: SET &Zaehler# = FORMAT("&Zaehler#", "00000")
: PRINT &Zaehler# &ACTIVATION# &DIFF# days - &name#
: ENDIF
: ELSE
: SET &MODIFIED# = GET_PROCESS_LINE(&HND#, 3)
: SET &ACT_Datum# = SUBSTR(&MODIFIED#, 1, 10)
: SET &DIFF# = DIFF_DATE("DD-MM-YYYY:&$PHYS_DATE_DD_MM_YYYY_d#", "YYYY-MM-DD:&ACT_Datum#")
: SET &DIFF# = FORMAT("&DIFF#", "00000")
: IF &DIFF# > 180
: SET &Zaehler# = ADD(&Zaehler#, 1)
: SET &Zaehler# = FORMAT("&Zaehler#", "00000")
: PRINT "&Zaehler# &ACT_Datum# none &DIFF# - &name#"
: ENDIF
: ENDIF
: ENDIF
!
:ENDPROCESS
:CLOSE_PROCESS &HND#
!
:SET &ZaehlerGes# = FORMAT("&ZaehlerGes#", "00000")
:PRINT
:PRINT " &Zaehler# from &ZaehlerGes#"
:PRINT " Processing completed."

Ralf

[Alain Moisy]

Hi Laura,


Your question is effectively a recurring one from different people. But you must include a few additional filters if you want to be more accurate in your selection :


1 - Object "never" run since creation  =>  add adelay like "never run since creation x days/months ago" because you may have created an object but will activate its schedule only in a few days or at end of the month i.e.

2 - Object not run since XXX days  =>  what about the monthly, quaterly, yearly jobs ? They may be outside the XXX days since last run.

3 - Depending on your settings, the average duration is calculated immediately or only at a regular interval. So if you look in the OH table for the last "usage" it may not be updated properly and it may not be longer available in AH table because of a short time retention / small number of versions or ILM ....


This is usually points where people start having nightmares to find a solution to this type of requests. I have a small script that uses SQL variables to retreive the path=folder tree for any object if you need it. So you can generate a list of objects and their folder location quite easily for eventually moving them to an "Obsolete to remove" folder =-) .

Regards.

Alain