This article makes me a bit nostalgic: In May 2011, it was only the fourth article in the AutomicBlog (called UC4-Blog at that time). And in spite of its age in 2015, it was still among the TOP 10 articles with the highest number of readers per month.

It had been one of my customers who drew my attention to this subject. He wanted to find JOBS which meet the following two conditions:

  1. They have not been activated for at least 60 days
  2. They are located below a specific file folder in the Automic directory structure

Especially the second part is a challenge and requires good knowledge of SQL and the AE database. Therefore, this article and its query is not necessarily designed for beginners. However, you can use it as a basis any time you want to conduct an analysis of something located below a specific folder level.

The Directory Structure of the Automation Engine

The directory structure is located in table OFS of the database. It contains the five fields OFS_IDNR, OFS_OH_IDNR_F, OFS_LEVEL, OFS_OH_IDNR_O and OFS_LINK.

For this article, OFS_OH_IDNR_F and OFS_OF_IDNR_O are relevant.

These two values describe a parent-child relationship (e.i., a file-object relationship). OFS_OH_IDNR_F is the ID of the file folder and OFS_OH_IDNR_O is the ID of the object within the file folder. Of course, the child object can again be a file folder.

In this example, 1000001 is the ID of the client, „AUTOMIC_SYSTEM“ is a file folder on the top level of the client:

Folder Structure of the AE

As you can see, JOBS and VARA are sub-directories of AUTOMIC_SYSTEM. Therefore, both have the OFS_OH_IDNR_F 1000100, which is equal to OFS_OH_IDNR_O of AUTOMIC_SYSTEM.

Find Objects Below a File Folder – With a Recursive SQL Query

We need a query to find all objects below a file folder in AE. This query has to reference to entries in the table OFS which are part of the same table – therefore, it is a recursive query. To do that, we use the WITH clause as provided in the ANSI Standard.

The following query finds the IDs of all AE objects located below the file folder „AUTOMIC_SYSTEM“:

with OBJECTS(OFS_OH_IDNR_O) as
(
  select OFS_OH_IDNR_O
  from OFS
  where OFS_OH_IDNR_F = 
       (
             select OH_IDNR from OH where OH_NAME like '%\AUTOMIC_SYSTEM' and
             OH_CLIENT = 1
   ) 

  union all
  
  select f.OFS_OH_IDNR_O
  from OFS as f, OBJECTS
  where OBJECTS.OFS_OH_IDNR_O = f.OFS_OH_IDNR_F
)
select OFS_OH_IDNR_O from OBJECTS order by OFS_OH_IDNR_O;

Identifying the Last Activation of an Object

The most complicated part is done: With the recursive query we have selected the objects within a file location in a directory structure.

Step 2 is next: Now we want to select only objects whose last run goes back more than 60 days.

To do that, we first identify the latest set of statistics (the last activation time) of an object:

select max(AH_TIMESTAMP1) from AH where AH_OH_IDNR = ;

Now we must test whether the latest set of statistics goes back more than 60 days. The functions for the date calculation vary between different database systems.

For T-SQL the code looks like this:

where (
  select max(AH_TIMESTAMP1) from AH where AH_OH_IDNR = 
) < DATEADD(dd,-60,GETUTCDATE())

In Oracle this works a bit differently:

where (
  select max(AH_TIMESTAMP1) from AH where AH_OH_IDNR = 
) < SYSTIMESTAMP AT TIME ZONE 'UTC' -60

The Full Query

We now have completed the two basic building blocks. Next we are constructing the full query. As a result, we will get all jobs that fulfill the above-mentioned conditions, listing the job names and the timestamps of the last activation:

  1. They have not been activated in at least 60 days.
  2. They are located below a specific file folder in the Automic directory structure.

This code works for T-SQL; for use in Oracle, you need to comment and uncomment the relevant lines:

with OBJECTS(OFS_OH_IDNR_O) as
(
       select OFS_OH_IDNR_O
       from OFS
        where OFS_OH_IDNR_F =
       -- get OH_IDNR of root folder for recursion
       -- subquery must return exactly one row
       (
             select OH_IDNR from OH where OH_NAME like '%\AUTOMIC_SYSTEM' and
             OH_CLIENT = 1
       ) union all
       select f.OFS_OH_IDNR_O
       from OFS f
       inner join OBJECTS on OBJECTS.OFS_OH_IDNR_O = f.OFS_OH_IDNR_F
)

-- PART II - the query itself
-- get fields from AH
select AH_1.AH_NAME "Name", max(AH_1.AH_TIMESTAMP1) "Last Activation"
from AH AH_1

-- JOIN between AH and the result of the recursive query
inner join OBJECTS on AH_1.AH_OH_IDNR = OBJECTS.OFS_OH_IDNR_O

-- filter for objects, where the latest activation was at least 60 days ago
where (
       select max(AH_2.AH_TIMESTAMP1)
       from AH as AH_2
       where AH_2.AH_OH_IDNR = AH_1.AH_OH_IDNR
) < DATEADD(dd,-60,GETUTCDATE())

-- With Oracle, you use the following line instead
--) < SYSTIMESTAMP AT TIME ZONE 'UTC' -60

-- filter for objects of type JOBS
and AH_OTYPE = 'JOBS'

-- the GROUP BY for the output of the last activation in the resultset
group by AH_1.AH_NAME

-- sort by activation time
order by max(AH_1.AH_TIMESTAMP1);

Have You Taken a Look at My Old Articles?

I’m trying to update and republish as many of the most popular articles as possible from my old blog. This will take time because there are more than 60 articles in the old blog. You can help me prioritize my work by briefly letting me know in the comments which of the old articles are of special interest to you. I will pull those to the head of the queue. If you do not yet have the old articles, just subscribe to my Newsletter via the form below this article and you will get an archive file with all articles.