Guru: Analyzing User Session Statistics, Part 2

October 26, 2026 Mike Larsen

In part one of this tip, I showed how to determine the number of times a user signed in and signed out of IBM i for a given period. In this tip, I will show how to get the days a user signed in, and how long their session was active.

I use the same table function, QSYS2.HISTORY_LOG_INFO, as before. The code (Figure 1 below) is similar to the code used in the first part of this series, but there are some differences I will point out.

Note that I purposely left the user ID out of the result set as I didn’t want to show that, but you’ll want to add that when you use this code.

SELECT from_job_number, MESSAGE_ID, MESSAGE_TIMESTAMP
FROM TABLE(QSYS2.HISTORY_LOG_INFO(
    START_TIME => CURRENT TIMESTAMP - 5 days,  
    END_TIME => CURRENT TIMESTAMP
)) AS H

WHERE MESSAGE_ID in ('CPF1124', 'CPF1164')  
  AND from_job_user = 'USER_ID'
order by from_job_number, MESSAGE_ID, MESSAGE_TIMESTAMP;

^{Figure 1. SQL statement to determine the number of times a user signed in}

In this statement, I select three columns that include job number, message id, and message timestamp. I’m looking at the past five days, and this time, I’ve selected both the start time of a job and the end time of a job. When I run this statement, I get the results shown in Figure 2.

Figure 2. Sign in and sign off statistics for the past five days

When I look at the results, I can clearly see, by job number, the timestamps of when one signed in for the day, and when they logged out. The information is interesting, but wouldn’t it be better to flatten this out so I can calculate how long the user was signed in for the day? Let’s do that next.

To achieve the objective, I use two common table expressions (CTE), to get sign on and sign off times separately, then join them together to perform the duration calculation (Figure 3).

With CTE_SignOn as
(
  SELECT from_job_user, from_job_number, MESSAGE_ID, MESSAGE_TIMESTAMP, 'Signed on at' as signOnStatus
  FROM TABLE(QSYS2.HISTORY_LOG_INFO(
       START_TIME => CURRENT TIMESTAMP - 5 days,  
       END_TIME => CURRENT TIMESTAMP
  )) AS H
  WHERE MESSAGE_ID in('CPF1124')  
  AND from_job_user = 'USER_ID'
  order by from_job_user, from_job_number, MESSAGE_ID, MESSAGE_TIMESTAMP
),

CTE_SignOff as
(
  SELECT from_job_user, from_job_number, MESSAGE_ID, MESSAGE_TIMESTAMP, 'Signed off at' as signOnStatus
  FROM TABLE(QSYS2.HISTORY_LOG_INFO(
       START_TIME => CURRENT TIMESTAMP - 5 days,  
       END_TIME => CURRENT TIMESTAMP
  )) AS H
  WHERE MESSAGE_ID in('CPF1164')  
  AND from_job_user = 'USER_ID'
  order by from_job_user, from_job_number, MESSAGE_ID, MESSAGE_TIMESTAMP
)  

select a.from_job_user, a.from_job_number, a.MESSAGE_ID, a.signOnStatus, a.MESSAGE_TIMESTAMP, 
       b.signOnStatus, b.MESSAGE_TIMESTAMP, 
       TIMESTAMPDIFF(4, char(b.MESSAGE_TIMESTAMP - a.MESSAGE_TIMESTAMP)) AS duration_minutes,
       TIMESTAMPDIFF(8, char(b.MESSAGE_TIMESTAMP - a.MESSAGE_TIMESTAMP)) AS duration_hours 
from CTE_SignOn a inner join
     CTE_SignOff b on a.from_job_user = b.from_job_user and a.from_job_number = b.from_job_number;

^{Figure 3. CTE to calculate duration of sign in and sign off}

It seems like there is a lot going on here, so let’s break down each section of code.

In the first CTE, CTE_SignOn, I am selecting sign on information for the user for the past five days.

In the second CTE, CTE_SignOff, I am selecting sign off information for the user for the past five days.

Next, I join the two CTEs together by user ID and job number, and calculate the duration of time their session was active. Before I show the results of this query, I want to describe how I calculated the duration. I used the TIMESTAMPDIFF scalar function to perform the calculation. I used the function in two ways. First, I used a 4 in the first argument of the function. That tells the function that I want the result returned in minutes. Second, I used an 8 in the first argument. That tells the function to return the duration in hours. Why did I do that? I did that because if someone was logged in for less than an hour, it would return a result of zero hours. I didn’t like that, so I decided to return the number of minutes as well. Figure 4 shows the result of the query.

Figure 4. Duration of a user’s session

This query gives a detailed look into when a user was signed onto the system, and how long they stayed signed on. How to best use this information is up to you!

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