(IBM i fans only) How to Sniff User Access

If you need to monitor user access on your IBM i system, you can use SQL to query the history log.

Note: At the end of this post, you'll find a request directly addressed to you, the readers.

Here’s a simple example of how you can do this:

SELECT MESSAGE_ID,
       MESSAGE_TIMESTAMP,
       FROM_USER,
       MESSAGE_TEXT
    FROM TABLE (
            QSYS2.HISTORY_LOG_INFO()
        )
    WHERE message_id IN ('CPF1164', 'CPF1124')
          AND FROM_USER IN ('QSECOFR', 'QSYS', 'QSYSOPR')
    ORDER BY MESSAGE_TIMESTAMP

In this query:

- QSYS2.HISTORY_LOG_INFO(): This function retrieves information from the history log.

-  message_id IN ('CPF1164', 'CPF1124') : Filters the results to include only specific message IDs, which indicate sign-on and sign-off events.

- FROM_USER in ('QSECOFR', 'QSYS', 'QSYSOPR'): Filters the results to include only messages from specific users.

Update 28/07/2024: A Smart Enhancement

Thanks to my reader Greg, we can make an intelligent enhancement to our query. By refining the list of users to those with specific administrative permissions, we ensure more accurate monitoring.

Here’s the improved query:

SELECT MESSAGE_ID,
       MESSAGE_TIMESTAMP,
       FROM_USER,
       MESSAGE_TEXT
    FROM TABLE (
            QSYS2.HISTORY_LOG_INFO()
        )

    WHERE message_id IN ('CPF1164', 'CPF1124')
          AND FROM_USER IN (SELECT user_name
                  FROM QSYS2.USER_INFO_BASIC
                  WHERE POSSTR(special_authorities, '*ALLOBJ') > 0
                        OR POSSTR(special_authorities, '*SECADM') > 0
                        OR POSSTR(special_authorities, '*IOSYSCFG') >0)
    ORDER BY MESSAGE_TIMESTAMP

This adjustment filters the users to only those with the administrative permissions *ALLOBJ, *SECADM, and *IOSYSCFG.
Thank you Greg.

Update 30/07/2024: Another Smart Enhancement (Improved Performance with JOIN)

A reader suggested an enhancement to the original query for better performance. Instead of using the "IN" operator, we can join the tables. This approach has been tested and proven to return results faster. Below is the updated SQL query:

SELECT history.MESSAGE_ID,
       history.MESSAGE_TIMESTAMP,
       history.FROM_USER,
       history.MESSAGE_TEXT

    FROM TABLE (
             QSYS2.HISTORY_LOG_INFO()
         ) history

         JOIN QSYS2.USER_INFO_BASIC usrInf
             ON history.FROM_USER = usrInf.USER_NAME
    WHERE history.MESSAGE_ID IN ('CPF1164', 'CPF1124')
          AND (POSSTR(usrInf.SPECIAL_AUTHORITIES, '*ALLOBJ') > 0
              OR POSSTR(usrInf.SPECIAL_AUTHORITIES, '*SECADM') > 0
              OR POSSTR(usrInf.SPECIAL_AUTHORITIES, '*IOSYSCFG') > 0)
    ORDER BY history.MESSAGE_TIMESTAMP;

This query helps you track the login and logout activities of critical system users.

Try running this on your system and see the results.



Starting from the suggested SQL, you can derive countless pieces of information thanks to the QSYS2.HISTORY_LOG_INFO() function - Utilize it as you see fit.

Monitoring Failed Login Attempts

Another important aspect to monitor is failed login attempts. Use the following SQL statements to create a view and retrieve the users who have unsuccessfully attempted to log in.

Create a View for User Information:

CREATE OR REPLACE VIEW yourLib/USR_LIST
AS SELECT *                          
     FROM QSYS2.USER_INFO;

Retrieve Users with Failed Login Attempts

SELECT *
FROM yourLib/USR_LIST
WHERE SIGN_ON_ATTEMPTS_NOT_VALID > 0
ORDER BY SIGN_ON_ATTEMPTS_NOT_VALID DESC;

This will list users who have attempted to log in unsuccessfully, ordered by the number of invalid sign-on attempts.

Feel free to comment below if you have any questions or suggestions.

I appreciate all the comments made on this blog.

If any of you readers are aware of a way through the OS400 operating system to 'sniff' or monitor access by users with administrative permissions such as QSECOFR, QSYS, QSYSOPR, QPGMR and could share the solution here, it would be greatly appreciated.