Read WP role from DB

[Michael A. Lowry]

Is there a way to read the role of a Work Process from the AE database?

Specifically, I'd like retrieve the following information:

• Is a WP a Dialog Work Process (DWP)?
• Is a WP assigned the O or R role?
• Is a JWP assigned the AUT, IDX, PER, or UTL role?

Thanks in advance.

[Michael A. Lowry]

Can anyone help with this?

Ping @Kaj Wierda & @Markus Embacher

[Michael A. Lowry]

This question was also asked back in 2022, but there was no satisfactory answer.

[Thierry Tarrason]

Hi Michael,

I was the one asking the question in 2022. At that time, the support confirmed that the info is not in the DB.

In the end, i'm parsing through the server logs to find the information.

Best regards,
Thierry

-------------------------------------------

[Michael A. Lowry]

Hi @Thierry Tarrason. That's what I'm doing too. It's slow and not 100% reliable, but it's better than nothing.

See Bash shell functions to aid AAKE troubleshooting for details.

[Michael A. Lowry]

It seems it should be possible to query this information using the Java API, using the ServerList class.

I was unable to find an equivalent endpoint in the AE REST API.

I’d still like to find a way to do this in pure SQL if possible. Thanks in advance for any ideas or suggestions.

[Christoph Rekers at Tricise]

Hi @Michael A. Lowry

You could check EMI:

Cheers

Christoph 

------------------------------
--------------------------------------------------------
Automic Consultant and Trainer since 2000
--------------------------------------------------------
now Tricise
------------------------------

[Michael A. Lowry]

I've never used that tool before. How does it work? To what host/port does it connect, and how does it authenticate? Is there a command-line version?

Keep in mind that we are running AAKE, so each server process runs in its own pod/container in Kubernetes.

[Michael A. Lowry]

I just read up a bit on External Monitoring Interface. I do not believe we use it, and I'm not sure that Broadcom supports it in AAKE systems.

Moreover I'm not sure it would be worth the trouble to install and maintain another component when the JCPs deliver the same information via the Java Application Interface.

Please correct me if I am mistaken in my understanding.

[Michael A. Lowry]

I've run into at least a few situations wherein I was unable to identify JWP roles based on log messages. In many cases, the JWP role can be identified by the most recent message U00045395 in its logs. E.g.,

U00045395 Assigned roles: [AUT]

JWPs sometimes write tens of message U00045395 in a few minutes, and sometimes they don't write a single message U00045395 in weeks.

I do not understand the reason for this behavior. One thing is clear though: if not a single U00045395 message is present in any extant log file, it is impossible to determine the JWP's role based on the logs.

[Bogdan Mustiata]

NOTE: The following works only on Broadcom-hosted SaaS instances and will be available for on-prem customers starting with the next major release.

Hi, please try:

SELECT mqsrv_name,

CASE mqsrv_type

WHEN 1 THEN 'CP'

WHEN 2 THEN 'WP'

WHEN 4 THEN 'PWP'

WHEN 16 THEN 'JWP'

WHEN 32 THEN 'REST'

WHEN 64 THEN 'JCP'

ELSE '?'

END AS mqsrv_type, -- The alias keeps the column name as mqsrv_type in the output

mqsrv_roles

FROM mqsrv m

This would yield, for example:

|mqsrv_name   |mqsrv_type|mqsrv_roles        |
|-------------|----------|-------------------|
|AUTOMIC#CP001|JCP       |                   |
|AUTOMIC#WP005|WP        |M                  |
|AUTOMIC#WP001|JWP       |AUT,IDX,PER,UTL,JWP|
|AUTOMIC#CP003|CP        |                   |
|AUTOMIC#CP004|CP        |                   |
|AUTOMIC#CP002|REST      |                   |
|AUTOMIC#WP004|WP        |S                  |
|AUTOMIC#WP003|WP        |R                  |
|AUTOMIC#WP002|PWP       |O                  |

Hope that suffices

-------------------------------------------

[Michael A. Lowry]

Thanks, @Bogdan Mustiata for the information. I'm glad to know that these pieces of information will soon be accessible from the DB.

Looking at the AE v24.5 DB Schema, I see that there are several new fields compared to the AE v24.4 DB Schema.

Below is my understanding of how these DB fields map to methods of the ServerListItem Java API class.

Description	Java API method (≥ v11.2)	DB field (≥ v24.5)	REST API (≥ v24.5)
% process utilization - last minute	getB01()	MQSRV_Busy01	GET /{client_id}/system/processes
% process utilization - last 10 minutes	getB10()	MQSRV_Busy10
% process utilization - last 60 minutes	getB60()	MQSRV_Busy60
Process ID	getProcessId()	MQSRV_ProcessId
Role(s)1	getRole()	MQSRV_Roles
	getRoles()   (≥ v21.0)
Available roles	NA	MQSRV_ProvRoles

Notes

1. Includes WP roles & JWP roles.

Please let me know if I am mistaken about something.

[Bogdan Mustiata]

The getRoles() is only a split of getRole() with , as separator - it uses the same field. Both WPs and JWPs have their roles written in the MQSRV_Roles column. The API only returns data from this column.

The other column, the MQSRV_ProvRoles , shows what the JWP can do as roles. I.e. if you have a dedicated JWP for a role, while it can do more roles, it will do only the dedicated role, and you'll see only one role in MQSRV_Roles.

-------------------------------------------

[Michael A. Lowry]

OK, thanks. I updated the table.

-------------------------------------------

[Michael A. Lowry]

Thanks @Bogdan Mustiata for the clarification.

My testing of ServerListItem.getRole() revealed that for JWPs with multiple assigned roles, the method returns a comma-delimited list, e.g., 'AUT,IDX'.

However, the documentations suggests that in situations wherein a normal WP has both the O and R roles, it returns the simple concatenation 'RO'.

Is the behavior of this method inconsistent between WPs and JWPs?

[Bogdan Mustiata]

deleted

[Bogdan Mustiata]

deleted

[Michael A. Lowry]

Until v24.7 the next major release comes out, here is an (Oracle) SQL query that will list AE server processes including JWP roles.

-- List active AE server processes, including JWP roles & MQ table block counts for CPs.
-- CTE to parse MQLS table entry with ID ASSGNRLS and retrieve assigned JWP roles.
WITH JWP_Roles AS (
SELECT
  REGEXP_SUBSTR(token, '[^=]+', 1, 1) AS JWP_Name,
  REGEXP_SUBSTR(token, '[^=]+', 1, 2) AS JWP_Roles
FROM (
  SELECT REGEXP_SUBSTR(AssgnRls, '[^;]+', 1, LEVEL) AS token
  FROM (select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(mqls_content, 4000, 1)) AssgnRls from mqls where mqls_id = 'ASSGNRLS')
  CONNECT BY REGEXP_SUBSTR(AssgnRls, '[^;]+', 1, LEVEL) IS NOT NULL
         AND PRIOR SYS_GUID() IS NOT NULL
         AND PRIOR AssgnRls = AssgnRls
))
-- Main query
SELECT SUBSTR(OH_NAME, INSTR(oh_name,'#') + 1) NAME, CASE MQSRV_TYPE
 WHEN 1  THEN 'CP   - Communications process'
 WHEN 2  THEN 'WP   - Work process'
 WHEN 4  THEN 'PWP  - Primary work process'
-- WHEN 8  THEN 'A    - Agent'
 WHEN 16 THEN 'JWP  - Java work process'
 WHEN 32 THEN 'REST - REST process'
 WHEN 64 THEN 'JCP  - Java communications process'
 ELSE TO_CHAR(MQSRV_TYPE)
END AS TYPE, JWP_Roles, HOST_HOSTNAME, HOST_TCPIPADDR  IP, HOST_TCPIPPORT PORT,
MQSRV_CONNCNT CONN, 
GET_BLOCK_COUNT('MQ1' || SUBSTR(OH_NAME, INSTR(OH_NAME,'#') + 1)) MQ1,
GET_BLOCK_COUNT('MQ2' || SUBSTR(OH_NAME, INSTR(OH_NAME,'#') + 1)) MQ2,
HOST_NETAREA AREA, HOST_VERSION, MQSRV_STARTTIME, MQSRV_LASTUPDATE, MQSRV_RESTENDPOINT
--, HOST_SMTCPIPPORT, HOST_SMPHRASE, HOST_SMDISPLAYNAME
 FROM OH
 JOIN HOST ON OH_IDNR = HOST_OH_IDNR
 LEFT OUTER JOIN MQSRV ON OH_NAME = MQSRV_NAME
 LEFT JOIN JWP_Roles ON OH_NAME = JWP_Roles.JWP_Name
WHERE OH_OTYPE = 'SERV'
AND MQSRV_TYPE  IS NOT NULL
ORDER BY OH_NAME

I still do not know how, using SQL alone, to distinguish regular WPs from DWPs, or to identify regular (non-Java) WP roles.

[Michael A. Lowry]

Here's an Oracle SQL query to list AE server processes in v26. (Also works in the SaaS-only v25 release).

-- List active AE server processes (Automic Automation v26+)
-- Uses the new MQSRV columns for roles, process ID, timing and utilization.
SELECT
    SUBSTR(OH_NAME, INSTR(OH_NAME, '#') + 1) AS NAME,

    CASE MQSRV_TYPE
        WHEN 1  THEN 'CP   - Communications process'
        WHEN 2  THEN 'WP   - Work process'
        WHEN 4  THEN 'PWP  - Primary work process'
        -- WHEN 8  THEN 'A    - Agent'
        WHEN 16 THEN 'JWP  - Java work process'
        WHEN 32 THEN 'REST - REST process'
        WHEN 64 THEN 'JCP  - Java communications process'
        ELSE TO_CHAR(MQSRV_TYPE)
    END AS TYPE,

    -- Process identity / roles
    MQSRV_PROCESSID AS PROCESS_ID,
    MQSRV_ROLES     AS ROLES,
    MQSRV_PROVROLES AS PROVIDED_ROLES,

    -- Host / network
    HOST_HOSTNAME,
    HOST_TCPIPADDR AS IP,
    HOST_TCPIPPORT AS PORT,
    MQSRV_CONNCNT  AS CONN,

    -- MQ usage
    GET_BLOCK_COUNT(
        'MQ1' || SUBSTR(OH_NAME, INSTR(OH_NAME, '#') + 1)
    ) AS MQ1,
    GET_BLOCK_COUNT(
        'MQ2' || SUBSTR(OH_NAME, INSTR(OH_NAME, '#') + 1)
    ) AS MQ2,

    -- Performance / utilization
    MQSRV_TIMEDIFF   AS TIME_DIFF,
    MQSRV_BUSY01     AS BUSY_01,
    MQSRV_BUSY10     AS BUSY_10,
    MQSRV_BUSY60     AS BUSY_60,
    MQSRV_CPUUTIL    AS CPU_UTIL,
    MQSRV_MEMORYUTIL AS MEMORY_UTIL,

    -- Environment / process information
    HOST_NETAREA AS AREA,
    HOST_VERSION,
    MQSRV_STARTTIME,
    MQSRV_LASTUPDATE,
    MQSRV_RESTENDPOINT

FROM OH
JOIN HOST
    ON OH_IDNR = HOST_OH_IDNR
LEFT JOIN MQSRV
    ON OH_NAME = MQSRV_NAME

WHERE OH_OTYPE = 'SERV'
  AND MQSRV_TYPE IS NOT NULL

ORDER BY OH_NAME;

As far as I know, there is still no way to identify Dialog Work Processes (DWPs) using only information from the database. For this, you need to use either the ServerList class of the Java API, or the system/processes endpoint of the REST API.

Correction: The system/processes endpoint of the REST API in v26 does not distinguish dialog work processes from normal work processes. Both WPs and DWPs are listed as type WP.