Bei diesem Artikel werde ich ein wenig nostalgisch: Im Mai 2011 war es erst der vierte Artikel im AutomicBlog (damals noch UC4-Blog). Und trotz seines Alters, war er auch 2015 noch unter den TOP 10 der Artikel mit den meisten Lesern pro Monat.

Ein Kunde hatte mich damals auf das Thema aufmerksam gemacht. Er wollte JOBS finden, die zwei Bedingungen erfüllen:

  1. Sie sind mindestens 60 Tage lang nicht mehr gelaufen.
  2. Sie befinden sich unterhalb eines bestimmten Orders in der Automic Ordnerstruktur

Vor allem der zweite Teil davon ist eine Herausforderung und erfordert gute Kenntnisse im Umgang mit SQL und der AE Datenbank. Deshalb ist dieser Artikel und die enthaltene Query auch nicht unbedingt für Einsteiger geeignet. Dafür können Sie sie fast immer als Grundlage benutzen, wenn eine Auswertung unter einem bestimmten Ordner stattfinden soll.

Die Ordnerstruktur der Automation Engine

Die Ordnerstruktur ist in der Tabelle OFS der Datenbank hinterlegt. Sie enthält die fünf Felder OFS_IDNR, OFS_OH_IDNR_F, OFS_LEVEL, OFS_OH_IDNR_O und OFS_LINK.

Für uns sind OFS_OH_IDNR_F und OFS_OF_IDNR_O relevant.

Diese zwei Werte beschreiben eine Parent-Child-Beziehung (also eine Ordner-Objekt-Beziehung ). Dabei ist OFS_OH_IDNR_F die ID des Ordners und OFS_OH_IDNR_O ist die ID des Objekts innerhalb des Ordners. Das Child-Objekt kann natürlich selbst wieder ein Ordner sein.

In diesem Beispiel ist 1000001 die ID des Mandanten, “AUTOMIC_SYSTEM” ist ein Ordner auf oberster Ebene im Mandanten:

Sie sehen, JOBS und VARA sind Unterverzeichnisse von AUTOMIC_SYSTEM. Deshalb haben beide die OFS_OH_IDNR_F 1000100, was der OFS_OH_IDNR_O vonn AUTOMIC_SYSTEM entspricht.

Objekte unterhalb eines Ordners finden – mit rekursiver SQL Query

Um alle Objekte unterhalb eines Ordners in AE zu ermitteln, benötigt man eine Query, die innerhalb der Tabelle OFS auf Einträge in derselben Tabelle referenziert – also eine rekursive Query. Dafür benutzen wir die WITH-Klausel, so wie es im ANSI-Standard vorgesehen ist.

Die folgende Query ermittelt die IDs aller AE-Objekte unterhalb des Ordners “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;

Identifizieren der letzten Ausführung eines Objekts

Der komplizierteste Teil ist geschafft: mit der rekursiven Abfrage haben wir die Objekte innerhalb eines Speicherorts in einer Ordnerstruktur ausgewählt.

Jetzt folgt Schritt 2: Wir wollen nur Objekte, deren letzte Ausführung mindestens 60 Tage her ist.

Dafür identifizieren wir zunächst den jüngsten Statistiksatz (es geht um den Aktivierungszeitpunkt) eines Objektes:

select max(AH_TIMESTAMP1) from AH where AH_OH_IDNR = ;

Jetzt müssen wir testen, ob der jüngste Statistiksatz mehr als 60 Tage alt ist. Die Funktionen zur Datumsberechnung unterscheiden sich in den verschiedenen Datenbanksystemen.

Für T-SQL sieht der Code dafür so aus:

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

In Oracle funktioniert es ein wenig anders:

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

Die vollständige Query

Wir haben jetzt die zwei Grundbausteine fertig und bauen daraus die Abfrage zusammen. Sie gibt den Jobnamen und den Zeitpunkt der letzten Aktivierung von allen Jobs aus, die die oben genannten Bedingungen erfüllen:

  1. Sie sind mindestens 60 Tage lang nicht mehr gelaufen.
  2. Sie befinden sich unterhalb eines bestimmten Orders in der Automic Ordnerstruktur

Der Code funktioniert für T-SQL, für die Nutzung mit Oracle müssen Sie die entsprechenden Zeilen ein- und auskommentieren.

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);

Haben Sie sich meine alten Artikel schon angesehen?

Ich versuche möglichst viele der beliebten Artikel aus meinem alten Blog hier neu zu veröffentlichen und zu aktualisieren. Das braucht aber Zeit, denn im alten Blog gab es mehr als 60 Artikel. Sie können mir bei der Priorisierung helfen, indem Sie mir in den Kommentaren kurz schreiben, welche der alten Artikel Sie besonders interessieren. Die werde ich mir dann besonders schnell wieder vornehmen. Falls Sie die alten Artikel bisher noch nicht haben, melden Sie sich einfach mit dem Formular unter dem Artikel für den Newsletter an und Sie bekommen die Artikel zugeschickt.