Guru: Analyzing User Session Statistics, Part 1

July 20, 2026 Mike Larsen

I wanted to report on user activity on our IBM i. Knowing that there is a wealth of SQL table functions available to me, I knew it wouldn’t be hard to find something that would help me. For this report, I wanted to see how many times a user logged into the system during a certain timeframe.

The table function I used is QSYS2.HISTORY_LOG_INFO. I’ll show the code I built, followed by a breakdown of what is does.

Here is the SQL statement to determine the number of times a user signed in:

SELECT COUNT(DISTINCT DATE(MESSAGE_TIMESTAMP)) AS DAYS_LOGGED_IN
FROM TABLE(QSYS2.HISTORY_LOG_INFO(
    START_TIME => CURRENT TIMESTAMP - 5 days, 
    END_TIME => CURRENT TIMESTAMP
)) AS H
WHERE MESSAGE_ID = 'CPF1124' 
  AND from_job_user = 'USER_ID';

Since one can log in and out multiple times in a day, I chose to get a distinct count of the times one logged into the system. To narrow the results, I used a date range for the past five days. You can change the date range to suit your needs. Note that in my where clause, I have two criteria. The first column I used in my SQL statement is MESSAGE_ID. I did this because I know that message id CPF1124 tells me when a job started. The second selection criteria I used is from_job_user. Here, I specified the user ID I would like to see.

When I run this statement, I get the distinct number of times a user logged in over the past five days:

It’s worth noting that I can also determine the number of times the user logged out of the system by making a small change to the SQL statement:

SELECT COUNT(DISTINCT DATE(MESSAGE_TIMESTAMP)) AS DAYS_LOGGED_IN
FROM TABLE(QSYS2.HISTORY_LOG_INFO(
    START_TIME => CURRENT TIMESTAMP - 5 days, 
    END_TIME => CURRENT TIMESTAMP
)) AS H
WHERE MESSAGE_ID = 'CPF1164' 
  AND from_job_user = 'USER_ID';

There is one small change to the code, and that is the MESSAGE_ID for which I’m filtering. To see when a job ended, I change this selection criteria to CPF1164. When I run this code, I get the result shown below:

Using these SQL statements provides useful insight into how many times people are signing into the system. In the second part of this tip, I’ll show how to get the days people signed into the system, and how long their session was active.

Mike Larsen is a director of information technology at Auburn Pharmaceutical and has been working with IBM i systems for over 20 years. He specializes in RPG, CL, and SQL and recently has been working with PHP and Python. Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services. Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search