Monitoring QSYSOPR Messages: SQL to Retrieve Messages and Replies

I take inspiration from a response by Michael Mayer on the Midrange.com mailing lists to someone who asked how to monitor the messages in QSYSOPR and their corresponding replies.

With this simple SQL query, it is possible to check the messages from a specific day or a defined period, obtaining a list of the messages and their corresponding replies, along with the timestamp of both;

SELECT A.MESSAGE_TIMESTAMP AS "Date/Time of Error Msg",
       A.MESSAGE_ID AS "Msg ID",
       A.FROM_JOB AS "Job Generated Error Msg",
       A.MESSAGE_TEXT AS "Msg Text",
       B.MESSAGE_TIMESTAMP AS "Reply Date/Time",
       SUBSTR(B.MESSAGE_TEXT, 1, 10) AS "Reply",
       A.FROM_USER AS "Reply by"
    FROM TABLE (QSYS2.MESSAGE_QUEUE_INFO(QUEUE_LIBRARY => 'QSYS',
                QUEUE_NAME => 'QSYSOPR',
                SEVERITY_FILTER => 99)) A,
         LATERAL (SELECT MESSAGE_TIMESTAMP,
                    MESSAGE_TEXT, FROM_USER
                 FROM TABLE (QSYS2.MESSAGE_QUEUE_INFO(QUEUE_LIBRARY => 'QSYS',
                             QUEUE_NAME => 'QSYSOPR',
                             SEVERITY_FILTER => 99))

---  Single Date             
---                 WHERE CHAR(A.MESSAGE_TIMESTAMP) LIKE '%2024-01-15%'
---                   AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B

---  Range of Dates               
                WHERE CHAR(A.MESSAGE_TIMESTAMP) >= '2024-09-01' AND
                  CHAR(A.MESSAGE_TIMESTAMP)     <= '2024-09-13'
                 AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B

     ORDER BY "Date/Time of Error Msg" ASC;

We can use this example to query other conditions and messages, not just QSYSOPR!

HTH

Roberto