The new year is more than a month old which means it’s about time for a blog article.

For that, I once more tackled a technical topic: today we look at statistics for periods of time.

While the statistics for periods of time are pretty straightforward (open selective statistics, enter criteria, done), it gets a little trickier for points in time. For that we need SQL.

Does the topic seem familiar to you? That’s quiet possible. Many, many years ago, I’d already written about this in my old blog.

As the topic is still up to date for many Automic Users, I decided to take it up again.

Statistics and Activities

Let’s get started with some useful general information.

As you know, all statistics are recorded in table AH.

As long as a task still is in the activity window, you will find current information about the task in table EH.

Particularly interesting is the status of the task, which is in EH_Status. In AH_Status, active tasks usually have the value 0, but you should not rely on that. There are some exceptions, for example, post-processing activities (this is the EH_Status 1574) will determine whether the return code is 0 or not (AH_Status ENDED_OK 1900) or not (AH_Status ENDED_NOT_OK 1800).

In the two databases, each job has several timestamps.

In table EH there are

  • EH_ActivationTime: The activation time (= AH_TimeStamp1)
  • EH_Starttime: The start time of the task (= AH_Timestamp2)
  • EH_Endtime: The end time of the task, when everything is finished (even post processing)

In the table AH there’s even more information about time:

  • AH_Timestamp1: The activation time (= EH_ActivationTime)
  • AH_Timestamp2: The start time of the task (= EH_Startime)
  • AH_Timestamp3: The end time of the task BEFORE post-processing. If there is no post-processing the value is NULL.
  • AH_Timestamp4: The end time of the task AFTER post-processing (or the end of the task if there is no post-processing). It always has the same value as EH_Endtime.

Much Ado about Time Zones

As always with the Automation Engine, all timestamps are stored in UTC. In order to work with it, we must first transform them to our own time zone.

In my article about Design Patterns, I emphasized the importance of best practices. We can also apply one here:

Whenever possible, perform time zone transformation on the client side.

But sometimes, you have to or want to convert with SQL. For these cases, I’ve collected some useful SQL snippets that you can easily apply.

Current Timestamp (“now”) in UTC

Conversion of Local Time to UTC
Replace [local timestamp to convert] with the timestamp you want to convert.

Conversion of UTC timestamp to Local Time Zone
Replace [utc timestamp to convert] with the timestamp you want to convert.

What was Running at a Given Point in Time?

On July 27, 2018, at around 9:30 pm in Vienna, a total lunar eclipse happened.

Did you follow the exciting event as enthusiastically as me?

But do you also know what was happening on your Automation Engine at that point?

You can find out through SQL.

Who was Logged in at a Point in Time?

User Sessions, too, create database entries.

This means that you only have to slightly change above query to find all user sessions active at a given point in time.

In below snippet, I made a tiny addition: I used a Join with the table USR to also get the users first name, last name and email. You may, of course, also access all other user information.

What was Running Parallel to a Specific Task?

Let’s rephrase this requirement.

We are looking for all tasks, that fulfill at least one of the following conditions:

  • The task started after the reference task started and before it ended.
  • The task ended after the reference task started and before it ended.

The following diagram shows some examples.

The red arrow is the reference task. The green arrows are tasks, the script should filter. The violet arrows are irrelevant tasks, that don’t run parallel to the reference task.

The query for this is a simple modification of the first example in this article.

Only difference: In the CTE, we do not determine the UTC time of a fixed time, but the relevant time stamp of the reference task. To identify the red task, the query uses the RunID, here 08154711.

Do You Want More SQL?

Lately, technical articles with a fous on SQL and the AE database were rare in my blog. Do you want more of them? It would be great if you could tell me in the comments.

And if you want to learn a lot more about the topic: There are still some free spots for the AE Database Knowledge Workshop (GERMAN version) in Nuremberg from 25. to 27. of March.

Get concentrated knowledge from 5 years of AutomicBlog

Download an archive with all old articles:

  • Features, SQL-Tricks, and handy Scripts.
  • 63 extensive articles (+ comments).
  • Used by Automic experts around the world.

Subscribe to the newsletter and immediately get all articles!

[caldera_form id="CF572741b60f87f"]