How to collect system usage data of the AE

If you want to take a look at the workload (system usage) data of the Automation Engine, you will have a problem: The system overview displays only the last 24 hours. And older data would often be very useful for analyses and statistics.

Workload data in the user interface (only last 24h).

That’s why AE users always ask me: Where in the database can I find the historic workload (system usage) values?

And I have to give the disappointing answer: Nowhere. This data is not available in the database.

Luckily, there are functions in script language with which you can find the data. I want to show you a method to collect the data elegantly.

The relevant script functions

The Automation Engine offers four functions for querying the workload (system usage). The links point to the public documentation of AE Version 11.2.

SYS_BUSY_01() – returns the average workload (system usage) of an Automation Engine process during the last minute.

SYS_BUSY_10() – returns the average workload (system usage) of an Automation Engine process during the last 10 minutes.

SYS_BUSY_60() – returns the average workload (system usage) of an Automation Engine process during the last hour.

SYS_INFO( […] ) – reads the system information.

For our example, we will use the first three functions and the last function with the arguments:

SYS_INFO(Message queue, COUNT)

We are interested in the following three message queues:

• MQPWP (message queue of the primary work process)
• MQWP (message queue of the work processes)
• MQDWP (message queue of the dialog processes)

You can find detailed information regarding the functions under the documentation links.

How to use the Archive Values

Sure, we could now create a VARA or similar data container and save the data there at regular intervals. However, that would be cumbersome and inefficient. Therefore, I am reverting to a little trick: I use the Archive Values.

AV (short for Archive Values) is the name of a table in the AE database. It exists since AE Version 9, and the values of each historic run of your objects from PromptSet variables and Object variables are stored there.

That is pretty nifty: We can simply write the workload (system usage) values at regular intervals as object variables and then read them in the AV table.

In other words, we are creating an SCRI object which runs regularly and saves the value as object variables.

SCRI-Object to store the data

With the above function you are creating a simple SCRI object which you run regularly:

:PSET &LOAD_01# = SYS_BUSY_01()
:PSET &LOAD_10# = SYS_BUSY_10()
:PSET &LOAD_60# = SYS_BUSY_60()
:PSET &COUNT_PWP# = SYS_INFO(MQPWP,COUNT)
:PSET &COUNT_WP# = SYS_INFO(MQWP,COUNT)
:PSET &COUNT_DWP# = SYS_INFO(MQDWP,COUNT)

The object saves the workload (system usage) data of the last 1, 10 and 60 minutes and additionally the number of reports in MQPWP, MQWP and MQDWP.

Run this SCRI object periodically, for instance, every 10 minutes. The data is saved automatically in the AV table, and you can access it via SQL.

Accessing the AV Table Data via SQL

The AV table contains the variable name and value of all object variables for each runID. In the AH table (Archive Header) you will find the objectname, runID, and the timestamp (and much more useful information).

In the following SQL script we use the SELECT statement and select with “INNER JOIN” all rows of Table AH, for which the runID is also in the AV table. Then we search for the values timestamp, variablename and value.

Attention: The timestamps are in UTC!

Naturally, only the values from the SCRI object created above and for the desired client are of interest to us. So, remember to adjust AH_CLIENT and AH_NAME accordingly in the script. We will sort the result according to the timestamp.

select AH_TIMESTAMP1, AV_VName, AV_Value
from AH
inner join AV on AH_IDNR = AV_AH_Idnr
--!Modify Client and SCRI-Name according to your system!
where AH_CLIENT = 1
and AH_NAME = 'SCRI_WORKLOAD'
order by AH_TIMESTAMP1;

The results of the scripts after two runs of the SCRI object could look something like this:

Visualization of the Data in Excel

select AH_TIMESTAMP1,
MAX(CASE AV_VName WHEN '&LOAD_01#' THEN AV_Value END) as 'LOAD_01',
MAX(CASE AV_VName WHEN '&LOAD_10#' THEN AV_Value END) as 'LOAD_10',
MAX(CASE AV_VName WHEN '&LOAD_60#' THEN AV_Value END) as 'LOAD_60',
MAX(CASE AV_VName WHEN '&COUNT_DWP#' THEN AV_Value END) as 'COUNT_DWP',
MAX(CASE AV_VName WHEN '&COUNT_PWP#' THEN AV_Value END) as 'COUNT_PWP',
MAX(CASE AV_VName WHEN '&COUNT_WP#' THEN AV_Value END) as 'COUNT_WP'
from AH
inner join AV on AH_IDNR = AV_AH_Idnr
--!Modify Client and Name according to your system!
where AH_CLIENT = 1
and AH_NAME = 'SCRI_WORKLOAD'
group by AH_TIMESTAMP1
order by AH_TIMESTAMP1;

In Excel we can now turn this table into a graphic immediately. Simply highlight the table in Excel and select the desired type of graph in the “Charts” area (menu point “Insert”. I have chosen a line graph. The result looks as follows:

Implementation for your System

I hope that you can use my tips and implement them in your system. Unfortunately, you can no longer retrieve historical data with this method. But at least from now on you will be able to easily save and access workload (system usage) information.

If you have been following the old AutomicBlog, you will have noticed this: This article was published there in similar form. Additional old articles will appear here in updated form. However, if you urgently need an old article, take a look at how you can get access to the old posts.